question Re: Unable to map numeric fields from Hbase table to Phoenix in Archives of Support Questions (Read Only)

Unable to map numeric fields from Hbase table to Phoenix

saurabh_rathi — Mon, 08 Aug 2016 23:11:39 GMT

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.html

Please help @Josh Elser

Re: Unable to map numeric fields from Hbase table to Phoenix

elserj — Mon, 08 Aug 2016 23:15:23 GMT

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.

Re: Unable to map numeric fields from Hbase table to Phoenix

saurabh_rathi — Mon, 08 Aug 2016 23:19:39 GMT

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.

Re: Unable to map numeric fields from Hbase table to Phoenix

elserj — Mon, 08 Aug 2016 23:45:00 GMT

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.

Re: Unable to map numeric fields from Hbase table to Phoenix

asinghal — Tue, 09 Aug 2016 16:08:16 GMT

@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

Re: Unable to map numeric fields from Hbase table to Phoenix

saurabh_rathi — Tue, 09 Aug 2016 17:44:43 GMT

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?

Re: Unable to map numeric fields from Hbase table to Phoenix

elserj — Tue, 09 Aug 2016 21:38:43 GMT

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

Re: Unable to map numeric fields from Hbase table to Phoenix

saurabh_rathi — Thu, 11 Aug 2016 20:01:25 GMT

@Josh Elser

Can I use PhoenixStorageHandler while creating Hive table?

Mentioned here:

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

Re: Unable to map numeric fields from Hbase table to Phoenix

elserj — Thu, 11 Aug 2016 22:15:18 GMT

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.

Re: Unable to map numeric fields from Hbase table to Phoenix

saurabh_rathi — Fri, 12 Aug 2016 20:11:39 GMT

I'm planning to use approach mentioned below using phoenix-pig integration.

https://community.hortonworks.com/questions/12538/phoenix-storage-in-pig.html

Re: Unable to map numeric fields from Hbase table to Phoenix

ssoldatov — Tue, 16 Aug 2016 15:47:48 GMT

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

Re: Unable to map numeric fields from Hbase table to Phoenix

saurabh_rathi — Wed, 17 Aug 2016 11:32:32 GMT

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.

Re: Unable to map numeric fields from Hbase table to Phoenix

SatyaPraveen — Tue, 25 Apr 2017 02:01:12 GMT

idea work-around if you don't have minimal versions of

Phoenix 4.8.0+
Hive 1.2.1+

to use Phoenix Storage Handler for Hive.

Phoenix-Pig Integration worked for me.. thank you @Saurabh Rathi