Quickly create BigQuery demo/dev environments using SQL
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:
This call provisioned everything in our BigQuery project that we needed for the demo.
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
""", projectname, datasetname);
EXECUTE IMMEDIATE FORMAT("""
CREATE OR REPLACE TABLE `%s`.%s.num_flights_by_airport AS
airline, departure_airport, COUNT(*) AS num_flights
GROUP BY 1, 2;
""", projectname, datasetname);END;
The first query in the script creates the dataset:
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!