Vertica Hcatalog Integration issue - unable to query the data - Exception in prepare()
![[Deleted User]](https://us.v-cdn.net/6029397/uploads/defaultavatar/nD0LWW9MQTB29.jpg)
HI all,
I tried to create external table with HCatalog connector, Althought the Exteranl table which in Hive seems visable in Vertica, I could not query the data out, with below error ....
Error Message ---------------------------------------------- (query external table in Hive)
dbadmin=> SELECT * FROM store_ext.store_dimension_ext LIMIT 10;
ERROR 3399: Failure in UDx RPC call InvokePrepareUDL(): Error in User Defined Object [VHCatParser], error code: 0
Exception in prepare()
Error Message ----------------------------------------------- (create external table and query it)
dbadmin=> create external table t (a int, b int) as
dbadmin-> copy source VHCatSource(hive_server_port=13935, hive_server_host='DEMOP4', schema_name='hcat', table_name='t')
dbadmin-> parser VHCatParser(hive_server_port=13935, hive_server_host='DEMOP4', schema_name='hcat', table_name='t');
CREATE TABLE
dbadmin=>
dbadmin=> select * from t;
ERROR 3399: Failure in UDx RPC call InvokePlanUDL(): Error in User Defined Object [VHCatSource], error code: 0
Error message is [ org.apache.hive.hcatalog.common.HCatException : 2004 : HCatOutputFormat not initialized, setOutput has to be called. Cause : java.io.IOException: com.google.common.util.concurrent.UncheckedExecutionException: java.lang.RuntimeException: Unable to instantiate org.apache.hive.hcatalog.common.HiveClientCache$CacheableHiveMetaStoreClient ] HINT If error message is not descriptive or local, may be we cannot read metadata from hive metastore service thrift://DEMOP4:13935 or HDFS namenode (check UDxLogs/UDxFencedProcessesJava.log in the catalog directory for more information)
dbadmin=> drop table t;
DROP TABLE
/ Checking----------------------------------------------
------------------------ Some checking on system and UDx ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
dbadmin=> SELECT * FROM CONFIGURATION_PARAMETERS WHERE parameter_name = 'JavaBinaryForUDx';
-[ RECORD 1 ]-----------------+----------------------------------------------------------
node_name | ALL
parameter_name | JavaBinaryForUDx
current_value | /usr/bin/java
restart_value | /usr/bin/java
database_value | /usr/bin/java
default_value |
current_level | DATABASE
restart_level | DATABASE
is_mismatch | f
groups |
allowed_levels | NODE, DATABASE
superuser_only | f
change_under_support_guidance | f
change_requires_restart | f
description | Path to the java binary for executing UDx written in Java
dbadmin=> SELECT * FROM USER_LIBRARY_MANIFEST WHERE lib_name = '
-[ RECORD 1 ]---------------------------------------------------
schema_name | public
lib_name | VHCatalogLib
lib_oid | 58546795155851670
obj_name | com.vertica.hcatalogudl.WebHCatConfLoaderFactory
obj_type | Scalar Function
arg_types |
return_type | Varchar
-[ RECORD 2 ]---------------------------------------------------
schema_name | public
lib_name | VHCatalogLib
lib_oid | 58546795155851670
obj_name | com.vertica.hcatalogudl.HCatalogSplitsParserFactor
obj_type | Load Parser Function
arg_types |
return_type |
-[ RECORD 3 ]---------------------------------------------------
schema_name | public
lib_name | VHCatalogLib
lib_oid | 58546795155851670
obj_name | com.vertica.hcatalogudl.HCatalogSplitsNoOpSourceFa
obj_type | Load Source Function
arg_types |
return_type |
dbadmin=> SELECT * FROM HCATALOG_TABLES WHERE table_name = 'store_dimension_ext';
-[ RECORD 1 ]---------+-------------------------------------------------------------
table_schema_id | 58546795155851704
table_schema | store_ext
hcatalog_schema | store_ext
table_name | store_dimension_ext
hcatalog_user_name | dbadmin
min_file_size_bytes | 27102
total_number_files | 1
location | maprfs:/user/hive/warehouse/store_ext.db/store_dimension_ext
last_update_time | 2017-01-10 12:00:24.621+08
output_format | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
last_access_time | 2017-01-10 12:00:24+08
max_file_size_bytes | 27102
is_partitioned | f
partition_expression |
table_owner | mapr
input_format | org.apache.hadoop.mapred.TextInputFormat
total_file_size_bytes | 27102
hcatalog_group |
permission |
dbadmin=> SELECT * FROM v_catalog.hcatalog_table_list;
table_schema_id | table_schema | hcatalog_schema | table_name | hcatalog_user_name
-------------------+------------------+------------------+---------------------------+--------------------
58546795155851704 | store_ext | store_ext | store_dimension_ext | dbadmin
58546795155851704 | store_ext | store_ext | store_orders_fact_ext | dbadmin
58546795155851704 | store_ext | store_ext | store_sales_fact_ext | dbadmin
58546795155851710 | online_sales_ext | online_sales_ext | call_center_dimension_ext | dbadmin
58546795155851710 | online_sales_ext | online_sales_ext | online_page_dimension_ext | dbadmin
58546795155851710 | online_sales_ext | online_sales_ext | online_sales_fact_ext | dbadmin
58546795155851716 | public_ext | public_ext | customer_dimension_ext | dbadmin
58546795155851716 | public_ext | public_ext | date_dimension_ext | dbadmin
58546795155851716 | public_ext | public_ext | employee_dimension_ext | dbadmin
58546795155851716 | public_ext | public_ext | inventory_fact_ext | dbadmin
58546795155851716 | public_ext | public_ext | product_dimension_ext | dbadmin
58546795155851716 | public_ext | public_ext | promotion_dimension_ext | dbadmin
58546795155851716 | public_ext | public_ext | shipping_dimension_ext | dbadmin
58546795155851716 | public_ext | public_ext | vendor_dimension_ext | dbadmin
58546795155851716 | public_ext | public_ext | warehouse_dimension_ext | dbadmin
(15 rows)
Checking ----------------------------------------------- (query in hive)
hive> SELECT * FROM store_ext.store_dimension_ext LIMIT 10;
OK
1 Store1 1 16 Elm St Concord CA West Plan1 Premium None 1000 2000 NULL NULL 18 12576 39 2284
2 Store2 2 448 School St Columbia SC East Plan2 Premium Mortgage 200 2000 NULL NULL 47 27875 366 2844
3 Store3 3 224 Humphrey St Peoria AZ SouthWest Plan3 1 hr CheckCashing 400 1000 NULL NULL 12 28732 40 1704
4 Store4 4 240 School St Bellevue WA NorthWest Plan4 24 hr ATM 2400 8000 NULL NULL 44 25144 42 8960
5 Store5 5 199 Green St Topeka KS SouthWest Plan0 24 hr None 900 9000 NULL NULL 28 19830 166 9899
6 Store6 6 82 Church St New Haven CT East Plan1 24 hr ATM 800 4000 NULL NULL 30 11178 221 4736
7 Store7 7 125 Main St Berkeley CA West Plan2 1 hr Mortgage 600 6000 NULL NULL 15 19397 392 6968
8 Store8 8 299 Lake St Carrollton TX South Plan3 Premium Mortgage 900 3000 NULL NULL 50 24086 171 3153
9 Store9 9 222 Elm St Gary IN MidWest Plan4 DIY Bank 800 4000 NULL NULL 26 20974 40 4873
10 Store10 10 180 Maple St Vallejo CA West Plan0 1 hr Bank 2800 7000 NULL NULL 15 15643 91 7321
Time taken: 0.144 seconds, Fetched: 10 row(s)
/ Checking----------------------------------------------
Hope someone could share some insight on this error message to help troubleshoot it, Many Thanks !!!
Comments
Hi,
We need to review logs to analyze more about issue. Could you please open a support case ? we will be happy to help you
Sruthi