BigQuery从专案中的所有表格中选择__TABLES__吗？
技术问答
67 人阅读
|
0 人回复
|
2023-09-14
|
|
使用BigQuery，是否可以__TABLES__选择项目中的每个数据集中？我试过了，SELECT * FROM'*.__TABLES'但是BigQuery不允许这样做。任何帮助都将是巨大的，谢谢！
+ D+ |6 J( s5 H+ \, t7 [ $ m3 H9 J$ ?4 ]$ ^8 ~* J
解决方案:
2 x# N0 I. [* M3 ^ __TABLES__ 语法只适用于特定数据集，不适用于所有数据集
; f) |, I4 f7 w% P' ~% F你能做的事情如下3 l V, U6 C' b2 w ^
#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; D9 k* k- u g6 P, R' D" L- t
ls进行操作7 J7 {! @( X9 _/ _' T# t
请注意：上述方法仅适用于同一位置的数据集。如果您的数据集数据位于不同的位置，您需要在两个不同的查询中查询它们$ ^. ?# w) U* |1 B9 U
例如：" g! r% e k s! w$ ~6 G$ ^9 b9 @
#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__ |
|
|
|
|
|
|
|
|
|
