HBase集成hive

一、为了创建一个新的由Hive管理的HBase表，请使用CREATE TABLE

CREATE TABLE hbase_table_1(key int, value string) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:val")
TBLPROPERTIES ("hbase.table.name" = "xyz", "hbase.mapred.output.outputtable" = "xyz");

The hbase.columns.mapping property is required and will be explained in the next section.
The hbase.table.name property is optional;
- it controls the name of the table as known by HBase, and allows the Hive table to have a different name.
- In this example, the table is known as hbase_table_1 within Hive, and as xyz within HBase.
- If not specified, then the Hive and HBase table names will be identical.
The hbase.mapred.output.outputtable property is optional;
- it's needed if you plan to insert data to the table (the property is used by hbase.mapreduce.TableOutputFormat)

2、列的映射

There are two SERDEPROPERTIES that control the mapping of HBase columns to Hive:

hbase.columns.mapping
hbase.table.default.storage.type: Can have a value of either string (the default) or binary, this option is only available as of Hive 0.9 and the string behavior is the only one available in earlier versions

多列和多列族

hive中创建表

CREATE TABLE hbase_table_1(key int, value1 string, value2 int, value3 int) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = ":key,a:b,a:c,d:e"
);

插入数据

hive> insert into table hbase_table_1 values(100,val_100,101,102);
hive> insert into table hbase_table_1 values(100,val_100,101,102);

hbase查看表结构

HBASE查看数据

hbase(main):004:0> scan "hbase_table_1"
ROW                       COLUMN+CELL                                                            
 100                      column=a:b, timestamp=1595817016732, value=val_100                     
 100                      column=a:c, timestamp=1595817016732, value=101                         
 100                      column=d:e, timestamp=1595817016732, value=102                         
 98                       column=a:b, timestamp=1595817050488, value=val_98                      
 98                       column=a:c, timestamp=1595817050488, value=99                          
 98                       column=d:e, timestamp=1595817050488, value=100                         
2 row(s) in 0.0410 seconds

总结

（1）hive的key即为hbase的rowkey

（2）"hbase.columns.mapping" = ":key,a:b,a:c,d:e"中，:key 即为rowkey

3、列的映射

hbase中插入数据

hbase(main):006:0> put "hbase_table_1",102,"a:b","val_102"
hbase(main):008:0> put "hbase_table_1",102,"a:c","101"
hbase(main):009:0> put "hbase_table_1",102,"d:e","102"

scan数据

hive查看数据