Many features added to bigquery, since last year. One of it is scripting capability. Scripting enable us to write complex logic, such variable declaration, logical operation, loop, exception handling, stored procedure, temporary table, etc. Implicitly scripting features also enable us to be able to execute multiple SQL statement on a single Query execution.
Scripting feature has broad range of application. As user we are not only able to write analytics query, now we are able to write complex ETL transformation and produce other information like metadata of the transformation itself. For example we can produce our own statistic such as how many partition that is modified, inserted, updated, or producing list of modified partitions.
For example if we have ETL script :
CREATE TEMP TABLE transformed_booking(id string; status string, created_time timestamp, updated_time timestamp);# complex transformation here# produced metadata
select count(1) as new_record_count from transformed_booking;
select date(created_time)) as modified_partition from transformed_booking GROUP BY modified_partition;
On a query execution, when the type of statement is a script bigquery internally spawn some child jobs for each SQL statement. To get the result of each scripts we need to get the child jobs. Once we able to get each job we can process each result to get our metadata, for example :
Scripting and stored procedures now in BigQuery | Google Cloud Blog
Scripting and stored procedures are now available in BigQuery to make queries and tasks easier to execute.