BigQuery に対して外部 SQL ファイルを実行してみた

2020.03.18

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

BigQuery 勉強中です。

GCP 管理コンソール以外から BigQuery に対して SQL の実行方法は確認しましたが、確認時、実行する SQL はコード(コマンド)にべた書きしてました。

でも、実際のシステムでは、SQL は別ファイルに外出しした方が管理しやすいものです。

BigQuery でも外部ファイル(SQLファイル)実行できるよね?(どうやるの?

やりたいこと

BigQuery に対して、別ファイル( *.sql )に記載したSQL文を実行したい

bq コマンドで実行

bq コマンド実行環境は準備できていることを前提としています。

bq コマンドで SQL ファイルを実行する方法を調べてみたら、そのものズバリな記事が!

bq query コマンドにリダイレクトの標準入力でファイル内の SQL 渡してあげれば、難なく実行できるようです!(簡単でいいですねv

実際にやってみます。

BigQuery には、47都道府県データをロードした pref テーブルがある状態です。

このテーブルに対して、レコードを INSERT & UPDATE する、下記 SQL ファイルを準備しました。

test_exec_file.sql

INSERT INTO `cm-da-mikami-yuki-258308.test_s3.pref` VALUES (100, '新しい都道府県');
UPDATE `cm-da-mikami-yuki-258308.test_s3.pref` SET name = '琉球' WHERE code = 100;

bq query コマンドで SQL ファイルを指定して実行してみます。

[ec2-user@ip-10-0-43-239 ~]$ bq query --use_legacy_sql=false < test_exec_file.sql
Waiting on bqjob_r31257d17c18626a4_00000170ed5d34c5_1 ... (5s) Current status: DONE

GCP 管理コンソールから確認してみると

レコードが INSERT & UPDATE されていることが確認できました。

Python クライアントライブラリ( google-cloud-bigquery )で実行

続いて、Pythonコードから SQL ファイルを実行してみます。

BigQuery の Python クライアントライブラリ実行環境は準備できていることを前提としています。 Python 3.7.4 環境で確認しました。

以下の SQL ファイルと Python スクリプトを準備しました。

test_exec_file_2.sql

INSERT INTO `cm-da-mikami-yuki-258308.test_s3.pref` VALUES (99, '甲斐');
UPDATE `cm-da-mikami-yuki-258308.test_s3.pref` SET code = 1000 WHERE name = '甲斐';

test.py

from google.cloud import bigquery

path = '/home/ec2-user/test_exec_file_2.sql'
try:
    with open(path, 'r', encoding='utf-8') as f:
        query = f.read()
except Exception as e:
    raise e

client = bigquery.Client()
try:
    query_job = client.query(query)
except Exception as e:
    raise e

Python ファイルを実行してみます。

(test_bq) [ec2-user@ip-10-0-43-239 ~]$ python test.py
(test_bq) [ec2-user@ip-10-0-43-239 ~]$
(

GCP 管理コンソールから確認してみると

こちらも期待通り、レコードが INSERT & UPDATE されていることが確認できました。

BigQuery のトランザクション処理を確認する

BigQueryでは、複数クエリの同一トランザクション実行(ロールバック)機能はないとのこと。。

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

実際に確認してみました。

1つ目の INSERT クエリは成功するけれど、2つ目の UPDATE クエリで失敗する(存在しないカラムの値を UPDATE しようとする)、以下のSQL ファイルを準備しました。

test_exec_file_2.sql

INSERT INTO `cm-da-mikami-yuki-258308.test_s3.pref` VALUES (9999, '越後');
UPDATE `cm-da-mikami-yuki-258308.test_s3.pref` SET names = '越後製菓' WHERE code = 9999;

bq query コマンドで実行してみます。

(test_bq) [ec2-user@ip-10-0-43-239 ~]$ bq query --use_legacy_sql=false < test_exec_file_error.sql
Waiting on bqjob_r513e5590d32a6ee4_00000170ed748d8b_1 ... (2s) Current status: DONE
Error in query string: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r513e5590d32a6ee4_00000170ed748d8b_1':
Query error: Unrecognized name: names; Did you mean name? at [2:52]

GCP 管理コンソールから結果を確認してみると

INSERT 結果がロールバックされず残ったままで、想定通りの状態になりました。

まとめ(わかったこと・所感)

  • BigQuery でも、外部 SQL ファイルの実行は簡単にできる
  • 外部 SQL ファイルで複数の SQL を書いて、同時実行することもできる
  • BigQuery には複数クエリのトランザクション実行はできない

BigQuery ユーザーの方からすると、トランザクション処理サポートしていないのは自明なのかと思いますが、これまで RDBMS や Redshift をメインで使用していた自分としては、けっこう衝撃でした@@;

では BigQuery で、差分データ更新などのトランザクション処理を実行したい場合、どうすればいいの? あたりについて、引き続き検討してみたいと思います。