GUI Mode for BigQuery not using proper query structure

Queries and Resources
1

Hi, i am building some Retool apps that connect to BigQuery to fetch and insert data.
I want to use the GUI Mode to make setting up new operations more efficient. However, i am stuck as there is an error that i encounter no matter what type of action i try to do.

Table "table_name" must be qualified with a dataset (e.g. dataset.table).

Looking into the error i get:

  • statusCode:422
  • error:"Unprocessable Entity"
  • message:"insert into table_name (field1, field2, field3) values ('val1', 'val2', 'val3') - Table "table_name" must be qualified with a dataset (e.g. dataset.table)."
  • data:null
  • isRetoolSystemError:false

It looks like Retool is trying to query the table without the dataset prefix? (it should be more like "insert into dataset.table_name")
In the dropdown where i select a table, it does correctly show it with dataset prefix, ie. dataset.table_name

As mentioned it does not matter which type of action i try or which table i select, i always get this same error when trying to run it.
I don't see anywhere to change any settings or add the dataset name so im really stuck on how to get this working.

Thanks

3

Hello there,

I have gone through your post and in my opinion you can manually add the dataset prefix to your table name in the SQL query.

Try this: insert into dataset.table_name (field1, field2, field3) values ('val1', 'val2', 'val3')

Make sure to replace dataset with your actual dataset name in BigQuery. This should resolve the error you're encountering.

4

Hi, thanks for the reply.

This is in the GUI Mode, i don't see any options to customize the query here, or am i missing something?

5

Are you able to read from this table in SQL mode? Is the table name listed in the schema tab of the query?

Sometimes, we've seen errors like this in relation to the db region. Retool will only fetch the database schemas for datasets located in a single region. If you want to use the autocomplete or GUI mode for multiple regions - your best bet would be to create a new resource in Retool using the same credentials and then select a different Region qualifier in the resource page.