Search code examples
apachehbaseupsertapache-phoenix

How to upsert a Timestamp using Apache Phoenix in plain SQL

We are using Apache Phoenix to interact with our HBase installation. We chose Phoenix because it gives us the capability to add data types to HBase columns and because it gives you the possibility to use plain SQL to interact with the underlying database.

Using Phoenix you can declare the type of a table column as TIMESTAMP (refer to this link). For example, let declare the following table:

Create table T1
(
    T1_KEY VARCHAR(10) NOT NULL,
    TMSTP TIMESTAMP,
    CONSTRAINT PK_T1 PRIMARY KEY (T1_KEY)
);

The question is: which is the correct syntax to UPSERT a row in such a table using plain old SQL? In which format have you to pass the timestamp value to TMSTP column?

Solution

  • Well, to UPSERT a value to a column of type TIMESTAMP in plain old SQL, you have to resume the XML standard date format. Following this format a timestamp value looks like as the the following:

    2002-05-30T09:30:10.5

    Then, the resulting UPSERT operation to table T1 will be

    UPSERT INTO T1 (T1_KEY, TMSTP) VALUES ('0123456789', '2002-05-30T09:30:10.5');

    Notice that you have to pass the timestamp value as a properly formatted VARCHAR.