How to push data to bigquery

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]"

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

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 !!

image

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

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