Support Questions
Find answers, ask questions, and share your expertise

org.apache.phoenix.hive.PhoenixStorageHandler not able to map columns that are in mixed case.

New Contributor

Unable to map some columns of Hive table to a Phoenix table which has Mixed case in column name exaxmple:"AP_ifSpeed","DisplaySpeed"

 

ERROR:

Error: java.io.IOException: java.lang.RuntimeException: org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined column. columnName=ap_ifspeed (state=,code=0)

---------------------------------------------------------
PHOENIX ON EXISTING HBASE TABLE
---------------------------------------------------------
--------------------------
PHOENIX TABLE
--------------------------

CREATE TABLE "ppm"."ppm" (
"kp_pk" VARCHAR NOT NULL,
"id_pk" VARCHAR NOT NULL,
"day_pk" VARCHAR NOT NULL,
"timestamp_pk" VARCHAR NOT NULL,
"parent_id_pk" VARCHAR NOT NULL,
"resource_id_pk" VARCHAR NOT NULL,
"data"."resource_id" VARCHAR,
"data"."resource_name" VARCHAR,
"data"."timestamp" VARCHAR,
"data"."value" VARCHAR,
"data"."AP_ifSpeed" VARCHAR,
"data"."DisplaySpeed" VARCHAR,
"data"."description" VARCHAR,
"data"."id" VARCHAR,
"data"."name" VARCHAR,
"data"."parent_name" VARCHAR,
"data"."partition" VARCHAR,
"data"."readable_timestamp" VARCHAR
CONSTRAINT PK PRIMARY KEY(
"kp_pk",
"id_pk",
"day_pk",
"timestamp_pk",
"parent_id_pk",
"resource_id_pk")) IMMUTABLE_ROWS=true;

 

 

PHOENIX VIEW
------------

CREATE VIEW PPM AS SELECT * FROM "ppm"."ppm";

 

 

--------------------------------------------------
HIVE EXTERNAL TABLE USING-PHOENIX STORAGE HANDLER
--------------------------------------------------

set hive.support.sql11.reserved.keywords=false

CREATE EXTERNAL TABLE PPM.HBASE_DATA_PX (
`KP_PK` STRING,
`ID_PK` STRING,
`DAY_PK` STRING,
`TIMESTAMP_PK` STRING,
`PARENT_ID_PK` STRING,
`RESOURCE_ID_PK` STRING,
`RESOURCE_ID` STRING,
`RESOURCE_NAME` STRING,
`TIMESTAMP` STRING,
`VALUE` STRING,
`AP_IFSPEED` STRING,
`DISPLAYSPEED` STRING,
`DESCRIPTION` STRING,
`ID` STRING,
`NAME` STRING,
`PARENT_NAME` STRING,
`PARTITION` STRING,
`READABLE_TIMESTAMP` STRING
)
STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler'
TBLPROPERTIES(
'phoenix.table.name'='PPM',
'phoenix.zookeeper.quorum'='c99a01.svt.dan.local, c99a02.svt.dan.local,c99a03.svt.dan.local',
'phoenix.zookeeper.znode.parent'='/hbase-secure',
'phoenix.zookeeper.client.port'='2181',
'phoenix.rowkeys'='"kp_pk","id_pk","day_pk","timestamp_pk","parent_id_pk","resource_id_pk"',
'phoenix.column.mapping'='
`KP_PK`:"kp_pk",
`ID_PK`:"id_pk",
`DAY_PK`:"day_pk",
`TIMESTAMP_PK`:"timestamp_pk",
`PARENT_ID_PK`:"parent_id_pk",
`RESOURCE_ID_PK`:"resource_id_pk",
`RESOURCE_ID`:"data"."resource_id_pk",
`RESOURCE_NAME`:"data"."resource_name",
`TIMESTAMP`:"data"."timestamp",
`VALUE`:"data"."value",
`AP_IFSPEED`:"data"."`AP_ifSpeed`",
`DISPLAYSPEED`:"data"."DisplaySpeed",
`DESCRIPTION`:"data"."description",
`ID`:"data"."id",
`NAME`:"data"."name",
`PARENT_NAME`:"data"."parent_name",
`PARTITION`:"data"."partition",
`READABLE_TIMESTAMP`:"data"."readable_timestamp"'
)

 

 

Error: java.io.IOException: java.lang.RuntimeException: org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined column. columnName=ap_ifspeed (state=,code=0)

 

0 REPLIES 0