ワイルドカード テーブル

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

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

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

    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 standard 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
    

レガシー SQL のテーブル ワイルドカード関数からの移行

レガシー SQL では、次のテーブル ワイルドカード関数を使用して複数のテーブルをクエリできます。

  • TABLE_DATE_RANGE()TABLE_DATE_RANGE_STRICT()
  • TABLE_QUERY()

TABLE_DATE_RANGE() 関数

レガシー SQL の TABLE_DATE_RANGE() 関数は <prefix>YYYYMMDD という命名規則に一致するテーブルに作用します。ここで <prefix> はテーブル名の最初の部分を表し、YYYYMMDD はそのテーブルのデータに関連付けられた日付を表します。

たとえば、次のレガシー SQL クエリはシアトル地域の気象データを含む日次テーブルのセットから平均気温を返します。

#legacySQL
    SELECT
      ROUND(AVG(TemperatureF),1) AS AVG_TEMP_F
    FROM
      TABLE_DATE_RANGE([mydataset.sea_weather_],
                        TIMESTAMP("2016-05-01"),
                        TIMESTAMP("2016-05-09"))
    

標準 SQL の同等のクエリでは、テーブル ワイルドカードと BETWEEN 句を使用します。

#standardSQL
    SELECT
      ROUND(AVG(TemperatureF),1) AS AVG_TEMP_F
    FROM
      `mydataset.sea_weather_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20160501' AND '20160509'
    

TABLE_QUERY() 関数

レガシー SQL の TABLE_QUERY() 関数を使用すると、パターンに基づいてテーブル名を特定できます。TABLE_QUERY() 関数を標準 SQL に移行する場合、標準 SQL では TABLE_QUERY() 関数がサポートされていないため、代わりに _TABLE_SUFFIX 疑似列を使用してフィルタリングします。移行時には以下の違いに注意してください。

  • レガシー SQL では TABLE_QUERY() 関数を FROM 句に配置しますが、標準 SQL では _TABLE_SUFFIX 疑似列を WHERE 句で使用します。

  • レガシー SQL の TABLE_QUERY() 関数はテーブル名全体(すなわち table_id)に対して作用しますが、標準 SQL の _TABLE_SUFFIX 疑似列には、ワイルドカード文字の使い方に応じてテーブル名の一部または全体が含まれます。

WHERE 句のフィルタ

レガシー SQL から標準 SQL に移行する場合、フィルタを WHERE 句に移動します。たとえば、次のクエリは数字 0 で終わるすべての年の最高気温を検索します。

#legacySQL
    SELECT
      max,
      ROUND((max-32)*5/9,1) celsius,
      year
    FROM
      TABLE_QUERY([bigquery-public-data:noaa_gsod],
                   'REGEXP_MATCH(table_id, r"0$")')
    WHERE
      max != 9999.9 # code for missing data
      AND max > 100 # to improve ORDER BY performance
    ORDER BY
      max DESC
    

標準 SQL の同等のクエリでは、テーブル ワイルドカードを使用し、WHERE 句に正規表現関数 REGEXP_CONTAINS() を配置します。

#standardSQL
    SELECT
      max,
      ROUND((max-32)*5/9,1) celsius,
      year
    FROM
      `bigquery-public-data.noaa_gsod.gsod*`
    WHERE
      max != 9999.9 # code for missing data
      AND max > 100 # to improve ORDER BY performance
      AND REGEXP_CONTAINS(_TABLE_SUFFIX, r"0$")
    ORDER BY
      max DESC
    

table_id と _TABLE_SUFFIX の違い

レガシー SQL の TABLE_QUERY(dataset, expr) 関数では、2 番目のパラメータは値 table_id を使用してテーブル名全体に作用します。標準 SQL に移行した場合、WHERE 句のフィルタは _TABLE_SUFFIX の値に作用します。この値にはワイルドカード文字の使い方に応じてテーブル名の一部または全体が含まれます。

たとえば、次のレガシー SQL クエリは正規表現でテーブル名全体を使用して、数字 0 で終わるすべての年の最高気温を検索します。

#legacySQL
    SELECT
      max,
      ROUND((max-32)*5/9,1) celsius,
      year
    FROM
      TABLE_QUERY([bigquery-public-data:noaa_gsod],
                   'REGEXP_MATCH(table_id, r"gsod\d{3}0")')
    WHERE
      max != 9999.9 # code for missing data
      AND max > 100 # to improve ORDER BY performance
    ORDER BY
      max DESC
    

標準 SQL の同等のクエリでは、テーブル名全体を使用するか、テーブル名の一部のみを使用します。標準 SQL で接頭辞を省略すると、フィルタはテーブル名全体に作用します。

# Standard SQL empty prefix
    FROM
      `bigquery-public-data.noaa_gsod.*`
    

ただし、接頭辞が長い方が省略した場合よりもパフォーマンスが向上するため、次の例では長い接頭辞を使用しています。この場合、_TABLE_SUFFIX の値はテーブル名の一部のみになります。

#standardSQL
    SELECT
      max,
      ROUND((max-32)*5/9,1) celsius,
      year
    FROM
      `bigquery-public-data.noaa_gsod.gsod*`
    WHERE
      max != 9999.9 # code for missing data
      AND max > 100 # to improve ORDER BY performance
      AND REGEXP_CONTAINS(_TABLE_SUFFIX, r"\d{3}0")
    ORDER BY
      max DESC