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

[#1]

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

[#2 · Accepted solution]

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.

[#3]

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