topic Re: tBigQueryInput DML Update BigQuery in Talend Studio

tBigQueryInput DML Update BigQuery

geloofy — Sat, 16 Nov 2024 08:33:57 GMT

I'm running a big query update DML command using the tBigQueryInput component.

The command executes successfully but the component doesn't stop running or move onto the next task. It's as if it is waiting for a resultset from the DML command however there is no result because it is an UPDATE.

Does anyone have experience with this?

Re: tBigQueryInput DML Update BigQuery

Anonymous — Tue, 20 Mar 2018 08:42:27 GMT

Hello,

Could you please indicate on which build version you got this issue? What does your job design look like? Screenshots will be preferred.

Best regards

Sabrina

Re: tBigQueryInput DML Update BigQuery

geloofy — Tue, 20 Mar 2018 19:53:24 GMT

Hi Sabrina

I'm using Talend Open Studio for Big Data Version: 6.5.1.20180116_1512

tBigQueryInput executes a DML statement, eg. delete from table where 1=1

This runs against BQ and deletes all data in a table but tBigQueryInput doesn't finish. I have to use tJavaRow to kill the process using this piece of code: System.exit(99);

Re: tBigQueryInput DML Update BigQuery

Anonymous — Thu, 22 Mar 2018 08:44:56 GMT

Hello,

Could you please try to use tBigQueryInput with a Query, and include: "#standardSQL\n" then the query you wish to execute to see if it works?

Here are some links for reference:

https://cloud.google.com/bigquery/docs/updating-data
https://cloud.google.com/bigquery/docs/reference/standard-sql/

Best regards

Sabrina

Re: tBigQueryInput DML Update BigQuery

geloofy — Thu, 22 Mar 2018 17:32:47 GMT

Hi Sabrina

Sorry I didn't mention that I am already executing the commands using "#standardSQL\n".

Unfortunately I have the same issue with or without the "#standardSQL\n".

Regards

Geoff

Re: tBigQueryInput DML Update BigQuery

Anonymous — Mon, 26 Mar 2018 04:35:03 GMT

Hello,

Would you mind posting your tBigQueryInput DML statement here?

Best regards

Sabrina

Re: tBigQueryInput DML Update BigQuery

geloofy — Mon, 26 Mar 2018 05:22:19 GMT

Hi Sabrina

Here is an example of a DML that I'm running:

"#standardSQL\r\n
INSERT INTO `sdk.app` (_id,package_name)
SELECT S.*
FROM `sdk.appDelta` AS S
LEFT JOIN `sdk.app` AS D
ON S._id = D._id
WHERE D._id IS NULL"

Re: tBigQueryInput DML Update BigQuery

Anonymous — Mon, 26 Mar 2018 08:52:11 GMT

Hello,

It seems that this is something that the Spark API doesn't offer yet. We will make an investigation on this issue to see if there is any workaround and will keep you posted.

Best regards

Sabrina

Re: tBigQueryInput DML Update BigQuery

geloofy — Mon, 26 Mar 2018 20:53:14 GMT

Thank you! Hope to hear back from you soon.

Re: tBigQueryInput DML Update BigQuery

Anonymous — Wed, 28 Mar 2018 07:19:12 GMT

Hello,

Here exists a new feature jira issue:https://jira.talendforge.org/browse/TBD-3547.

Feel free to vote for it.

Best regards

Sabrina

Re: tBigQueryInput DML Update BigQuery

mclee — Tue, 09 Oct 2018 08:18:53 GMT

Hi All,

I have the similar issue as well. Executing BigQuery DML using tBigQueryInput component.

The statement has been executed successfully but the process just won't move on to the next component.

I am using Talend Integration Cloud 7.0.1, and below is my DML statement: -

"#standardSQL\n
INSERT `data-mgmt-191603.BL_ODS_DL.dl_PassengerJourneySegment_History` (PassengerID, SegmentID, DepartureDate, DepartureStation, ArrivalStation, JourneyNumber, SegmentNumber, ClassOfService, TripType, CreatedDate, ModifiedDate, DeleteFlag, ETLModifiedDate, FareJourneyType, ProductClassCode, LoadedDate,RowType)
SELECT src.PassengerID, src.SegmentID, CAST( src.DepartureDate AS TIMESTAMP),src.DepartureStation , src.ArrivalStation, src.JourneyNumber, src.SegmentNumber, src.ClassOfService, src.TripType, src.CreatedDate, src.ModifiedDate, upd.DeleteFlag, src.ETLModifiedDate, src.FareJourneyType, src.ProductClassCode, src.LoadedDate,'Updated with Reference Column' FROM `data-mgmt-191603.BL_ODS_DL.dl_PassengerJourneySegment` src,`data-mgmt-191603.BL_ODS_STG.stg_PassengerJourneySegment_update` upd
WHERE Concat(CAST(src.PassengerID AS STRING),'A',CAST(src.SegmentID AS STRING))=Concat(CAST(upd.PassengerID AS STRING),'A',CAST(upd.SegmentID AS STRING))"

Is there no solution yet?

Regards.

Re: tBigQueryInput DML Update BigQuery

mclee — Tue, 09 Oct 2018 11:00:14 GMT

Hi All,

I face the same issue, I wrote a big query INSERT DML command using the tBigQueryInput component.

The command executes successfully where I can see the target table is inserted with data but the component doesn't stop running or move onto the next task too.

Is there a resolution yet?

Regards,

MC Lee

Re: tBigQueryInput DML Update BigQuery

Anonymous — Wed, 17 Oct 2018 20:50:53 GMT

Hi ,

I am writing a similar INSERT statement but i receive an error everytime as:

---> Reason: invalidQuery
Message: Cannot set destination table in jobs with DML statements

Even a simple insert like below gives me the same error:

INSERT INTO `SurveyMonkey.Survey_Monkey_Customer_Names`(person_customer_id,person_customer_name)
VALUES (1234,'test1'),(2345,'test2')

Re: tBigQueryInput DML Update BigQuery

Anonymous — Mon, 22 Oct 2018 05:43:56 GMT

Hello @yliu,

We have replied to your another topic:https://community.talend.com/t5/Design-and-Development/tbigqueryinput-support-for-DML-statements/m-p/137695/highlight/false#M85578

Could you please have a look at it?

Best regards

Sabrina

Re: tBigQueryInput DML Update BigQuery

Anonymous — Mon, 22 Oct 2018 16:05:06 GMT

I can see that people are running insert statements in this thread but i am not able to do so. Can people help me if there needs to be some adjustment at BigQuery or Talend required apart adding #StandardSQL on top of the query?

Re: tBigQueryInput DML Update BigQuery

Anonymous — Fri, 14 Jun 2019 12:04:23 GMT

Hi,

I see a similar issue on version 6.5.1. I am using tBigQueryInput component to execute a DELETE statement on BigQuery. As suggested in one of your posts, I also used #standardSQL\n before my DELETE query.

I can see that query execute fine and deletes the data on BigQuery. But the job never ends or goes to the next Component.

Please help.

Regards,

Karna