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"

DML DELETE によるパーティションの削除

修飾子付きの DELETE ステートメントがパーティション内のすべての行をカバーする場合、BigQuery はパーティション全体を削除します。この削除は、バイトをスキャンすることもスロットを消費することもなく行われます。次の DELETE ステートメントの例では、_PARTITIONDATE 疑似列のフィルタのパーティション全体を対象としています。

DELETE mydataset.mytable
WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');

よくある不承認の理由

次の特性を持つクエリは、最適化のメリットが得られない場合があります。

パーティションの一部が対象
パーティショニング以外の列への参照
BigQuery の Storage Write API または以前のストリーミング API を介した最近取り込まれたデータ
サブクエリまたはサポートされていない述語を含むフィルタ

最適化の対象となるかどうかは、パーティショニングのタイプ、基盤となるストレージメタデータ、フィルタ述語によって異なります。ベストプラクティスとして、ドライランを実行して、クエリの結果で処理されたバイトが 0 バイトであることを確認します。

マルチステートメントトランザクション

この最適化は、マルチステートメントトランザクション内で機能します。次のクエリの例では、DELETE ステートメントのためにパーティションをスキャンすることなく、単一トランザクションでパーティションを別のテーブルのデータに置き換えます。

DECLARE REPLACE_DAY DATE;
BEGIN TRANSACTION;

-- find the partition which we want to replace
SET REPLACE_DAY = (SELECT MAX(d) FROM mydataset.mytable_staging);

-- delete the entire partition from mytable
DELETE FROM mydataset.mytable WHERE part_col = REPLACE_DAY;

-- insert the new data into the same partition in mytable
INSERT INTO mydataset.mytable
SELECT * FROM mydataset.mytable_staging WHERE part_col = REPLACE_DAY;

COMMIT TRANSACTION;

データの更新

パーティション分割テーブルの行を更新するには、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 ステートメントを作成する方法を学習する