I have Hbase table with integer field.
When I map Phoenix table on to this Hbase table, integer field show values like : -213456236
hbase> create 'emp','f1' hbase> put 'emp',1,'f1:eid',1001
Corresponding Phoenix table:
Create table "emp" (pk integer primary key, "f1"."eid" integer);
When I try to select from emp table, I get large number in eid field:
select "eid" from "emp"; => 299345678
This is happening for float values also. Whereas varchar values get populated correctly.
What wrong am I doing? Am I wrongly mapping the Hbase table to phoenix? I have followed steps from FAQ page.
I have read below question, but unable to get any clue.
Please help @Josh Elser
You should use Apache Phoenix's APIs for writing data to Phoenix tables. There is often special encoding of values in these tables. Integers are one example. They are stored as a sequence of 4 bytes (with a sign bit) to ensure that they sort properly. The "string" form of the integer is being parsed as a number.
Thank for the reply Josh.
My use-case is: Data is going to get populated into Hbase table.
I'm going to create a Phoenix table on top of Hbase table so that I can fire SQL on it.
So I'm going to just read data from Phoenix table.
@Saurabh Rathi, the workaround , you can do is to declare your field "eid" as VARCHAR and use TO_NUMBER function to cast it to number during query if required.
Or, @Josh Elser said, while storing the data in hbase , you can encode your integer to phoenix representation of INT by using below API.(same for other data types)
byte byte_representation = PInteger.INSTANCE.toBytes(<integer_value>);
Please see for more details:-
Thanks for the reply @Ankit Singhal
I am populating Hbase table through Hive table.
CREATE TABLE sdm_report__301__301_hive (order_by bigint, virus_id bigint, virus_name string, server_date date) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ('hbase.columns.mapping' = ':key, family:vid, family:name, family:server_date') TBLPROPERTIES ('hbase.table.name' = 'sdm_report__301__301_hbase'); INSERT OVERWRITE TABLE sdm_report__301__301_hive SELECT * FROM sdm_report__301__301;
How should I use API you mentioned while inserting data into Hbase table through Hive?
Is there a way to map 'PInteger' class datatype while creating Hbase table?
Or is there a way to directly create Phoenix table through Hive?
I'm not sure of the state of that repository, but such a feature is included in Apache Phoenix 4.8.0 and will be included in HDP 2.5. I would assume that the integration would handle this natively, but I am not positive.
As Josh already mentioned, it's included in Apache Phoenix 4.8.0. And it would handle it correctly. One additional note - the implementation that was accepted in Apache Phoenix is a bit different. More information can be found there: https://github.com/mini666/hive-phoenix-handler
Thanks for the reply. We are using HDP2.3 and Phoenix 4.4. Hence I'm planning to use pig integration mentioned below for Hive to Phoenix table transfer.