hive → phoenix数据导入导出方案

来源：互联网发布：螨虫与痘痘的知乎编辑：程序博客网时间：2024/06/17 12:19

主要是使用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;

0 0