Querying multiple tables in Big Query

Question

As it is not possible to update data within a table in BigQuery, and supports only append mechanism, I have decided to create new tables on monthly basis. So suppose for year 2012 the tables would be (tbl_012012, tbl_022012, tbl_032012,...tbl_122012). Each record will be stored along with date timestamp as string.

Now, if my application wants to fetch records ranging from Jan 2012(tbl_012012) to March 2012(tbl_032012), will BigQuery API automatically traverse through the desired tables via single SQL range query or will I have to write multiple SQL queries with extra application code to retrieve each query result and then aggregate them altogether?

Answer 1

You can also use a Table Wildcard Function. Here's one example from the docs for StandardSQL:

SELECT 
  name
FROM 
  mydata.people
WHERE 
  age >= 35
  AND
  (_TABLE_SUFFIX BETWEEN '20140325' AND '20140327')

And here's a similar example for LegacySQL (docs).

SELECT 
  name
FROM 
  (TABLE_DATE_RANGE([mydata.people], 
                TIMESTAMP('2014-03-25'), 
                TIMESTAMP('2014-03-27'))) 
WHERE 
  age >= 35

This will query the tables:

• mydata.people20140325
• mydata.people20140326
• mydata.people20140327

There are a few other options on the docs. I'd recommend checking them out.

Answer 2

One SQL query can reference multiple tables. Just separate each table with a comma in the FROM clause to query across all mentioned tables.