BigQuery dynamic SQL and managing temp tables

Google introduced support for dynamic SQL in BigQuery. Developers working particularly in Oracle must have some liking for EXECUTE IMMEDIATE, the way you execute dynamic SQL queries. Such a feature in BigQuery was missing for a long time, and now that it is here, I can't wait to use it.

Choosing a problem statement

Let's choose a problem that easily resonates with every developer working with the Google BigQuery world. Who isn't noticing the large volume of temporary tables churned by the client drivers and large datasets. This is particularly true where downstream products implement a version of BigQuery driver and fail to leverage nice features like auto expiration of tables. Not so good part is the hygiene of the dataset, these tables stay forever until explicitly cleared. What is important for this blog is a problem statement to demonstrate the utility of dynamic SQL.

Lets address it using Dynamic SQL

Temporary tables do offer the convenience of caching large result sets. With data rapidly changing on BigQuery dataset, let us target the old temporary tables and remove those from the datasets.

• Our primary goal is to clear all temporary tables older than 24 hours.
• Achieving this goal needs some more information. We need to identify when a table was created. This is when INFORMATION_SCHEMA of BigQuery is helpful.
• Last step is that I want this to be scheduled every day, without my intervention. Yes, you can schedule SQL statements using the BigQuery scheduled query feature.

To clear temporary tables across all datasets, let's write code employing dynamic SQL, iterate all the dataset using the INFORMATION_SCHEMA and delete the temp table using the timestamp and the name starting with temp_table_. And schedule the SQL code using the BigQuery scheduled query option. With this, all the temp tables that are older than 1 day should get automatically cleared at a daily cadence.

	BEGIN
	-- Iteratively search for temp tables before today and clear them
	DECLARE project_id string;
	DECLARE tables_list ARRAY<string>;
	DECLARE dataset_list ARRAY<string>;
	DECLARE table_counter INT64 DEFAULT 0;
	DECLARE table_count INT64;
	DECLARE dataset_count INT64;
	DECLARE dataset_counter INT64 DEFAULT 0;
	-- Set your project id here
	SET project_id = '<<GCP_PROJECT_ID>>';
	-- Fetching the list of datasets available in the project
	EXECUTE IMMEDIATE CONCAT("select ARRAY_AGG(schema_name) from `",project_id, "`.INFORMATION_SCHEMA.SCHEMATA;") INTO dataset_list;
	-- Iterating over the list of datasets
	SET dataset_count = ARRAY_LENGTH(dataset_list);
	WHILE dataset_counter < dataset_count DO
	LOOP
	-- Fetching the list of temp tables older than today in each dataset
	EXECUTE IMMEDIATE CONCAT("SELECT ARRAY_AGG(table_name) from (select t.table_name as table_name FROM `", project_id, ".", dataset_list[OFFSET(dataset_counter)],'.INFORMATION_SCHEMA.TABLES` t WHERE t.table_name LIKE "temp_table_%" AND t.table_name NOT LIKE CONCAT( "temp_table_", FORMAT_DATE("%a", current_date), "_", FORMAT_DATE("%d", current_date), "_", FORMAT_DATE("%m", current_date), "_%") limit 1000) temp;')
	INTO tables_list;
	SET table_count = ARRAY_LENGTH(tables_list);
	IF table_count <= 0 THEN
	LEAVE;
	END IF;
	SET table_counter = 0;
	--Iterating over the list of tables in each dataset
	WHILE table_counter < table_count DO
	-- Executing drop statement for each of the table
	EXECUTE IMMEDIATE CONCAT('DROP TABLE `', project_id, '.' , dataset_list[OFFSET(dataset_counter)], '.', tables_list[OFFSET(table_counter)], '`;');
	SET table_counter = table_counter + 1;
	END WHILE;
	END LOOP;
	SET dataset_counter = dataset_counter + 1;
	END WHILE;
	END;

view raw BigQuery Dynamic SQL to clear temp tables hosted with ❤ by GitHub