How to specify dataset id as variable in FROM statement

DECLARE dataset_ids ARRAY<STRING> DEFAULT ["dataset_id_1","dataset_id_2"];

FOR dataset_id IN (
  SELECT * FROM UNNEST(dataset_ids) AS dataset_def
) DO

FOR table_row IN (
 SELECT * FROM CONCAT("project_id.", dataset_id.dataset_def, ".__TABLES__")
) DO
SELECT table_row.creation_time;
END FOR;
END FOR;

After executing this query, in which the dataset id is dynamically defined via a variable, the following error message is returned:

>Query error: Table-valued function not found: CONCAT at [5:16]

Query notes:

• note 1: the query has been simplified as such that the focus is purely on the issue as outlined
• note 2: "project_id", "dataset_id_1" and "dataset_id_2" are fictional in this simple example)

How (if possible in the first place) to successfully insert the dataset as a variable in the FROM statement in the query?

Since a table name can't be parameterized, you should consider a dynamic sql in a script like below.

-- datasets in `bigquery-public-data` project
DECLARE dataset_ids DEFAULT ["austin_bikeshare", "austin_crime"];

-- below is a temp table for storing query result.
CREATE TEMP TABLE result (table_id STRING, creation_time INT64);

FOR dataset_id IN (
  SELECT * FROM UNNEST(dataset_ids) AS dataset_def
) DO
  EXECUTE IMMEDIATE FORMAT("""
    INSERT INTO result
    SELECT table_id, creation_time FROM `bigquery-public-data.%s.__TABLES__`;
  """,  dataset_id.dataset_def);
END FOR;

SELECT * FROM result;

-- script result
+--------------------+---------------+
|      table_id      | creation_time |
+--------------------+---------------+
| bikeshare_trips    | 1495684542045 |
| bikeshare_stations | 1495683814344 |
| crime              | 1496769664576 |
+--------------------+---------------+

See also

• EXECUTE IMMEDIATE