読者です 読者をやめる 読者になる 読者になる

技術のメモ帳

気が向いたときに書いてます

BigQueryのQueryBuilderをgemで公開してみた

Ruby

昨日、ようやくgemとして公開できました。

github.com

6月中旬あたりから始めて、移動中などの時間があるときに コツコツ書いてきたので、「一ヶ月もかかってしまったかー」とも思うのだけど 公開できたので、よかったなーと素直に喜びたいなと思います。

なにができるの?

BigQueryのSELECT文をAR風に書けますよー! ってgemです。 あくまで「風」であって、再現度はそこまで高くないんですけどね!

コード見てもらった方が早いかと思います。

BB.select("word", "corpus", "COUNT(word)").
   from("publicdata:samples.shakespeare").
   where(word_cont: "th").
   group(:word, :corpus).
   to_sql

# => "SELECT word, corpus, COUNT(word) FROM publicdata:samples.shakespeare WHERE (word CONTAINS 'th') GROUP BY word, corpus"

なにがうれしいの?

「え、こんなんわざわざメソッドチェーンで書かないで、素のSQL書いたらいいじゃん」と思いがちですが、

フォームから渡されたparamsに沿って動的に実行クエリを変えて、リクエストを送る という要件があったとき、 毎度毎度、WHERE句を作るのが、とってもダルいのです。case文とか毎回書いてられんのです。

たとえば、次のようなフォームオブジェクトがあったとします。

class Form
  include Virtus.model

  attribute :name, String
  attribute :created_at_gteq, DateTime
  attribute :created_at_lt, DateTime
end

そして処理は次のようなオーソドックスな流れです。

form_for→format_params(StrongParameter)→Form.new(params)→valid→search★

★で、BigQueryとやりとりするとき、Formのattributesをそのまんま使って、クエリを構築したいなと、 ずっと思っていたのですが、いい感じのgemが世の中になかったんですね。

そんな経緯もあって、作ってみた次第です。

使い方

単純なビルダーなので、部分的にも使えるようにしました。 上述のような、WHERE句だけで使う...なんてこともできるように。

READMEのまんまですが、ざっとおさらいです。

SELECT 句

これはフツウに、文字列ないしシンボル渡すと、 よしなにSELECT句を作るってメソッドです。

BB.select(:id, :name, :state).to_sql
# => "SELECT id, name, state"

BB.select("id", "name", "COUNT(*)").to_sql
# => "SELECT id, name, COUNT(*)"

FROM 句

TABLE_DATE_RANGE 関数に対応したり、Array渡してUNION作ったりと、 なんだかんだとBigQueryの仕様に沿っています。

BB.from("publicdata:samples.shakespeare").to_sql
# => "SELECT * FROM publicdata:samples.shakespeare"

BB.from("[applogs.events_20120501]", "[applogs.events_20120502]", "[applogs.events_20120503]").to_sql
# => "SELECT * FROM [applogs.events_20120501], [applogs.events_20120502], [applogs.events_20120503]"

BB.from("applogs.events_", on: Date.new(2012, 5, 1)).to_sql
# => "SELECT * FROM applogs.events_20120501"

BB.from("mydata.people", from: Date.new(2014, 3, 25), to: Date.new(2014, 3, 27)).to_sql
# => "SELECT * FROM TABLE_DATE_RANGE(mydata.people, TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27'))"

BB.from(BB.from("publicdata:samples.shakespeare"), as: shakespeare).to_sql
# => "SELECT * FROM (SELECT * FROM publicdata:samples.shakespeare) AS shakespeare"

JOIN 句

複数JOINにも対応していて、 JOIN EACH など、BigQuery独自のJOINにも対応しています。

BB.from(:customers, as: :t1).inner_join(:orders, as: :t2).on("t1.customer_id = t2.customer_id").to_sql
# => "SELECT * FROM customers AS t1 INNER JOIN orders AS t2 ON t1.customer_id = t2.customer_id"

BB.from(:customers, as: :t1).join_each(BB.select(:id, :name).from(:orders), as: :t2).on("t1.customer_id = t2.customer_id").to_sql
# => "SELECT * FROM customers AS t1 JOIN EACH (SELECT id, name FROM orders) AS t2 ON t1.customer_id = t2.customer_id"

WHERE 句

ここはAR風に、プレースホルダー、または、ハッシュ渡して条件作れます。

BB.where(id: 1..10, name: "donald", flag: false).to_sql
# => "WHERE (id BETWEEN 1 AND 10 AND name = 'donald' AND flag IS false)"

BB.where("id = ? OR name CONTAINS ?", 123, "john").to_sql
# => "WHERE (id = 123 OR name CONTAINS 'john')"

じゃっかんARと変えたのは、 ornot を直感的に書きやすくしたところでしょうか。

BB.where(id: 123).or.where(id: 456).to_sql
# => "WHERE (id = 123) OR (id = 456)"

BB.not.where(id: 123).or.not.where(id: 456).to_sql
# => "WHERE (id <> 123) OR (id <> 456)"

また、ハッシュの場合はSuffixで条件が変えられるようにもしています。

BB.where(name_cont: "Jack", id_gteq: 123).to_sql
# => "WHERE (name CONTAINS "Jack" AND id > 123)"

OMIT RECORD IF句

BigQuery独自のClauseです。

BB.omit_record_if("COUNT(payload.pages.page_name) <= ?", 80).to_sql
# => "OMIT RECORD IF (COUNT(payload.pages.page_name) <= 80)"

GROUP BY 句

GROUP EACH に対応しているくらいで、他は特に言うことないっす。

BB.group(:age, :gender).to_sql
# => "GROUP BY age, gender"

BB.group("ROLLUP(year, is_male)").to_sql
# => "GROUP BY ROLLUP(year, is_male)"

BB.group_each(:age, :gender).to_sql
# => "GROUP EACH BY age, gender"

HAVING 句

ほぼWHERE句の流用なので割愛。

ORDER BY 句

これはほぼARと同じです。

LIMIT 句

OFFSET オプションを付けたくらいかな。

BB.limit(1000).to_sql
# => "LIMIT 1000"

BB.limit(1000).offset(500).to_sql
# => "LIMIT 1000 OFFSET 500"

ネームスペースについて

当初は、「BigQuery::QueryBuilder」とバカ正直なネーミングで 作っていたのですが、名前が長すぎて、使う気になれないんですよね。

なので、2文字くらいで使えるように短くしました。

なにより「b_b」ってnamingは、カオっぽくていいんですよ!

作ってみて良かったこと

  1. 設計力の足りなさに気づけた。

    コードリーディングの分量が足りず、引き出し少ない感をヒシヒシ感じました。 結局は、デザインパターンを再学習したり、コード読みまくることでしか解決できなそうです。

    「この書き方(or設計)、オシャンティやんか」という喜びも発見できるので、 他人のコード読むのは、非常に楽しい瞬間でもあります。

  2. テクが向上した。

    まだまだだなーと思うところもあるのですが、Ruby力のベースはあがった気がします。 gem作ってみると、業務でRails書いていると気付かないことに、いろいろ気付かされます。

  3. 取り組める時間の少なさに気づいた。

    フリーのエンジニアなので、鎌倉←→新宿を毎日往復しているのですが、そこでの1〜1.5時間程度、運良く座れた場合に限り、コードを書いていました。

    「くっ...時間さえあれば...」と何度か心が折れそうになりました。リモート or 鎌倉で業務委託したいと本気で願いました(それくらい時間が作りにくかった)。

  4. スクリプト書くの楽しいよねー!という悦び。

最後に

ということで、興味がある方は gem install b_b してみて使ってみてください。

また、こんなんじゃなくて、BigQueryとActiveRecordの「本気な関係」をお望みの方は、以下で甘いひとときをお過ごしください(Good luck)

github.com