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"}
    ]

例にある COLUMN_ID は、操作する列の名前で置き換えます。

データの挿入

パーティション分割テーブルに行を追加するには、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 ステートメントを実行するときに、サブクエリフィルタ、search_condition フィルタ、または merge_condition フィルタのいずれかでパーティショニング列を使用すると、スキャンされるパーティションを制限できます。ソーステーブル、ターゲットテーブル、またはその両方をスキャンするときに、プルーニングが発生することがあります。

以下の各例では、_PARTITIONTIME 疑似列をフィルタとして使用して、取り込み時間パーティション分割テーブルにクエリを実行しています。

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

次の MERGE ステートメントでは、USING 句のサブクエリにより、ソーステーブルの _PARTITIONTIME 疑似列をフィルタしています。

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

クエリ実行プランでは、まずサブクエリが実行されます。ソーステーブルの '2018-01-01' パーティションの行のみがスキャンされます。クエリプランの関連ステージは次のとおりです。

READ $10:name, $11:_PARTITIONTIME
FROM temp.source
WHERE equal($11, 1514764800.000000000)

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

search_condition にフィルタが含まれている場合、クエリオプティマイザはパーティションをプルーニングしようとします。たとえば、次の MERGE ステートメントでは、WHEN MATCHED 句と WHEN NOT MATCHED 句に _PARTITIONTIME 疑似列のフィルタが含まれています。

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

結合ステージでは、ターゲットテーブルの '2018-01-01'、'2018-01-02'、'2018-01-03' の各パーティション（すべての search_condition フィルタのユニオン）のみがスキャンされます。

クエリ実行プランから:

READ
$1:COLUMN_ID, $2:_PARTITIONTIME, $3:$file_temp_id, $4:$row_temp_id
FROM temp.target
WHERE or(equal($2, 1514764800.000000000), equal($2, 1514851200.000000000), equal($2, 1514937600.000000000))

ただし、次の例では、WHEN NOT MATCHED BY SOURCE 句にフィルタ式がありません。

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

このクエリは、WHEN NOT MATCHED BY SOURCE 句を計算するために、ターゲットテーブル全体をスキャンする必要があります。そのため、パーティションはプルーニングされません。

`merge_condition` で定数 false 述語を使用する

WHEN NOT MATCHED 句と WHEN NOT MATCHED BY SOURCE 句を一緒に使用すると、BigQuery は通常、プルーニングできない完全な外部結合を実行します。ただし、マージ条件で false 述語が常に使用されている場合は、フィルタ条件を使用してパーティションをプルーニングできます。定数 false 述語の使用方法については、MERGE ステートメントの merge_condition 句の説明をご覧ください。

次の例では、ターゲットテーブルとソーステーブルの両方で '2018-01-01' パーティションのみをスキャンします。

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

`merge_condition` でフィルタを使用する

クエリオプティマイザは、merge_condition に指定されたフィルタを使用してパーティションをプルーニングしようとします。結合の種類によって、クエリオプティマイザが述語をテーブルスキャンステージまでプッシュダウンできる場合と、できない場合があります。

次の例では、merge_condition が、ソーステーブルとターゲットテーブルを結合する述語として使用されています。クエリオプティマイザは、両方のテーブルをスキャンするときにこの述語をプッシュダウンできます。その結果、クエリはターゲットテーブルとソーステーブルの両方で '2018-01-01' パーティションのみをスキャンします。

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

次の例では、merge_condition にソーステーブルの述語が含まれていないため、ソーステーブルでパーティションのプルーニングを実行できません。ステートメントにはターゲットテーブルの述語が含まれていますが、ステートメントでは WHEN MATCHED 句ではなく、WHEN NOT MATCHED BY SOURCE 句を使用しています。つまり、一致しない行を見つけるには、クエリでターゲットテーブル全体をスキャンする必要があります。

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

制限事項

DML の制限事項の詳細については、DML リファレンスページの制限事項をご覧ください。

割り当て

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

料金

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

テーブルのセキュリティ

BigQuery でテーブルへのアクセスを制御するには、テーブルのアクセス制御の概要をご覧ください。

次のステップ

パーティション分割テーブルの作成方法を学習する
パーティション分割テーブルをクエリする方法を学習する
DML の概要を理解する
DML 構文を使用して DML ステートメントを作成する方法を学習する