hive对接phoenix

最新推荐文章于 2024-01-04 15:49:19 发布

码太狼

最新推荐文章于 2024-01-04 15:49:19 发布

阅读量8.1k

点赞数

分类专栏： phoenix-hbase二级索引的实现 hive-sql on hadoop技术文章标签： phoenix hive

本文链接：https://blog.csdn.net/dante_003/article/details/79081615

版权

hive-sql on hadoop技术同时被 2 个专栏收录

8 篇文章 3 订阅

订阅专栏

phoenix-hbase二级索引的实现

3 篇文章 1 订阅

订阅专栏

1.配置

解压phoenix程序包，拷贝phoenix-xx-HBase-xx-hive.jar到hive的lib中
并配置hive-site.xml,增加phoenix-xx-HBase-xx-hive.ja

<property>
    <name>hive.aux.jars.path</name>
    <value>$HIVE_HOME/lib/phoenix-xx-HBase-xx-hive.ja</value>
</property>

2.hive的对接

索引需要在phoenix中建立，暂不支持在hive中建立，任务安排放在后面
经过修改hive中的源代码，在hive中可以直接执行对hbase以及基于phoenix的二级索引sql查询，支持的sql where条件种类:
= != > >= < <= like(因为索引是要从头开始检索的，支持%后缀，%前缀走的是普通hbase filter速度并不快)
支持各类子查询、复合索引查询

#建立phoenix(外)表
create external table car_zw (
id string,
coorid string,
cx string,
date1 string,
hphm string,
ys string
)
STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler'
TBLPROPERTIES (
"phoenix.table.name" = "car",
"phoenix.zookeeper.quorum" = "192.168.2.3",
"phoenix.zookeeper.znode.parent" = "/hbase",
"phoenix.zookeeper.client.port" = "2181",
"phoenix.rowkeys" = "id",
"phoenix.column.mapping" = "id:ID,coorid:COORID,cx:CX,date1:DATE,hphm:HPHM,ys:YS"
);

#测试二级索引查询
0: jdbc:phoenix:localhost:2181:/hbase> select * from "car" where "f1"."date">='20170110' and "f1"."date"<='20170210' and "f1"."hphm" like '.E3G%' limit 10;
+-------------------------+---------+------+-----------------+----------+-----+
|           ID            | coorid  |  cx  |      date       |   hphm   | ys  |
+-------------------------+---------+------+-----------------+----------+-----+
| 20170110005537_苏E3GS62  | �    | 奥迪   | 20170110005537  | 苏E3GS62  | 银   |
| 20170110010344_苏E3G21Q  | K    | 讴歌   | 20170110010344  | 苏E3G21Q  | 黄   |
| 20170110013131_苏E3G21Q  | �    | 讴歌   | 20170110013131  | 苏E3G21Q  | 黄   |
| 20170110013318_苏E3G21Q  | �    | 讴歌   | 20170110013318  | 苏E3G21Q  | 黄   |
| 20170110013452_苏E3GQCF  | C    | 沃尔沃  | 20170110013452  | 苏E3GQCF  | 白   |
| 20170110034239_苏E3G7S8  | �    | 丰田   | 20170110034239  | 苏E3G7S8  | 银   |
| 20170110041044_苏E3G2SA  | 6    | 奔驰   | 20170110041044  | 苏E3G2SA  | 黄   |
| 20170110041829_苏E3GEM2  | m    | 讴歌   | 20170110041829  | 苏E3GEM2  | 蓝   |
| 20170110045503_苏E3G2SA  |     | 奔驰   | 20170110045503  | 苏E3G2SA  | 黄   |
| 20170110050616_苏E3GS62  | O    | 奥迪   | 20170110050616  | 苏E3GS62  | 银   |
+-------------------------+---------+------+-----------------+----------+-----+
10 rows selected (0.706 seconds)