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?

Answer 1

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: