Quickly create BigQuery demo/dev environments using SQL

A neat trick that uses Stored Procedure with a BigQuery script

My colleague Polong Lin did a brilliant thing recently. He wanted to do a demo, and rather than ask us to follow a checklist of instructions, he simply had us make a single BigQuery call:

CALL 
`ai-analytics-solutions`.publicdata.setup_flights_demo(
'MY-PROJECT-NAME', 'demods')

Stored Procedure with a Script

The idea is to create a Stored Procedure that contains a script. The script creates the dataset, and makes other SQL calls. In this case, I’m using EXECUTE IMMEDIATE to create the query dynamically:

CREATE OR REPLACE PROCEDURE publicdata.setup_flights_demo (
projectname STRING, datasetname STRING)
BEGINEXECUTE IMMEDIATE FORMAT("""
CREATE SCHEMA `%s`.%s
OPTIONS(
default_table_expiration_days=1.5,
labels=[("purpose","demo"),("pii","none")]
);
""", projectname, datasetname);

EXECUTE IMMEDIATE FORMAT("""
CREATE OR REPLACE TABLE `%s`.%s.num_flights_by_airport AS
SELECT
airline, departure_airport, COUNT(*) AS num_flights
FROM
`bigquery-samples.airline_ontime_data.flights`
GROUP BY 1, 2;
""", projectname, datasetname);
END;
CREATE SCHEMA
CREATE OR REPLACE TABLE

Data Analytics & AI @ Google Cloud