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

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

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

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

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

制限事項

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

  • ワイルドカード テーブル機能ではビューがサポートされていません。ワイルドカード テーブルがデータセットのビューと一致すると、クエリでビューを除外するために WHERE 句に _TABLE_SUFFIX 疑似列が含まれていても、クエリはエラーを返します。
  • ワイルドカードを使用した複数のテーブルに対するクエリでは、キャッシュに保存された結果はサポートされていません([キャッシュされた結果を使用] オプションがオンになっている場合も同様です)。同じワイルドカード クエリを複数回実行した場合は、クエリごとに課金されます。
  • ワイルドカード テーブルは、組み込みの BigQuery ストレージのみをサポートします。ワイルドカードを使用して、外部テーブルまたはビューをクエリすることはできません。
  • 互換性のないパーティショニングがされたテーブルや、パーティション分割テーブルと分割なしのテーブルが混在している場合には、ワイルドカード クエリを使用できません。また、クエリ対象のテーブルは、同じクラスタリング仕様を持つ必要があります。
  • パーティション分割テーブルとともにワイルドカード テーブルを使用でき、パーティションのプルーニングとクラスタ プルーニングの両方がサポートされています。ただし、クラスタ化されているがパーティション分割されていないテーブルでは、ワイルドカードを使用してもクラスタ プルーニングのメリットはありません。
  • データ操作言語(DML)ステートメントを含むクエリでは、クエリ対象としてワイルドカード テーブルを使用できません。たとえば、UPDATE クエリの FROM 句ではワイルドカード テーブルを使用できますが、ワイルドカード テーブルを UPDATE オペレーションの対象として使用することはできません。
  • JavaScript ユーザー定義関数を含む _TABLE_SUFFIX または _PARTITIONTIME 疑似列のフィルタでは、ワイルドカード テーブルでスキャンされるテーブルの数に制限がありません。
  • ワイルドカード クエリは、顧客管理の暗号鍵(CMEK)で保護されているテーブルではサポートされていません。
  • ワイルドカード クエリで参照されるすべてのテーブルには、まったく同じタグキーと値のセットが必要です。
  • ワイルドカード テーブルを使用する場合、_TABLE_SUFFIXREGEXP_CONTAINS と組み合わせて使用し、正規表現(例: ^[0-9]{2}$)を指定する場合でも、* の前のテーブル名で始まるデータセット内のすべてのテーブルがスキャンされます。例:

    SELECT *
    FROM `my_project.my_dataset.my_table_*`
    WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, '^[0-9]{2}$');
    
  • スキャンされた 1 つのテーブルでスキーマの不一致があると(つまり、同じ名前の列が別の型である場合)、クエリが失敗し、「Cannot read field of type X as Y Field: column_name」というエラーが返されます。等価演算子 = を使用していても、すべてのテーブルが一致します。たとえば、次のクエリでは、テーブル my_dataset.my_table_03_backup もスキャンされます。スキーマの不一致が原因でクエリが失敗する可能性があります。ただし、スキーマの不一致がなければ、想定どおり、テーブル my_dataset.my_table_03 からのみ結果が返されます。

    SELECT *
    FROM my_project.my_dataset.my_table_*
    WHERE _TABLE_SUFFIX = '03'
    

始める前に

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

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

ワイルドカード テーブルの構文

ワイルドカード テーブルの構文は次のとおりです。

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression
<project-id>
Cloud Platform プロジェクト ID。デフォルトのプロジェクト ID を使用する場合はオプションになります。
<dataset-id>
BigQuery データセット ID。
<table-prefix>
ワイルドカード文字によって一致するすべてのテーブルに共通する文字列。テーブル接頭辞はオプションです。テーブル接頭辞を省略すると、データセット内のすべてのテーブルが一致します。
*(ワイルドカード文字)
ワイルドカード文字「*」は、テーブル名の 1 文字以上を表します。ワイルドカード文字はワイルドカード テーブル名の最後の文字としてのみ使用できます。

ワイルドカード テーブルがあるクエリでは、WHERE 句内の _TABLE_SUFFIX 疑似列がサポートされます。この列にはワイルドカード文字に一致する値が含まれているため、アクセスされるテーブルをクエリでフィルタできます。たとえば、次のように WHERE 句で比較演算子使用して、一致したテーブルをフィルタします。

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

_TABLE_SUFFIX 疑似列の詳細については、選択したテーブルの _TABLE_SUFFIX によるフィルタリングをご覧ください。

ワイルドカードを含むテーブル名をバッククォートで囲む

ワイルドカード テーブル名には特殊文字(*)が含まれているため、ワイルドカード テーブル名はバッククォート(`)文字で囲む必要があります。たとえば次のクエリは、バッククォートを使用しているため有効です。

#standardSQL
/* Valid SQL query */
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

次のクエリは、正しくバッククォートで囲まれていないため有効ではありません。

#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
  max
FROM
  # missing backticks
  bigquery-public-data.noaa_gsod.gsod*
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

引用符は機能しません。

#standardSQL
/* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */
SELECT
  max
FROM
  # quotes are not backticks
  'bigquery-public-data.noaa_gsod.gsod*'
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

ワイルドカード テーブルを使用したテーブルのクエリ

ワイルドカード テーブルを使用すると、簡潔なクエリによって複数のテーブルを検索できます。たとえば、BigQuery でホストされている一般公開データセット NOAA Global Surface Summary of the Day Weather Dataset に含まれる、1929 年から現在に至るまでの年次テーブルはすべて、共通の接頭辞「gsod + 4 桁の年」を使用したテーブル名になっています。つまり、テーブル名は 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 を表します。これらの _TABLE_SUFFIX 値を WHERE 句で使用して対象を特定のテーブルのみに絞り込むことができます。

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

#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* でスキャンされるテーブルは制限されません。フィルタでは table_id 列の動的値が使用されるためです。

#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_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

もう 1 つの例として、次のクエリでは、最初のフィルタ条件 _TABLE_SUFFIX BETWEEN '40' and '60' が定数式であるため、この条件に基づいてスキャンを制限します。ただし、次のクエリは、2 番目のフィルタ条件 _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1) FROM bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'gsod194%') が動的フィルタであるため、この条件に基づいてスキャンを制限することはありません。

#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_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

回避策として、2 つのクエリを別々に実行することもできます。例:

最初のクエリ:

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name 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'

以下のクエリの例では、INFORMATION_SCHEMA.TABLES ビューを使用しています。INFORMATION_SCHEMA テーブルの詳細については、INFORMATION_SCHEMA を使用したテーブル メタデータの取得をご覧ください。

_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

ただし、接頭辞が長い方が通常はパフォーマンスが高くなることに注意してください。詳しくは、おすすめの方法をご覧ください。

クエリ実行の詳細

クエリの評価に使用されるスキーマ

ワイルドカード テーブルを使用した GoogleSQL クエリを実行するため、BigQuery は自動的にそのテーブルのスキーマを推測します。ワイルドカードと一致する最新のテーブルのスキーマが、ワイルドカード テーブルのスキーマとして使用されます。WHERE 句の _TABLE_SUFFIX 疑似列を使用して、ワイルドカード テーブルから使用するテーブル数を制限しても、BigQuery はワイルドカードに一致する最近作成されたテーブルのスキーマを使用します。

推定されるスキーマの列が一致したテーブルに存在しない場合、BigQuery は、対象の列が欠落しているテーブルの行に対象列の NULL 値を返します。

ワイルドカード クエリで一致したテーブル間でスキーマに一貫性がない場合、BigQuery はエラーを返します。これは、一致したテーブルの列のデータ型が異なる場合や、一致したすべてのテーブルに存在しない列に null 値が含まれていると想定できない場合が、これに該当します。

おすすめの方法

  • 長い接頭辞は通常、短い接頭辞よりもうまく機能します。たとえば、次のクエリでは長い接頭辞(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 で費用を管理するためのベスト プラクティスについては、BigQuery での費用の管理をご覧ください。

次のステップ