🐰

BigQueryでDELETE/INSERTしたくて色々試してみた

2023/06/17に公開

Google Cloud

SQL

BigQuery

tech

BigQueryを使ったデータウェアハウス(DWH)の構築に携わっていました。
その際に、BigQueryについて色々と調べたのですが、日本語の情報が少なく、自分で機能を検証することも多かったので、備忘録も兼ねて、この機会にまとめていきます。

Dataform

私が携わっていたプロジェクトのDWHでは、Dataformでデータ変換パイプラインを構築しておりました。
DWHのデータ管理が非常に便利で感動しております。
Dataformの主な機能は以下になります。

データ変換を管理するためのオープンソースの SQL ベースの言語

Dataform Core を使用すると、データエンジニアやデータアナリストは、テーブル定義の一元的な作成、依存関係の構成、列の説明の追加、データ品質アサーションの構成を SQL のみを使用して単一のリポジトリで行うことができます。
Dataform Core 関数は、既存のコードを変更することなく、段階的に追加して導入できます。
Dataform Core はオープンソースで、ローカルで使用できるため、特定のベンダーに依存することがなく、より高度なユースケースにも柔軟に対応できます。

データパイプラインのフルマネージドサーバーレスオーケストレーション

Dataform は、テーブル間の依存関係に従って、最新バージョンのコードを使用してテーブルを更新する運用インフラストラクチャを処理します。Dataform が統合されたことで、リネージとデータ情報をシームレスに追跡できるようになります。SQL ワークフローを手動でトリガーすることも、Cloud Composer、Workflows、サードパーティのサービスを介してスケジュールすることもできます。

SQL で開発を行うための多機能のクラウド開発環境

ウェブブラウザから離れることなく、単一のインターフェースからテーブルの定義、リアルタイムのエラーメッセージに関する問題の修正、依存関係の可視化、Git への変更の commit を行い、数分でパイプラインをスケジュールします。リポジトリを GitHub や GitLab などのサードパーティプロバイダに接続します。変更を commit して、IDE から push または pull リクエストを行います。

また、BigQueryはクエリで処理されたデータ量に応じて料金が発生するため, 処理されるデータ量が大きくならないように注意したいところです。

データ管理の大まかな流れとして、BigQueryにベースとなるテーブルを作成し、そのベースのテーブルに対して、DELETE/INSERTクエリを実行してデータを更新していきます。

BigQuery上でクエリを定期的に自動実行することを想定しているので、コスト面について気を配った設計を考える必要があります。さらに、多量のデータを持つテーブルを作成するとき、そのテーブルに対するクエリのデータ量を抑えるためにパーティション分割テーブルがよく用いられるので、そちらについて強引なチャレンジもしてみました。具体的には後述したいと思います。

DELETE / INSERT 準備

以下の状況を前提にして話を進めます。

更新対象テーブル(table_A)
- テーブルサイズ：2TB
- パーティションキー：P_key(登録した年月YYMM)
- テーブル構造：

id	P_key	name	age	favorite	...
1	2301	たろう	21	読書	...
2	2301	あきら	35	サーフィン	...
3	2302	はなこ	18	映画鑑賞	...
4	2303	みなみ	25	カフェ巡り	...
5	2303	ゆうた	29	海外旅行	...
...

更新用データ(table_B)
- テーブルサイズ：10GB
- テーブル構造：

id	P_key	name	age	favorite	...
1	2301	たろう	21	筋トレ	...
5	2303	ゆうた	29	海外旅行	...
...

次に、DELETEとINSERTの操作について詳しく見ていきます。

DELETEの制限と注意点

DELETE文の制限と注意点について説明します。

DELETE

DELETE
FROM table_A AS A
JOIN table_B AS B 
    ON A.name = B.name 
    AND A.P_key = B.P_key

WHERE句が必須になるので、内部結合(JOIN)ではエラーになります。

DELETE

DELETE
FROM table_A AS A
WHERE 1 = 1 -- WHERE句を追加
JOIN table_B AS B
    ON A.name = B.name 
    AND A.P_key = B.P_key

こんな書き方も、GoogleのStandardSQLではエラーになってしまいます。
回避策としては、以下で進めます。

DELETE

DELETE
FROM table_A
WHERE 
    EXISTS (
        SELECT A.P_key, A.name FROM table_A AS A
        JOIN table_B AS B
        ON A.name = B.name
        AND A.P_key = B.P_key
    )

SQLの内容としては今までと変わりません。
しかし、レコードの絞り込みはできていますが、これではtable_Aの全データをスキャンしてしまうことに後から気がつきました。。

分割テーブルの使用

ここで改めてパーティションについて学び直したいと思います。

パーティション：分割テーブルの概要

パーティション分割テーブルはパーティションと呼ばれるセグメントに分割されるため、データの管理や照会が簡単になります。大きなテーブルを小さなパーティションに分割することで、クエリのパフォーマンスを高めることや、クエリによって読み取られるバイト数を減らしてコストを抑えることができます。テーブルのパーティショニングに使用されるパーティション列を指定して、テーブルを分割します。
クエリで、パーティショニング列の値に対する限定フィルタを使用すると、BigQuery では、フィルタに一致するパーティションがスキャンされ、残りのパーティションはスキップされます。このプロセスは「プルーニング」と呼ばれます。
パーティション分割テーブルでは、データが物理ブロックに保存され、各ブロックに 1 つのデータパーティションが保持されます。各パーティション分割テーブルには、そのテーブルを変更するすべてのオペレーションについて、並べ替えプロパティに関するさまざまなメタデータが保持されます。メタデータを使用すると、BigQuery でクエリを実行する前にクエリの費用をより正確に見積もることができます。

パーティションの分割について、日付で範囲を絞ることはググったらたくさん出てきましたが、特定のパーティションキーをピックアップして範囲を絞りたい。今回でいえば、table_AのP_keyをパーティションキーとして設定してみます。おそらく、Googleが想定するパーティションの使い方ではないかもしれませんが、強引にチャレンジしてみます。

まず、先ほどのDELETE処理で、全データスキャンになる原因は何かと言うと、パーティションキーが動的に定まる範囲だからです。
具体的には、以下が非常に参照になったので、気になる方は見てみてください。

例えば、以下のようなSQLではデータのプルーニングができて、コストカットすることができます。

DELETE

DELETE
FROM table_A
WHERE 
    EXISTS (
        SELECT A.P_key, A.name FROM table_A AS A
        JOIN table_B AS B
        ON A.name = B.name
        AND A.P_key = 2303 -- パーティションキーに定数を指定
    )

動的に定まる範囲でも、簡単な計算であれば、プルーニングできるので、どこまでいけるか試しました。

配列を使用した絞り込み

DELETE_INSERT

-- P_keyの配列を定義。
DECLARE partition_keys ARRAY<INT64> DEFAULT [2301,2303,2306,...]

DELETE
FROM table_A
WHERE 
    EXISTS (
        SELECT A.P_key, A.name FROM table_A AS A
        JOIN table_B AS B
        ON A.P_key = B.P_key
        AND A.name = B.name
    )
    AND P_key IN UNNEST(partition_keys) --　配列で絞り込み

変数partition_keysの中身を定数にして渡した場合、プルーニングができました。
削除処理に必要なデータのみに絞り込むことができたので、大きなコストカットができました。これは期待。
では、動的にして、やってみましょう。

DELETE_INSERT

-- idの配列を定義。
DECLARE partition_keys ARRAY<INT64> DEFAULT ARRAY(
    SELECT P_key FROM table_B
)

DELETE
FROM table_A
WHERE 
    EXISTS (
        SELECT A.P_key, A.name FROM table_A AS A
        JOIN table_B AS B
        ON A.P_key = B.P_key
        AND A.name = B.name
    )
    AND P_key IN UNNEST(partition_keys) --　配列で絞り込み

しかし、動的な配列ではプルーニングできておらず、全データをスキャン(2TB)することになりました。
「配列を使用した絞り込み」ではなく、他の方法を試してみましょう。

パーティションキーごとの削除処理。

DELETE

FOR partition_keys IN (
    SELECT P_key FROM table_B
)
DO
DELETE
FROM table_A
WHERE 
    EXISTS (
        SELECT A.P_key, A.name FROM table_A AS A
        JOIN table_B AS B
        ON A.P_key = B.P_key
        AND A.name = B.name
    )
    AND A.P_key = partition_keys.P_key --　絞り込み
END

FORループを使用して、table_BのP_keyの種類だけ削除処理を繰り返します。ちなみに、DECLARE等は手続き型言語を使用しております。
実際に動かしてみたところ、プルーニングはできました。しかし、処理経過時間が大幅に増加しました。経過時間が増えることはわかってはいましたが倍以上の時間になり、使い物になりませんでした。

分割テーブルの使用についての結論として、現実的な分割はできないと言うことになりました。私の強引なチャレンジは失敗に終わってしまいました。
そもそも、大なり小なりなどで範囲の絞り込みができないP_keyをそもそもパーティションに指定すべきではないと言うことになりました。

クラスタリングの使用

クラスタリングは、データの順序を並び替えてまとまりを作ることで、クエリのパフォーマンスを向上させるための機能です。クラスタリングを使用することで、コスト削減が可能となります。
詳しくはクラスタ化テーブルの概要を参照してください。

クラスタリングは最大4つまで設定できますが、設定する順番によって並び替え方が変わるため、コストカットにも差が出てきます。
設計に携わる方々は、こちらも考慮する必要がありそうです。

上ではDELETE処理しか載せていませんでしたが、INSERT処理も含めて、改めてSQLを掲載しておきます。

DELETE_INSERT

DELETE
FROM table_A
WHERE 
    EXISTS (
        SELECT A.P_key, A.name FROM table_A AS A
        JOIN table_B AS B
        ON A.P_key = B.P_key
        AND A.name = B.name
    );

INSERT INTO table_A
SELECT * FROM table_B;

まとめ

結果として、DELETE処理にはパーティションは設定しないことになりました。クラスタリングは特に制約なく設定できたので、全データスキャンという事態は免れましたが、まだまだ改善の余地はあるかもしれません。BigQueryは非常に有効ですが使い方によっては制約もあるので、私の検証が誰かのお役に立てればと思います。また、皆さんからもこんな方法があるよというものがあれば、ぜひコメントにて教えていただきたいと思います。

Dataform

データ変換を管理するためのオープンソースの SQL ベースの言語

データ パイプラインのフルマネージド サーバーレス オーケストレーション

SQL で開発を行うための多機能のクラウド開発環境

DELETE / INSERT 準備

DELETEの制限と注意点

分割テーブルの使用

クラスタリングの使用

まとめ

Discussion

データパイプラインのフルマネージドサーバーレスオーケストレーション