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

Four Stars

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

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.i...

 

Please help me.

 

Thanks in Advance,

Chandra

Moderator

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

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Highlighted
Four Stars

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

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. 

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog