topic Re: Run Multiple Queries in Thive Row Component in Talend Studio

Run Multiple Queries in Thive Row Component

talensn — Sat, 16 Nov 2024 09:19:27 GMT

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

Re: Run Multiple Queries in Thive Row Component

Anonymous — Thu, 07 Sep 2017 08:53:49 GMT

Hello ,

Can you try this

"BEGIN

DROP TABLE... ;

CREATE TABLE ;

DROP TABLE ;

CREATE TABLE ;

DROP TABLE ;

CREATE TABLE ; [...]

COMMIT;

END;"

Good luck,

SGV

Re: Run Multiple Queries in Thive Row Component

talensn — Thu, 07 Sep 2017 15:43:48 GMT

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

Re: Run Multiple Queries in Thive Row Component

Anonymous — Thu, 07 Sep 2017 16:08:15 GMT

What the type of you db source ?

Re: Run Multiple Queries in Thive Row Component

talensn — Thu, 07 Sep 2017 16:13:46 GMT

Hue-Hive.

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

Re: Run Multiple Queries in Thive Row Component

Anonymous — Thu, 07 Sep 2017 19:35:45 GMT

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.

Re: Run Multiple Queries in Thive Row Component

KarthikGs — Fri, 10 Nov 2017 07:26:26 GMT

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

Re: Run Multiple Queries in Thive Row Component

Anonymous — Fri, 10 Nov 2017 13:15:56 GMT

try this.

Re: Run Multiple Queries in Thive Row Component

KarthikGs — Sat, 11 Nov 2017 07:53:02 GMT

@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?

Re: Run Multiple Queries in Thive Row Component

Anonymous — Tue, 14 Nov 2017 09:52:33 GMT

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,

Re: Run Multiple Queries in Thive Row Component

Anonymous — Tue, 14 Nov 2017 17:13:46 GMT

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

Re: Run Multiple Queries in Thive Row Component

KarthikGs — Thu, 16 Nov 2017 07:33:56 GMT

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

Re: Run Multiple Queries in Thive Row Component

KarthikGs — Tue, 12 Dec 2017 07:03:47 GMT

any updates?

Re: Run Multiple Queries in Thive Row Component

Anonymous — Thu, 12 Jul 2018 17:42:53 GMT

Any updates, new workaround?

A think this is a very important topic.

Thanks!