M.T.Burn の BigQuery 利用事例

class: center, middle, inverse

# M.T.Burn の BigQuery 利用事例

## #bq_sushi tokyo #2

### 守山晃生 ([@cou929](http://twitter.com/cou929))

---
class: inverse

# 自己紹介

- 守山晃生 ([@cou929](http://twitter.com/cou929))
- Software Engineer
- [FreakOut, Inc.](https://www.fout.co.jp/)
- [M.T.Burn](http://mtburn.jp/)

![](bq_sushi_img/profile.jpeg)

---
class: inverse

# 会社紹介

- [M.T.Burn 株式会社](http://mtburn.jp/)

![](bq_sushi_img/mtb_logo.png)

---
class: inverse

# 広告の会社です

- モバイル向けの InFeed 型アドネットワーク
    - いわゆるネイティブ広告

![](bq_sushi_img/reason_sample@2x-300x410.png)

---
class: inverse

# Hike という製品を作っています

![](bq_sushi_img/00_Hike_logo.jpg)

---
class: inverse

# [Ignis](https://1923.co.jp/) & [FreakOut](https://www.fout.co.jp/)

![](bq_sushi_img/ignis.png)
![](bq_sushi_img/fo.png)

- [Ignis](https://1923.co.jp/) & [FreakOut](https://www.fout.co.jp/) のジョイントベンチャー
- FreakOut は perl で RTB
    - 最近は Scala や Objc, Java も
- M.T.Burn も perl + golang

---
class: center, middle, inverse

# M.T.Burn の BigQuery 利用事例

---
class: inverse

# アジェンダ

- システム概要
- BigQuery の利用方法
- 実装について
- 運用について
- まとめ

---
class: center, middle, inverse

# システム概要

---
class: inverse

# システム概要

---
class: inverse

# システム概要

- 広告配信を行うアプリケーションサーバ
    - ELB 配下
    - Elasticache を参照
- ログ集計などを行うバッチサーバ
    - cron でバッチが動作
    - mysql や BigQuery がデータソースとなり、計算結果を mysql や Elasticache に格納する
- 管理画面
    - 広告案件の入稿や SDK の取得など

---
class: inverse

# 構築にあたってのポイント

- プロダクト開発にエンジニアリソースを割り当てたい
- 小さなチームで回さなければならない
- インフラ専属の担当がいない
    - シンプル
    - フルマネージド
    - キャッチアップ可能

---
class: center, middle, inverse

# BigQuery の利用方法

---
class: inverse

# BigQuery の利用方法

## 利用箇所

- 広告配信系ログの収集・集計
    - インプレッション、クリック、コンバージョンのログをはじめ、広告配信機能に関わるログの収集・集計
- システム運用系ログの収集・集計
    - バッチ実行結果ログやロードバランサーのアクセスログなど、システム運用に関わるログの収集・集計
- 調査、分析、プロトタイピング
    - アドホックな調査や KPI のモニタリングなど

---
class: inverse

# 広告配信

- 広告配信に関わるログ
    - インプレッション、クリック、コンバージョン
    - 配信実績の集計、レポート
    - 予算管理
    - 広告審査
- 生ログをすべて BigQuery で保持。RDBMS にはサマリーを持つ

---
class: inverse

# システム運用

- バッチ実行ログ
    - バッチ開始・完了時間、終了ステータス、処理内容などを記録
- elb ログ、nginx ログ
- アプリケーションログ
    - 必要なときにロード。問題調査に
- 管理画面の操作ログ
    - 問い合わせ調査に

---
class: inverse

# 調査、分析、プロトタイピング

- 分析のための中間データ置き場
- re:dash での KPI モニタリング
- アドホックな各種分析、問題調査

---
class: inverse

# BigQuery の利用方法

## 規模感

- データ量
    - 数百 GB / day
    - Total 数十 TB
- 費用感
    - 十数万円~ / 月
    - 内訳
        - ストレージ 40 %
        - Streaming Insert 40 %
        - その他 20%
            - クエリ費用
            - 有償サポート費用

---
class: center, middle, inverse

# 実装について

---
class: inverse

# 実装について

- データの投入方法
- データの保管方法 (テーブル設計)
- データの参照方法

---
class: inverse

# データの投入方法

2種類のデータに分類できる

- ある程度リアルタイム性が求められるもの
- アドホックな集計やバッチで良いもの

---
class: inverse

# ある程度リアルタイム性が求められるもの

- [fluent-plugin-bigquery](https://github.com/kaizenplatform/fluent-plugin-bigquery) での Streaming Insert
    - 手軽にはじめられたので
- S3 へも同時 put し冗長化
    - バックアップ用途
    - Streaming Insert 不調時、失敗時には S3 からリカバリする
- Slack へエラー通知
    - 専用 channel に通知
- App サーバから直接
    - シンプルな構成で十分なため
    - ログ集約サーバや kinesis 等のミドルウェアは設けていない

---
class: inverse

# td-agent.conf

- BigQuery への Streaming Insert と S3 への put
    - BigQuery では 1 テーブルに Insert
        - テーブルの設計は後述
    - s3 上ではログ種別と時間帯でディレクトリ分け
        - `s3://bucket/ad/20150101/00/2015010100_mtburn.ad.ip-10-0-1-1_1.gz`
    - [fluent-plugin-forest](https://github.com/tagomoris/fluent-plugin-forest) で設定ファイル整理

[td-agent.conf](https://gist.github.com/cou929/145866169d58d0827f8a)

---
class: inverse

# fluent-plugin-bigquery のパラメータ

```sh
flush_interval 3s
try_flush_interval 0.05
queued_chunk_flush_interval 0.01
buffer_type file
buffer_chunk_limit 512k
buffer_queue_limit 1000
buffer_chunk_records_limit 300
retry_limit 10
max_retry_wait 1s
num_threads 10
```

- 1 行あたり 300 KB 程度 (無圧縮)
- リクエストは 5000 qps 程度 (全体)
- Insert するデータが大きすぎたり細かすぎたり、リトライ間隔が短すぎたりするとエラーとなる
- この設定で 1 年以上安定稼働

---
class: inverse

# エラーの Slack 通知

- `td-agent.log` の内容を Slack に通知している
    - メッセージの例
    - このように、BigQuery が 5xx を返した事象などは、この通知で把握している

```sh
app-notifier BOT [12:42 AM]
fluentd.internal.message.ip-10-0-1-160
---
[00:42:14] tabledata.insertAll API project_id="xxxxxx" dataset="mtburnlog" table="media_auth_log" code=503 message="Error encountered during execution. Retrying may solve the problem."
```

- [td-agent.slack.conf](https://gist.github.com/cou929/40e8af9639aad31fedf6) の例
    - 頻出かつ意味の小さいメッセージは filter
    - 同内容のメッセージは [suppress](https://github.com/fujiwara/fluent-plugin-suppress)

---
class: inverse

# アドホックな集計やバッチで良いもの

- S3 に入っているログを調査のため一時的に BigQuery に入れる場合や、すぐに解析する必要のないシステム系のログなどは、定期的にバッチでロードする
- [embulk](https://github.com/embulk/embulk) と [bq load コマンド](https://cloud.google.com/bigquery/bq-command-line-tool?hl=en) で import する
- [fluent-plugin-s3](https://github.com/fluent/fluent-plugin-s3) でログを S3 に保存
    - S3 の安定性
    - その他にも elb のログなど、AWS のサービスが直接 S3 に保存するものもあるため
- embulk で S3 から GCS に移動
    - [embulk-output-gcs](https://github.com/hakobera/embulk-output-gcs)
- `bq load` コマンド

---
class: inverse

# データの保管方法 (テーブル設計)

- ひとつのテーブルに全て Insert
- メリット
    - 初期のシンプルな設計
    - ほぼ何も考えずに使える
- デメリット
    - デコレーター範囲外の過去データへのクエリが高くつく
    - リカバリが難しい
    - 不要データの purge が不可能
        - データ保持料金は安価なので、それなりに将来まで不要ではある

---
class: inverse

# データの保管方法 (テーブル設計)

## 改善策

- 日次でテーブル分割
    - 過去データも一日単位のデータ量からクエリ可能
    - テーブル単位でデータを入れなおすリカバリが可能
    - テーブル単位で削除可能
- 必要な物を順次移行中

---
class: inverse

# テーブル分割の実装

- `td-agent.conf` で [table 名を動的に変える](https://github.com/kaizenplatform/fluent-plugin-bigquery#table-id-formatting)
    - `table accesslog_%Y_%m` など
- `auto_create_table true` などのパラメータを適宜設定
- クエリの際は [Table wildcard functions](https://cloud.google.com/bigquery/query-reference?hl=en#tablewildcardfunctions) を利用
    - `TABLE_DATE_RANGE` や `TABLE_QUERY`
    - 複数のテーブルを指定できる

---
class: inverse

# データの参照方法

- API (perl から)
    - Table Decorators
- CLI
- Web UI
- Google Spreadsheets
- re:dash

---
class: inverse

# API (perl から)

- 配信ログの集計バッチなどを perl で実装
- クライアントライブラリが当時なかったため [API](https://cloud.google.com/bigquery/docs/reference/v2/) を直接コール
    - クエリを投げるだけの簡単なもの
    - 必要最低限の機能しかないため未公開
- リクエストタイムアウトと失敗時のリトライ
    - この程度で十分使えている
- query API の JobID を記録
    - サポートへの問い合わせの際に必ず必要になるため
- バッチはリトライ可能な設計

```sh
----------------------------------------
[INFO] analyze ad timed logs 2015-11-19 15:00:00
[INFO] start update_db_ad_timed_logs
[INFO] Processed 1,231,736,298 bytes
[INFO] Job completed  jobId:job_9gAODMW3B-uAwSokm3NujxydURY
[INFO] Processed 1,331,850 bytes
[INFO] Job completed  jobId:job_Ks2hfvpMLFrMaPuOn0ZbodHxBpE
[INFO] Processed 2,149,222,948 bytes
[INFO] Job completed  jobId:job_S2ETfchOCHFKAdBVnDmMrRiMpp8
[INFO] finish update_db_timed_ad_logs :updated_count=60284
----------------------------------------
```

---
class: inverse

# Table Decorators

[Table Decorators - BigQuery — Google Cloud Platform](https://cloud.google.com/bigquery/table-decorators)

- 探索範囲を絞ることで、クエリ料金を節約
    - 実質全クエリに必須
- インサートされたレコードの日付時刻の範囲を指定する
    - `レコードがインサートされた日付時刻` なので、リカバリの際は本来対象となるレコードがきちんと含まれるかを注意する
        - 「そのレコードがいつインサートされたものなのか」を知る術は無い ([How to refer commit time of the records on BigQuery - Stack Overflow](http://stackoverflow.com/questions/26836491/how-to-refer-commit-time-of-the-records-on-bigquery))
- 指定できるのは 7 日間以内

```sql
SELECT
  DATE_ADD(time, 9, 'HOUR') jst,
  ROUND((finish_time - start_time)/1000/1000/60, 2) elapsed_min
FROM
  [mtburnlog.cli_log@1447250400000-]
WHERE
  subcommand = 'automate_bidding_price'
ORDER BY
  time ASC
```

---
class: inverse

# CLI

[bq Command-Line Tool - BigQuery — Google Cloud Platform](https://cloud.google.com/bigquery/bq-command-line-tool?hl=en)

- エンジニアはこれ
- デコーレーターを補助する [dbq](https://github.com/yoheimuta/dbq) コマンドをよく利用している

---
class: inverse

# dbq

[Go - dbq（CLI tool to easily Decorate BigQuery table name）を作ってみた - Qiita](http://qiita.com/yoheimuta/items/bedb9080c9601cffa093)

- デコレーターを補完してくれる CLI ツール
- このような入力で

```sh
dbq query "SELECT * FROM [mtburnlog.cli_log@]" --startDate="2015-11-10 00:00:00" --tz="-9"
```

- このように補完してくれる

```sql
SELECT * FROM [mtburnlog.cli_log@1447077600000-]
```

---
class: inverse

# dbq

- `--dryRun` で走査バイト数、推定料金を表示
    - デコレーターなしの場合との比較も

```sh
Raw: SELECT time FROM [mtburnlog.cli_log]
Query successfully validated. Assuming the tables are not modified, running this query will process 5925360 bytes of data.

- 5925360 bytes equal to 5,925,360 bytes
- 5925360 bytes equal to 5.7MiB
- 5925360 bytes equal to $0.00003 (= 0.00001 TiB * $5)

Decorated: SELECT time FROM [mtburnlog.cli_log@1447077600000-]
Query successfully validated. Assuming the tables are not modified, running this query will process 125256 bytes of data.

- 125256 bytes equal to 125,256 bytes
- 125256 bytes equal to 122KiB
- 125256 bytes equal to $0.00000 (= 0.00000 TiB * $5)
```

---
class: inverse

# Web UI

- セールス、ディレクターはこれ
- クエリ料金については周知徹底

---
class: inverse

# Google Spreadsheets

- [BigQuery Connector for Excel - BigQuery — Google Cloud Platform](https://cloud.google.com/bigquery/bigquery-connector-for-excel) を利用
- セールス・ディレクターが使い慣れたツールというメリット
  - セールスサイドでエンジニアを介さずダッシュボーディング
- 現在は re:dash に移行中

---
class: inverse

# re:dash

[re:dash - Data Collaboration and Visualization Platform](http://redash.io/)

- クエリを登録していろんなデータソースからデータを取得して可視化できるダッシュボードにできる
- bq と mysql の JOIN や Alert 機能も
- Spreadsheets の課題であったメンテナンス性・共有性を解決できそう

---
class: center, middle, inverse

# 運用について

---
class: inverse

# 運用について

細々とした運用 tips について

- 運用
    - テーブルスキーマ管理
    - カラム追加
    - バッチ実装のポイント
- 障害
    - データ増によるクエリのサイズ上限超過
    - Streaming Insert 失敗
    - クエリの遅延
- 監視
    - クエリのパフォーマンス
    - gcp の status
    - `#gcpug`, `#bq_sushi`
- チューニング
    - 費用のチューニング

---
class: center, middle, inverse

# 運用

---
class: inverse

# [運用] テーブルスキーマ管理

- 単純に table_schema の json ファイルをリポジトリにコミット
  - bq コマンドに渡せる形式

```json
[
    { "name":"time", "type":"timestamp" },
    { "name":"start_time", "type":"timestamp" },
    { "name":"finish_time", "type":"timestamp" },
    { "name":"subcommand", "type":"string" },
    { "name":"log_level", "type":"string" },
    { "name":"message", "type":"string" }
]
```

---
class: inverse

# [運用] カラム追加

- 前提
    - `fluent-plugin-bigquery` は動的にスキーマを fetch する運用にしている。
    - 動的なロードは `td-agent` をリロードした際に行われる

1. `schema.json` を更新
2. p-r を作成しレビュー、マージ
3. `bq update` コマンドで新 schema を適用。カラムが追加される
  - この時点で新カラムにが NULL のレコードが追加されている
4. td-agent をリロード
  - この時点で新テーブルスキーマを fetch。新カラムにも値が入り始める

ポイントは `td-agent 側が新カラムを未 fetch で bq 側に新しいカラムがある状態では、値が NULL となる` ということ。

---
class: inverse

# [運用] バッチ実装のポイント

- クエリはまあまあ失敗する。実行時間もある程度ぶれる
    - カジュアルにリトライする作りにする。あるいは並列で複数投げる
    - タイムアウトを設定
    - バッチ実行の冪等性を担保
- データを投入しなおした場合
    - デコレーターの指定範囲に入っているかを確認
    - リトライ用にデコレーターをつけないようにするオプションを準備するなど

---
class: center, middle, inverse

# 障害

---
class: inverse

# [障害] データ増によるクエリのサイズ上限超過

## 現象

- サービスの成長に伴いデータサイズが増加。API の quota にひっかかる
    - 集計バッチの実行失敗で気がつく

---
class: inverse

# [障害] データ増によるクエリのサイズ上限超過

## 対応

- クエリを分割する
    - 集計対象を時間で分けて、1 リクエストあたりのデータサイズを小さくする
- クエリをチューニングする
- response size をログに落として監視

---
class: inverse

# [障害] Streaming Insert 失敗

## 現象

- Streaming Insert が失敗する
  - 5xx のレスポンスが返ってくる
  - 数時間継続

---
class: inverse
# [障害] Streaming Insert 失敗

## 対応

- 失敗分は S3 へのバックアップから再投入。その後必要に応じて再集計
    - 時間はかかっても最終的に正しい状態に収束することが担保されている
    - 例外は配信予算管理
- 広告配信予算管理
    - 広告配信ログの集計が遅れると、配信に使った金額が一時的に未集計になる。そのため最終的に予算を超えた配信をしてしまうことになる
        - 支払い時の調整、広告主への説明などで、社内の対応コストが高い。また利益にならない配信をすることでの機会損失も起こっている
    - アプリケーション側でカバーする仕組みを実装
        - 配信実績を KVS に保存し、超過配信を防ぐ
- [有償サポートプラン加入](https://cloud.google.com/support/)
    - 障害状況の詳細を聞く、個別対応の push など
    - シルバーサポートを利用中
        - 加入をおすすめ

---
class: inverse

# [障害] クエリの遅延

## 現象

- Streaming Insert をしてから、それが SELECT できるようになるまでの遅延が発生
    - 正しい SELECT 結果が返ってくるまでに数時間かかる
    - クエリは成功として返ってくるので問題の検知が遅れた
    - Streaming Insert だけの症状だった

---
class: inverse

# [障害] クエリの遅延

## 対応

- サポートチケットを起票し情報収集。対応を push
    - 一番早くて正確な情報が得られる
- 集計異常値の監視を追加
    - エラーがなくても急減に気がつけるように
- Streaming Insert ではない投入方法への移行
    - 最近は安定しているので優先度は見計らう

---
class: center, middle, inverse

# 監視

---
class: inverse

# [監視] クエリのパフォーマンス

- クエリにかかる時間と処理したデータ量をログに残す
    - データ量はクエリのレスポンスの `totalBytesProcessed`
- API quota やクエリ料金への対応の目安として
- また jobId を記録することも重要
    - サポートへの問い合わせの際に必ず必要になる

---
class: inverse

# [監視] gcp の status

- [Google Cloud Status](https://status.cloud.google.com/) の RSS フィード

![](bq_sushi_img/gcp_status.png)

---
class: inverse

# [監視] #gcpug, #bq_sushi

- [GCPUG Slack](https://docs.google.com/forms/d/1Pe2idyW79_GsYPHkON_oulC7hfzszzwWORbOi6q46uI/viewform?c=0&w=1) や [#bq_sushi](https://www.facebook.com/groups/bq.sushi/) を ROM
    - 監視ではないですが :sweat:

---
class: center, middle, inverse

# チューニング

---
class: inverse

# [チューニング] 費用

[Pricing - BigQuery — Google Cloud Platform](https://cloud.google.com/bigquery/pricing)

- かかる費用は 3 種類
- データの保持
    - 1 TB を 1 ヶ月保持すると $20
- データの Insert (Streaming Insert のみ)
    - 200MB ごとに $0.01
- データの取得
    - 1GB 処理するごとに $5 かかる

---
class: inverse

# [チューニング] データ保持費用

- 古いデータを消すしか無い
    - そのためにも時間でテーブルを分割する設計が有効。古いテーブルを消せばよい
- ただし、この値段で過去データをいつでも引けるのはメリットなので、弊社では過去データの削除は行っていない

---
class: inverse

# [チューニング] データの Insert 費用

- Streaming Insert をしないことを検討
    - `bq load` など別の方法なら無料
    - すぐに参照できなくてよいものは検討価値あり
    - 一時期は Streaming Insert が不安定だったので安定化にも?
        - 最近は安定している印象
- ただし Streaming Insert はメリットも多い
    - すぐにログデータを使えるようになるのは大きなメリット
        - 近年のニーズにも合っている
    - [kaizenplatform/fluent-plugin-bigquery](https://github.com/kaizenplatform/fluent-plugin-bigquery) のおかげで導入ハードルが低い
    - 8 月のアップデートから費用は安くなった (弊社の場合)
        - 以前は 100,000 行ごとに $0.01 だった
        - 小さい大量のログを入れる場合に有利になった

---
class: inverse

# [チューニング] クエリ料金

- 必ずデコレーターをつける
    - 対象データが過去 7 日間以内の場合
- SELECT を最低限に
    - `SELECT *` は禁止
- 走査バイト数を確認する
    - `bq query --dry_run` で処理データ量がわかる
    - Web UI の場合 :white_check_mark: マークをクリック
- [dbq](https://github.com/yoheimuta/dbq) がおすすめ

---
class: center, middle, inverse

# まとめ

---
class: inverse

# まとめ

- 完全フルマネージド
    - チームのリソースを機能開発に充てられる。スタートアップのニーズにぴったり
    - いくつかコツを押さえておけば運用コストは低い
- 基本的に費用は安い
    - 特にデータ保持費用は安価なので、何も考えずにとりあえずデータを入れておける
    - クエリも失敗を気にせず投げてしまったほうが早い
    - ある程度「雑」にプラットフォームを使うイメージで、考え方を変える
- 類似のプロダクトはなかなかない

---
class: center, middle, inverse

# 最後に

---
class: inverse

# 最後に

[世界で通用する新しい広告プラットフォームを作りたいエンジニア WANTED - エム・ティ・バーン株式会社の求人 - Wantedly](https://www.wantedly.com/projects/24007)

- 弊社の GCP 運用を良くしてくれる方
- 弊社のログ基盤を構築してくれる方
- 世界で通用する新しい広告プラットフォームを作りたい方
- スマホ広告を良くしたい方
- SQL/Qiita:Team 標準装備のセールスチームと働きたい方