topic Re: How to run DML statements Insert, Update, Delete in Talend Open Studion with Google BigQuery in Talend Studio

How to run DML statements Insert, Update, Delete in Talend Open Studion with Google BigQuery

Anonymous — Sat, 16 Nov 2024 07:01:15 GMT

Hi,

I am new to Talend & BigQuery.

I tried below steps.

1) I am able to load data using tBigQueryinput & tBigQueryOutput components. Here i filled all required fields like below

Authentication Mode : Service Account

Service account credential file (json file)

Project ID, Dataset, Table

Google storage configuration (Access Key, Secret Key, Bucket ,File)

Using above details i am able to load the data(Ref.Manual_Connection.gif).

But if i am keeping Insert or Update Query field in tBigQuery, It throws the error. Can we run Insert/Update in simple transformation?

2) Tried using simba JDBC drivers given by google cloud(Below are provide links)

https://cloud.google.com/bigquery/partners/simba-drivers/

Checked both JDBC 4.1-compatible & JDBC 4.2-compatible drivers. I am able to read/export data into flat files

but not able to write data into Big Query tables. Please help me on it.

Created jdbc connection string like this(Ref:JDBC Drivers_Connection). Here we are able to connect biqquery metadata and able to drag tables into Components page. But while running using below jdbc connection getting error

[Simba][JDBC](10220) Driver does not support this optional feature.

JDBC URL jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=xyz;OAuthType=0;OAuthServiceAcctEmail=xyz@xyz.iam.gserviceaccount.com;OAuthPvtKeyPath=D:\\BigQuery\\Talend\\xyz-asdfghdf456.p12

Please help me.

Thanks in Advance,

Chandra

Re: How to run DML statements Insert, Update, Delete in Talend Open Studion with Google BigQuery

Anonymous — Wed, 19 Dec 2018 06:37:27 GMT

Hello,

Our Google BigQuery components rely on Google "SQL". This is a subset of SQL, that limits actions to simple selections.

Google now offers "Standard SQL" in their Data Manipulation Language (DML), which features extended actions (such as insert, update, delete operations).

For some examples of their DML cf. https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax

Here exists a jira issue:https://jira.talendforge.org/browse/TBD-5959.

Best regards

Sabrina

Re: How to run DML statements Insert, Update, Delete in Talend Open Studion with Google BigQuery

Anonymous — Wed, 19 Dec 2018 10:52:22 GMT

Hi Sabrina,

As per Jira,

To run Run this job: <BigQueryInput> + RowComp (create_table, drop_table, trunc_table) + Output (including sub-queries when applicable.

To define Row(tDBRow) Component, We need to define database component. Using Simba jdbc drivers, created jdbc connectivity and trying to write data into the BigQuery. Which was mentioned in step2 in the 1st communication. Since I am new to talend, am I doing correct way. Using same drivers I was able to write data from Pentaho open etl tool. But from talend, I am not able to write data. Please correct me if am doing any wrong.