Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Error while mapping Phoenix View with Hbase.

Error while mapping Phoenix View with Hbase.

Hello,

I'm trying to create a Phoenix view to map it with Hbase table.

Here, I have a column name "PK" which is a primary key belonging to column family "cf".

While creating the Phoenix view in Zeppelin, I'm getting an error which says primary key can't belong to any column family. When I write "cf":"PK".

When I write it as "PK" and execute the query, a complete different table with the same name gets created in my Hbase shell.

What should be done in this case ?

5 REPLIES 5

Re: Error while mapping Phoenix View with Hbase.

Super Collaborator

a Hbase table will only have one rowkey, valid for all columnfamilies. It is the only primary key and it can't belong to any column family.

So from here we must be clear on what you mean with 'I have a column name "PK" which is a primary key belonging to column family "cf"'. You might have a column PK in the column family cf, but for sure not as the rowkey (primary key) in Hbase. Within a column family any column is optional.

Can you provide a table description of the Hbase table you try to map and the Phoenix view statement?

Re: Error while mapping Phoenix View with Hbase.

@Harald Berghoff

Hello sir,

PFA attached Table Description

capture.png

Here is my Phoenix View Statement which gave me error: Primary Key can't be included in column family.

CREATE VIEW "FNL.ADDRESSES" ( 
"cf".HJMPTS DECIMAL(20,0),
"cf".CREATEDTS	TIME,
"cf".MODIFIEDTS	TIME,
"cf".TYPEPKSTRING	DECIMAL(20,0),
"cf".OWNERPKSTRING	DECIMAL(20,0),
"cf".PK	DECIMAL(20,0) PRIMARY KEY,
"cf".P_ORIGINAL	DECIMAL(20,0),
"cf".P_DUPLICATE	DECIMAL(1,0),
"cf".P_APPARTMENT	VARCHAR(255),
"cf".P_BUILDING	VARCHAR(255),
"cf".P_CELLPHONE	VARCHAR(255),
"cf".P_COMPANY	VARCHAR(255),
"cf".P_COUNTRY	DECIMAL(20,0),
"cf".P_DEPARTMENT	VARCHAR(255),
"cf".P_DISTRICT	VARCHAR(255),
"cf".P_EMAIL	VARCHAR(255),
"cf".P_FAX	VARCHAR(255),
"cf".P_FIRSTNAME	VARCHAR(255),
"cf".P_LASTNAME	VARCHAR(255),
"cf".P_MIDDLENAME	VARCHAR(255),
"cf".P_MIDDLENAME2	VARCHAR(255),
"cf".P_PHONE1	VARCHAR(255),
"cf".P_PHONE2	VARCHAR(255),
"cf".P_POBOX	VARCHAR(255),
"cf".P_POSTALCODE	VARCHAR(255),
"cf".P_REGION	DECIMAL(20,0),
"cf".P_STREETNAME	VARCHAR(255),
"cf".P_STREETNUMBER	VARCHAR(255),
"cf".P_TITLE	DECIMAL(20,0),
"cf".P_TOWN	VARCHAR(255),
"cf".P_GENDER	DECIMAL(20,0),
"cf".P_DATEOFBIRTH	TIME,
"cf".P_REMARKS	VARCHAR(255),
"cf".P_URL	VARCHAR(255),
"cf".P_SHIPPINGADDRESS	DECIMAL(1,0),
"cf".P_UNLOADINGADDRESS	DECIMAL(1,0),
"cf".P_BILLINGADDRESS	DECIMAL(1,0),
"cf".P_CONTACTADDRESS	DECIMAL(1,0),
"cf".P_VISIBLEINADDRESSBOOK	DECIMAL(1,0),
"cf".P_STATE	VARCHAR(255),
"cf".P_LANDMARK	VARCHAR(255),
"cf".P_CODELIGIBLE	DECIMAL(1,0),
"cf".ACLTS	DECIMAL(20,0),
"cf".PROPTS	DECIMAL(20,0),
"cf".P_ISHOMEADDRESS	DECIMAL(1,0)
);

Re: Error while mapping Phoenix View with Hbase.

Super Collaborator

but you still have a column name PK in the column family I guess (I remember that table was migrated from Oracle to Hbase originally)? In that case remove the "primary key" from the column definition. The actual primary key must be outside any column family.

Re: Error while mapping Phoenix View with Hbase.

Hello @Harald Berghoff,

So is this orientation right ?

CREATE VIEW "FNL.ADDRESSES" ( 
"cf".HJMPTS DECIMAL(20,0),
"cf".CREATEDTS    TIME,
"cf".MODIFIEDTS    TIME,
"cf".TYPEPKSTRING    DECIMAL(20,0),
"cf".OWNERPKSTRING    DECIMAL(20,0),
PK    DECIMAL(20,0) PRIMARY KEY,
"cf".P_ORIGINAL    DECIMAL(20,0),
"cf".P_DUPLICATE    DECIMAL(1,0),
"cf".P_APPARTMENT    VARCHAR(255),
"cf".P_BUILDING    VARCHAR(255),
"cf".P_CELLPHONE    VARCHAR(255),
"cf".P_COMPANY    VARCHAR(255),
"cf".P_COUNTRY    DECIMAL(20,0),
"cf".P_DEPARTMENT    VARCHAR(255),
"cf".P_DISTRICT    VARCHAR(255),
"cf".P_EMAIL    VARCHAR(255),
"cf".P_FAX    VARCHAR(255),
"cf".P_FIRSTNAME    VARCHAR(255),
"cf".P_LASTNAME    VARCHAR(255),
"cf".P_MIDDLENAME    VARCHAR(255),
"cf".P_MIDDLENAME2    VARCHAR(255),
"cf".P_PHONE1    VARCHAR(255),
"cf".P_PHONE2    VARCHAR(255),
"cf".P_POBOX    VARCHAR(255),
"cf".P_POSTALCODE    VARCHAR(255),
"cf".P_REGION    DECIMAL(20,0),
"cf".P_STREETNAME    VARCHAR(255),
"cf".P_STREETNUMBER    VARCHAR(255),
"cf".P_TITLE    DECIMAL(20,0),
"cf".P_TOWN    VARCHAR(255),
"cf".P_GENDER    DECIMAL(20,0),
"cf".P_DATEOFBIRTH    TIME,
"cf".P_REMARKS    VARCHAR(255),
"cf".P_URL    VARCHAR(255),
"cf".P_SHIPPINGADDRESS    DECIMAL(1,0),
"cf".P_UNLOADINGADDRESS    DECIMAL(1,0),
"cf".P_BILLINGADDRESS    DECIMAL(1,0),
"cf".P_CONTACTADDRESS    DECIMAL(1,0),
"cf".P_VISIBLEINADDRESSBOOK    DECIMAL(1,0),
"cf".P_STATE    VARCHAR(255),
"cf".P_LANDMARK    VARCHAR(255),
"cf".P_CODELIGIBLE    DECIMAL(1,0),
"cf".ACLTS    DECIMAL(20,0),
"cf".PROPTS    DECIMAL(20,0),
"cf".P_ISHOMEADDRESS    DECIMAL(1,0)
);

Re: Error while mapping Phoenix View with Hbase.

Super Collaborator

it really depends on how you mapped the primary key during migration to Hbase. If you mapped it into a column pk within the column familiy, you will not see the pk column as from the original table (i.e. oracle). I suggest you try it as below, which would map the Hbase rowkey into the column "rowkey" in your Phoenix table and would also map the column PK from the column family in case it exists.

CREATE VIEW "FNL.ADDRESSES" ( 
rowkey VARCHAR PRIMARY KEY,
"cf".HJMPTS DECIMAL(20,0),
"cf".CREATEDTS    TIME,
"cf".MODIFIEDTS    TIME,
"cf".TYPEPKSTRING    DECIMAL(20,0),
"cf".OWNERPKSTRING    DECIMAL(20,0),
"cf".PK    DECIMAL(20,0),
"cf".P_ORIGINAL    DECIMAL(20,0),
"cf".P_DUPLICATE    DECIMAL(1,0),
"cf".P_APPARTMENT    VARCHAR(255),
"cf".P_BUILDING    VARCHAR(255),
"cf".P_CELLPHONE    VARCHAR(255),
"cf".P_COMPANY    VARCHAR(255),
"cf".P_COUNTRY    DECIMAL(20,0),
"cf".P_DEPARTMENT    VARCHAR(255),
"cf".P_DISTRICT    VARCHAR(255),
"cf".P_EMAIL    VARCHAR(255),
"cf".P_FAX    VARCHAR(255),
"cf".P_FIRSTNAME    VARCHAR(255),
"cf".P_LASTNAME    VARCHAR(255),
"cf".P_MIDDLENAME    VARCHAR(255),
"cf".P_MIDDLENAME2    VARCHAR(255),
"cf".P_PHONE1    VARCHAR(255),
"cf".P_PHONE2    VARCHAR(255),
"cf".P_POBOX    VARCHAR(255),
"cf".P_POSTALCODE    VARCHAR(255),
"cf".P_REGION    DECIMAL(20,0),
"cf".P_STREETNAME    VARCHAR(255),
"cf".P_STREETNUMBER    VARCHAR(255),
"cf".P_TITLE    DECIMAL(20,0),
"cf".P_TOWN    VARCHAR(255),
"cf".P_GENDER    DECIMAL(20,0),
"cf".P_DATEOFBIRTH    TIME,
"cf".P_REMARKS    VARCHAR(255),
"cf".P_URL    VARCHAR(255),
"cf".P_SHIPPINGADDRESS    DECIMAL(1,0),
"cf".P_UNLOADINGADDRESS    DECIMAL(1,0),
"cf".P_BILLINGADDRESS    DECIMAL(1,0),
"cf".P_CONTACTADDRESS    DECIMAL(1,0),
"cf".P_VISIBLEINADDRESSBOOK    DECIMAL(1,0),
"cf".P_STATE    VARCHAR(255),
"cf".P_LANDMARK    VARCHAR(255),
"cf".P_CODELIGIBLE    DECIMAL(1,0),
"cf".ACLTS    DECIMAL(20,0),
"cf".PROPTS    DECIMAL(20,0),
"cf".P_ISHOMEADDRESS    DECIMAL(1,0)
);
Don't have an account?
Coming from Hortonworks? Activate your account here