BigQuery: Querying multiple datasets and tables using Standard SQL

Question

I have Google Analytics data that's spread across multiple BigQuery datasets, all using the same schema. I would like to query multiple tables each across these datasets at the same time using BigQuery's new Standard SQL dialect. I know I can query multiple tables within a single database like so:

  FROM `12345678`.`ga_sessions_2016*` s
  WHERE s._TABLE_SUFFIX BETWEEN '0501' AND '0720'

What I can't figure out is how to query against not just 12345678 but also against 23456789 at the same time.

Answer 1

How about using a simple UNION, with a SELECT wrapping around it (I tested this using the new standard SQL option and it worked as expected):

SELECT
  SUM(foo)
FROM (
  SELECT
    COUNT(*) AS foo
  FROM
    <YOUR_DATASET_1>.<YOUR_TABLE_1>
  UNION ALL
  SELECT
    COUNT(*) AS foo
  FROM
    <YOUR_DATASET_1>.<YOUR_TABLE_1>)

Answer 2

I believe that using table wild card & union (in bigquery, use comma to achieve the union function) will get what you need very quickly, if the tables have the same schema.

select * 
from
(select * from table_table_range([dataset1], date1, date2),
(select * from table_table_range([dataset2], date3, date4),
......