BigQuery - Union All tables in a dataset

Question

I have a dataset in BigQuery where I upload a new table every 2 days. All tables are the same.

Want to write and schedule a query than Union all tables, including when a newone is uploaded to the dataset.

Using the below one and is working, however every time I add a new table I need to manually include it in the query. Looking for an option where the query is querying all tables in the dataset.

(Select * from PnL.kapitani as cl WHERE cl.billing_date is not null)
Union ALL (Select * from PnL.One_Time WHERE billing_date is not null)
Union ALL (Select * from PnL.DSP WHERE billing_date is not null)
Union ALL (Select * from PnL.strypes WHERE billing_date is not null)
Union ALL (Select * from PnL.Cloud_Office WHERE billing_date is not null))```

score 1 · Answer 1 · answered Sep 03 '20 at 14:26

1

Below is for BigQuery Standard SQL

All tables are the same.
Looking for an option where the query is querying all tables in the dataset.

You can use Wildcard tables feature of BigQuery

So, you query can look simply like below

Select * from PnL.* WHERE billing_date is not null

answered Sep 03 '20 at 14:26

Mikhail Berlyant

165,386
8
154
230

Maybe I didn't explain it good enough. Need to to get a result of all the information from within the tables. – Dimitar Pavlov Sep 04 '20 at 06:20
this is still not clear what exactly your expected output.please update your question with more details! – Mikhail Berlyant Sep 04 '20 at 13:11

BigQuery - Union All tables in a dataset

1 Answers1