ワイルドカード テーブルを使用した複数テーブルの照会

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

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

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

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

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

始める前に

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

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

ワイルドカード テーブルはデータセットに、互換性のあるスキーマを持つ類似した名前のテーブルが複数含まれている場合に便利です。こうしたデータセットには通常、それぞれ単一の日、月、年などのデータを表すテーブルが含まれています。たとえば、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 ストレージのみをサポートします。外部テーブルまたはビューをクエリする場合は、ワイルドカードを使用できません。

ワイルドカード テーブルを使用したテーブルセットの照会

ワイルドカード テーブルを使用すると、簡潔なクエリによって複数のテーブルを検索できます。たとえば、米国海洋大気庁（NOAA）の GSOD 気象データセットに含まれる年次テーブルは、テーブル名が「共通の接頭辞 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'

注: このクエリ例では __TABLES__SUMMARY メタテーブルが使用されています。メタテーブルの詳細については、メタテーブルの使用をご覧ください。

_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 は自動的にそのテーブルのスキーマを推測します。ワイルドカードと一致する最も最近作成されたテーブルのスキーマがワイルドカード テーブルのスキーマとして使用されます。ワイルドカード テーブルと一致するテーブルの間でスキーマが一貫していない場合は、エラーが返されます。

おすすめの方法

長い接頭辞は通常、短い接頭辞よりもうまく機能します。たとえば、次のクエリでは長い接頭辞（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

制限事項

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

• ワイルドカード テーブルはビューをサポートしません。ワイルドカード テーブルがデータセット内のビューと一致した場合、クエリはエラーを返します。
• 現時点で、キャッシュされた結果は、ワイルドカード関数を使用した複数のテーブルに対するクエリに対してサポートされません（[キャッシュされた結果を使用] オプションがオンになっている場合でも）。同じワイルドカード クエリを複数回実行した場合は、クエリごとに課金されます。
• ワイルドカード テーブルは、ネイティブ BigQuery ストレージのみをサポートします。外部テーブルまたはビューをクエリする場合は、ワイルドカードを使用できません。

次のステップ

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