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 !!!
