Access Hbase Increment column via hive/impala

[#1]

Hbase has a nice feature called counter increment, where you can atomicly increment a value and get back the result. I want to create ta simple external table over this hbase table, but I dont know how to choose the correct data type for Hive/Impala.

The value of colfam:FL_Lock is this:

20160512_000006 column=colfam:FL_Lock, timestamp=1464120550634, value=\x00\x00\x00\x00\x00\x00\x00\x00
 

If I create external table with string, the query returns nothing, no error

If I create external table with bigint/decimal/int, the query returns NULL and ERROR from Impala:

Error converting column colfam:FL_Lock: '' TO INT

Any ideas how to map correctly this Hbase column?

Thanks

Tomas

[#2]

Given the encoding used internally by HBase, the only way to achieve this would be to apply a UDF on top of the returned binary data that decodes the value to an integer.

You can use the HBase API of http://archive.cloudera.com/cdh5/cdh/5/hbase/apidocs/org/apache/hadoop/hbase/util/Bytes.html#toLong(... to perform the transformation within the UDF.

X-Ref: https://github.com/cloudera/hbase/blob/cdh5.7.1-release/hbase-server/src/main/java/org/apache/hadoop...

[#3 · Accepted solution]

Actually this has been already resolved, we changed the create table statetment, added #b (hash b -  as binary).

create external table md_extract_file_status ( table_key string, fl_counter bigint )
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ('hbase.columns.mapping' = ':key,colfam:FL_Counter#b ) TBLPROPERTIES('hbase.table.name' ='HBTABLE');