BigQuery Cursor equivalent and Dynamic SQL.
In Oracle, cursors plays major role in PL/SQL blocks or Procedures to iterate through the table or datasets, there was always a question how the same can be implemented in BigQuery SQL specially while writing procedural language code, so this can be achieved by using for — do — end for loop. Lets understand this more in detail.
Say, for example you want to iterate through all the tables in your dataset and take the count for each table, in oracle we will simply write a cursor to achieve this. Here in BigQuery, we have a cursor equivalent iterator which can be used to do the same with the help of Dynamic SQL.
for record in
(select table_name from dataset.INFORMATION_SCHEMA.TABLES)
do
execute immediate format(“”” select count(*) from `project_name.dataset_name.%s`”””,record.table_name);
end for;
Here we are selecting each table (table_name) from information schema table, then inside the do-end for loop passing the table name as dynamic value to the select count(*) statement using %s. This will iterate in a loop until it reaches the last table (table_name) for the dataset selected, you can then print or export to a dummy table to view the output.
Similarly the above code can be modified as per the use case of iterating through a table or dataset for performing the required validations.
Another example of using dynamic SQL is Execute Immediate Using clause.
for record in
(select table_name from dataset.INFORMATION_SCHEMA.TABLES)
do
execute immediate format(“”” select count(*) from `project_name.dataset_name.%s like @str”””,record.table_name using ‘%temp’ as str);
end for;
The above code iterates through the list of tables from Information Schema table for given dataset, it then selects count for the table where the table_name end with temp. So here we used, using clause for the wild character matching. You can also do positional variables using question marks if you have more than one condition.
EXECUTE IMMEDIATE “””
SELECT *
FROM `bigquery-public-data`.dataset_name.table_name
WHERE country_region LIKE ?
ORDER BY cases DESC LIMIT ?
“””
USING ‘UK’, 2;
To conclude, both iterating using do loop and dynamic sql can be used together to get the desired output. These were few simple examples to illustrate how loops and Dynamic SQL works in BigQuery SQL.
Hope this helps!
