Project Overview in BigQuery with Dynamic SQL

How to easily identify obsolete tables within your project in BigQuery? Answer: Dynamic SQL!

The Problem

How to get an overview of a project in BigQuery (existent tables with their creation / last updated dates, size, etc.)?

In order to build this query, it’s necessary to have all the dataset names. Datasets can be deleted or created at any time, therefore, it’s not a good idea to handle these changes manually.

So, how to deal with this problem using SQL? Answer: Dynamic SQL!

Ok… But what is Dynamic SQL?

https://cloud.google.com/blog/topics/developers-practitioners/smile-new-user-friendly-sql-capabilities-bigquery

Building the Query String

The steps are described as following:

• Query the datasets names ( SCHEMATA );
• Build the string ( CONCAT or || ) to query tables metadata ( TABLES: additional data requires .__TABLES__);
• Build the string ( STRING_AGG ) to query all the tables metadata ( UNION ALL );

String built with CONCAT function

String resultant

String built with concatenation operator ||

Final Query

This query generates a table that feeds a dashboard on Google Data Studio.

Results

References

Procedural language | BigQuery | Google Cloud

Execute Immediate and other new user-friendly SQL capabilities in BigQuery | Google Cloud Blog

Introduction to BigQuery INFORMATION_SCHEMA | Google Cloud