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')

This call provisioned everything in our BigQuery project that we needed for the demo.

Try it!

You will see that a dataset named demods will get created in your project, and the dataset will contain stuff that is needed for the demo:

How does this magic work?

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;

The first query in the script creates the dataset:

CREATE SCHEMA

and the second query creates a table and populates it with data by selecting the necessary rows:

CREATE OR REPLACE TABLE

Obviously, this trick is useful beyond creating demo environments. You can also schedule the CALL, although a Materialized View/Table is a better solution for that.

It’s fun to work with brilliant people! Enjoy!

Data Analytics & AI @ Google Cloud