BigQuery从专案中的所有表格中选择__TABLES__吗？
技术问答
180 人阅读
|
0 人回复
|
2023-09-14
|
|
使用BigQuery，是否可以__TABLES__选择项目中的每个数据集中？我试过了，SELECT * FROM'*.__TABLES'但是BigQuery不允许这样做。任何帮助都将是巨大的，谢谢！
) `) ^6 a' g. [ 6 M x% W4 I- P; p
解决方案: 9 h' ], @! [* X5 L3 I
__TABLES__ 语法只适用于特定数据集，不适用于所有数据集- x! R8 u& S8 U. `; D* V5 [* c! Q
你能做的事情如下
! _2 V* j4 D) }2 k0 h: Z! t#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
/ H& V2 Y4 B1 V: j% Dls进行操作
9 U5 |' |/ {: I: Q1 ~/ q8 y请注意：上述方法仅适用于同一位置的数据集。如果您的数据集数据位于不同的位置，您需要在两个不同的查询中查询它们
& A4 O- [, Q3 h9 J例如：9 I, j8 H& K. f/ K# D
#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__ |
|
|
|
|
|
|
|
|
|
