BigQuery从专案中的所有表格中选择__TABLES__吗？
技术问答
66 人阅读
|
0 人回复
|
2023-09-14
|
|
使用BigQuery，是否可以__TABLES__选择项目中的每个数据集中？我试过了，SELECT * FROM'*.__TABLES'但是BigQuery不允许这样做。任何帮助都将是巨大的，谢谢！
) Y F5 b. J: ^+ _. V. ?: W
: `) X$ |; m' w0 A3 } 解决方案:
% _9 h: ~6 D5 L __TABLES__ 语法只适用于特定数据集，不适用于所有数据集! v) G2 c; {$ x; X6 L5 _8 T
你能做的事情如下. G) T" ~' {7 g/ J0 c
#standardSQLWITH ALL__TABLES__ AS ( SELECT * FROM `bigquery-public-data.1000_genomes.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.baseball.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.bls.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.census_bureau_usa.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.cloud_storage_geo_index.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.cms_codes.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.common_us.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.fec.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.genomics_cannabis.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.ghcn_d.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.ghcn_m.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.github_repos.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.hacker_news.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.irs_990.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.medicare.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.new_york.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.nlm_rxnorm.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.noaa_gsod.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.open_images.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.samples.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.san_francisco.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.stackoverflow.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.usa_names.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.utility_us.__TABLES__` )SELECT *FROM ALL__TABLES__在这种情况下，您需要提前知道数据集列表，并且可以很容易地通过Datasets: listAPI或使用相应的bq
- B+ l* k4 b* `/ O& v# |7 Pls进行操作# b h# N2 Z8 D' n, B, q% B$ g
请注意：上述方法仅适用于同一位置的数据集。如果您的数据集数据位于不同的位置，您需要在两个不同的查询中查询它们7 W$ w& r% r1 j
例如：
/ C1 \9 G8 x( c#standardSQLWITH ALL_EU__TABLES__ AS ( SELECT * FROM `bigquery-public-data.common_eu.__TABLES__` UNION ALL SELECT * FROM `bigquery-public-data.utility_eu.__TABLES__` )SELECT *FROM ALL_EU__TABLES__ |
|
|
|
|
|
|
|
|
|
