Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Please see the Cloudera blog for information on the Cloudera Response to CVE-2021-4428

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