How can I make an INSERT with an SQL Query recipe?

ligia_tzab

According to the documentation( https://doc.dataiku.com/dss/latest/connecting/sql/bigquery.html#writing-data-into-bigquery ), the insert method is not optimal to add data to a Big Query (BQ) table. How can I make an insert with an SQL Query recipe?

The idea is the following:

A table is obtained from a datacenter through a query. This table has rows with specific dates. Then, this table is sent to BQ (let's call table A).

I want to make a INSERT of the rows of table "A" to another BQ table (table "B") using a SQL Query recipe. There is wanted to use the INSERT method because we don't want to process the data of table "B"; we want to keep these exactly as these are.

I already tried to do an INSERT in recipe but it return me an error:

Job Failed: com.dataiku.dss.shadelib.com.google.cloud.bigquery.bigqueryexception: Syntax Error: Unexpected insert target name at [3: 1], caused by: Bigqueryexception: Syntax Error: Unexpeted insert target name at [3 : 1], Caused by: GooglejsonSrSException: 400 BAD Request Post https: //www.googleapis.com/...........

I tested in the BIg Query environment the INSERT query that was in my recipe and it worked, so this isn't a syntax error.

Turribeach

I would suggest you just use BigQuery directly using the stardard BigQuery Python API:

https://cloud.google.com/python/docs/reference/bigquery/latest/index.html

Dataiku doesn't support this directly but t the end of the day it's Python code so it's as good as any other Python code you write. I would agree with Dataiku that this won't be the fastest way to insert rows but for small datasets it should be fine.

Marlan

Hi @ligia_tzab
,

Have you tried using a SQL Script recipe? I don't think a SQL Query recipe would work for INSERTs but a SQL Script recipe should.

We do INSERTs all the time in SQL Script recipes in Snowflake.

Marlan