BigQuery MERGEクエリを生成してくれるCLIを作成した話

Transcript

1. BigQuery MERGEクエリを生成してくれるCLIを作成した 話 Yuu ITO 2022/02/26 nagano.rb#9
2. 自己紹介 Yuu ITO Github ID: u110 Twitter ID: yuu_ito Web / Data Engineer 2021.03 ~ 長野県塩尻市 職場は東京なので、フルリモ ート（そろそろ２年目） trocco というETLサービスを運営 する会社で、 開発したり、実際にtroccoを使っ た仕事しています。
3. BigQueryのMERGEクエリがちょっと大変 業務で、BigQueryのMERGEクエリが便利なので多用しているけど、記述が大変。 https://cloud.google.com/bigquery/docs/reference/standard-sql/dml- syntax#merge_statement UPDATE SET ... の記述に対象のカラム名すべてを記載しないといけない。 BigQueryのAPI使って、テーブルスキーマ取ってくればほぼ自動でクエリ生成できるの では？ ※ Google BigQuery: Googleの提供するデータウェアハウス(DWH) たくさんのデータを格納できて、集計が高速。 制限ありだけど、無料枠もある。
4. MERGEの使いかた例 例えばWebサービスのデータベースにユーザテーブルある。 DB id name favorites created_at updated_at 1 taro Ruby, SQL 2021/01/01 2022/02/01 2 jiro Java 2021/01/02 2022/02/10
5. MERGEの使いかた例 初回のデータ取り込みはBigQueryに全件転送 ex) select * from users BigQuery (users) id name favorites created_at updated_at 1 taro Ruby, SQL 2021/01/01 2022/02/01 2 jiro Java 2021/01/02 2022/02/10
6. MERGEの使いかた例 Webサービスを介して、以下のように追加と更新があった場合 DB (users) id name favorites created_at updated_at 1 taro Ruby, SQL 2021/01/01 2022/02/01 2 jiro Java, Python 2021/01/02 2022/02/26 3 saburo Python, SQL 2022/02/26 2022/02/26
7. MERGEの使いかた例 updated_at を基準にフィルタしたものを一時テーブルに抽出する ex) select * from users where updated_at >= '2022/02/26' BigQuery (tmp_users) id name favorites created_at updated_at 2 jiro Java, Python 2021/01/02 2022/02/26 3 saburo Python, SQL 2022/02/26 2022/02/26
8. MERGEの使いかた例 BigQuery上の、users、tmp_usersを使ってMERGEクエリを書く場合は以下のようにな る。 MERGE `dataset.users` target USING `dataset.tmp_users` tmp ON (target.id = tmp.id) WHEN MATCHED and target.updated_at < tmp.updated_at THEN UPDATE SET target.name = tmp.name, target.favorites = tmp.favorites, target.updated_at = tmp.updated_at WHEN NOT MATCHED THEN INSERT ROW ;
9. MERGEをつかわないと。。 重複しないようにデータ取り込むのが大変。 MERGEを使うと tmp_usersを雑に作っても、重複しないで取り込むことができる。 過去１年分を取ってきて、漏れている行のみを追加・更新するなど。
10. アプローチ カラム名が取れたらあとは列挙するだけ(漏れなく列挙できれば十分) 妥協したところ 比較テーブルのスキーマも同じとして、INSERTの記述は固定。 細かい条件は後からできるから固定
11. コード require "genbqq" require "google/cloud/bigquery" require "thor" module GenBqq class CLI < Thor desc "merge {project.dataset.table}", "generate merge sql template from BigQuery table" def merge(full_table_str) prj, dataset, tbl = full_table_str.split "." puts "-- project: #{prj}" puts "-- dataset: #{dataset}" puts "-- table: #{tbl}" bq = Google::Cloud::Bigquery.new project: prj target_tbl = bq.dataset(dataset).table(tbl) # puts target_tbl.headers cols_str = target_tbl.headers.map{|col| " target.#{col} = tmp.#{col}"}.join(",\n") result = <<-EOQ MERGE `#{full_table_str}` target USING `#{full_table_str}_tmp` tmp ON(target.id = tmp.id) WHEN MATCHED AND target.updated_at < tmp.updated_at THEN -- id が一致かつ、更新日時が増えている場合は行を更新する UPDATE SET -- 更新対象としたいカラムをすべて記述する #{cols_str} WHEN NOT MATCHED THEN -- 不一致 = 新規行として判断し、追加する INSERT ROW EOQ puts result end end end
12. デモ(準備) BigQueryにデモ用のテーブルを作成
13. デモ https://github.com/u110/genbqq $ gem install genbqq --version "0.1.0" --source "https://rubygems.pkg.github.com/u110"
14. デモ $ genbqq Commands: genbqq help [COMMAND] # Describe available commands or one specific command genbqq merge {project.dataset.table} # generate merge sql template from BigQuery table
15. デモ $ BIGQUERY_CREDENTIALS=/path/to/credentials.json genbqq merge web-u-project.sample_dataset.many_cols_tbl -- project: web-u-project -- dataset: sample_dataset -- table: many_cols_tbl MERGE `web-u-project.sample_dataset.many_cols_tbl` target USING `web-u-project.sample_dataset.many_cols_tbl_tmp` tmp ON(target.id = tmp.id) WHEN MATCHED AND target.updated_at < tmp.updated_at THEN -- id が一致かつ、更新日時が増えている場合は行を更新する UPDATE SET -- 更新対象としたいカラムをすべて記述する target.id = tmp.id, ... -- ここにたくさんカラム名が記載される target.created_at = tmp.created_at, target.updated_at = tmp.updated_at WHEN NOT MATCHED THEN -- 不一致 = 新規行として判断し、追加する INSERT ROW
16. デモ Macであれば、 pbcopy などでクリップボードにコピーし、BigQueryコンソールにペースト すればOK
17. デモ Billing has not been enabled for this project. Enable billing at https://console.cloud.google.com/billing. DML queries are not allowed in the free tier. Set up a billing account to remove this restriction. なんと、無料枠だとDMLが記述できなかった。。
18. 結果 CLIが無かった時 頑張って手でSQLを書く。。。 CLIを使った後 ベースとなるテーブルを用意すれば、MERGEクエリを生成できるようになった!! (∩ ´∀ ｀)∩ ﾜｰｲ
19. 次にできそうなこと・改善できそうなこと そのまま使えるわけじゃない。 結合条件は固定の文字列 日付条件以外もありそう。 他のDB, DBMSに対応？ (が、先にBigQueryがMERGEクエリの記述ルールを簡略化してくれるんじゃないかと期待)
20. まとめ BigQueryのMERGEを書くのが大変だったので（ある程度）生成してくれるCLIを作成し た。 ちょっとしたCLI作るにはRubyは便利ですね