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

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

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

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

BigQuery DML ステートメントの一覧と使用例については、GoogleSQL のデータ操作言語ステートメントをご覧ください。DML ステートメントで処理されるバイト数を計算する方法については、オンデマンド クエリサイズの計算をご覧ください。

同時実行ジョブ

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

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

DML statement has been queued for too long

DML ステートメントの競合

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

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

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

きめ細かい DML

きめ細かい DML は、UPDATE、DELETE、MERGE ステートメント（変更 DML ステートメントとも呼ばれます）の実行を最適化するように設計されたパフォーマンス強化機能です。きめ細かい DML を有効にしないと、ミューテーションはファイル グループ レベルで実行されるため、データの書き換えが非効率になる可能性があります。きめ細かい DML では、書き換えが必要なデータの量を減らし、スロットの総使用量を削減することを目的とした、よりきめ細かいアプローチが導入されています。

きめ細かい DML プレビューにプロジェクトを登録する意向をお持ちの場合は、BigQuery きめ細かい 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);

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

テーブルできめ細かい DML を無効にするには、ALTER TABLE DDL ステートメントを使用して enable_fine_grained_mutations を FALSE に設定します。

料金

テーブルにきめ細かい DML を有効にすると、きめ細かい DML オペレーションに関連付けられた追加のミューテーション メタデータを保存するために、BigQuery ストレージ費用が追加で発生する可能性があります。実際の費用は、変更されるデータの量によって異なりますが、ほとんどの状況では、テーブル自体のサイズと比較して無視できると想定されます。

reservationsを使用するように構成されたプロジェクトでは、スロットを使用して、テーブルまたはミューテーションのメタデータのバックグラウンド処理など、きめ細かい DML ステートメントを処理します。

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

きめ細かい DML オペレーションは、削除されたデータをオフラインで処理します。

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

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

制限事項

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

• tabledata.list メソッドを使用して、きめ細かい DML が有効になっているテーブルからコンテンツを読み取ることはできません。代わりに、Storage Read API を使用して、API でテーブル レコードを読み取ります。
• きめ細かい DML が有効になっているテーブルのテーブル スナップショットまたはテーブル クローンは作成できません。
• 複製されたデータセット内のテーブルできめ細かい DML を有効にすることはできません。また、きめ細かい DML が有効になっているテーブルを含むデータセットを複製することもできません。
• マルチステートメント トランザクションで実行される DML ステートメントは、きめ細かい 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 に存在するデータをクエリできるようになります。

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

制限事項

• 各 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 構文をご覧ください。
• スケジュールされたクエリで DML ステートメントを使用する方法については、クエリのスケジューリングをご覧ください。