Where to put JAR files to be able to query HIVE external table (Elasticsearch)

[Pierre HENRY]

Hi,

I would like to insert values in a HIVE external table previously created within Hive with such query :

CREATE EXTERNAL TABLE es_test (

lastname STRING

)

STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'

TBLPROPERTIES(

'es.nodes' = 'mynode1, mynode2',

'es.resource' = 'ppe/test',

'es.index.auto.create' = 'true');

For this to work in hive, I have added elasticsearch-hadoop-6.1.2.jar to /usr/hdp/<version>/hive/auxlib folder (as explained here : https://community.hortonworks.com/questions/29283/ambari-hives-hiveauxjarspath-configuration.html)

Within KYLO, under "Feed Manager -> Tables", I can see this external table but when I try to see the content I have an error ... in kylo-services.log, I see :

2018-01-24 15:16:13 ERROR http-nio-8420-exec-3:ThrowableMapper:43 - toResponse() caught throwable

com.thinkbiganalytics.schema.SchemaParserException: Unable to describe schema [default] table [es_test]; nested exception is org.apache.hive.service.cli.HiveSQLException: MetaException(message:java.lang.ClassNotFoundException Class org.elasticsearch.hadoop.hive.EsSerDe not found)

        at com.thinkbiganalytics.schema.DBSchemaParser.describeTable(DBSchemaParser.java:250)

        at com.thinkbiganalytics.hive.service.HiveService.lambda$getTableSchema$7(HiveService.java:119)

I guess kylo needs extra JAR files ... Any idea which one and where do I need to copy them for Kylo to see them ?

Best regards,

Pierre

[Ruslans Uralovs]

For kylo to see the jar you'll need to put it into /opt/kylo/kylo-services/lib directory. Do make the jar readable by kylo user.

Potentially see additional configuration for ExecuteSparkJob processors described here for similar use case: https://github.com/Teradata/kylo/tree/master/integrations/input-format/input-format-omniture

[Pierre HENRY]

Hi Ruslans,

This is exactly what I did, I have placed the JAR in /opt/kylo/kylo-services/lib/ folder, but this is not enough :-( Are you able to make this working on your side ?

[Pierre HENRY]

Sorry, after restatrint hive server, I'm able to see the schema of such table. But trying a query like SELECT * FROM es_test LIMIT 10 is not working.

2018-01-24 17:39:00 ERROR http-nio-8420-exec-8:HiveRestController:175 - Error Querying Hive for query: SELECT * FROM es_repuation_db LIMIT 20

2018-01-24 17:39:00 ERROR http-nio-8420-exec-8:ThrowableMapper:43 - toResponse() caught throwable

org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [SELECT * FROM es_repuation_db LIMIT 20]; SQL state [null]; error code [0]; java.io.IOException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.apache.hadoop.io.LongWritable cannot be cast to org.apache.hadoop.io.IntWritable; nested exception is org.apache.hive.service.cli.HiveSQLException: java.io.IOException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.apache.hadoop.io.LongWritable cannot be cast to org.apache.hadoop.io.IntWritable

        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)

        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)

        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470)

        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:475)

        at com.thinkbiganalytics.schema.QueryRunner.query(QueryRunner.java:87)

        at com.thinkbiganalytics.hive.service.HiveService.lambda$query$8(HiveService.java:141)

        at com.thinkbiganalytics.kerberos.KerberosUtil.runWithOrWithoutKerberos(KerberosUtil.java:78)

        at com.thinkbiganalytics.hive.service.HiveService.query(HiveService.java:138)

        at com.thinkbiganalytics.hive.rest.controller.HiveRestController.queryResult(HiveRestController.java:170)

        at sun.reflect.GeneratedMethodAccessor878.invoke(Unknown Source)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:498)

[Ruslans Uralovs]

Can you run the same query directly in Hive?

[Pierre HENRY]

Yes I'm able to run the exact same query in hive.

[Ruslans Uralovs]

Cannot tell what is wrong. The error seems to come from Hive rather than from Kylo. 

Can you try selecting some specific fields to narrow down the issue rather than all of them with a * statement?

I would recommend enquiring on ElasticSearch forums about this error.

[Pierre HENRY]

I have investigated a bit further, and found that the query is working directly on Hive if using "mr" execution engine. If I configure Hive to use TEZ execution engine, it's not working either. I don't know if I need to configure something in TEZ, or in KYLO. Any help ?

Best regards,

Pierre

[Pierre HENRY]

Sorry, weird behavior in fact. I will investigate further and let you know.