BigQuery — An Encounter with Dynamic SQL’s

4 min readMay 24, 2020

Their are times when we want to run dynamically generated SQL’s, more so when the queries are based on some underling data that also needs to be regularly updated. The idea of sourcing the SQL from a configuration table shines bright at the end of the tunnel. Today we shall try to get close to it as much as possible.

Note : I will be using temp tables so that it will be easy for you to do a hands on as well.

Lets delve into the problem statement a bit more….

CREATE TEMP TABLE data1 AS 
  SELECT 'has 0 in it' desc1 UNION ALL
  SELECT 'has 1 in it'  UNION ALL
  SELECT 'has 2 in it' ;SELECT * FROM data1;

Given the data above we need to have a configurable SQL query that can identify ‘1’ , ‘2’ etc in them. I get that you can simply apply a case statement like below and get the job done.

SELECT *, 
CASE 
WHEN desc1 like '%2%' THEN 'two'  
WHEN desc1 like '%1%' THEN 'one'  
WHEN desc1 like '%3%' THEN 'three' 
ELSE 'other'  
END 
testcol 
from data1;

Well So far so good, but what if I ask to add one more clause to this for ‘4’ ,’5' etc. you would have to update the SQL again. Well that not what we want we want this to be driven by configuration tables

To make this query come from a configuration table lets first create a configuration table that we think would have all the config data.

CREATE TEMP TABLE rell AS 
  SELECT '%1%' search, 'one' return1 UNION ALL
  SELECT '%2%', 'two' UNION ALL
  SELECT '%3%', 'three' ;SELECT * FROM rell;

Well well, we have now defined the problem statement here. Based on config ‘rel1’ table can we generate the second query and create a table ? If you want to give this puzzle a try, you can go ahead and give it a shot now. I will delve into the solution for now.

Lets have the case statement created first

SELECT CONCAT('WHEN desc1 like \'',search,'\' THEN \'',return1,'\'') from rell;

Woopsey Doozey , our when clauses are ready. Next step, lets concatenate them to a single statement so that we can create a single string query

SELECT  CONCAT('SELECT *, CASE ',
  STRING_AGG(CONCAT('WHEN desc1 like \'',search,'\' THEN \'',return1,'\''), "  " ) 
  ,' ELSE \'more\' ',' END testcol from data1'
    ) AS SQLagg 
from rell

This should return what we need. if we run the above query we get the output as below, exactly the query we need.

SELECT *, 
CASE WHEN desc1 like '%2%' THEN 'two'  
WHEN desc1 like '%1%' THEN 'one'  
WHEN desc1 like '%3%' THEN 'three' ELSE 'more'  END 
testcol
from data1;

Now to store this in a variable and execute it.

SET steps = (
SELECT  CONCAT('SELECT *, CASE ',
  STRING_AGG(CONCAT('WHEN desc1 like \'',search,'\' THEN \'',return1,'\''), "  " ) 
  ,' ELSE \'more\' ',' END testcol  from data1'
    ) AS SQLagg 
from rell );EXECUTE IMMEDIATE  
 steps;

Now to another problem, We will have to run this query every time to get the data, the whole point of making it configurable was to make this automatic. Lets add table creation commands and well and schedule it. You will have to change the ‘DATASET.TABLENAME’ as per your needs

SET steps = (
SELECT  CONCAT('CREATE OR REPLACE TABLE DATASET.TABLENAME AS SELECT *, CASE ',
  STRING_AGG(CONCAT('WHEN desc1 like \'',search,'\' THEN \'',return1,'\''), "  " ) 
  ,' ELSE \'more\' ',' END testcol from data1'
    ) AS SQLagg 
from rell );EXECUTE IMMEDIATE  
 steps;

The final script will looks like this

-- Declare variables
DECLARE SQLScript STRING DEFAULT '';-- Data table
CREATE TEMP TABLE data1 AS 
  SELECT 'has 0 in it' desc1 UNION ALL
  SELECT 'has 1 in it'  UNION ALL
  SELECT 'has 2 in it' ;-- Config table
CREATE TEMP TABLE rell AS 
  SELECT '%1%' search, 'one' return1 UNION ALL
  SELECT '%2%', 'two' UNION ALL
  SELECT '%3%', 'three' ;-- SQL Generation (TODO : Update the dataset and table name)
SET SQLScript = (
SELECT  CONCAT('CREATE OR REPLACE TABLE DATASET.TABLENAME AS SELECT *, CASE ',
  STRING_AGG(CONCAT('WHEN desc1 like \'',search,'\' THEN \'',return1,'\''), "  " ) 
  ,' ELSE \'more\' ',' END testcol ,CURRENT_TIMESTAMP() as now  from data1'
    ) AS SQLagg 
from rell );--Execution
EXECUTE IMMEDIATE  SQLScript;

So that takes care of it. Well until next encounter then …

BigQuery — An Encounter with Dynamic SQL’s

Written by Ibrezm