Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Yuu Ito
February 26, 2022

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

Yuu Ito

February 26, 2022
Tweet

More Decks by Yuu Ito

Other Decks in Technology

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 ~ 長野県塩尻市 職場は東京なので、フルリモ ート(そろそろ2年目) 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を雑に作っても、重複しないで取り込むことができる。 過去1年分を取ってきて、漏れている行のみを追加・更新するなど。

  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は便利ですね

  21. None