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

mlamairesse — Mon, 04 Sep 2017 20:36:06 GMT

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

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

elserj — Tue, 05 Sep 2017 21:45:33 GMT

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.

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

asinghal — Thu, 07 Sep 2017 16:58:16 GMT

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

question When creating a Phoenix view over an existing Hbase table, is it possible to map values to anything other than "VARCHAR" in Archives of Support Questions (Read Only)

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

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

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