Menu
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.
975
Automating BigQuery results to an email Set up a BigQuery dataset and Cloud Storage bucket for your exports. Build a Cloud Function with the code that runs the query, exports results, and sends an email. Create a Cloud Scheduler job tied to the Pub/Sub topic to automatically run the function on a scheduled basis.
A wildcard table enables you to query multiple tables using concise SQL statements. A wildcard table represents a union of all the tables that match the wildcard expression. Wildcard tables are available only in Google Standard SQL. For equivalent functionality in legacy SQL, see Table wildcard functions.
BigQuery stores data using a columnar storage format that is optimized for analytical queries. BigQuery presents data in tables, rows, and columns and provides full support for database transaction semantics (ACID).
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.
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
