CODEWITHSUNDEEP
google-bigquerysql-insertdynamic-tables
RM Baig
RM Baig

Reputation: 1

Dynamic Table name in BigQuery:

I am working with a live streaming data from GA4. GA4 creates a new table each day and dope the previous one, so i have only one day of data. I want to store a history of data so I need to create a new table and by scheduling a query insert the data into new created table. The problem I am facing right now is in creating a dynamic table name.

GA4 creates tables like today table name will be events_20230718 and tomorrow it will be events_20230719. So I have used format_date('%Y%m%d', date_sub(current_date(), interval 0 day)) to get the suffix of the table and used concatenation to create the name of the table. And I am getting the required table name on each day. You can see the proof in the following screenshot. Getting required table name.

However while using this table name in my query raises an error which says,

Invalid value: Table "source_table" must be qualified with a dataset (e.g. dataset.table). at [6:1]

The screenshot of the code is here:Source code

Upvotes: 0

Views: 1442

Answers (1)

Daryl Wenman-Bateson
Daryl Wenman-Bateson

Reputation: 3964

Store your query as a string and use EXECUTE IMMEDIATE [query_string] to execute the dynamic query.

e.g.

DECLARE source_table_name string DEFAULT 'project.dataset.sourceTableName';
DECLARE query STRING;
SET query = "INSERT INTO `project.dataset.table` SELECT * FROM `" || source_table_name || "` ";
EXECUTE IMMEDIATE query;

Upvotes: 1

Related Questions