hive整合phoenix-创建外表注意事项

1.phoenix端创建表并插入一条数据
CREATE TABLE export_hive(
id decimal(10, 0) not null,
branch_no varchar(6)
CONSTRAINT pk PRIMARY KEY (id)
);

upsert into export_hive (id,branch_no) values (11111,'33333');

2.hive shell添加依赖包，并创建外表
add jars /usr/phoenix/phoenix/phoenix-4.14.0-cdh5.11.2-hive.jar;

create external table export_hive 
(
 id decimal,
 branch_no string
 )
STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler'   TBLPROPERTIES
 (
"phoenix.table.name" = "EXPORT_HIVE",
"phoenix.zookeeper.quorum" = "zklist",
"phoenix.zookeeper.znode.parent" = "/hbase",
"phoenix.zookeeper.client.port" = "2181",
"phoenix.rowkeys" = "ID",
"phoenix.column.mapping" = "id:ID, branch_no:BRANCH_NO"
);

注意:"phoenix.column.mapping" = "id:ID, branch_no:BRANCH_NO" 字段对应关系为冒号左侧为hive字段名小写，右侧为phoenix字段名必须大写，否则会抛异常：Failed with exception java.io.IOException:java.lang.RuntimeException: org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined column. columnName=EXPORT_HIVE.id

hive> select * from export_hive ;
OK
11111   33333
Time taken: 0.259 seconds, Fetched: 1 row(s)