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)
BEGIN
EXECUTE 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!