How to split HBase row key into 2 columns in Hive table

Question

HBase Table
rowkey: 2020-02-02^ghfgewr3434555, cf:1 timestamp=1604405829275, value=true
rowkey: 2020-02-02^ghfgewr3434555, cf:2 timestamp=1604405829275, value=true
rowkey: 2020-02-02^ghfgewr3434555, cf:3 timestamp=1604405829275, value=false
rowkey: 2020-02-02^ghfgewr3434555, cf:4 timestamp=1604405829275, value=false

Transfer HBase data into Hive table like below

Hive table
date ========= Id ======== cf:no == boolean
2020-02-02 ==== ghfgewr3434555 == 1 ======= true
2020-02-02 ==== ghfgewr3434555 == 2 ======= true
2020-02-02 ==== ghfgewr3434555 == 3 ======= false
2020-02-02 ==== ghfgewr3434555 == 4 ======= false

Answer 1

If you are thinking to transfer it only to be queried, you can actually create a connection in hive to that table specifying the properties

CREATE TABLE foo(rowkey STRING, a STRING, b STRING)
STORED BY ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
WITH SERDEPROPERTIES (‘hbase.columns.mapping’ = ‘:key,f:c1,f:c2’)
TBLPROPERTIES (‘hbase.table.name’ = ‘bar’);

Proper doc here: https://blog.cloudera.com/hbase-via-hive-part-1/

Answer 2

I have solved this problem using 2 table/View. First 1 just coping data from HBase table and second table/view split the rowkey into 2 columns.

First Table query in Hive

CREATE EXTERNAL TABLE hbase_hive_table(key string, t1 boolean,t2 boolean) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = "cf:1#b,cf:2#b) TBLPROPERTIES ("hbase.table.name" = "hbase_table");

First Table/View query in Hive

create view if not exists hbase_hive_view as SELECT CONCTNS.rowkey[0] AS date, CONCTNS.rowkey[1] AS req_id, t1, t2 FROM ( SELECT split(key,'\^') AS rowkey, t1, t2 FROM hbase_hive_table) CONCTNS;