DML を使用したパーティション分割テーブルデータの更新

このページでは、パーティション分割テーブルに対するデータ操作言語（DML）のサポートの概要について説明します。

DML の詳細については、次をご覧ください。

この例に使用されているテーブル

次の JSON スキーマ定義は、このページの例で使用されているテーブルを表します。

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

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
    ]

mytable2: 標準の（パーティション分割されていない）テーブル 

    [
      {"name": "id", "type": "INTEGER"},
      {"name": "ts", "type": "TIMESTAMP"}
    ]

mycolumntable: ts TIMESTAMP 列を使用してパーティショニングされたパーティション分割テーブル

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
      {"name": "field3", "type": "BOOLEAN"}
      {"name": "ts", "type": "TIMESTAMP"}
    ]

データの挿入

パーティション分割テーブルに行を追加するには、DML の INSERT ステートメントを使用します。

取り込み時間パーティション分割テーブルへのデータの挿入

DML ステートメントを使用して取り込み時間パーティション分割テーブルに行を追加する場合は、行を追加するパーティションを指定できます。_PARTITIONTIME 疑似列を使用してパーティションを参照します。

たとえば、次の INSERT ステートメントでは、mytable の 2017 年 5 月 1 日のパーティション（“2017-05-01”）に行を追加します。

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01"),
  1,
  "one"

厳密な日付境界に対応するタイムスタンプのみを使用できます。たとえば、次の DML ステートメントではエラーが返されます。

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01 21:30:00"),
  1,
  "one"

パーティション分割テーブルへのデータの挿入

DML を使用してパーティション分割テーブルにデータを挿入することは、パーティショニングされていないテーブルにデータを挿入することと同じです。

たとえば、次の INSERT ステートメントでは、mytable2（パーティショニングされていないテーブル）からデータを選択して、パーティション分割テーブル mycolumntable に行を追加します。

INSERT INTO
  project_id.dataset.mycolumntable (ts,
    field1)
SELECT
  ts,
  id
FROM
  project_id.dataset.mytable2

データの削除

パーティション分割テーブルから行を削除するには、DML の DELETE ステートメントを使用します。

取り込み時間パーティション分割テーブルのデータの削除

次の DELETE ステートメントでは、mytable の 2017 年 6 月 1 日のパーティション（"2017-06-01"）から、field1 が 21 に等しいすべての行を削除します。_PARTITIONTIME 疑似列を使用してパーティションを参照します。

DELETE
  project_id.dataset.mytable
WHERE
  field1 = 21
  AND _PARTITIONTIME = "2017-06-01"

パーティション分割テーブルのデータの削除

DML を使用してパーティション分割テーブルのデータを削除することは、パーティショニングされていないテーブルからデータを削除することと同じです。

たとえば、次の DELETE ステートメントでは、mycolumntable の 2017 年 6 月 1 日のパーティション（"2017-06-01"）から、field1 が 21 に等しいすべての行を削除します。

DELETE
  project_id.dataset.mycolumntable
WHERE
  field1 = 21
  AND DATE(ts) = "2017-06-01"

データの更新

パーティション分割テーブルの行を更新するには、UPDATE ステートメントを使用します。

取り込み時間パーティション分割テーブルのデータの更新

次の UPDATE ステートメントでは、あるパーティションから別のパーティションに行を移動します。 field1 が 21 に等しい mytable の 2017 年 5 月 1 日のパーティション（“2017-05-01”）の行が、2017 年 6 月 1 日のパーティション（“2017-06-01”）に移動します。

UPDATE
  project_id.dataset.mytable
SET
  _PARTITIONTIME = "2017-06-01"
WHERE
  _PARTITIONTIME = "2017-05-01"
  AND field1 = 21

パーティション分割テーブルのデータの更新

DML を使用してパーティション分割テーブルのデータを更新することは、パーティショニングされていないテーブルのデータを更新することと同じです。たとえば、次の UPDATE ステートメントでは、あるパーティションから別のパーティションに行を移動します。field1 が 21 に等しい mytable の 2017 年 5 月 1 日のパーティション（“2017-05-01”）の行が、2017 年 6 月 1 日のパーティション（“2017-06-01”）に移動します。

UPDATE
  project_id.dataset.mycolumntable
SET
  ts = "2017-06-01"
WHERE
  DATE(ts) = "2017-05-01"
  AND field1 = 21

時間、月、年単位のパーティション分割テーブルの DML

DML ステートメントを使用して、時間単位、月単位、または年単位のパーティション分割テーブルを変更できます。関連する日付 / タイムスタンプ / 日時の時間、月、年の範囲を指定します。月単位のパーティション分割テーブル用の例を次に示します。

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    TIMESTAMP_TRUNC(ts_column, MONTH) = "2020-01-01 00:00:00";'

または、DATETIME 列を使用したパーティション分割テーブル用の別の例は次のとおりです。

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    dt_column BETWEEN DATETIME("2020-01-01")
    AND DATETIME("2020-05-01");'

MERGE ステートメントの使用

DML の MERGE ステートメントを使用すると、パーティション分割テーブルに対する INSERT、UPDATE、DELETE オペレーションを 1 つのステートメントに結合してアトミックに実行できます。

MERGE ステートメント使用時のパーティションのプルーニング

パーティション分割テーブルに対して MERGE ステートメントを実行する際に、ステートメントに含まれるパーティションを制限するには、_PARTITIONTIME 疑似列を使用するか（取り込み時間パーティション分割テーブルの場合）、日付、タイムスタンプ、または日時の列を使用します（パーティション分割テーブルの場合）。パーティションをプルーニングすると、コストが節約され、クエリのパフォーマンスが向上します。

パーティションのプルーニング条件は、サブクエリ フィルタ、search_condition フィルタ、または merge_condition フィルタで指定できます。

以下の各例では、_PARTITIONTIME 疑似列を使用して取り込み時間パーティション分割テーブルを照会しています。

サブクエリを使用してソースデータをフィルタリングする

サブクエリでフィルタを使用してパーティションをプルーニングできます。たとえば、次の MERGE ステートメントは、ソーステーブルの '2018-01-01' パーティションにある行のみをスキャンします。

MERGE dataset.target T
USING (SELECT * FROM dataset.source WHERE _PARTITIONTIME = '2018-01-01') S
ON T.c1 = S.c1
WHEN MATCHED THEN
  DELETE

when_clause の search_condition でフィルタを使用する

クエリ オプティマイザは、search_condition に指定されたフィルタを使用してパーティションをプルーニングしようとします。たとえば、次の MERGE ステートメントは、ターゲット テーブルの '2018-01-01'、'2018-01-02'、'2018-01-03' パーティションにある行のみをスキャンします。

MERGE dataset.target T
USING dataset.source S
ON T.c1 = S.c1
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET c1 = S.c1
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-02' THEN
  UPDATE SET c1 = c1 + 10
WHEN NOT MATCHED BY SOURCE AND T._PARTITIONTIME = '2018-01-03' THEN
  DELETE

次の例では、WHEN NOT MATCHED BY SOURCE 句に対してターゲット テーブル内のすべてのデータが必要となります。その結果、すべてのパーティションがスキャンされ、すべてのパーティションで読み取られたバイト数が課金されます。

MERGE dataset.target T
USING dataset.source S
ON T.c1 = S.c1
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET c1 = S.c1
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET c1 = c1 + 1

一般に、WHEN NOT MATCHED 句と WHEN NOT MATCHED BY SOURCE 句を組み合わせて使用すると、ソーステーブルとターゲット テーブルの FULL OUTER JOIN が行われます。通常、FULL OUTER JOIN でパーティションをプルーニングすることはできません。ただし、常時 false 述語を使用した場合、フィルタ条件をパーティションのプルーニングに使用できます。次のクエリでは、パーティション プルーニングを使用して、ターゲット テーブルとソーステーブルの両方でスキャン対象を '2018-01-01' パーティションのみに限定しています。

MERGE dataset.target T
USING dataset.source S
ON FALSE
WHEN NOT MATCHED AND _PARTITIONTIME = '2018-01-01' THEN
  INSERT(c1) VALUES(c1)
WHEN NOT MATCHED BY SOURCE AND _PARTITIONTIME = '2018-01-01' THEN
  DELETE

merge_condition でフィルタを使用する

クエリ オプティマイザは、merge_condition に指定されたフィルタを使用してパーティションをプルーニングしようとします。たとえば、次のクエリでは、ターゲット テーブルとソーステーブルの両方でスキャン対象を '2018-01-01' パーティションのみに限定しています。

MERGE dataset.target T
USING dataset.source S
ON T.c1 = S.c1 AND
  T._PARTITIONTIME = '2018-01-01' AND
  S._PARTITIONTIME = '2018-01-01'
WHEN MATCHED THEN
  UPDATE SET c1 = S.c1

この例では、merge_condition が、ソーステーブルとターゲット テーブルを結合する述語として使用されています。クエリ オプティマイザが述語プッシュダウンを使用できるかどうかは結合の種類によります。

次の MERGE ステートメントの例では、パーティションをプルーニングできません。その理由は、パーティション フィルタが、テーブルに直接適用できない結合条件の述語であるためです。

MERGE dataset.target T
USING dataset.source S
ON T.c1 = S.c1 AND T._PARTITIONTIME = '2018-01-01'
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET c1 = S.c1

制限事項

DML の制限事項の詳細については、データ操作言語ページの制限事項をご覧ください。

割り当て

DML の割り当て情報については、割り当てと上限ページの DML ステートメントをご覧ください。

料金

DML の料金については、パーティション分割テーブルの DML の料金をご覧ください。

次のステップ