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)