I am trying to create Hive table over Hbase table. Details shown below:
HBase table has data like:
Connected to: Phoenix (version 4.7)
Driver: PhoenixEmbeddedDriver (version 4.7)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)... 1341/1341 (100%) Done
Done
sqlline version 1.1.8
0: jdbc:phoenix:maxiqtesting1.lti.com:2181:/h>
select * from HBASE_TEST_6JULY_1792409;
+---------+---------+---------+
| FIELD0 | FIELD1 | FIELD2 |
+---------+---------+---------+
| 1 | qq | 23 |
| 2 | ee | 12 |
| 3 | dd | 123|
+---------+---------+---------+
3 rows selected (0.139 seconds)
0: jdbc:phoenix:maxiqtesting1.lti.com:2181:/h>
create Hive Table command:
CREATE EXTERNAL TABLE HBASE_TEST_6JULY(FIELD0 int,FIELD1 string, FIELD2 int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,0:FIELD1,0:FIELD2","hbase.table.default.storage.type" = "binary", 'serialization.format'='1')
TBLPROPERTIES("hbase.table.name" = "HBASE_TEST_6JULY_1792409");
SELECT command on Hive table gives result as:
hive> select * from HBASE_TEST_6JULY;
OK
-2147483647 qq -2147483625
-2147483646 ee -2147483636
-2147483645 dd -2147483525
Time taken: 0.963 seconds, Fetched: 3 row(s)
The integer columns values are not displayed correctly. If I give all columns as String in hive then I am getting null for corresponding integer columns in HBase.
On the other hand when I appended records in same hive table (over Hbase) by following query
Insert in to HBASE_TEST_6JULY Select * From abc query, I can see the new records correctly while the old records stored in Hbase through Phoneix API have same issue.
Is this a version related issue?
Hadoop Version: 2.7.3.2.5.3.0-37(HBase 1.1.2)
Phoneix: 4.7(HBase 1.1.3)
Can anyone please help me and provide solution to read numeric/non-string columns with correct value by exposing Hive tables on HBase ?
