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

ligia_tzab Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 9

According to the documentation( ), 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: 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: //

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
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,708 Neuron

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

    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
    Marlan Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Dataiku Frontrunner Awards 2021 Participant, Neuron 2023 Posts: 316 Neuron

    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.


Setup Info
      Help me…