How to load Xpath query output into hive table?

KNIME Extensions
1

This forum question come under Bigdata extension.

Hi Knime Team,

I have retrieved the element values from xml using Knime Xpath node. The output as follows,

Id   name  street  city   state

1    name1 str1    city1 state

2    name2  str2   city2 state

..

etc.,

 

now, how to load this Xpath output into hive tables?

Please, provide your suggestion in more detail. if any sample workflow available, it will be more helpful.

Thanks in advance.

 

Best Regards,

Velu

2

Hi Velu,

for a long time Hive did not support insertion of records. That is why we provide the Hive Loader node in our commercial KNIME Big Data Extension which performs a sort of batch insertion into a Hive table. However starting with Hive 0.14 Hive also seems to support Insertion so you could try to use the Database Connector node and the Database Writer node to insert the values into a Hive table. To use the Database Connector node with Hive you have to register the Hive jdbc driver and add all required libraries into the class path. For details about this have a look at this forum post.

Bye,

Tobias

3

Hi Tobias,

Thanks for your reply.

Getting following warining while I have tried with Database connector and Database writer to insert the values into hive table. 

"WARN  Database Writer      0:36       Connected database does not support insert operations"

In addition, I have added all the jars as mentioned in the forum post link.(under \jre\lib\ext directory).

Similarly, tried with hive loader and getting following error.

ERROR Hive Loader          5:6        Execute failed: Could not create ResultSet: Required field 'operationHandle' is unset! Struct:TGetResultSetMetadataReq(operationHandle:null)

Is there any other way available to achieve this?

Kindly, provide your suggestion on this.

 

Best Regards,

Velu

 

 

4

Hi Velu,

to allow insertions you have to change the database type in the "Advanced" tab of the Database Connector node to default since the hive2 type does not allow them.

The error of the Hive Loader is strange. Can you have a look in the KNIME log file if you can find more information on it. To open the KNIME log file open KNIME and go to View open KNIME Log.

Thanks

Tobias

5

Hi Tobias,

Thanks for your reply.

I have changed the database type as default and the warning has disappeared now.

After that, while I try to execute the Database Writer node, It went to green with exclamation symbol.

Getting the following warnings in the console.

WARN  Database Writer      5:36       Existing table "table_velan" will be dropped!
WARN  Database Writer      5:36       Error while adding row #1 (Row8531_1), reason: Error while compiling statement: FAILED: ParseException line 1:12 missing TABLE at 'table_velan' near 'table_velan' in statement
line 1:24 cannot recognize input near '(' 'add_resp_request_id' ',' in statement
WARN  Database Writer      5:36       Error while adding row #2 (Row8570_1), reason: Error while compiling statement: FAILED: ParseException line 1:12 missing TABLE at 'table_velan' near 'table_velan' in statement
line 1:24 cannot recognize input near '(' 'add_resp_request_id' ',' in statement
WARN  Database Writer      5:36       Error while adding row #3 (Row8574_1), reason: Error while compiling statement: FAILED: ParseException line 1:12 missing TABLE at 'table_velan' near 'table_velan' in statement
line 1:24 cannot recognize input near '(' 'add_resp_request_id' ',' in statement
WARN  Database Writer      5:36       Error while adding row #4 (Row8740_1), reason: Error while compiling statement: FAILED: ParseException line 1:12 missing TABLE at 'table_velan' near 'table_velan' in statement
line 1:24 cannot recognize input near '(' 'add_resp_request_id' ',' in statement
WARN  Database Writer      5:36       Error while adding row #5 (Row14418_1), reason: Error while compiling statement: FAILED: ParseException line 1:12 missing TABLE at 'table_velan' near 'table_velan' in statement
line 1:24 cannot recognize input near '(' 'add_resp_request_id' ',' in statement
WARN  Database Writer      5:36       Errors "5" writing 5 rows

 

Still, Is there anything I missed in the configuration? Please, suggest me to achieve this.

Please, find the attached configuration screenshot and knime log.

 

Thanks & Regards,

velu

 

 

6

Hi,

This is resolved now. Thanks for the support.

Regards,

velu

7

Hi,

How did you solve this problem. i have the same error while using database writer.

Thanks,

Navya