Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive with HBase integration: how to load dates

Hive with HBase integration: how to load dates

New Contributor

Dear all,

I created the following table in HBase (using Phoenix):

CREATE TABLE test_table (username VARCHAR PRIMARY KEY, firstlogin DATE, lastlogin DATE);
UPSERT INTO test_table VALUES ('abc', '2016-03-05', '2016-11-20'); 

When I query tha data, I get the expected result:

 
 USERNAME  |        FIRSTLOGIN        |        LASTLOGIN         |
+-----------+--------------------------+--------------------------+
| abc       | 2016-03-05 00:00:00.000  | 2016-11-20 00:00:00.000 

After this, I want to access this data from Hive, creating the following table:

CREATE EXTERNAL TABLE hive_test(
USERNAME STRING,
FIRSTLOGIN INT,
LASTLOGIN INT
) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,0:FIRSTLOGIN#b,0:LASTLOGIN#b")
TBLPROPERTIES ("hbase.table.name" = "TEST_TABLE"); 

However, when querying the data, I do not get the expected results for FIRSTLOGIN and LASTLOGIN columns, that are supposed to be unix timestamps:

 hive_test.username  | hive_test.firstlogin  | hive_test.lastlogin  |
+---------------------+-----------------------+----------------------+--+
| abc                 | -2147483309           | -2147483304    

What am I doing wrong?

Thanks in advance!

3 REPLIES 3

Re: Hive with HBase integration: how to load dates

Super Collaborator

@Gabriela Martinez , you are assigning INT data type to your date attributes. Can you try with DATE datatype. Try this:

CREATE EXTERNAL TABLE hive_test(
USERNAME STRING,
FIRSTLOGIN DATE,
LASTLOGIN DATE)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping"=":key,0:FIRSTLOGIN#b,0:LASTLOGIN#b")
TBLPROPERTIES ("hbase.table.name"="TEST_TABLE");

Re: Hive with HBase integration: how to load dates

New Contributor

Dear @Mushtaq Rizvi,

Following your suggestion, I got this error message when trying to query the table fields with SELECT * FROM hive_test;:

Error: java.io.IOException: org.apache.hadoop.hive.ql.metadata.HiveException: Error evaluating username (state=,code=0)
0: jdbc:hive2://localhost:10000>
Highlighted

Re: Hive with HBase integration: how to load dates

Guru

You should use the Hive -> Phoenix connector instead of the Hive -> HBase connector since the table is a Phoenix table.

More information here: https://phoenix.apache.org/hive_storage_handler.html