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

この記事は[SENSY Advent Calendar 2019](https://qiita.com/advent-calendar/2019/sensy)の9日目の記事です。
この記事ではSENSY内で活用している社内ツールbq_profileを紹介していきます！

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

## bq_profile とは

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

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

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

## bq_profile でできること

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

### 基本の使い方

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

```shell
# 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コマンドで様々な情報がわかるため、社内でもとても便利に使っています。

<img width="1213" alt="Screen Shot 2019-12-08 at 13.05.01.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/92660/43a74ee2-43c0-d109-4d47-47ff75e6f032.png">
<img width="1206" alt="Screen Shot 2019-12-08 at 13.04.12.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/92660/94ad5994-09a4-e63a-783b-be0cd8cc31da.png">
<img width="1206" alt="Screen Shot 2019-12-08 at 13.04.46.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/92660/0c0553a9-5599-d655-4e06-a870e8a47ad6.png">

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

### --modeオプション

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

#### mode: sql

`--mode sql`を指定すると、[同僚のna0氏が書いたSQL](https://github.com/COLORFULBOARD/bq_profile/blob/master/src/bq_profile/profiler/bq_sql.py#L53)を利用して統計値を算出します。先ほどのコマンドはこれを指定していました。BQのリソースで計算を行うため、**データが大きくても処理時間がそれほどかからない**点が最大の魅力です。
結果の出力先は次のオプションで指定できます。

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

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

<img width="285" alt="Screen Shot 2019-12-08 at 13.14.43.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/92660/ce083325-8a7e-b3df-2c6b-fdb3dc2d3fa2.png">

#### mode: pandas-profiling

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

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

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

```shell
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`を利用してください。

<img width="964" alt="Screen Shot 2019-12-08 at 13.32.14.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/92660/39965796-33ed-334a-0f65-7c0f9d13b979.png">
<img width="975" alt="Screen Shot 2019-12-08 at 13.32.35.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/92660/0b7be2c2-3948-7b9b-5115-01da30cfb971.png">
<img width="973" alt="Screen Shot 2019-12-08 at 13.32.49.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/92660/3ce6bed1-78c3-0744-79b0-15b6e1fe7d1c.png">


#### mode: describe

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

<img width="301" alt="Screen Shot 2019-12-08 at 13.18.04.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/92660/127664f8-4d5b-fbe4-7a82-e43cdec8460d.png">

## 最後に

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

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