Hive添加hbase外表

如果大量的数据已经存放再hbase上面，需要对已经存在的数据进行数据分析处理，那么phoenix并不合适做特别复杂的SQL处理，此时可以使用hive映射hbase的表格，之后写HQL进行分析处理。

添加hbase与hive集成

在hive-site.xml中添加zookeeper的属性，如下

<property>
    <name>hive.zookeeper.quorum</name>
    <value>hadoop01</value>
</property>
<property>
    <name>hive.zookeeper.client.port</name>
    <value>2181</value>
</property>

添加完配置以后进入hive使其配置生效并且连接zookeeper,然后进入hbase创建需要和hive关联的外表

hbase shell

创建hbase表

create 'order','order_info'

向列族内插入数据

put 'order','1','order_info:id','1'
put 'order','2','order_info:id','2'
put 'order','3','order_info:id','3'
put 'order','4','order_info:id','4'
put 'order','5','order_info:id','5'
put 'order','6','order_info:id','6'
put 'order','7','order_info:id','7'
put 'order','1','order_info:money','2945'
put 'order','2','order_info:money','1200'
put 'order','3','order_info:money','6793'
put 'order','4','order_info:money','6857'
put 'order','5','order_info:money','6953'
put 'order','6','order_info:money','7049'
put 'order','7','order_info:money','6377'
put 'order','1','order_info:date','1653926400000'
put 'order','2','order_info:date','1653926400000'
put 'order','3','order_info:date','1654272000000'
put 'order','4','order_info:date','1654185600000'
put 'order','5','order_info:date','1654185600000'
put 'order','6','order_info:date','1654012800000'
put 'order','7','order_info:date','1654012800000'
put 'order','1','order_info:goods','14739'
put 'order','2','order_info:goods','6073'
put 'order','3','order_info:goods','13407'
put 'order','4','order_info:goods','19652'
put 'order','5','order_info:goods','14551'
put 'order','6','order_info:goods','13407'
put 'order','7','order_info:goods','6073'
put 'order','1','order_info:status','1'
put 'order','2','order_info:status','1'
put 'order','3','order_info:status','0'
put 'order','4','order_info:status','0'
put 'order','5','order_info:status','0'
put 'order','6','order_info:status','1'
put 'order','7','order_info:status','1'

进入hive创建表

选择默认的数据库
use default;

创建连接外表
create external table hbase_order(
    id int,
    money int,
    dates bigint,
    goods string,
    status string
)stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' with serdeproperties(
    "hbase.columns.mapping" = "
    :key,
    order_info:money,
    order_info:date,
    order_info:goods,
    order_info:status"
)tblproperties(
    "hbase.table.name" = "order"
);

此时外表已经创建成功,查询一下是否有数据

select * from hbase_order;

结果如下

hive>     select * from hbase_order;
OK
1       2945    1653926400000   14739   1
2       1200    1653926400000   6073    1
3       6793    1654272000000   13407   0
4       6857    1654185600000   19652   0
5       6953    1654185600000   14551   0
6       7049    1654012800000   13407   1
7       6377    1654012800000   6073    1
Time taken: 0.399 seconds, Fetched: 7 row(s)