hive与hbase的表关联

hbase

创建表空间kb15并创建表customer，列族为addr、order

hbase(main):004:0> create_namespace 'kb15'
hbase(main):063:0> create 'kb15:customer','addr','order'

查看指定表空间kb15下的表

hbase(main):064:0> list_namespace_tables 'kb15'
TABLE                                                                                                         
customer                                                                                                 
2 row(s) in 0.0140 seconds

查看表customer结构

hbase(main):065:0> desc 'kb15:customer'
Table kb15:customer is ENABLED                                                                                
kb15:customer                                                                                                 
COLUMN FAMILIES DESCRIPTION                                                                                   
{NAME => 'addr', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', D
ATA_BLOCK_ENCODING => 'NONE', TTL => 'FOREVER', COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'tru
e', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'}                                                           
{NAME => 'order', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', 
DATA_BLOCK_ENCODING => 'NONE', TTL => 'FOREVER', COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'tr
ue', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'}                                                          
2 row(s) in 0.0210 seconds

hive

创建外部表customer

hive> create external table customer(
    > name string,
    > order_id string,
    > order_date string,
    > addr_city string,
    > addr_street string
    > )
    > stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' with 
    > serdeproperties ("hbase.columns.mapping"=":key,order:orderid,order:date,addr:city,addr:street")
    > tblproperties("hbase.table.name"="kb15:customer");
OK
Time taken: 1.771 seconds

hive插入一条数据

hive> insert into table customer values('tom','12345','2021-11-22','nanjing','xiaohanglu');
Query ID = root_20211122140909_c73e2a92-1018-42b8-908c-0dd4fe51be00
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1637539169231_0001, Tracking URL = http://mihaoyu151:8088/proxy/application_1637539169231_0001/
Kill Command = /opt/soft/hadoop260/bin/hadoop job  -kill job_1637539169231_0001
Hadoop job information for Stage-0: number of mappers: 1; number of reducers: 0
2021-11-22 14:09:29,847 Stage-0 map = 0%,  reduce = 0%
2021-11-22 14:09:36,455 Stage-0 map = 100%,  reduce = 0%, Cumulative CPU 1.62 sec
MapReduce Total cumulative CPU time: 1 seconds 620 msec
Ended Job = job_1637539169231_0001
MapReduce Jobs Launched: 
Stage-Stage-0: Map: 1   Cumulative CPU: 1.62 sec   HDFS Read: 11825 HDFS Write: 0 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 620 msec
OK
Time taken: 20.709 seconds

hive查询数据

hive> select * from customer;
OK
tom	12345	2021-11-22	nanjing	xiaohanglu
Time taken: 0.148 seconds, Fetched: 1 row(s)

hbase查询数据

hbase(main):066:0> scan 'kb15:customer'
ROW                          COLUMN+CELL                                                                     
 tom                         column=addr:city, timestamp=1637561375956, value=nanjing                        
 tom                         column=addr:street, timestamp=1637561375956, value=xiaohanglu                   
 tom                         column=order:date, timestamp=1637561375956, value=2021-11-22                    
 tom                         column=order:orderid, timestamp=1637561375956, value=12345                      
1 row(s) in 0.0220 seconds

hbase插入数据

hbase(main):067:0> put 'kb15:customer','jerry','addr:city','shanghai'
0 row(s) in 0.0070 seconds

hbase查询数据

hbase(main):068:0> scan 'kb15:customer'
ROW                          COLUMN+CELL                                                                     
 jerry                       column=addr:city, timestamp=1637561466015, value=shanghai                       
 tom                         column=addr:city, timestamp=1637561375956, value=nanjing                        
 tom                         column=addr:street, timestamp=1637561375956, value=xiaohanglu                   
 tom                         column=order:date, timestamp=1637561375956, value=2021-11-22                    
 tom                         column=order:orderid, timestamp=1637561375956, value=12345                      
2 row(s) in 0.0110 seconds

hive查询数据

hive> select * from customer;
OK
jerry	NULL	NULL	shanghai	NULL
tom	12345	2021-11-22	nanjing	xiaohanglu
Time taken: 0.123 seconds, Fetched: 2 row(s)

hbase插入数据

hbase(main):069:0> put 'kb15:customer','jerry','addr:street','nanjinglu'
0 row(s) in 0.0070 seconds
hbase(main):071:0> put 'kb15:customer','jerry','order:date','2021-11-11'
0 row(s) in 0.0070 seconds
hbase(main):072:0> put 'kb15:customer','jerry','order:orderid','54321'
0 row(s) in 0.0070 seconds

hbase查询数据

hbase(main):073:0> scan 'kb15:customer'
ROW                          COLUMN+CELL                                                                     
 jerry                       column=addr:city, timestamp=1637561466015, value=shanghai                       
 jerry                       column=addr:street, timestamp=1637561527748, value=nanjinglu                    
 jerry                       column=order:date, timestamp=1637561612812, value=2021-11-11                    
 jerry                       column=order:orderid, timestamp=1637561631412, value=54321                      
 tom                         column=addr:city, timestamp=1637561375956, value=nanjing                        
 tom                         column=addr:street, timestamp=1637561375956, value=xiaohanglu                   
 tom                         column=order:date, timestamp=1637561375956, value=2021-11-22                    
 tom                         column=order:orderid, timestamp=1637561375956, value=12345                      
2 row(s) in 0.0080 seconds

hive查询数据

hive> select * from customer;
OK
jerry	54321	2021-11-11	shanghai	nanjinglu
tom	12345	2021-11-22	nanjing	xiaohanglu
Time taken: 0.095 seconds, Fetched: 2 row(s)