hive → phoenix数据导入导出方案

主要是使用Phoenix Storage Handler for Apache Hive Feature Installation，优势是phoenix自带的feature，支持力度比较好
phoenix主页上相关的介绍[https://phoenix.apache.org/hive_storage_handler.html ]

1. 调研的环境

phoenix 4.8
hive 1.2.1
测试环境共有两个集群。 hbase和hadoop

2. 操作过程

2.1 需要开启的配置项

hbase-site.xml 集群和客户端都需要开启这两项配置

<property>
 <name>phoenix.schema.isNamespaceMappingEnabled</name>
 <value>true</value>
</property>
<property>
 <name>phoenix.schema.mapSystemTablesToNamespace</name>
 <value>false</value>
</property>

hive-site.xml

<property>
    <name>hive.aux.jars.path</name>
    <value>file:///home/hadoop/apache-hive-1.2.1-bin/lib/phoenix-4.8.0-HBase-0.98-hive.jar</value>
</property>

hive-env.sh

export HIVE_AUX_JARS_PATH=/home/hadoop/apache-hive-1.2.1-bin/lib/phoenix-4.8.0-HBase-0.98-hive.jar

2.2 创建phoenix schema

create schema if not exists ABC;

2.3 创建hive内部表，关联到phoenix

create table g_order_phoenix 
(
 order_id bigint,
 driver_id bigint,
 driver_phone string,
 passenger_id bigint
 )
STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler'   TBLPROPERTIES
 (
"phoenix.table.name" = "ABC.G_ORDER_PHOENIX",
"phoenix.zookeeper.quorum" = "10.93.18.30,10.93.18.31,10.93.18.32",
"phoenix.zookeeper.znode.parent" = "/hbase",
"phoenix.zookeeper.client.port" = "2181",
"phoenix.rowkeys" = "order_id,driver_id",
"phoenix.column.mapping" = "order_id:order_id, driver_id:driver_id, driver_phone:driver_phone, passenger_id:passenger_id",
"phoenix.table.options" = "SALT_BUCKETS=10, DATA_BLOCK_ENCODING='DIFF'"
);

###2.4 创建待导出数据的hive表，这部分数据在hadoop HDFS上

CREATE TABLE `g_order`
(
   `order_id` bigint COMMENT '订单id', 
   `driver_id` bigint COMMENT '司机id，司机抢单前该值为0', 
   `driver_phone` string COMMENT '司机电话', 
   `passenger_id` bigint COMMENT '乘客id'
 );

2.5 插入数据

insert into table g_order 
select order_id, driver_id,driver_phone,passenger_id from g_order_phoenix where driver_id!=0 and order_id!=0;