通过hive表整合查询hbase数据

大家知道，直接从hbase的读取数据是一个比较繁锁的过程，需要java代码或是spark 查询
通过Hive整合HBase,可以通过hive表查询hbase数据，下面是测试过程
--创建hbase表

create "user","account","address","info","userid"

--创建映射hbase表列族的hive外部表

CREATE EXTERNAL TABLE hbase_user(key string, idcard string,passport string,country string,name string,password string,
   province string,city string,age string,sex string ,id string)   
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'  
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,account:idcard,account:passport,account:country,account:name,account:password,
   address:province,address:city,info:age,info:sex,userid:id")   
TBLPROPERTIES("hbase.table.name" = "user");

--添加hbase新的记录

put 'user','zhangsan','account:idcard','420923156366998855'
put 'user','lisi','account:idcard','520369856366998855'
put 'user','lisi','account:country','china'
put 'user','lichangzai','account:idcard','132901981090831721'
put 'user','changfei','account:idcard','132901983090831736'
put 'user','lichangzai','info:age','35'
put 'user','lichangzai','info:sex','man'
put 'user','changfei','info:age','30'

-- hive查询hbase数据

hive> select * from user;

--修改hbase记录，直接put行建的的列值

put 'user','lisi','account:idcard','520369856366998856'

--添加列族

alter 'user','work','adu'

--添加新列族的字段值

put 'user','lichangzai','edu:mschool','rq no.1'
put 'user','lichangzai','edu:university','qhddx'
put 'user','lichangzai','work:company1','12580'
put 'user','lichangzai','work:company2','china mobile'
put 'user','lichangzai','info:site','blog.csdn.net/lichangzai'
put 'user','lichangzai','info:mobile','13712345678'
put 'user','changfei','edu:university','bjdx'
put 'user','changfei','work:company1','LG'
put 'user','changfei','info:mobile','13598765401'
put 'user','changfei','info:site','hi.baidu/lichangzai'

再查询hive表，可以看到新添加的字段值没有被显示。

下面可以创建删除hive外部表，再重新创建映射hbase的外部表，把hbase添加的列族映射进来
--删除hive外部表（外部表的特性，表会删除，数据还在）
hive> drop table user;

--重新创建外表
CREATE EXTERNAL TABLE hbase_user(key string, idcard string,passport string,country string,name string,password string,
   province string,city string,age string,sex string,site string,mobile string,work1 string,work2 string,edu_mschool string,edu_univ string,id string)   
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'  
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,account:idcard,account:passport,account:country,account:name,account:password,
   address:province,address:city,info:age,info:sex,info:site,info:mobile,work:company1,work:company2,edu:mschool,edu:university,userid:id")   
TBLPROPERTIES("hbase.table.name" = "user");

--再次查询hive表
select * from hbase_user