ワイルドカード テーブルを使用すると、簡潔な SQL 文を使用して複数のテーブルをクエリできます。ワイルドカード テーブルは標準 SQL でのみ使用できます。レガシー SQL でのこれに相当する機能については、テーブル ワイルドカード関数をご覧ください。
ワイルドカード テーブルは、ワイルドカード式に一致するすべてのテーブルが結合されたものを表します。たとえば次の FROM
句は、ワイルドカード式 gsod*
を使用しており、noaa_gsod
データセット内の gsod
という文字列で始まるすべてのテーブルに一致します。
FROM
`bigquery-public-data.noaa_gsod.gsod*`
ワイルドカード テーブルの各行には、ワイルドカード文字が一致した値を含む特別な列があります。
ワイルドカード テーブル構文の詳細については、標準 SQL リファレンスのワイルドカード テーブルをご覧ください。
制限事項
ワイルドカード テーブルクエリには、次の制限が課されます。
- ワイルドカード テーブルの機能はビューをサポートしません。ワイルドカード テーブルがデータセットのビューと一致すると、クエリはエラーを返します。クエリの
_TABLE_SUFFIX
擬似列にWHERE
句を含めてビューを除外していても同様です。 - 現在のところ、ワイルドカードを使用した複数のテーブルに対するクエリでは、キャッシュされた結果はサポートされていません([キャッシュされた結果を使用] オプションがオンになっている場合も同様です)。同じワイルドカード クエリを複数回実行した場合は、クエリごとに課金されます。
- ワイルドカード テーブルは、ネイティブ BigQuery ストレージのみをサポートします。外部テーブルまたはビューに対してクエリを実行する場合は、ワイルドカードを使用できません。
- データ操作言語(DML)ステートメントを含むクエリで、クエリ対象としてワイルドカード テーブルを使用できません。たとえば、
UPDATE
クエリのFROM
句ではワイルドカード テーブルを使用できますが、ワイルドカード テーブルをUPDATE
オペレーションの対象として使用することはできません。 - ワイルドカード クエリは、顧客管理の暗号鍵(CMEK)で保護されているテーブルではサポートされていません。
始める前に
- 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 でホストされている一般公開データセット 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 は自動的にそのテーブルのスキーマを推測します。ワイルドカードと一致する最も最近作成されたテーブルのスキーマがワイルドカード テーブルのスキーマとして使用されます。ワイルドカード テーブルと一致するテーブルの間でスキーマが一貫していない場合は、エラーが返されます。
おすすめの方法
長い接頭辞は通常、短い接頭辞よりもうまく機能します。たとえば、次のクエリでは長い接頭辞(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
次のステップ
- 標準 SQL の詳細については、標準 SQL クエリ リファレンスをご覧ください。