ワイルドカードテーブルを使用した複数テーブルに対するクエリ

ワイルドカードテーブルを使用すると、簡潔な SQL ステートメントを使用して複数のテーブルをクエリできます。ワイルドカードテーブルは標準の SQL でのみ使用できます。レガシー SQL でのこれに相当する機能については、テーブルワイルドカード関数をご覧ください。

ワイルドカードテーブルは、ワイルドカード式に一致するすべてのテーブルが結合されたものを表します。たとえば次の FROM 句は、ワイルドカード式 gsod* を使用しており、noaa_gsod データセット内の gsod という文字列で始まるすべてのテーブルに一致します。

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

ワイルドカードテーブルの各行には、ワイルドカード文字が一致した値を格納する特別な列があります。

ワイルドカードテーブル構文の詳細については、標準 SQL リファレンスのワイルドカードテーブルをご覧ください。

制限事項

ワイルドカードテーブルクエリには、次の制限が課されます。

ワイルドカードテーブルの機能はビューをサポートしません。ワイルドカードテーブルがデータセットのビューと一致すると、クエリはエラーを返します。クエリの WHERE 句に _TABLE_SUFFIX 擬似列を使用してフィルタを除外していても同様です。
現時点で、キャッシュされた結果は、ワイルドカードを使用した複数のテーブルに対するクエリでサポートされません（[キャッシュされた結果を使用] オプションがオンになっている場合も同様です）。同じワイルドカードクエリを複数回実行した場合は、クエリごとに課金されます。
ワイルドカードテーブルは、ネイティブ BigQuery ストレージのみをサポートします。外部テーブルまたはビューに対してクエリを実行する場合は、ワイルドカードを使用できません。
データ操作言語（DML）ステートメントを含むクエリで、クエリ対象としてワイルドカードテーブルを使用できません。たとえば、UPDATE クエリの FROM 句ではワイルドカードテーブルを使用できますが、ワイルドカードテーブルを UPDATE オペレーションの対象として使用することはできません。

始める前に

BigQuery の標準 SQL を使用していることを確認します。詳細については、標準 SQL を有効にするをご覧ください。
レガシー SQL を使用する場合は、テーブルワイルドカード関数をご覧ください。
このページの例の多くは米国海洋大気庁（NOAA）の一般公開データセットを使用しています。このデータの詳細については、NOAA Global Surface Summary of the Day Weather データをご覧ください。

ワイルドカードテーブルの用途

ワイルドカードテーブルはデータセットに、互換性のあるスキーマを持つ類似した名前のテーブルが複数含まれている場合に便利です。こうしたデータセットには通常、それぞれ単一の日、月、年などのデータを表すテーブルが含まれています。たとえば、BigQuery でホストされている一般公開データセット NOAA Global Surface Summary of the Day Weather Dataset には、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 Global Surface Summary of the Day Weather Dataset に含まれる、1929 年から現在に至るまでの年次テーブルはすべて、「共通の接尾辞 gsod + 4 桁の年」という形式のテーブル名を共有しています。つまり、テーブル名は gsod1929、gsod1930、gsod1931 のようになっています。

共通の接頭辞を持つテーブルのグループを照会するには、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 疑似列には 0～9 の値が含まれ、これはテーブル gsod1940～gsod1949 を表します。これらの _TABLE_SUFFIX 値を WHERE 句で使用して対象を特定のテーブルのみに絞り込むことができます。

たとえば、最高温度を取得する年を 1940 年と 1944 年に限定するには、次のように _TABLE_SUFFIX に対して値 0 と 4 を指定します。

#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%')

サブクエリを含む条件で制限するには、2 つの別々のクエリを実行します。

最初のクエリ:

#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%'

2 番目のクエリ:

#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 を使用した特定のテーブル範囲のスキャン

特定のテーブル範囲をスキャンするには、_TABLE_SUFFIX 疑似列と BETWEEN 句を組み合わせます。たとえば、1929 年から 1935 年までの間（1929 年と 1935 年を含む）に報告された最高気温を検索するには、テーブルワイルドカードを使用して年の最後の 2 桁を表します。

#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 を使用した取り込み時間分割テーブルの特定の範囲のスキャン

取り込み時間分割テーブルの範囲をスキャンするには、_PARTITIONTIME 疑似列を _TABLE_SUFFIX 疑似列と併用します。たとえば、次のクエリは、テーブル 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 は自動的にそのテーブルのスキーマを推測します。ワイルドカードと一致する最も最近作成されたテーブルのスキーマがワイルドカードテーブルのスキーマとして使用されます。ワイルドカードテーブルと一致するテーブルの間でスキーマが一貫していない場合は、エラーが返されます。

次のステップ

標準 SQL の詳細については、標準 SQL クエリリファレンスをご覧ください。

このページは役立ちましたか？評価をお願いいたします。

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

最終更新日: 3月 27, 2019