Four Stars

Run Multiple Queries in Thive Row Component

Hi Team,

 

Could someone please help me through understand, how to run the sequential SQL queries in Thive Row Component?

 

For Example: 

 

DROP TABLE IF EXISTS abcd.table1;

CREATE TABLE abcd.table1;

 

Something like this, which we generally do. 

 

Thanks,
Santosh

11 REPLIES
Seven Stars sgv
Seven Stars

Re: Run Multiple Queries in Thive Row Component

Hello ,

Can you try this

 

"BEGIN

DROP TABLE... ;

CREATE TABLE ;

DROP TABLE ;

CREATE TABLE ;

DROP TABLE ;

CREATE TABLE ; [...]

COMMIT;

END;"

 

Good luck,

SGV

Four Stars

Re: Run Multiple Queries in Thive Row Component

Thanks for your help, but it is still throwing an error. Please find below for the error.

ERROR:
Error while compiling statement: FAILED: ParseException line 1:0 cannot recognize input near 'BEGIN' 'drop' 'table'
at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:235)
at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:221)
at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:244)
at
Seven Stars sgv
Seven Stars

Re: Run Multiple Queries in Thive Row Component

What the type of you db source ?

Four Stars

Re: Run Multiple Queries in Thive Row Component

Hue-Hive.

I am able to execute single statements but not in combination with multiple queries separated by ';'
Highlighted
Six Stars

Re: Run Multiple Queries in Thive Row Component

You can always hook up either a tForEach or a tFixedFlowInput in front of the tHiveRow, where each value is a separate query then just pass that object (row.column for tFixedFlowInput, or ((String)globalMap.get("tForeach_1_CURRENT_VALUE") for tForEach) into the tHiveRow query block.

Five Stars

Re: Run Multiple Queries in Thive Row Component

@evansdar Nah, not working. Can you share a snapshot of your subjob?

Six Stars

Re: Run Multiple Queries in Thive Row Component

try this. Untitled.png

Five Stars

Re: Run Multiple Queries in Thive Row Component

@evansdar thanks man!

I was doing syntactical errors in tforeach, now it works.

But i think there is a limitation while using tForeach component with tHiverow, i'm not able to pass a string bigger than 130 characters in tForeach as a value. I tried to create a table with more columns, but the value column cannot take more than 130 characters.

Is there any workaround for this?

Capture.PNG

Seven Stars

Re: Run Multiple Queries in Thive Row Component

If storing queries in a flat file is ok, then you can use a tFileInput component along with the tflowtoiterate component as a workaround. Something like this,

job.png

 

Six Stars

Re: Run Multiple Queries in Thive Row Component

You could also just store them in a String or context, although depending on size + amount it may be hard to manage

Five Stars

Re: Run Multiple Queries in Thive Row Component

that is not how i want to do, can we run multiple queries by using any "additionalJDBCparameters" in hive connection and execute queries?
As, we can use "allowMultileQueries=true" in additionalJDBCparameters for other relational dbs components