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:

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:

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:

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!

--

--

articles are personal observations and not investment advice.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store