How to push data to bigquery

[roger]

Hello,

I want to create an app where I get data from an API and push the same data to a bigquery table.
I cant figure out how to send the data to bigquery.
My data is json type.

Example data:

  "rows": [
    {
      "id": "d38c92bd-6477-4a60-acfe-b4dfa43d92a3",
      "ticker": "link",
      "version": null,
      "amount": "0.064688",
      "createdAt": "2022-03-23T17:40:05.792Z",
      "status": "SUCCESS",
      "fee": "0",
      "type": "SAVING_EARN",
      "transactionId": "5eed363e-7d15-4cd0-b539-f5c95921bf4c",
      "txHash": null
    },
    {
      "id": "ae47b518-a884-493a-81fc-d50c9f053db8",
      "ticker": "ada",
      "version": null,
      "amount": "0.973114",
      "createdAt": "2022-03-23T17:40:05.792Z",
      "status": "SUCCESS",
      "fee": "0",
      "type": "SAVING_EARN",
      "transactionId": "5eed363e-7d15-4cd0-b539-f5c95921bf4c",
      "txHash": null
    }
]
}

I tried with bigquery ressource query:
insert into dataset.retool_table values {{JSON.stringify(query3.data)}}
but get the error : message:"Braced constructors are not supported at [1:48]"

[jocen]

Hi @roger, welcome to the retool community!

If you are using SQL mode, you want to make sure your syntax fits how BigQuery wants it, i.e.:

INSERT dataset.Inventory (product, quantity)
VALUES('top load washer', 10),
      ('front load washer', 20),
      ('dryer', 30),
      ('refrigerator', 10),
      ('microwave', 20),
      ('dishwasher', 30),
      ('oven', 5)

What you are trying to insert has curly brackets which the error has pointed out.

If you feel lazy to do the transformation to fit the SQL syntax, I would suggest you change it to GUI mode and use Bulk Insert Records action type. Based on your example data above, you can chuck in {{query3.data.rows}} or just {{query3.data}} to the "Array of records to insert" field. You have to be sure that the keys in your data matches the table names of dataset.retool_table for this to work, otherwise, some of your columns will be empty/have null values.

Let me know how you go about with this

[roger]

Hi @jocen, thanks for the help, this solution seems to fit my needs.
However I can't understand why the "GUI" option is greyed out !!

[jocen]

Hi @roger, does your credential have write access to bigquery? check the scope you have with your google cloud credentials.

[roger]

I've created 2 bigquery ressources queries with the same credentials, the second one could not access GUI mode. I've rolled back to only one and it works.

And @jocen thanks for the solution, it works as expected and I can now push my data to bigquery!
Have a good one

[Ali_Ashfaq]

HI @roger ,
In reference to your case:

Can you please let me know how can I achieve this?

Background: i want to insert data to Bigquery table from the retool table table component in SQL mode.

[victoria]

Hey @Ali_Ashfaq! If you just want to insert records from a Retool Table component, could you try something like this?

1. Change the query type from SQL mode -> GUI mode
2. Select the "Bulk insert records" action type
3. Pass in {{table1.data}} as the array of records to insert