BigQueryのクエリ結果の各種統計値を1コマンドで算出するbq_profileの紹介

この記事はSENSY Advent Calendar 2019の9日目の記事です。
この記事ではSENSY内で活用している社内ツールbq_profileを紹介していきます！

GitHubのCOLORFULBOARD/bq_profileにソースコードとインストール方法を載せています。興味のある方はぜひ使ってみてください。Dockerが動く環境であれば利用できます。

bq_profile とは

bq_profile はBigQuery上の任意のクエリ結果に対する各種統計値を1コマンドで算出するツールです。

SENSYではたくさんのクライアントから日々様々なデータを受領、分析し、機械学習などに利用しています。そして、5日目の記事「BigQuery で 1 円も溶かさない人の顔 (ZERO BYTE STRUCT を考案した)」でも触れられているように、SENSYの業務はBigQuery中心と言っても過言ではないほどにBigQueryがフル活用されています。

クライアントから受領するデータも例に漏れず、まず最初にBigQueryに格納しています。この受領データは本当に様々なものがあり、先方の担当者でさえデータの詳細を把握していないことは日常茶飯事です。したがって、BigQuery格納後はまずどんなデータなのかを確認する必要があり、そのためにbq_profileを作りました。

bq_profile でできること

BigQuery上の任意のクエリ結果に対して、各種統計値を1コマンドで算出できます。

基本の使い方

例えば、ローカルから次のコマンドを実行し、パブリックデータbaseball.games_post_wideの全データの統計量を算出してみましょう。

# local は~/.config/gcloudの認証情報を利用する場合に指定。GCE上で実行する場合は不要。
$ bq_profile local \
  --sql "SELECT * FROM \`bigquery-public-data.baseball.games_post_wide\`;" \
  --project your_project \
  --mode sql \
  --output-table temporary.sample_stats

これを実行すると、your_project:temporary.sample_statsテーブルにクエリ結果に対する各種統計値が書き出されます。

この結果のテーブルは次の画像のようになります。
nameフィールドが元データの列名、typeが元データのデータ型に対応しており、元データのデータ型により取得できる統計値は異なります。
たった1コマンドで様々な情報がわかるため、社内でもとても便利に使っています。

ここから、もう少し詳細を紹介していきます。

--modeオプション

bq_profileは「いかに簡単に統計値を算出するか」に焦点を当てたツールです。
そのため、統計値の算出方法自体は既存のツールを利用しています。
ツールは--modeオプションで指定でき、現在は次の3つのツールを利用できます。
どのモードでも任意のSQLに対して統計値を算出できるため、様々なシーンで活用できます。

mode: sql

--mode sqlを指定すると、同僚のna0氏が書いたSQLを利用して統計値を算出します。先ほどのコマンドはこれを指定していました。BQのリソースで計算を行うため、データが大きくても処理時間がそれほどかからない点が最大の魅力です。
結果の出力先は次のオプションで指定できます。

• --output-table BQテーブル名を指定するとBigQueryのテーブルに書き出される
• --output ファイル名を指定するとCSV形式でローカルファイルやGCS(gs://~で指定)へ書き出される

結果となるテーブルのスキーマは次の通りです。

mode: pandas-profiling

pandas-profilingは、DataFrameの情報をグラフィカルに表示してくれる便利なツールです。
bq_profileはもともとこのpandas-profilingを楽に利用したくて作り始めたツールでした。
--mode pandas-profilingを指定すると、このpandas-profilingを利用して統計値を算出できます。
結果の出力先は次のオプションで指定できます。

• --output ファイル名を指定するとHTML形式でローカルファイルやGCS(gs://~で指定)へ書き出される

例えば、次のコマンドを実行してみましょう。

bq_profile local \
  --sql "SELECT * FROM \`bigquery-public-data.baseball.games_post_wide\` ORDER BY gameId LIMIT 1000;" \
  --project your_project \
  --mode pandas-profiling \
  --output output.html

しばらく待つと、次のoutput.htmlが作成されます。とても見やすくて素晴らしいツールですね。

ただし、pandas-profilingは小さなデータに対してはとても有効ですが、データが大きくなればなるほど処理に時間がかかります。ある程度大きなデータの場合は、先に紹介した--mode sqlを利用してください。

mode: describe

先に説明した2つのモードがあればほとんど十分だと思いますが、--mode describeを指定すると、pandas.DataFrame.describeを利用して統計値を算出できます。
--mode sqlとほとんど同じですが、結果となるテーブルのスキーマは次の通りです。

最後に

bq_profileは単なる既存ツールの薄いラッパー¹であり、まだできて間もないツールです。
ただ、それでも実際に使っていて便利だなと思えるツールになっています。みなさんもぜひ使ってみてください！
社内でもこのbq_profileをもっと活用し、定期連携で過去と傾向が見つかった際に自動でアラートを出す仕組みなどを構築したいと考えています。

1. bq_profileのように「すでにあるツールを簡単に利用できるようにする」ツールの作成は、手をつけやすい上に効果も実感しやすいです。そのため自作ツールのネタとしてもおすすめできます。 ↩