Unable to map numeric fields from Hbase table to Phoenix

[#1]

I have Hbase table with integer field.

When I map Phoenix table on to this Hbase table, integer field show values like : -213456236

E.g.

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.

https://community.hortonworks.com/questions/15381/created-phoenix-view-to-map-existing-hbase-table-b...

Please help @Josh Elser

[#2]

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.

[#3]

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.

[#4]

Ok, beware that you are going down an unsupported path. You may be able to use the HBase Bytes class for properly converting data in your write-side, but I am not sure if this will be 100% accurate.

[#5]

@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:-

https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_table

[#6]

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?

[#7]

Probably only by making your own Hive UDF. The VARCHAR and TO_NUMBER approach would be simplest.

[#8]

@Josh Elser

Can I use PhoenixStorageHandler while creating Hive table?

Mentioned here:

https://github.com/nmaillard/Phoenix-Hive

[#9]

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.

[#10]

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