BigQuery using Dynamic sql as the input source of a query

Question

How can I use a dynamic query as an input source for a larger query?

There is a query I'm getting the union of values in different datasets/tables scattered around and the list is growing so I'm thinking of using the dynamic query than to write queries for each tables like this

SET QUERY = "";
SET tables = ["table1", "table2"...];
SET tables_size = ARRAY_LENGTH(tables);
WHILE i < tables_size DO
    IF (i = tables_size -1) THEN
      BEGIN
        SET query = CONCAT(query, " SELECT id, name FROM ", tables[OFFSET(i)]);
        BREAK;
      END;
    ELSE 
       SET query = CONCAT(query, " SELECT id, name FROM ", tables[OFFSET(i)], ' UNION ALL ');
    END IF;
    SET i = i + 1;
 END WHILE;    
EXECUTE IMMEDIATE query;

My goal is to use the output of the executed query as a FROM clause for a larger query. It will be something like

Select A, B, C, D ... From *EXECUTE IMMEDIATE query* LEFT JOIN ... ON..

Is there a way to inject an output of a dynamic query as a table for another query?

I don't see TABLE as a variable type for bigquery so that was not my option.

I'm getting a bit tired of copy pasting table names to the exact query every time a new table is introduced to this logic.

SELECT id, name FROM table1 UNION ALL 
SELECT id, name FROM table1 UNION ALL 
SELECT id, name FROM table3...

If there is a simple way to do this? or maybe a reason to not use dynamic queries for performance reasons?

Jaytiger · Accepted Answer · 2022-05-21 00:14:19Z

Hope one of these are helpful:

1. Wildcard tables

If tables you want to union have a common prefix, you can consider to use a wildcard table like below. I think this is more concise form rather than union-all:

-- Sample Tables
CREATE TABLE IF NOT EXISTS testset.table1 AS SELECT 1 AS id, 'aaa' AS name;
CREATE TABLE IF NOT EXISTS testset.table2 AS SELECT 2 AS id, 'bbb' AS name;
CREATE TABLE IF NOT EXISTS testset.table3 AS SELECT 3 AS id, 'ccc' AS name;

--- Wildcard tables
SELECT * FROM `testset.table*` WHERE _TABLE_SUFFIX IN ('1', '2', '3');

2. Dynamic SQL & Temp Table

You can't inject a dynamic SQL directly into another query but you can use a temp table to emulate it.

2.1 Dynamic SQL

More concise dynamic query to union all tables:

DECLARE tables DEFAULT ["testset.table1", "testset.table2", "testset.table3"];

SELECT ARRAY_TO_STRING(ARRAY_AGG(FORMAT('SELECT id, name FROM %s', t)), ' UNION ALL\n')
  FROM UNNEST(tables) t;

2.2 Using a temp table

I thinks you can modify your larger query to use a dynamically generated temp table.

DECLARE tables DEFAULT ["testset.table1", "testset.table2", "testset.table3"];

CREATE TABLE IF NOT EXISTS testset.table1 AS SELECT 1 AS id, 'aaa' AS name;
CREATE TABLE IF NOT EXISTS testset.table2 AS SELECT 2 AS id, 'bbb' AS name;
CREATE TABLE IF NOT EXISTS testset.table3 AS SELECT 3 AS id, 'ccc' AS name;

EXECUTE IMMEDIATE (
  SELECT "CREATE TEMP TABLE IF NOT EXISTS union_tables AS \n"
         || ARRAY_TO_STRING(ARRAY_AGG(FORMAT('SELECT id, name FROM %s', t)), ' UNION ALL\n')  FROM UNNEST(tables) t
);

-- your larger query using a temp table
SELECT * FROM union_tables;

output:

Thank you so much for the help! temp table seems like the way for me to go! Also, thanks for the concise sql, This really cleaned up the unnecessary complexity in my code.

Collectives™ on Stack Overflow

BigQuery using Dynamic sql as the input source of a query

1 Answer 1

1. Wildcard tables

2. Dynamic SQL & Temp Table

2.1 Dynamic SQL

2.2 Using a temp table

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1. Wildcard tables

2. Dynamic SQL & Temp Table

2.1 Dynamic SQL

2.2 Using a temp table

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related