Unable to query Hive 3 data from PXF: Your client does not appear to support insert-only tables
Article ID: 296368
Updated On:
Products
Issue/Introduction
Unable to execute SQL select query SELECT * FROM hive_table_name; due to ERROR: remote component error (500) from '127.0.0.1:5888': Type Exception Report Message javax.servlet.ServletException: MetaException(message:Your client does not appear to support insert-only tables. To skip capability checks, please set metastore.client.capability.check to false. This setting can be set globally, or on the client for the current metastore session. Note that this may lead to incorrect results, data loss, undefined behavior, etc. if your client is actually incompatible. You can also specify custom client capabilities via get_table_req API.) Description The server encountered an unexpected condition that prevented it from fulfilling the request. Exception javax.servlet.ServletException: javax.servlet.ServletException: MetaException(message:Your client does not appear to support insert-only tables. To skip capability checks, please set metastore.client.capability.check to false. This setting can be set globally, or on the client for the current metastore session. Note that this may lead to incorrect results, data loss, undefined behavior, etc. if your client is actually incompatible. You can also specify custom client capabilities via get_table_req API.) (libchurl.c:935) (seg36 slice1 172.16.10.26:4000 pid=93248) (libchurl.c:935)
Resolution
PXF does not support Hive 3 managed tables (CRUD transactional, insert-only transactional, or temporary). It only supports Hive 3 external tables, where data is known to Hive but is not directly managed by it. See the note in Reading Hive Table Data.
More details on Hive 3 table types can be found in the following resource: Apache Hive 3 tables
Note: Changing the metastore.client.capability.check as suggested on the error output should not be done and will result in incomplete data being returned.
Possible workarounds:
-
Re-create the Hive table as an external table and PXF should be able to read from it.
-
For managed tables, it is still possible to read from them via PXF JDBC using the JDBC profile to connect to HiveServer2. Note that performance will likely be worse than simply using the PXF Hive profile because all the data will go via HiveServer2.
-
Export the data into Hive external tables or HDFS files or use Spark to read them and use Greenplum Spark Connector to load them into Greenplum.
Feedback
