BigQuery でトランザクション処理がサポート開始！！

こんにちは、みかみです。

2021/06/29 のリリースで、BigQuery でマルチステートメントのトランザクションがサポートされるようになりました！ なお、2021/07/02 現在は、まだプレビュー版とのことなのでご注意ください。

• June 29, 2021 ｜ BigQuery Release notes
• Multi-statement transactions ｜ BigQuery ドキュメント

今回のリリース前には BigQuery で複数 SQL のトランザクション処理がサポートされていないことを確認済みです。 本当に複数 SQL でトランザクションがサポートされるようになったのでしょうか？！

やりたいこと

• BigQuery の複数 SQL 文のトランザクション処理を確認したい
• ロールバックやコミットは明示的に記述する必要があるのか知りたい
• INFORMATION_SCHEMA でトランザクション情報を確認したい

トランザクションのスコープ

トランザクションは BigQuery スクリプト（セミコロン繋ぎの複数 SQL）で利用することができ、 スクリプト内では複数のトランザクションを実行することができますが、ネストすることはできないそうです。

• Transaction scope ｜ BigQuery ドキュメント
• BigQuery スクリプト ｜ BigQuery ドキュメント

BEGIN TRANSACTION でトランザクションを開始して、COMMIT TRANSACTION または ROLLBACK TRANSACTION で終了します。

明示的なコミットまたはロールバック前にスクリプトが終了した場合、トランザクションは自動でロールバックされるそうです。

トランザクションのロールバック処理を確認

以前確認した時同様、BigQuery に以下の動作確認用のテーブルを作成しました。

また、BigQuery スクリプトを使用した以下の SQL ファイルを準備しました。 バックアップテーブルを作成し、元テーブルデータを全て削除した後に、x = 1 のレコードを元テーブルに戻してから、バックアップテーブルを削除します。

BEGIN
    CREATE TABLE ds_test.table1_bk AS SELECT * FROM ds_test.table1;
    DELETE FROM ds_test.table1 WHERE True;
    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_bk WHERE x = 1);
    DROP TABLE ds_test.table1_bk;
END;

SQL を実行します。

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ bq query --use_legacy_sql=false < test_exec_scripting.sql
Waiting on bqjob_r4336c65c42eea31e_0000017a65bb2cdf_1 ... (5s) Current status: DONE
CREATE TABLE ds_test.table1_bk AS SELECT * FROM ds_test.table1; -- at [2:5]
Created cm-da-mikami-yuki-258308.ds_test.table1_bk

DELETE FROM ds_test.table1 WHERE True; -- at [3:5]
Number of affected rows: 3

INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_bk WHERE x = 1); -- at [4:5]
Number of affected rows: 1

DROP TABLE ds_test.table1_bk; -- at [6:5]
Dropped cm-da-mikami-yuki-258308.ds_test.table1_bk

テーブルデータが意図通りに更新されることが確認できました。

続いて、元テーブルデータを削除した後にエラーが発生するように修正した、以下の SQL ファイルを実行してみます。

BEGIN
    CREATE TABLE ds_test.table1_bk AS SELECT * FROM ds_test.table1;
    DELETE FROM ds_test.table1 WHERE True;
--    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_bk WHERE x = 1);
    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_tmp WHERE x = 1);
    DROP TABLE ds_test.table1_bk;
END;

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ bq query --use_legacy_sql=false < test_exec_scripting.sql
Waiting on bqjob_r25cbf00db5a68f64_0000017a65c3787c_1 ... (3s) Current status: DONE
Error in query string: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r25cbf00db5a68f64_0000017a65c3787c_1': Not found: Table cm-da-mikami-yuki-258308:ds_test.table1_tmp was
not found in location asia-northeast1 at [5:5]

元テーブルデータは削除され、バックアップテーブルも残ったままです。 トランザクションを張っていないので、こちらも意図通りの動作です。

では、新しくサポートされた BEGIN TRANSACTION と COMMIT TRANSACTION ステートメントによるトランザクションを追加して実行してみます。

BEGIN
    BEGIN TRANSACTION;
    CREATE TABLE ds_test.table1_bk AS SELECT * FROM ds_test.table1;
    DELETE FROM ds_test.table1 WHERE True;
--    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_bk WHERE x = 1);
    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_tmp WHERE x = 1);
    DROP TABLE ds_test.table1_bk;
    COMMIT TRANSACTION;
END;

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ bq query --use_legacy_sql=false < test_exec_scripting.sql
Waiting on bqjob_r5926f2947318bd3d_0000017a65d0287b_1 ... (3s) Current status: DONE
Error in query string: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r5926f2947318bd3d_0000017a65d0287b_1': Query error: DDL statements are not supported in a transaction,
except for those creating or droping temporary tables or temporary functions. at [3:5]

DDL statements are not supported in a transaction とのことで、DDL はトランザクションがサポートされていないと怒られました。。

CREATE TABLE 文を、CREATE TEMP TABLE 文に変更して再度実行してみます。

BEGIN
    BEGIN TRANSACTION;
--    CREATE TABLE ds_test.table1_bk AS SELECT * FROM ds_test.table1;
    CREATE TEMP TABLE table1_bk AS SELECT * FROM ds_test.table1;
    DELETE FROM ds_test.table1 WHERE True;
--    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_bk WHERE x = 1);
    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_tmp WHERE x = 1);
--    DROP TABLE ds_test.table1_bk;
    COMMIT TRANSACTION;
END;

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ bq query --use_legacy_sql=false < test_exec_scripting.sql
Waiting on bqjob_r1b77876b1a8f1bca_0000017a65dfc874_1 ... (1s) Current status: DONE
Error in query string: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r1b77876b1a8f1bca_0000017a65dfc874_1': Query error: DDL statements are not supported in a transaction,
except for those creating or droping temporary tables or temporary functions. at [4:5]

先ほど同様、トランザクション内では DDL がサポートされていないとのことでエラーになってしまいました。。 ドキュメントによると一時テーブルはサポートされているとのことでしたが、まだプレビュー版だからでしょうか？

• Statements supported in transactions ｜ BigQuery ドキュメント

DDL をトランザクション外で実行するように修正して再度実行してみます。

BEGIN
    CREATE TABLE ds_test.table1_bk AS SELECT * FROM ds_test.table1;
    BEGIN TRANSACTION;
    DELETE FROM ds_test.table1 WHERE True;
--    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_bk WHERE x = 1);
    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_tmp WHERE x = 1);
    COMMIT TRANSACTION;
    DROP TABLE ds_test.table1_bk;
END;

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ bq query --use_legacy_sql=false < test_exec_scripting.sql
Waiting on bqjob_r3a47b3d6d3db995c_0000017a65e7509e_1 ... (6s) Current status: DONE
Error in query string: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r3a47b3d6d3db995c_0000017a65e7509e_1': Not found: Table cm-da-mikami-yuki-258308:ds_test.table1_tmp was
not found in location asia-northeast1 at [6:5]

意図した通り、元テーブルデータを削除した直後の SQL でエラーになりました。

トランザクションが効いていれば、元テーブルデータが削除前の状態にロールバックされているはずです。

途中でエラーが発生したためトランザクション外のバックアップテーブル削除が実行されずに終わってしまったものの、元テーブルデータは期待通り削除前の状態にロールバックされました。 ROLLBACK TRANSACTION ステートメントを明示的に実行しなくても、トランザクション内の処理は自動でロールバックされることが確認できました。

なお、トランザクション外のクエリも含めてエラー発生時の処理を考慮する必要がある場合は、EXCEPTION 句を追加する必要があります。

BEGIN
    CREATE TABLE ds_test.table1_bk AS SELECT * FROM ds_test.table1;
    BEGIN TRANSACTION;
    DELETE FROM ds_test.table1 WHERE True;
--    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_bk WHERE x = 1);
    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_tmp WHERE x = 1);
    COMMIT TRANSACTION;
    DROP TABLE ds_test.table1_bk;
EXCEPTION WHEN ERROR THEN
    ROLLBACK TRANSACTION;
    DROP TABLE ds_test.table1_bk;
END;

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ bq query --use_legacy_sql=false < test_exec_scripting_exp.sql
Waiting on bqjob_r12fc3ac19f0e41a9_0000017a663cfadb_1 ... (7s) Current status: DONE
CREATE TABLE ds_test.table1_bk AS SELECT * FROM ds_test.table1; -- at [2:5]
Created cm-da-mikami-yuki-258308.ds_test.table1_bk

BEGIN TRANSACTION; -- at [3:5]
DELETE FROM ds_test.table1 WHERE True; -- at [4:5]
Number of affected rows: 3

ROLLBACK TRANSACTION; -- at [10:5]
DROP TABLE ds_test.table1_bk; -- at [11:5]
Dropped cm-da-mikami-yuki-258308.ds_test.table1_bk

トランザクションサポート前には EXCEPTION 句内にデータを元に戻すクエリを記述する必要がありましたが、 ROLLBACK TRANSACTION だけでロールバックが実行されるので、エラーケースの考慮がだいぶ楽になりました。

トランザクションのコミット処理を確認

ROLLBACK TRANSACTION ステートメントは明示的に記載しなくてもロールバックされることが確認できましたが、COMMIT TRANSACTION ステートメントはどうか、確認してみます。

BEGIN
    CREATE TABLE ds_test.table1_bk AS SELECT * FROM ds_test.table1;
    BEGIN TRANSACTION;
    DELETE FROM ds_test.table1 WHERE True;
    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_bk WHERE x = 1);
--    COMMIT TRANSACTION;
--    DROP TABLE ds_test.table1_bk;
END;

mikami_yuki@cloudshell:~/sample (cm-da-mikami-yuki-258308)$ bq query --use_legacy_sql=false < test_exec_scripting.sql
Waiting on bqjob_r6c2e474d894ee1c7_0000017a665f3a7d_1 ... (5s) Current status: DONE
CREATE TABLE ds_test.table1_bk AS SELECT * FROM ds_test.table1; -- at [2:5]
Created cm-da-mikami-yuki-258308.ds_test.table1_bk

BEGIN TRANSACTION; -- at [3:5]
DELETE FROM ds_test.table1 WHERE True; -- at [4:5]
Number of affected rows: 3

INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_bk WHERE x = 1); -- at [5:5]
Number of affected rows: 1

BigQuery テーブルデータを確認してみると

SQL の実行時にエラーが発生していないことは確認しましたが、元テーブルデータに削除や更新の処理結果は反映されていません。 ドキュメントに記載のあったとおり、トランザクションを明示的に終了する前にスクリプトが終了した場合は自動でロールバックされるため、 COMMIT TRANSACTION ステートメントは明示する必要があります。

INFORMATION_SCHEMA でトランザクション情報を確認

BigQuery では、トランザクション処理の実行情報を INFORMATION_SCHEMA で確認できるそうです。

• Viewing transaction information ｜ BigQuery ドキュメント

以下の SQL で、トランザクション関連のジョブ情報を取得してみます。

SELECT transaction_id, statement_type, total_bytes_billed, creation_time, start_time, end_time, query
FROM `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE statement_type like '%TRANSACTION%'
ORDER BY creation_time DESC
limit 30

トランザクションの開始、コミット、ロールバックそれぞれの開始・終了時間などが取得できました。 どのステートメントも total_bytes_billed が 0 なので、ロールバックが発生しても処理データ量は加算されず、課金は発生しないことも確認できました。

まとめ（所感）

OLAP 用途で用いられる BigQuery において、複数 SQL 間での一貫性を保証する必要性が低いことは理解できますが、他の DWH ではサポートされることの多いトランザクションがサポートされていないのは、正直使いにくいと感じていました。

特に BigQuery にデータをロードしたりソーステーブルからマートデータを作成したりする場合、 エラーケースの考慮が必要になるため実装負荷が高くなりますし、 また、他の DWH から BigQuery に移行する場合にも、SQL 移行コストがかかります。

今回のマルチステートメントトランザクションのサポート開始により、BigQuery がより使いやすくなったのではないかと思います！

参考

• June 29, 2021 ｜ BigQuery Release notes
• Multi-statement transactions ｜ BigQuery ドキュメント