分割テーブルのクエリ

分割テーブルのクエリ

分割テーブルのクエリを実行するには、BigQuery ウェブ UI を使用するか、コマンドライン ツールの bq query コマンドを使用するか、API の jobs.insert メソッドを呼び出してクエリジョブを構成します。

クエリ処理のロケーションを指定するには:

  • BigQuery のウェブ UI を使用してデータをクエリする場合は、[Show Options] をクリックし、[Processing Location] で [Unspecified] をクリックしてデータのロケーションを選択します。データが US または EU マルチリージョン ロケーションに存在する場合、処理ロケーションを未指定のままにしておくことができます。データが US または EU に存在する場合は、処理ロケーションが自動的に検出されます。
  • コマンドライン ツールを使用してデータをクエリする場合は、--location フラグを指定し、ロケーションに値を設定します。データが US または EU のマルチリージョン ロケーションにある場合は、--location フラグを省略できます。
  • API を使用してデータをクエリする場合は、ジョブリソースjobReference セクションにある location プロパティでリージョンを指定します。

クエリの実行の詳細については、インタラクティブ クエリとバッチクエリの実行をご覧ください。

必要な権限

データセット レベルで分割テーブルをクエリするには、テーブルが含まれるデータセットへの READER アクセス権が必要です。

データセット レベルの権限を使用する代わりに、bigquery.tables.getData 権限が含まれるプロジェクト レベルの IAM 役割を利用できます。 クエリの実行対象となるテーブルのデータを読み取るには、bigquery.tables.getData 権限が必要です。

次の定義済みのプロジェクト レベルの IAM 役割には、プロジェクト内のすべてのデータセットに対する bigquery.tables.getData 権限が含まれます。

クエリジョブを実行するには、bigquery.jobs.create 権限が付与されていることも必要です。次の定義済みのプロジェクト レベルの IAM 役割には bigquery.jobs.create 権限が含まれています。

BigQuery での IAM 役割と権限の詳細については、アクセス制御をご覧ください。データセット レベルの役割の詳細については、データセットに対する基本の役割をご覧ください。

取り込み時間分割テーブルの疑似列

取り込み時間分割テーブルを作成すると、_PARTITIONTIME 疑似列と _PARTITIONDATE 疑似列の 2 つの疑似列がテーブルに追加されます。_PARTITIONTIME 擬似列には、テーブルに読み込まれたデータの日付ベースのタイムスタンプが格納されています。_PARTITIONDATE 疑似列には日付表記が格納されています。両方の擬似列名は予約されています。つまり、これらの名前を持つ列はどのテーブルにも作成できません。

_PARTITIONTIME および _PARTITIONDATE は、取り込み時間分割テーブルでのみ使用できます。分割テーブルには疑似列はありません。分割テーブルをクエリする方法については、分割テーブルのクエリをご覧ください。

_PARTITIONTIME 疑似列

_PARTITIONTIME 擬似列には UTC 時間に基づくタイムスタンプが含まれており、UNIX エポックからの時間をマイクロ秒単位で表します。たとえば、2016 年 4 月 15 日にテーブルに追加されたデータ行はすべて、_PARTITIONTIME 列の値が TIMESTAMP("2016-04-15") になります。

_PARTITIONTIME 擬似列に対するクエリを実行するには、エイリアスを使用する必要があります。たとえば、次のクエリは _PARTITIONTIME を選択するためにエイリアス pt を疑似列に割り当てています。

SELECT
  _PARTITIONTIME AS pt,
  [COLUMN]
FROM
  [DATASET].[TABLE]

ここで:

  • [COLUMN] は、クエリを実行する列の名前です。複数の列は、カンマ区切りリストとして指定できます。
  • [DATASET] は、分割テーブルが含まれるデータセットです。
  • [TABLE] は分割テーブルです。

ストリーミング バッファのデータには、_PARTITIONTIME 列に NULL 値があります。

_PARTITIONDATE 擬似列

_PARTITIONDATE 擬似列には、_PARTITIONTIME 擬似列の値に対応する UTC 日付が格納されます。

_PARTITIONDATE 擬似列に対するクエリを実行するには、エイリアスを使用する必要があります。たとえば、次のクエリは _PARTITIONDATE を選択するためにエイリアス pd を疑似列に割り当てています。

SELECT
  _PARTITIONDATE AS pd,
  [COLUMN]
FROM
  [DATASET].[TABLE]

ここで:

  • [COLUMN] は、クエリを実行する列の名前です。複数の列は、カンマ区切りリストとして指定できます。
  • [DATASET] は、分割テーブルが含まれるデータセットです。
  • [TABLE] は分割テーブルです。

ストリーミング バッファのデータには、_PARTITIONDATE 列に NULL 値があります。

疑似列を使用して取り込み時間分割テーブルに対するクエリを実行

取り込み時間分割テーブルのデータに対してクエリを実行する場合、_PARTITIONTIME 疑似列または _PARTITIONDATE 疑似列の値を指定して、特定のパーティションを参照します。次に例を示します。

  • _PARTITIONTIME >= "2018-01-29 00:00:00" AND _PARTITIONTIME < "2018-01-30 00:00:00"
  • _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

または

  • _PARTITIONDATE >= "2018-01-29" AND _PARTITIONDATE < "2018-01-30"
  • _PARTITIONDATE BETWEEN '2016-01-01' AND '2016-01-02'

擬似列を使用してクエリが実行されるパーティションの制限

_PARTITIONTIME 疑似列および _PARTITIONDATE 擬似列を使用して、クエリの実行中にスキャンされるパーティションの数を制限します。これは、パーティションのプルーニングとも呼ばれます。パーティションのプルーニングは、BigQuery で入力スキャンから不要なパーティションを削除するために使用するメカニズムです。パーティションのプルーニングは、クエリによってスキャンされたバイトを計算するときには含まれないため、オンデマンド分析コストが削減されます。一般に、パーティションのプルーニングは、クエリの最初からフィルタを評価できる場合、サブクエリの評価やデータスキャンを必要としないため、クエリ費用が削減されます。

たとえば、次のクエリは分割テーブルの 2016 年 1 月 1 日から 2016 年 1 月 2 日までのパーティションのみをスキャンします。

_PARTITIONTIME

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE]
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01')
  AND TIMESTAMP('2016-01-02')

_PARTITIONDATE

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE]
WHERE
  _PARTITIONDATE BETWEEN '2016-01-01'
  AND '2016-01-02'

パーティションのプルーニングの例

この例では、サブクエリで擬似列フィルタを使用してスキャンするパーティションの数を制限しています。

_PARTITIONTIME

SELECT
  [COLUMN1],
  [COLUMN2]
FROM (
  SELECT
    [COLUMN1],
    [COLUMN2]
  FROM
    [DATASET].[TABLE]
  WHERE
    _PARTITIONTIME = TIMESTAMP('2016-03-28')) t1
CROSS JOIN
  [DATASET].[TABLE2] t2
WHERE
  t1.[COLUMN2] = "one"

_PARTITIONDATE

SELECT
  [COLUMN1],
  [COLUMN2]
FROM (
  SELECT
    [COLUMN1],
    [COLUMN2]
  FROM
    [DATASET].[TABLE]
  WHERE
    _PARTITIONDATE = '2016-03-28') t1
CROSS JOIN
  [DATASET].[TABLE2] t2
WHERE
  t1.[COLUMN2] = "one"

次のクエリでは、フィルタ条件 _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01') の一部に基づいてパーティションを制限します。サブクエリに関する条件に基づいて制限されることはありません。

_PARTITIONTIME

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE2]
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from [DATASET].[TABLE1])

_PARTITIONDATE

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE2]
WHERE
  _PARTITIONDATE BETWEEN '2017-01-01' AND '2017-03-01'
  AND _PARTITIONDATE = (SELECT MAX(date) from [DATASET].[TABLE1])

すべてのパーティションをスキャンする擬似列クエリ

次の例では擬似列を使用しますが、時間分割テーブルのすべてのパーティションをスキャンします。

レガシー SQL では、_PARTITIONTIME フィルタは可能な限りテーブル名に近いところに指定したときにのみ機能します。たとえば、次のクエリは _PARTITIONTIME フィルタが存在するにもかかわらず table1 のすべてのパーティションをスキャンします。

#legacySQL
# Scans all partitions on t1
SELECT
  t1.field1,
  t2.field1
FROM
  mydataset.table2 t1
CROSS JOIN
  mydataset.table2 t2
WHERE
  t1._PARTITIONTIME = TIMESTAMP('2016-03-28')
  AND t1.field2 = "one"

_PARTITIONTIME フィルタに他の列を含めないでください。たとえば次のクエリでは、field2 はテーブルの列であり、BigQuery が選択するパーティションをあらかじめ決定できないため、スキャンされるパーティションは制限されません。

# Scans all partitions of table2
SELECT
  field1
FROM
  mydataset.table2
WHERE
  _PARTITIONTIME + field2 = TIMESTAMP('2016-03-28');

サブクエリが含まれる _PARTITIONTIME フィルタを使用して、分割テーブルに対してスキャンされるパーティションの数を制限することはできません。たとえば、次のクエリを実行しても、テーブル mydataset.table2 でスキャンされるパーティションは制限されません。

# Scans all partitions of table2
SELECT
  field1
FROM
  mydataset.table2
WHERE
  _PARTITIONTIME = (SELECT MAX(timestamp) FROM mydataset.table1)

疑似列によるパフォーマンスの向上

クエリのパフォーマンスを向上させるには、_PARTITIONTIME 疑似列を単独で比較の左辺に配置します。たとえば、以下のクエリはデータの処理量は同じですが、2 番目の例の方が高速です。

例 1: 次のクエリは、WHERE フィルタで疑似列の値を別の演算と組み合わせているため、処理速度が遅くなる可能性があります。

標準 SQL

#standardSQL
/* Can be slower */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15")

レガシー SQL

#legacySQL
/* Can be slower */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  DATE_ADD(_PARTITIONTIME, 5, "DAY") > TIMESTAMP("2016-04-15")

例 2: 次のクエリは、疑似列を単独でフィルタ比較の左辺に配置しているため、パフォーマンスが向上する可能性があります。

標準 SQL

#standardSQL
/* Often performs better */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY)

レガシー SQL

#legacySQL
/* Often performs better */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  _PARTITIONTIME > DATE_ADD(TIMESTAMP('2016-04-15'), -5, "DAY")

テーブルサイズによっては、_PARTITIONTIME を単独で > 比較演算子の左辺に配置する 2 番目のクエリの方が最初のクエリより高速になります。クエリによって処理されるデータ量は同じであるため、課金されるバイト数はどちらも同じです。

ワイルドカード テーブルを使用して取り込み時間分割テーブルに対するクエリを実行

疑似列を使用してクエリの実行中にスキャンされるパーティションの数を制限することに加えて、ワイルドカード表を使用して分割テーブルの範囲に対するクエリを実行するために疑似列を使用することもできます。分割テーブルとともにワイルドカード テーブルを使用する方法については、_PARTITIONTIME を使用した分割テーブルの範囲のスキャンをご覧ください。

タイムゾーンを使用して取り込み時間分割テーブルに対するクエリを実行

_PARTITIONTIME の値はフィールド更新時の UTC における日付が基準となります。これは、パーティションが UTC の午前 12:00 に分割されることを意味します。UTC 以外のタイムゾーンに基づいてデータのクエリを行う場合には、データをテーブルに読み込む前に以下のいずれかの方法を選択する必要があります。

UTC 以外のカスタム タイムゾーンを使用して分割テーブルのデータを照会する方法は 2 通りあります。1 つは別のタイムスタンプ列を作成する方法、もう 1 つはパーティション デコレータを使用してデータを特定のパーティションに読み込む方法です。

タイムスタンプ列を使用する場合は、デフォルトの UTC ベースのパーティショニングをそのまま使用し、SQL クエリでタイムゾーンの差を考慮します。また、UTC 以外のタイムゾーンによってパーティションをグループ化する場合は、パーティション デコレータを使用して異なるタイムゾーンに基づいてデータをパーティションに読み込みます。

timestamp 列を使用したタイムゾーンのクエリ

タイムスタンプを使用してタイムゾーンを調整するには、別の列を作成してタイムスタンプを保持し、そのタイムスタンプに時または分を加算 / 減算して行を特定します。

UTC 以外のタイムゾーンに基づいてデータを照会するには、_PARTITIONTIME 疑似列とカスタム タイムスタンプ列の両方を使用します。_PARTITIONTIME を使用してテーブル スキャンを関連パーティションのみに制限し、さらにカスタム タイムスタンプを使用してその結果を目的のタイムゾーンに合わせて絞り込みます。たとえば、タイムスタンプ フィールド mydataset.partitioned_table を持つ分割テーブル([MY_TIMESTAMP_FIELD])から、2016-05-01 08:00:00 PST2016-05-05 14:00:00 PST の期間にテーブルに追加されたデータを照会するには、次のクエリを使用します。

標準 SQL

#standardSQL
SELECT
  field1
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND TIMESTAMP_ADD([MY_TIMESTAMP_FIELD], INTERVAL 8 HOUR) BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

レガシー SQL

#legacySQL
SELECT
  field1
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND DATE_ADD([MY_TIMESTAMP_FIELD], 8, 'HOUR') BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

取り込み時間分割テーブルの疑似列を使用してビューを作成

クエリの対象を特定のパーティションのセットのみに限定して、読み取るデータの量を抑えるには、_PARTITIONTIME 疑似列または_PARTITIONDATE 疑似列にフィルタを含むビューを作成します。たとえば、次のクエリを使用して、mydataset.partitioned_table という名前のテーブルの過去 7 日間のデータのみが含まれるビューを作成できます。

標準 SQL

#standardSQL
SELECT
  *
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 * 24 HOUR),DAY)
  AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(),DAY);

レガシー SQL

#legacySQL
SELECT
  *
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP(UTC_USEC_TO_DAY(NOW() - 7 * 60 * 60 * 24 * 1000000))
  AND TIMESTAMP(UTC_USEC_TO_DAY(CURRENT_TIMESTAMP()));

ビューの作成について詳しくは、ビューの作成をご覧ください。

取り込み時間分割テーブルの __UNPARTITIONED__ パーティション

分割テーブルにストリーミングされるデータがストリーミング バッファにある間、このデータは __UNPARTITIONED__ パーティションに一時的に保存されます。分割テーブルの特定のパーティションに直接ストリーミングされるデータは、__UNPARTITIONED__ パーティションを使用しません。このデータは、パーティションに直接ストリーミングされます。詳しくは、分割テーブルへのストリーミングをご覧ください。

__UNPARTITIONED__ パーティションのデータにクエリを実行するには、NULL 値を含む _PARTITIONTIME 疑似列を使用します。次に例を示します。

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE]
WHERE
  _PARTITIONTIME IS NULL

ここで:

  • [COLUMN] は、クエリを実行する列の名前です。複数の列は、カンマ区切りリストとして指定できます。
  • [DATASET] は、分割テーブルが含まれるデータセットです。
  • [TABLE] は分割テーブルです。

分割テーブルのクエリ

TIMESTAMP 列または DATE 列に基づく分割テーブルには、疑似列はありません。分割テーブルに対するクエリを実行する際にスキャンされるパーティションの数を制限するには、述部フィルタ(WHERE 句)を使用します。

パーティションのプルーニング(制限)

テーブルの識別子に可能な限り近いところで述部フィルタを記述してください。述部(内部クエリやサブクエリなど)を解決するために複数の段階でクエリの評価を行う必要がある複雑なクエリでは、クエリからパーティションがプルーニングされません。

たとえば、次のクエリではパーティションをプルーニングします。

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 t2
ON t1.id_field = t2 field2
WHERE
  t1.ts = CURRENT_TIMESTAMP()

次のクエリでは、パーティションをプルーニングしません(サブクエリの使用に注意してください)。

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 t2
ON
  t1.id_field = t2.field2
WHERE
  t1.ts = (SELECT timestamp from table3 where key = 2)

フィルタでパーティション列を分離する

フィルタを表現するときに、パーティション列を分離します。複数のフィールドのデータを計算することが必要なフィルタでは、パーティションをプルーニングしません。たとえば、分割する列と 2 番目のフィールドを使用して日付を比較するクエリや、いくつかのフィールドの連結が含まれるクエリでは、パーティションがプルーニングされません。

たとえば、次のフィルタでは、分割する ts フィールドと 2 番目のフィールド ts2 に基づく計算が必要なため、パーティションがプルーニングされません。

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

次のステップ

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

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。