202107282053 外联表--hive集成hbase

Hive外联表HBase
详细参见官网
https://cwiki.apache.org/confluence/display/Hive/HBaseIntegration

# hive内部表
创建hive表映射hbase表, 前提是hbase表不存在
drop ttt则，hbase表也会drop
create table ttt (
 rk string ,
 info string
)STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'    
WITH SERDEPROPERTIES ("hbase.columns.mapping" =":key, f:rts")    
TBLPROPERTIES("hbase.table.name" ="abc:sigimsi_test_09");

put 'abc:sigimsi_test_09','row1','f:rts','value1'

hive (app_abc)> select * from ttt ;
OK
ttt.rk  ttt.info
row1    value1
Time taken: 0.394 seconds, Fetched: 1 row(s)

# hive外部表
# 前提是hbase表已存在
# drop tttt, hbase表不会drop
create external table tttt (
 rk string ,
 info string
)STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'    
WITH SERDEPROPERTIES ("hbase.columns.mapping" =":key, f:rts")    
TBLPROPERTIES("hbase.table.name" ="abc:sigimsi_test_09");

create external table ttttt (
 rk string ,
 info string
)STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'    
WITH SERDEPROPERTIES ("hbase.columns.mapping" =":key, f:rts")    
TBLPROPERTIES("hbase.table.name" ="abc:sigimsi_test_19");

# hive映射hbase列族
CREATE TABLE hbase_table_1(value1 map<string,int>, row_key1 int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" ="cf:,:key"
);

INSERT OVERWRITE TABLE hbase_table_1 SELECT map(bar, foo), foo FROM pokes 
WHERE foo=98 OR foo=100;

hbase(main):012:0> scan "hbase_table_1"
ROW                          COLUMN+CELL                                                                     
 100                         column=cf:val_100, timestamp=1267739509194, value=100                            
 98                          column=cf:val_98, timestamp=1267739509194, value=98                              
2 row(s) in 0.0080 seconds


hive> select * from hbase_table_1;
Total MapReduce jobs = 1
Launching Job 1 out of 1
...
OK
{"val_100":100}	100
{"val_98":98}	98
Time taken: 3.808 seconds