データ操作言語（DML）でデータを変換する

BigQuery のデータ操作言語（DML）を使用して、BigQuery テーブルのデータを更新、挿入、削除できます。

DML ステートメントは、次の条件が満たされている場合、SELECT ステートメントと同じように実行できます。

• GoogleSQL を使用する必要があります。GoogleSQL を有効にするには、SQL 言語の切り替えをご覧ください。
• クエリの宛先テーブルを指定することはできません。

DML ステートメントで処理されるバイト数を計算する方法については、オンデマンド クエリサイズの計算をご覧ください。

制限事項

• 各 DML ステートメントは、暗黙のトランザクションを開始します。つまり、成功した各 DML ステートメントの終了時に、ステートメントによる変更が自動的にコミットされます。

• tabledata.insertall ストリーミング メソッドを使用して最近書き込まれた行は、UPDATE、DELETE、MERGE、TRUNCATE ステートメントなどのデータ操作言語（DML）を使用して変更することはできません。最近の書き込みとは、30 分以内に行われたものを指します。テーブル内の他のすべての行は引き続き、UPDATE、DELETE、MERGE、TRUNCATE ステートメントを使用して変更できます。ストリーミング データがコピー オペレーションで使用可能になるまでに最大 90 分かかることがあります。

  また、Storage Write API を使用して最近書き込まれた行は、UPDATE、DELETE、MERGE ステートメントを使用して変更できます。詳細については、最近のストリーミング データでデータ操作言語（DML）を使用するをご覧ください。

• MERGE ステートメントにおける when_clause、search_condition、merge_update_clause、merge_insert_clause 内の相関サブクエリはサポートされていません。

• DML ステートメントを含むクエリでは、クエリ対象としてワイルドカード テーブルを使用できません。たとえば、UPDATE クエリの FROM 句ではワイルドカード テーブルを使用できますが、ワイルドカード テーブルを UPDATE オペレーションの対象として使用することはできません。

DML ステートメント

以降のセクションでは、DML ステートメントの種類とその使用方法について説明します。

INSERT ステートメント

INSERT ステートメントは、既存のテーブルに新しい行を追加するために使用します。次の例では、明示的に指定された値を使用して、テーブル dataset.Inventory に新しい行を挿入します。

INSERT dataset.Inventory (product, quantity)
VALUES('whole milk', 10),
      ('almond milk', 20),
      ('coffee beans', 30),
      ('sugar', 0),
      ('matcha', 20),
      ('oat milk', 30),
      ('chai', 5)

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | sugar             |        0 |
 | whole milk        |       10 |
 +-------------------+----------+/

INSERT ステートメントの詳細については、INSERT ステートメントをご覧ください。

DELETE ステートメント

テーブルから行を削除するには、DELETE ステートメントを使用します。次の例では、quantity 値が 0 であるすべての行をテーブル dataset.Inventory から削除します。

DELETE dataset.Inventory
WHERE quantity = 0

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | whole milk        |       10 |
 +-------------------+----------+/

テーブル内のすべての行を削除するには、代わりに TRUNCATE TABLE ステートメントを使用します。DELETE ステートメントの詳細については、DELETE ステートメントをご覧ください。

TRUNCATE ステートメント

TRUNCATE ステートメントを使用すると、テーブルからすべての行を削除できますが、テーブル スキーマ、説明、ラベルなどのテーブルのメタデータはそのまま残ります。次の例では、テーブル dataset.Inventory からすべての行を削除しています。

TRUNCATE dataset.Inventory

テーブル内の特定の行を削除するには、代わりに DELETE ステートメントを使用します。TRUNCATE ステートメントの詳細については、TRUNCATE ステートメントをご覧ください。

UPDATE ステートメント

UPDATE ステートメントを使用して、テーブル内の既存の行を更新します。 UPDATE ステートメントには、条件を指定する WHERE キーワードも含める必要があります。次の例では、文字列 milk を含む商品の行の quantity 値を 10 減らします。

UPDATE dataset.Inventory
SET quantity = quantity - 10,
WHERE product LIKE '%milk%'

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       10 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       20 |
 | whole milk        |        0 |
 +-------------------+----------+/

UPDATE ステートメントには、結合テーブルを含める FROM 句を含めることもできます。UPDATE ステートメントの詳細については、UPDATE ステートメントをご覧ください。

MERGE ステートメント

MERGE ステートメントは、INSERT、UPDATE、DELETE オペレーションを 1 つのステートメントに結合し、オペレーションをアトミックに実行して、1 つのテーブルから別のテーブルにデータを統合します。MERGE ステートメントの詳細と例については、MERGE ステートメントをご覧ください。

同時実行ジョブ

BigQuery は、テーブル内の行を追加、変更、削除する DML ステートメントの同時実行を管理します。

注: DML ステートメントには、テーブル書き込みの最大レートなどのレート制限が適用されます。テーブルに対して一度に多数のジョブを送信すると、レート制限に達することがあります。これらのレートでは、実行できる DML ステートメントの合計数に上限はありません。レート制限の超過を示すエラー メッセージが表示された場合は、再試行の間に指数バックオフを使用してこのオペレーションを再試行してください。

INSERT DML の同時実行

24 時間の間、最初の 1,500 件の INSERT ステートメントは送信された直後に実行されます。この上限に達すると、テーブルに書き込む INSERT ステートメントの同時実行数は 10 件に制限されます。それ以上の INSERT ステートメントは PENDING キューに追加されます。一度に最大で 100 件の INSERT ステートメントをテーブルに対してキューに入れることができます。ある INSERT ステートメントが完了すると、次の INSERT ステートメントがキューから除かれて実行されます。

DML の INSERT ステートメントをより頻繁に実行する必要がある場合は、Storage Write API を使用してテーブルにデータをストリーミングすることを検討してください。

UPDATE、DELETE、MERGE DML の同時実行

UPDATE、DELETE、MERGE DML ステートメントは、変更 DML ステートメントと呼ばれます。あるテーブルに対して 1 つ以上の変更 DML ステートメントを送信したときに、他の変更 DML ジョブが実行中（または保留中）である場合、BigQuery はそれらのうち最大 2 個を同時に実行し、最大 20 個が PENDING としてキューに入れられます。実行中のジョブが終了すると、次の保留中のジョブがキューから出され、実行されます。 キューに入れられる変更 DML ステートメントはテーブルごとのキューを共有し、キューの最大長は 20 です。 各テーブルの最大キュー長を超える追加のステートメントは失敗し、次のエラー メッセージが表示されます。Resources exceeded during query execution: Too many DML statements outstanding against table PROJECT_ID:DATASET.TABLE, limit is 20.

インタラクティブ優先の DML ジョブが 7 時間を超えてキューに入ったままになると失敗し、次のエラー メッセージが表示されます。

DML statement has been queued for too long

DML ステートメントの競合

1 つのテーブルで同時に実行される DML ステートメントを変更するときに、そのステートメントで同じパーティションを変更しようとすると、DML ステートメントの競合が発生します。同じパーティションを変更しない限り、ステートメントは成功します。BigQuery は、失敗したステートメントの再実行を最大 3 回試行します。

• 行をテーブルに挿入する INSERT DML ステートメントは、同時に実行される他の DML ステートメントと競合しません。

• MERGE DML ステートメントは、行の挿入のみを行い、既存の行の削除または更新を行わない限り、同時に実行されている他の DML ステートメントと競合しません。UPDATE 句または DELETE 句がクエリの実行時に呼び出される場合を除き、これらの句を含む MERGE ステートメントも同様です。

きめ細かい DML

プレビュー版

この機能は サービス固有の規約の対象となります。 お客様は この機能 の個人データを Cloud のデータ処理に関する追加条項に記載されているとおりに処理できます。ただし、Google Cloud へのアクセスに関する契約に記載されている義務と制限事項が適用されます。 一般提供前の機能は「現状のまま」で提供されるものであり、サポートが制限されることがあります。 詳細については、リリース ステージの説明をご覧ください。

注: この機能に関するフィードバックやサポートのリクエストを行う場合は、[e-mail] 宛てにメールを送信してください。

きめ細かい DML は、UPDATE、DELETE、MERGE ステートメント（変更 DML ステートメントとも呼ばれます）の実行を最適化するように設計されたパフォーマンス強化機能です。

パフォーマンスに関する注意事項

きめ細かい DML を有効にしない場合、DML ミューテーションはファイルグループ レベルで実行されるため、特にスパースなミューテーションの場合にデータの書き換えが非効率になる可能性があります。これにより、スロットが余分に消費されたり、実行時間が長くなったりすることがあります。

こうした変更 DML ステートメントを最適化するように設計されたパフォーマンス強化機能がきめ細かい DML で、ファイルグループ レベルで書き換えるデータ量を減らせるよう、よりきめ細かいアプローチを導入しています。このアプローチにより、変更 DML ジョブで消費される処理量、I/O、スロット時間を大幅に削減できます。

きめ細かい DML を使用する際は、パフォーマンスに関する次の点を考慮してください。

• きめ細かい DML オペレーションは、書き換えコストを多数のテーブル ミューテーションに分散するハイブリッド アプローチで削除されたデータを処理します。各 DML オペレーションは、削除されたデータの一部を処理し、残りの削除されたデータの処理をバックグラウンドのガベージ コレクション プロセスにオフロードします。詳細については、削除されるデータに関する考慮事項をご覧ください。
• 変更 DML オペレーションが頻繁に実行されるテーブルでは、後に続く SELECT クエリと DML ジョブにおいてレイテンシが増加する可能性があります。この機能を有効にした場合の影響を評価するには、実際の利用を想定した一連の DML オペレーションと、後に続く読み取り処理のパフォーマンスをベンチマークします。
• 頻繁に変更されるパーティションが 2 TB を超える大きなテーブルの場合、きめ細かい DML はおすすめしません。これらのテーブルでは、後に続くクエリでメモリへの負荷が大きくなり、読み取りレイテンシの増加やクエリエラーが発生する可能性があります。
• きめ細かい DML を有効にしても、変更 DML ステートメント自体のスキャンされるバイト数は減りません。

きめ細かい DML を有効にする

きめ細かい DML を有効にするには、CREATE TABLE または ALTER TABLE DDL ステートメントを実行するときに、enable_fine_grained_mutations テーブル オプションを TRUE に設定します。

きめ細かい DML を使用して新しいテーブルを作成するには、CREATE TABLE ステートメントを使用します。

CREATE TABLE mydataset.mytable (
  product STRING,
  inventory INT64)
OPTIONS(enable_fine_grained_mutations = TRUE);

きめ細かい DML を使用して既存のテーブルを変更するには、ALTER TABLE ステートメントを使用します。

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = TRUE);

きめ細かい DML を使用してデータセット内の既存のテーブルをすべて変更するには、ALTER TABLE ステートメントを使用します。

FOR record IN
 (SELECT CONCAT(table_schema, '.', table_name) AS table_path
 FROM mydataset.INFORMATION_SCHEMA.TABLES)
DO
 EXECUTE IMMEDIATE
   "ALTER TABLE " || record.table_path || " SET OPTIONS(enable_fine_grained_mutations = TRUE)";
END FOR;

enable_fine_grained_mutations オプションが TRUE に設定されると、きめ細かい DML 機能が有効な状態で変更 DML ステートメントが実行され、既存の DML ステートメント構文が使用されます。

テーブルできめ細かい DML が有効になっているかどうかを確認するには、INFORMATION_SCHEMA.TABLES ビューにクエリを実行します。次の例では、データセット内のどのテーブルでこの機能が有効になっているかを確認します。

SELECT
  table_schema AS datasetId,
  table_name AS tableId,
  is_fine_grained_mutations_enabled
FROM
  DATASET_NAME.INFORMATION_SCHEMA.TABLES;

DATASET_NAME は、きめ細かい DML が有効になっているテーブルがあるかどうかを確認するデータセットの名前に置き換えます。

きめ細かい DML を無効にする

既存のテーブルできめ細かい DML を無効にするには、ALTER TABLE ステートメントを使用します。

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = FALSE);

きめ細かい DML を無効にすると、削除されたすべてのデータが完全に処理されるまでに時間がかかることがあります。削除されたデータの考慮事項をご覧ください。その結果、この処理が行われるまで、きめ細かい DML の制限が残る可能性があります。

料金

テーブルできめ細かい DML を有効にすると、追加費用が発生する場合があります。これらの費用には、次のものが含まれます。

• きめ細かい DML オペレーションに関連付けられた追加のミューテーション メタデータを保存するための BigQuery ストレージ費用。実際のストレージ費用は変更されるデータの量によって異なりますが、ほとんどの状況では、テーブル自体のサイズと比べて無視できる程度と考えられます。
• 削除されたデータをオフロードされたガベージ コレクション ジョブを使用して処理する際の BigQuery のコンピューティングの費用と、ガベージ コレクションがまだ行われていない削除メタデータを追加で処理する後続の SELECT クエリの費用。

BigQuery の予約を使用すると、オフロードされた削除データジョブの処理に専用の BigQuery コンピューティング リソースを割り当てることができます。予約を利用すると、これらのオペレーション実行の費用に上限を設定できます。このアプローチは、特に非常に大きなテーブルできめ細かい変更 DML オペレーションが頻繁に実行される場合に有効で、推奨されることがよくあります。こうしたテーブルでは、削除データのオフロード処理ジョブの実行時に処理されるバイト数が増加し、オンデマンド費用が高くなるためです。

きめ細かい DML のオフロード削除データ処理ジョブはバックグラウンド ジョブとして扱われるため、QUERY 予約割り当てタイプではなく、BACKGROUND 予約割り当てタイプを使用する必要があります。BACKGROUND 割り当てを使用せずにきめ細かい DML オペレーションを実行するプロジェクトでは、削除データのオフロード処理ジョブの処理にオンデマンド料金が適用されます。

オペレーション	オンデマンド料金	容量ベースの料金
変更 DML ステートメント	オンデマンドでスキャンされるバイト数の計算に標準の DML サイズ設定を使用します。 きめ細かい DML を有効にしても、DML ステートメント自体のスキャンされるバイト数は減少しません。	ステートメントの実行時に QUERY 型で割り当てられたスロットを使用します。
削除データのオフロード処理ジョブ	削除データ処理ジョブの実行時は、オンデマンドでスキャンされるバイト数の計算に標準の DML サイズ設定を使用します。	削除データ処理ジョブの実行時に、BACKGROUND タイプで割り当てられたスロットを消費します。

削除されるデータに関する考慮事項

BACKGROUND 割り当てを使用してきめ細かい DML オペレーションを実行するプロジェクトの場合、スロットを使用して削除されたデータを処理するため、構成された予約のリソース可用性の影響を受けます。構成した予約に十分なリソースがない場合、削除されたデータの処理に予想よりも時間がかかることがあります。

オンデマンド料金を使用するか、BACKGROUND 割り当てなしできめ細かい DML オペレーションを実行するプロジェクトでは、削除されたデータがオンデマンド料金で処理されます。削除されたデータは、内部 BigQuery リソースを使用して定期的に処理されます。

オフロードされたきめ細かい DML の削除データ処理ジョブを特定するには、INFORMATION_SCHEMA.JOBS ビューに対してクエリを実行します。

SELECT
  *
FROM
  region-us.INFORMATION_SCHEMA.JOBS
WHERE
  job_id LIKE "%fine_grained_mutation_garbage_collection%"

制限事項

きめ細かい DML が有効になっているテーブルには、次の制限があります。

• tabledata.list メソッドを使用して、きめ細かい DML が有効になっているテーブルからコンテンツを読み取ることはできません。代わりに、SELECT ステートメントを使用してテーブルをクエリし、テーブル レコードを読み取ります。
• きめ細かい DML が有効になっているテーブルは、BigQuery コンソールを使用してプレビューできません。
• UPDATE、DELETE、MERGE のいずれかのステートメントの実行後に、きめ細かい DML が有効になっているテーブルをコピーすることはできません。
• UPDATE、DELETE、MERGE のいずれかのステートメントの実行後に、きめ細かい DML が有効になっているテーブルのテーブル スナップショットまたはテーブル クローンを作成することはできません。
• 複製されたデータセット内のテーブルできめ細かい DML を有効にすることはできません。また、きめ細かい DML が有効になっているテーブルを含むデータセットを複製することもできません。
• マルチステートメント トランザクションで実行される DML ステートメントは、きめ細かい DML で最適化されません。
• CREATE TEMP TABLE ステートメントで作成された一時テーブルできめ細かい DML を有効にすることはできません。

ベスト プラクティス

最適なパフォーマンスを得るために、次のパターンをおすすめします。

• 個別の行の更新や挿入を大量に送信しないでください。可能であれば、DML オペレーションをグループ化します。詳細については、単一行を更新または挿入する DML ステートメントをご覧ください。

• 古いデータ、または特定の期間内に更新または削除が行われる場合は、テーブルのパーティショニングを検討してください。パーティショニングにより、変更をテーブル内の特定のパーティションのみに限定できます。

• 各パーティションのデータ量が小さく、各更新の際にテーブル内の大部分のパーティションを変更している場合は、テーブルのパーティショニングを行わないようにします。

• 1 つ以上の列が、値の狭い範囲内に収まる行を更新する場合は、クラスタ化テーブルの使用を検討してください。クラスタリングを行うことで、変更が特定の一連ブロックに制限されるため、読み取りと書き込みが必要なデータの量が削減されます。以下は、列の値の範囲をフィルタする UPDATE ステートメントの例です。

  UPDATE mydataset.mytable
  SET string_col = 'some string'
  WHERE id BETWEEN 54 AND 75;

  次に、列の値の小さなリストをフィルタする類似の例を示します。

  UPDATE mydataset.mytable
  SET string_col = 'some string'
  WHERE id IN (54, 57, 60);

  このような場合には、id 列のクラスタリングを検討してください。

• OLTP 機能が必要な場合は、Cloud SQL 連携クエリの使用を検討してください。これにより、BigQuery は Cloud SQL に存在するデータをクエリできるようになります。

• 割り当てエラー Too many DML statements outstanding against table, を解決して回避するには、BigQuery のトラブルシューティング ページでこのエラーのガイダンスを参照してください。

クエリのパフォーマンスを最適化するためのベスト プラクティスについては、クエリ パフォーマンスの最適化の概要をご覧ください。

次のステップ

• DML 構文の情報とサンプルについては、DML 構文をご覧ください。
• DML を使用したパーティション分割テーブルデータの更新の詳細を確認します。
• スケジュールされたクエリで DML ステートメントを使用する方法については、クエリのスケジューリングをご覧ください。