hbase和hive的整合

1. 准备hbase表和数据

创建hbase表

create 'mingxing',{NAME => 'base_info',VERSIONS => 1},{NAME => 'extra_info',VERSIONS => 1}

插入准备好的数据：

put 'mingxing','rk001','base_info:name','huangbo'
put 'mingxing','rk001','base_info:age','33'
put 'mingxing','rk001','extra_info:math','44'
put 'mingxing','rk001','extra_info:province','beijing'
put 'mingxing','rk002','base_info:name','xuzheng'
put 'mingxing','rk002','base_info:age','44'
put 'mingxing','rk003','base_info:name','wangbaoqiang'
put 'mingxing','rk003','base_info:age','55'
put 'mingxing','rk003','base_info:gender','male'
put 'mingxing','rk004','extra_info:math','33'
put 'mingxing','rk004','extra_info:province','tianjin'
put 'mingxing','rk004','extra_info:children','3'
put 'mingxing','rk005','base_info:name','liutao'
put 'mingxing','rk006','extra_info:name','liujialing'

2. 在hive中添加zookeeper的访问路径

set hbase.zookeeper.quorum=hadoop01:2181,hadoop02:2181,hadoop03:2181;

3. hive中设置hbase在zookeeper中的根目录

set zookeeper.znode.parent=/hbase;

4. 添加jar包到hive的classpath下

add jar /home/hadoop/apps/apache-hive-2.3.2-bin/lib/hive-hbase-handler-2.3.2.jar;

添加完之后记得查看一下

list jar|jars;

如图所示添加成功

5. 创建基于 HBase 表的 hive 表

整个表的解析：

create external table mingxing(rowkey string, base_info map<string, string>, extra_info 
map<string, string>) 
row format delimited fields terminated by '\t' 
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
with serdeproperties ("hbase.columns.mapping" = ":key,base_info:,extra_info:") 
tblproperties ("hbase.table.name" = "mingxing");

解析部分字段：

create external table mingxing01(rowkey string,name string,age string,province string)
row format delimited fields terminated by '\t'
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties ("hbase.columns.mapping" = ":key,base_info:name,base_info:age,extra_info:province")
tblproperties ("hbase.table.name" = "mingxing");

• org.apache.hadoop.hive.hbase.HBaseStorageHandler：处理 hive 到 hbase 转换关系的处理器
• serdeproperties 指定解析的属性的
• hbase.columns.mapping 指定需要解析的hbase表的rk 列族和 hive中的列的对应的关系
  • base_info: 解析hbase的整个base_info的列族
  • :key 获取行健的值（rowkey的获取）
• tblproperties 指定表的属性
  • hbase.table.name 指定需要解析的hbase的表名

6. 查询验证

select * from mingxing;
select rowkey,base_info['name'] from mingxing;
select rowkey,extra_info['province'] from mingxing;
select rowkey,base_info['name'], extra_info['province'] from mingxing;

select * from mingxing01;