Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Vertica Hcatalog Integration issue - unable to query the data - Exception in prepare()

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.