tBigQueryInput DML Update BigQuery

[geloofy]

Hi

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?

[mclee]

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.

[mclee]

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

[Anonymous]

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')

[Anonymous]

Hello @yliu,

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

Could you please have a look at it?

Best regards

Sabrina

[Anonymous]

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?

[Anonymous]

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