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

[Deleted User]

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

SruthiA

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