このページでは、パーティション分割テーブルに対するデータ操作言語(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 の料金をご覧ください。
次のステップ
- 取り込み時間パーティション分割テーブルを作成して使用する方法を学習する
- パーティション分割テーブルを作成して使用する方法を学習する
- パーティション分割テーブルをクエリする方法を学習する
- DML の概要を理解する
- DML 構文を使用して DML ステートメントを作成する方法を学習する