Dynamically Querying Multiple Tables In BigQuery

9
votes

I have a BigQuery database where daily data is uploaded into it's own table. So I have tables named "20131201", "20131202", etc. I can write a fixed query to "merge" those tables by doing:

SELECT * FROM db.20131201, db.20131202, ...

I'd like to have a single query that does not require me to update the Custom SQL everytime a new table is added. Something like:

SELECT * FROM db.*

Which currently doesn't work. I would like to avoid making one giant table. Is there a work-around that I can do, or will this have to be a feature request?

End-goal is for a Tableau data connection to all the tables.

2
google-bigquerytableau-api
this is an interesting feature request! – Felipe Hoffa
Agreed, this would be an interesting feature! A concept of Views in BigQuery would allow to do that and would give a lot of flexibility. – YABADABADOU
In my experience selecting * in big query is bad, and it really slows down the process. – Mariatta

2 Answers

3
votes

This isn't exactly what you've asked for, but I've managed to use https://developers.google.com/bigquery/query-reference#tablewildcardfunctions in particular

TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

to achieve a similar result for use in tableaux. You'll still need to provide 2 date parameters, but it's substantially better than dynamically generating the FROM clause.

Hope this helps.

0
votes

As of now in google bigquery this dynamic Sql [like "EXECUTE SQL" in mssqlserver] is not avilable...sulry google will look inthis i belive :)