How to upsert row_timestamp Phoenix Hbase

Question

I'm using HBase 1.1, Phoenix 4.7 Following this link about Row Timestamp (mapping HBase’s native row timestamp to a Phoenix column): https://phoenix.apache.org/rowtimestamp.html

I created a sample table

CREATE TABLE DESTINATION_METRICS_TABLE
(CREATED_DATE DATE NOT NULL,
METRIC_ID CHAR(15) NOT NULL,
METRIC_VALUE LONG
CONSTRAINT PK PRIMARY KEY(CREATED_DATE ROW_TIMESTAMP, METRIC_ID))
SALT_BUCKETS = 8;

and upsert a row

UPSERT INTO DESTINATION_METRICS_TABLE (METRIC_ID, METRIC_VALUE) VALUES (?, ?) - this sets the value of CREATED_DATE to the server side time

But it seems Phoenix doesn't automatically handle the row_timestamp CREATED_DATE.

My Java code does not work:

String sql = "UPSERT INTO DESTINATION_METRICS_TABLE (METRIC_ID, METRIC_VALUE) VALUES (?, ?)";

ps = connection.prepareStatement(sql);
connection.setAutoCommit(false);

ps.setString(1, "asdasd");
ps.setString(2, "123123");

ps.executeUpdate()

I get the error:

org.apache.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type mismatch. VARCHAR cannot be coerced to BIGINT

Does anyone give me an example about syntax UPSERT of row_stamp Apache Phoenix ?

Thank you in advance.

vmaroli vmaroli · Accepted Answer · 2018-05-25T11:29:38

Tried the same create/upsert statements in my environment. First of all create statement is giving problem with following error for METRIC_VALUE column.

Error: ERROR 201 (22000): Illegal data. Unsupported sql type: LONG (state=22000,code=201)

I changed the type to double and CREATE TABLE and the UPSERT also worked. Problem is in following line which is setting a string for a column of numeric type.

ps.setString(2, "123123");

How to upsert row_timestamp Phoenix Hbase

1 Answers