Using wildcard in bigquery project name-CodePudding

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__
...

CodePudding user response：

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);