BigQueryのクエリをコードとして管理する

この内容はWANTEDLY TECH BOOK 9でも書いてあるのでぜひそちらも見てもらえると嬉しいです。

Wantedlyのメンバーで作った9冊目のWANTEDLY TECH BOOKです。個々のメンバーが日々の業務で得られた知見や他のメンバーに知っておいてほしいことを1冊の本にまとめました。今回はユーザーの体験に大きく関わるマッチング領域について 2 つの章で扱い、その基盤となるデータエンジニアリングについても触れます。また、サービスに求められる信頼性も年々高まる中で続けてきたプロダクトの安定性を支える取り組みである post mortem や技術選定の背景についても扱います。 WANTEDLY

https://techbookfest.org/product/6095813658804224?productVariantID=5549265715724288

Wantedlyでエンジニアをしている一條です。普段はデータサイエンティストの人たちと一緒にプロダクトの改善であったりデータ周りの改善に取り組んでます。
今回は個人的に作っている rerost/bqvというツールについて書きたいと思っています。
これはWantedly社内で実際に導入して使っているツールです。

rerost/bqv

BigQuery view tool. Contribute to rerost/bqv development by creating an account on GitHub.

https://github.com/rerost/bqv

また社内で行った速習会のときの資料もあるのでそちらも合わせてみてもらえると嬉しいです。

また、最後にもありますが、この記事を書いてある最中に似たツールも見つけてしまったのでぜひそちらの利用も検討してみてほしいです。

k-kawa/bqv

The simplest tool to manage views of BigQuery. go install github.com/k-kawa/bqv Or you can use bqv as a Docker image which is available at kkawa/bqv Make a directory based on the names of the dataset and the view that you want to create.

https://github.com/k-kawa/bqv

課題意識

Wantedlyでは一部例外はありますが、基本的にはデータ分析はアプリケーションエンジニアが行います。
その上で課題意識としては「データ分析もアプリケーションと同じように行いたい」というのがありました。更にそれにより暗黙知が増えてしまうという現象が起こっていました。

具体的に、データ分析がアプリケーションのようにできない課題感をまとめると次になります。

レビューを頼める仕組みがない
クエリが一覧・検索できない
クエリの再利用がしづらい
クエリ名が社内で一意にならない
BIツールなど様々な箇所で色んなテーブルやクエリが参照されてしまう

僕のBigQueryの使い方のイメージを共有するために、実際に僕が分析する際のクエリを擬似的に書きたいと思います。テーブル名やカラムなどは架空のものです。これを前提に課題感を一つずつ説明していきます。

WITH people_posts AS (
  SELECT ...
  FROM ...
  INNER JOIN ...
  WHERE
    deleted_at IS NOT NULL AND
    ...
), ... AS (
  SELECT ...
  FROM ...
  INNER JOIN ...
  WHERE ...
), ... AS (
  SELECT ...
  FROM ...
  INNER JOIN ...
  WHERE ...
)

SELECT 
  COUNT(*) OVER ... AS daily_count,
  IF(week = day, COUNT(*) OVER ..., NULL) AS weekly_count,
  IF(month = day, COUNT(*) OVER ..., NULL) AS monthly_count,
  COUNT(DISTINCT user_id) OVER ... AS daily_users,
  IF(week = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS weekly_users,
  IF(month = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS monthly_users
FROM people_posts
INNER JOIN ...

レビューを頼める仕組みがない

Wantedlyではコードレビューはほぼ全てのPullRequestで行われています。クエリは基本BigQueryで直接実行し、クエリと結果をissueに貼り付ける、といったことをよくしています。
これにより、GitHubのコードレビュー機能に乗れなかったり、WITH句の部分が基本コピペになり、レビューのコストが高くなっています。

クエリが一覧・検索できない

どういった部分が自分にとって再利用できるか、似たようなクエリがないか、このテーブルを使っているクエリはどれか、などがぱっとわかる方法はありませんでした。
もちろんBigQueryのクエリの実行履歴からたどるなどの方法もありますがあまり使いやすくはなく、また普段のGitHubやエディタを使った検索などが行なえないのはストレスでした。

クエリの再利用がしづらい

コードであれば関数や構造体などを利用することで再利用可能になります。
しかし、導入当初はクエリに関してはなかなか再利用が行いづらく、分析する人それぞれがWITH … AS …
などのクエリをコピペするようになっていました。
これにより、そもそもコピペされたコードがあちこちにあるため、データの形式が変わった際や、定義が変わった際に修正を行いづらいという問題がありました。

クエリ名が社内で一意にならない

例えば、

WITH people_posts AS (
  SELECT ...
  FROM ...
  INNER JOIN ...
  WHERE
    deleted_at IS NOT NULL AND
    ...
)

というクエリがあったとき、people_posts という名前がついているため、人とのコミュニケーションでも people_posts という名前がよく出ます。しかし実はpeople_posts
には解釈が2つあって、「Wantedly Visit内で（botではなく）ユーザーが作成した投稿」と、「Wantedly People内でユーザーが作成した投稿」という2通りが考えられます。これによりコミュニケーションコストが高くなったり、そもそも認識違いが起こってしまう、といったことが起きます。このような問題は実際社内ではたまに問題になっていました。

BIツールなど様々な箇所で色んなテーブルやクエリが参照されてしまう

BIツールには必ずBIツール内でクエリを書く機能があります。
確かに便利なんですが、さまざまな箇所にクエリが書かれてしまうため、アプリケーションエンジニア目線だと、データの形式を変更をしてよいかや、そもそも使われていないので消したい、といったケースに対応できなくなります。

解決策

これらの課題意識を解決するために、基本的に次の方針にしました。

GitHubにクエリをコミットする
- 解決できる課題
- - レビューを頼める仕組みがない
  - クエリが一覧・検索できない
Viewで管理する
- 解決できる課題
- - クエリの再利用がしづらい
  - クエリ名が社内で一意にならない
  - BIツールなど様々な箇所で色んなテーブルやクエリが参照されてしまう

また、これらの課題を解決するために過去に行った施策の反省点として、「手軽さ」がないと誰も使わないというのがありました。競合としてはクエリのコピペだったので同じくらいの手軽さにする必要がありました。

なので色々な方法を試した結果、クエリを書いてGitHubにPushするだけでそのクエリを使ったviewがBigQuery上に生成される、という形にしました。
具体的にはつぎのようなフォルダ構成とファイルを作成しPushすると、次のようなデータセットがCIを通じて生成されます。
それぞれの*.sqlファイルにはviewで使うクエリを書きます。

レビューを頼める仕組みがない
- => GitHub上でレビューをすることで解決
クエリが一覧・検索できない
- => GitHub上で検索 or 手元で検索
クエリの再利用がしづらい
- => viewになっているためBigQuery上ではいくらでも再利用できる
クエリ名が社内で一意にならない
- => 少なくとも同一のデータセット内では一意。それ以外の場合でも一覧できるのでかぶった場合は検知できる。
BIツールなど様々な箇所で色んなテーブルやクエリが参照されてしまう
- => BIツールから参照するのはviewのみにする

こういったことを簡単に実現するためにCIから使われるツールとしてrerost/bqv を書きました。

なぜGoか

ちなみにこのツールはGoで書かれていますが一応ざっくり背景だけ書いておきます。

書かれるクエリの量が今後膨大になっていくことが考えられたのと、BigQueryはそこそこ遅いイメージがあったため、並列処理で一気にファイルとBigQueryのview同期を行いたかったです。また、体感ですがGCP周りのAPIはGoが一番使いやすいというのがありました。
また、今後の機能追加の際に、クエリパーサーであるzetasqlのようなものを使いたかったのでC言語を再利用できるものがよかったというのもあります。

google/zetasql

ZetaSQL defines a language (grammar, types, data model, and semantics) as well as a parser and analyzer. It is not itself a database or query engine. Instead it is intended to be used by multiple engines wanting to provide consistent behavior for all sema

https://github.com/google/zetasql

また、一番大きかった点としては、僕自身が普段Goでサービス開発を行っているというのがあります。

実際導入してみてどうだったか

抱えていた課題が全て解決され、また手軽さがかなり大きいため、僕などは積極的に使うようにしています。
現状では、

一部のBIツールで使うクエリなどはbqvで書いて参照する
機械学習用に使うクエリと分析用に使うクエリを一緒にする

という使い方をしています。

しかし、決めるべきルールはいくつかあり、試しつつ決めていきました。それを紹介します。

命名規則をどうするか

viewを作成する際には自由に命名できてしまうため、命名ルールをある程度決める必要がありました。また、フォルダを作ることでデータセットが簡単に作れてしまうため、それによりよくわからないデータセットが増える、というのも避けたいです。
なので、次のような命名規則にしました。

データセット名: (<domain>_)<purpose>
view名: (<condition>_)*<entity>

例えば、Wantedly Visitに関するクエリで、他のクエリと組み合わせて使われているものを想定しているものに関してはvisit_partsというデータセット名にします。また、クエリの内容としてはユーザーのうち、スカウトされた人、という場合のときはscouted_usersとします。
このようにすることで、viewを見たときにパッと使い方がわかり、また必要そうなものも名前から探すことができます。

クエリ自体を生成するようなBIツールとの付き合い方

viewとしてテーブルを作ることで、BigQueryを使う様々なアプリケーションからアクセスすることがメリットの一つとして考えられます。
例えば、機械学習のデータを集める際に利用したり、Push通知などを送る際の対象を出すために使うなどです。
しかし、クエリを生成するようなBIツールとなると、そのクエリ自体を再利用することが困難になります。これについては、あまりいい解決策ではないですが、一度BIツール側で生成されたクエリをviewに落とす、といったことをしています。
また、逆にBIツールの問題で、表現力的に直接SQLを書くようなケースも存在します。そういったケースでは積極的にrerost/bqvを使ってviewにする、ということをしています。

まとめ

言いたかったこととしては

クエリのコピペはやめてviewで管理する
GitHub上でそれらを管理するためにrerost/bqvを作成した
実際運用してみて、命名規則や他のツールと付き合い方を考える必要があった

です。
結果としてはかなりアプリケーション開発と似た形になるようになったので、体感としてはかなり生産性は上がりました。

補足

bigquery view manageなどで検索してもツールが見つからなかったため、このようなツールを作っていましたが、この記事を書いている最中にhttps://github.com/k-kawa/bqv を見つけました。
同じ思想で同じ名前ですが、k-kawa/bqv の方が高機能でドキュメントもしっかりしているため、ぜひ同じような問題を抱えているときはそちらも検討してみてほしいです。