Hi Wei,

Have you searched JIRA for issues that relate to the Phoenix-Hive integration? There have been a few in the recent past around invalid queries being generated, especially around column names.


On 4/14/18 7:39 PM, Lu Wei wrote:
## Version:

phoenix: 4.13.2-cdh5.11.2

hive: 1.1.0-cdh5.11.2


There is an ColumnNotFoundException when joining a hive internal table with an Phoenix external table.



## Table1: phoenix external table "ext_tmp":

+-----------+------------+--------------------+--+

| col_name  | data_type  |      comment       |

+-----------+------------+--------------------+--+

| cola      | string     | from deserializer  |

| colb      | string     | from deserializer  |

+-----------+------------+--------------------+--+


### Backend Phoenix table "TMP":

select * from TMP;

*+-------+------------+*

*| **cola** |**colb** |*

*+-------+------------+*

*| *a * | *aaaa * |*

*| *b * | *bbaaaa * |*

*| *ccc * | *cccbbaaaa* |*

*+-------+------------+*

*
*

### Hive external table creation statement:


+----------------------------------------------------+--+

|                   createtab_stmt                   |

+----------------------------------------------------+--+

| CREATE EXTERNAL TABLE `ext_tmp`(                   |

|   `cola` string COMMENT 'from deserializer',       |

|   `colb` string COMMENT 'from deserializer')       |

| ROW FORMAT SERDE                                   |

|   'org.apache.phoenix.hive.PhoenixSerDe'           |

| STORED BY                                          |

|   'org.apache.phoenix.hive.PhoenixStorageHandler'  |

| WITH SERDEPROPERTIES (                             |

|   'serialization.format'='1')                      |

| LOCATION                                           |

|   'hdfs://st:8020/data/user/hive/warehouse/ext_tmp'|

| TBLPROPERTIES (                                    |

|   'phoenix.column.mapping'='cola:cola,colb:colb',  |

|   'phoenix.rowkeys'='cola',                        |

|   'phoenix.table.name'='tmp',                      |

|   'phoenix.zookeeper.client.port'='2181',          |

|   'phoenix.zookeeper.quorum'='st1,st2,st3',        |

|   'phoenix.zookeeper.znode.parent'='/hbase',       |

|   'transient_lastDdlTime'='1523607352')            |

+----------------------------------------------------+--+



## Table2: hive internal table "native1":

+-----------+------------+----------+--+

| col_name  | data_type  | comment  |

+-----------+------------+----------+--+

| cola      | string     |          |

| colb      | string     |          |

+-----------+------------+----------+--+



## When join the two tables:


select *  from native1  join  ext_tmp t on native1.cola= t.cola;



Exception:

org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined column. columnName=TMP


Detailed exception as below. There is an empty column "" as readcolumn name, which is not exsit in Pheonix table at all. so the phoenix query will never be correct: select /*+ NO_CACHE  */ "","cola","colb" from tmp where "cola" is not null


Any thoughts?



---------

2018-04-14 21:13:40,923 INFO  org.apache.hadoop.hive.ql.io.HiveInputFormat: [HiveServer2-Background-Pool: Thread-304]: hive.io.file.readcolumn.ids=

2018-04-14 21:13:40,923 INFO  org.apache.hadoop.hive.ql.io.HiveInputFormat: [HiveServer2-Background-Pool: Thread-304]: hive.io.file.readcolumn.names=,cola,colb

2018-04-14 21:13:40,923 INFO  org.apache.hadoop.hive.ql.io.HiveInputFormat: [HiveServer2-Background-Pool: Thread-304]: Generating splits

2018-04-14 21:13:40,924 INFO  org.apache.phoenix.hive.query.PhoenixQueryBuilder: [HiveServer2-Background-Pool: Thread-304]: Input query : select /*+ NO_CACHE  */ "","cola","colb" from tmp where "cola" is not null

2018-04-14 21:13:40,932 ERROR org.apache.phoenix.hive.mapreduce.PhoenixInputFormat: [HiveServer2-Background-Pool: Thread-304]: Failed to get the query plan with error [ERROR 504 (42703): Undefined column. columnName=TMP]



Thanks,

Wei

Reply via email to