Using wildcard in bigquery project name

Question

I am trying to extract some information from all tables in the SALES dataset. I want to run it for multiple clients. Each client has a big query project. Bellow query returns what I want for our London client. How I can use a wildcard to do the same query for the other 10 clients and union all in one table?

SELECT *
FROM   london_prod_uk_eu.sales.__tables__

Basically, I want to simplify the below query:

SELECT *
FROM   london_prod_uk_eu.sales.__tables__
UNION ALL
SELECT *
FROM   toronto_prod_can_us.sales.__tables__

SELECT *
FROM   rome_prod_it_eu.sales.__tables__
UNION ALL
SELECT *
FROM   madrid_prod_sp_eu.sales.__tables__

Mikhail Berlyant · Accepted Answer · 2021-12-21 18:32:46Z

2

Consider below approach

declare query array<string> default [];
declare projects array<string>;
set projects = [
  'london-prod-uk-eu',
  'toronto-prod-ca-us',
  'rome-prod-it-eu',
  'madrid-prod-sp-eu'
];

for record IN (select project from unnest(projects) project) do
   set query =  query || [format('select * from `%s.SALES.__TABLES__`', record.project)];
end for;

execute immediate (select string_agg(line, ' union all ') from unnest(query) line);

answered Dec 21, 2021 at 18:32

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using wildcard in bigquery project name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related