Support Questions

Find answers, ask questions, and share your expertise

Unable to map numeric fields from Hbase table to Phoenix

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

1 ACCEPTED SOLUTION

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

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

View solution in original post

12 REPLIES 12

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.

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.

@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

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?

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

@Josh Elser

Can I use PhoenixStorageHandler while creating Hive table?

Mentioned here:

https://github.com/nmaillard/Phoenix-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.

Super Collaborator

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.

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

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

New Contributor

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