hive on hbase 示例

hive on hbase 示例

--创建hive表,并映射hbase的表
 CREATE TABLE `test_db.hbase_employee`(                              
   `key` string COMMENT '',                                             
   `companycode` string COMMENT '',                                     
   `t_name` string COMMENT '',                                           
   `t_postid` string COMMENT '')                                          
 ROW FORMAT SERDE                                                       
   'org.apache.hadoop.hive.hbase.HBaseSerDe'                            
 STORED BY                                                              
   'org.apache.hadoop.hive.hbase.HBaseStorageHandler'                   
 WITH SERDEPROPERTIES (                                                 
   'hbase.columns.mapping'=':key,cf1:companycode,cf1:t_name,cf1:t_postid', 
   'serialization.format'='1')                                          
 TBLPROPERTIES (                                                        
   'bucketing_version'='2',                                             
   'hbase.table.name'='realtime_fee:hbase_employee')                                
;

#使用hbase shell工具查询
hbase shell
#查询有哪些表
hbase(main):002:0> list
TABLE
realtime_fee:hbase_employee
1 row(s) in 0.0710 seconds
#查询表描述
hbase(main):003:0> desc 'realtime_fee:hbase_employee'
Table realtime_fee:hbase_employee is ENABLED
realtime_fee:hbase_employee
COLUMN FAMILIES DESCRIPTION
{NAME => 'cf1', BLOOMFILTER => 'ROW', IN_MEMORY => 'false', VERSIONS => '1', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'NONE', COMPRESSION => 'NONE', TTL => 'FOREVER', MIN_VERSIO
NS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'}
1 row(s) in 0.1850 seconds
#查询表数据量
count 'realtime_fee:hbase_employee'
0 row(s) in 0.3330 seconds
=> 0

--hive写入数据到hbase表
insert into test_db.hbase_employee values ('1','01','one','10');
insert into test_db.hbase_employee values ('2','02','two','20');

--hive查询表数据
select * from test_db.hbase_employee;
+---------------------+-----------------------------+------------------------+--------------------------+
| hbase_employee.key  | hbase_employee.companycode  | hbase_employee.t_name  | hbase_employee.t_postid  |
+---------------------+-----------------------------+------------------------+--------------------------+
| 1                   | 01                          | one                    | 10                       |
| 2                   | 02                          | two                    | 20                       |
+---------------------+-----------------------------+------------------------+--------------------------+
2 rows selected (1.472 seconds)

--hive更新表数据 
insert overwrite table test_db.hbase_employee 
select '3','03','three','30'
union all 
select '2','04','four','40';

--hive查询更新之后的表数据
select * from test_db.hbase_employee;
+---------------------+-----------------------------+------------------------+--------------------------+
| hbase_employee.key  | hbase_employee.companycode  | hbase_employee.t_name  | hbase_employee.t_postid  |
+---------------------+-----------------------------+------------------------+--------------------------+
| 1                   | 01                          | one                    | 10                       |
| 2                   | 04                          | four                   | 40                       |
| 3                   | 03                          | three                  | 30                       |
+---------------------+-----------------------------+------------------------+--------------------------+
3 rows selected (0.928 seconds)


#hbase shell查询表数据 
count 'realtime_fee:hbase_employee'
3 row(s) in 0.0640 seconds
=> 3

scan 'realtime_fee:hbase_employee'
scan 'realtime_fee:hbase_employee'
ROW                                              COLUMN+CELL
 1                                               column=cf1:companycode, timestamp=1737367211730, value=01
 1                                               column=cf1:t_name, timestamp=1737367211730, value=one
 1                                               column=cf1:t_postid, timestamp=1737367211730, value=10
 2                                               column=cf1:companycode, timestamp=1737367948418, value=04
 2                                               column=cf1:t_name, timestamp=1737367948418, value=four
 2                                               column=cf1:t_postid, timestamp=1737367948418, value=40
 3                                               column=cf1:companycode, timestamp=1737367948418, value=03
 3                                               column=cf1:t_name, timestamp=1737367948418, value=three
 3                                               column=cf1:t_postid, timestamp=1737367948418, value=30
3 row(s) in 0.0610 seconds