How to create a query which has dynamicly changed dataset in Google Big Query?

1
votes

I need to create a query which is linking the data from multiple datasets, new table is created every month. I would like to create a variable which i could use to change table when new tables in datasets apperas, with no need to change each name of the table. I was trying to use "set" and "declare" function but it seems that these variables do not work in dateset names in BQ. Do You know what should i do?

DECLARE reporting_time string; SET reporting_time = "202005";

SELECT layout_instability.cumulative_layout_shift.histogram.bin FROM chrome-ux-report.country_pl.reporting_time where origin like '%sanofi.pl' or origin like '%zentiva.pl' or origin like '%zakrzepica.pl' union all SELECT layout_instability.cumulative_layout_shift.histogram.bin FROM chrome-ux-report.country_de.reporting_time where origin like '%sanofi.de'

1
google-bigquery
@MikhailBerlyant can You share an example? my structure of data set is : chrome-ux-report.country_code.year_moth like chrome-ux-repor.country_pl.202005. I am using dataset with different country code and i want to be able to swith beetwen year_month in one place, so for example change once from 202005 to 202006 and all dataset names are updated - Adam Brzostek

1 Answers

0
votes

you can use EXECUTE IMMEDIATE

First build query string and then pass it to above function