Hive添加hbase外表
时间:2023-3-14 19:52 作者:Linh 分类: Hive
如果大量的数据已经存放再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)