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.

When creating a Phoenix view over an existing Hbase table, is it possible to map values to anything other than "VARCHAR"

Solved Go to solution

When creating a Phoenix view over an existing Hbase table, is it possible to map values to anything other than "VARCHAR"

New Contributor

Hi,

I've got the following existing table created in HBASE :

#Hbase table definition 
create 'WEB_STAT_INFO', 'info', 'usage'

#Populate HBase table 
put 'WEB_STAT_INFO', 'row1', 'info:host', 'Host A'
put 'WEB_STAT_INFO', 'row1', 'info:domain', 'Domain A'
put 'WEB_STAT_INFO', 'row1', 'usage:core', '15'

#view hbase data 
scan 'WEB_STAT_INFO'

I would like to create a Phoenix view on top of it.

Mapping all data to VARCHAR works fine

0: jdbc:phoenix:localhost:2181/hbase> CREATE VIEW "WEB_STAT_INFO" ( ROWKEY VARCHAR PRIMARY KEY, "info"."host" VARCHAR, "info"."domain" VARCHAR, "usage"."core" VARCHAR ) ;
No rows affected (0.069 seconds)

0: jdbc:phoenix:localhost:2181/hbase> select * from WEB_STAT_INFO ;
+---------+---------+-----------+-------+
| ROWKEY  |  host   |  domain   | core  |
+---------+---------+-----------+-------+
| row1    | Host A  | Domain A  | 15    |
+---------+---------+-----------+-------+
1 row selected (0.081 seconds)

Mapping to anything else results in an error...

ex :

0: jdbc:phoenix:localhost:2181/hbase> CREATE VIEW "WEB_STAT_INFO" ( ROWKEY VARCHAR PRIMARY KEY, "info"."host" VARCHAR, "info"."domain" VARCHAR, "usage"."core" INTEGER ) ;
No rows affected (0.064 seconds)
0: jdbc:phoenix:localhost:2181/hbase> select * from WEB_STAT_INFO ;
Error: ERROR 201 (22000): Illegal data. Expected length of at least 4 bytes, but had 2 (state=22000,code=201)
java.sql.SQLException: ERROR 201 (22000): Illegal data. Expected length of at least 4 bytes, but had 2
at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:442)
at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
at org.apache.phoenix.schema.KeyValueSchema.next(KeyValueSchema.java:211)
at org.apache.phoenix.expression.ProjectedColumnExpression.evaluate(ProjectedColumnExpression.java:115)
at org.apache.phoenix.compile.ExpressionProjector.getValue(ExpressionProjector.java:69)
at org.apache.phoenix.jdbc.PhoenixResultSet.getObject(PhoenixResultSet.java:524)
at sqlline.Rows$Row.<init>(Rows.java:157)
at sqlline.BufferedRows.<init>(BufferedRows.java:38)
at sqlline.SqlLine.print(SqlLine.java:1650)
at sqlline.Commands.execute(Commands.java:833)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:808)
at sqlline.SqlLine.begin(SqlLine.java:681)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:292)

Examples based on HDP 2.6.1

Note : I know that best practices recommend to create tables directly through Phoenix but in this particular use case the HBase table is used by an existing application.
The objective is to facilitate data access without having any rewrites to the existing application

1 ACCEPTED SOLUTION

Accepted Solutions

Re: When creating a Phoenix view over an existing Hbase table, is it possible to map values to anything other than "VARCHAR"

Unless you have explicitly written the data the first time in the byte-representation which Phoenix requires, you cannot treat the data as any other type than VARCHAR. This is not simply a "best practice" -- this fundamentally does not work when you write the data in a different representation than Phoenix expects.

2 REPLIES 2

Re: When creating a Phoenix view over an existing Hbase table, is it possible to map values to anything other than "VARCHAR"

Unless you have explicitly written the data the first time in the byte-representation which Phoenix requires, you cannot treat the data as any other type than VARCHAR. This is not simply a "best practice" -- this fundamentally does not work when you write the data in a different representation than Phoenix expects.

Highlighted

Re: When creating a Phoenix view over an existing Hbase table, is it possible to map values to anything other than "VARCHAR"

It's better you declare every field as VARCHAR and then use functions to convert them to numbers[1] for mathematical operations.

[1] https://phoenix.apache.org/language/functions.html#to_number

Don't have an account?
Coming from Hortonworks? Activate your account here