使用萬用字元資料表查詢多個資料表

萬用字元資料表可讓您使用精簡的 SQL 陳述式查詢多個資料表。只有標準 SQL 提供萬用字元資料表。如要在舊版 SQL 中使用對應的功能,請參閱資料表萬用字元函式一文。

萬用字元資料表是符合萬用字元運算式的所有資料表的集合。舉例來說,以下 FROM 子句使用萬用字元運算式 gsod* 來比對 noaa_gsod 資料集中開頭字串為 gsod 的所有資料表。

FROM
  `bigquery-public-data.noaa_gsod.gsod*`

萬用字元資料表中的每一列都包含一個特殊資料欄,當中有經萬用字元比對後所得出的值。

如要瞭解萬用字元資料表語法,請參閱標準 SQL 參考資料中的萬用字元資料表部分。

限制

萬用字元資料表查詢有下列限制。

  • 萬用字元資料表的功能不支援視圖。如果萬用字元資料表符合資料集中的任何視圖,查詢就會傳回錯誤。無論查詢是否在 _TABLE_SUFFIX 虛擬資料欄上加入 WHERE 子句來篩選檢視表,均會如此。
  • 目前使用萬用字元針對多個資料表進行查詢時,無法使用快取結果,即使已勾選 [Use Cached Results] (使用快取的結果) 選項也是如此。如果您執行相同的萬用字元查詢很多次,系統會針對每一筆查詢向您收費。
  • 萬用字元資料表僅支援原生的 BigQuery 儲存空間。查詢外部資料表檢視表時,不可以使用萬用字元。
  • 包含資料操縱語言 (DML) 陳述式的查詢,則無法使用萬用字元資料表做為查詢目標。例如,可在 UPDATE 查詢的 FROM 子句中使用萬用字元資料表,但無法將萬用字元資料表做為 UPDATE 運算子的目標。

事前準備

使用萬用字元表格的時機

如果資料集包含多個名稱類似且具有相容結構定義的資料表,就很適合使用萬用字元資料表。一般來說,這類資料集所包含的每一個資料表都分別代表單日、單月或單一年度的資料。舉例來說,BigQuery 所託管的公開資料集 NOAA 全球每日地面天氣摘要資料包含許多資料表,分別代表 1929 年至今每一年的資料。

執行查詢以掃描 1929 年到 1940 年之間所有的資料表 ID 時,如果要在 FROM 子句中指定所有 12 個資料表,查詢會變得相當冗長 (以下範例已省略大部分的資料表):

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM (
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1929` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1930` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1931` UNION ALL

  # ... Tables omitted for brevity

  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1940` )
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

如果使用萬用字元資料表,同一個查詢會變得精簡許多:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29'
  AND '40'
ORDER BY
  max DESC
萬用字元資料表僅支援原生的 BigQuery 儲存空間。查詢外部資料表檢視表時,不可以使用萬用字元。

使用萬用字元資料表查詢多組資料表

萬用字元表格可讓您以精簡的方式查詢多個表格。舉例來說,BigQuery 所託管的公開資料集 NOAA 全球每日地面天氣摘要資料包含許多資料表,分別代表 1929 年至今每一年的資料,而且全都共用相同的前置字串 gsod,後面加上四位數的年份,資料表名稱分別為 gsod1929gsod1930gsod1931 等。

如要查詢一組前置字元都相同的資料表,請在 FROM 陳述式中的資料表前置字元後方加上資料表萬用字元符號 (*)。舉例來說,以下查詢會找出 1940 年代期間所回報的最高氣溫:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

使用 _TABLE_SUFFIX 篩選所選資料表

如要限制查詢範圍,只掃描一組指定資料表,請在 WHERE 子句中使用 _TABLE_SUFFIX 虛擬資料欄。_TABLE_SUFFIX 虛擬資料欄包含經資料表萬用字元比對而得出的值。舉例來說,前述查詢範例使用了萬用字元來代表年份的最後一位數字,因此會掃描 1940 年代的所有資料表:

FROM
  `bigquery-public-data.noaa_gsod.gsod194*`

對應的 _TABLE_SUFFIX 虛擬資料欄包含介於 09 之間的值,分別代表 gsod1940gsod1949 之間的資料表。在 WHERE 子句中使用這些 _TABLE_SUFFIX 值即可篩選出特定資料表。

舉例來說,如要篩選出 1940 年到 1944 年之間的最高氣溫,請使用 04 來做為 _TABLE_SUFFIX 的值:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
  AND ( _TABLE_SUFFIX = '0'
    OR _TABLE_SUFFIX = '4' )
ORDER BY
  max DESC

使用 _TABLE_SUFFIX 可大幅減少掃描的位元組數量,進而降低執行查詢的成本。

如果您針對包含子查詢的 _TABLE_SUFFIX 設定了篩選器,這類篩選器無法用於限制萬用字元資料表掃描的資料表數量。舉例來說,以下查詢不會限制使用萬用字元資料表 bigquery-public-data.noaa_gsod.gsod19* 時所掃描的資料表數量:

#standardSQL
# Scans all tables that match the prefix `gsod19`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%')

以下查詢會根據篩選條件 _TABLE_SUFFIX BETWEEN '40' and '60' 限制掃描範圍,但不會根據與子查詢相關的條件予以限制。

#standardSQL
# Scans all tables with names that fall between `gsod1940` and `gsod1960`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX BETWEEN '40' AND '60'
  AND _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%')

如要根據與子查詢相關的條件限制掃描範圍,您可以執行兩個不同的查詢。

第一個查詢:

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%'

第二個查詢:

#standardSQL
# Construct the second query based on the values from the first query
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '49'

使用 _TABLE_SUFFIX 掃描特定範圍的資料表

如要掃描特定範圍的資料表,請搭配 BETWEEN 子句使用 _TABLE_SUFFIX 虛擬資料欄。舉例來說,如要找出 1929 年到 1935 年 (包含這兩年) 之間回報的最高氣溫,請使用資料表萬用字元來代表年份的最後兩位數字:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29' and '35'
ORDER BY
  max DESC

使用 _PARTITIONTIME 掃描特定範圍的擷取時間分區資料表

如要掃描特定範圍的擷取時間分區資料表,請搭配 _TABLE_SUFFIX 虛擬資料欄使用 _PARTITIONTIME 虛擬資料欄。舉例來說,以下查詢會掃描資料表 my_dataset.mytable_id1 中 2017 年 1 月 1 日分區的資料。

#standardSQL
SELECT
  field1,
  field2,
  field3
FROM
  `my_dataset.mytable_*`
WHERE
  _TABLE_SUFFIX = 'id1'
  AND _PARTITIONTIME = TIMESTAMP('2017-01-01')

查詢資料集中的所有資料表

如要掃描資料集中的所有資料表,您可以使用空白前置字元和資料表萬用字元,亦即 _TABLE_SUFFIX 虛擬資料欄包含完整的資料表名稱。舉例來說,以下 FROM 子句會掃描 GSOD 資料集中的所有資料表:

FROM
  `bigquery-public-data.noaa_gsod.*`

如果將前置字元留空,_TABLE_SUFFIX 虛擬資料欄就會包含完整的資料表名稱。舉例來說,以下查詢與前述範例一樣,會找出 1929 年到 1935 年之間的最高氣溫,但在 WHERE 子句中使用了完整資料表名稱:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN 'gsod1929' and 'gsod1935'
ORDER BY
  max DESC

不過請注意,前置字元越長通常成效越佳。詳情請參閱最佳做法部分。

查詢執行詳細資料

用於評估查詢作業的結構定義

為了執行使用了萬用字元資料表的標準 SQL 查詢,BigQuery 會自動推測該資料表的結構定義。BigQuery 會找出最近建立且符合萬用字元的資料表,並使用其結構定義做為萬用字元資料表的結構定義。如果萬用字元資料表比對出多個資料表,但其結構定義並不一致,BigQuery 就會傳回錯誤。

最佳做法

相較於較短的前置字元,較長的前置字元通常成效較佳。舉例來說,以下查詢使用了較長的前置字元 (gsod200):

#standardSQL
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod200*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '0' AND '1'
ORDER BY
  max DESC

以下查詢使用了空白前置字元,因此通常成效較差:

#standardSQL
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN 'gsod2000' AND 'gsod2001'
ORDER BY
  max DESC

後續步驟

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁