※ こちらはミツモアAdvent Calendar 2021の12/20分の記事です。

こんにちは、ミツモアCTOの柄澤（@fmy）です。今回は、ちょうど1年半前ぐらいに整備したミツモアデータ分析基盤についてご紹介いたします。（記事にするのが遅くなってすみません）

全体像

ミツモアのデータパイプラインの全体像は以下のようになっています。

f:id:meetsmore:20211218140910p:plain — ミツモアのデータパイプライン

このパターンはETL（Extract / Transform / Load）ではなくELTと言われ、ExtractとLoadを行ったのちに、データウェアハウスの中でTransformを実施する戦略となっています。BigQueryに代表される今日のデータウェアハウスは膨大なデータを保持し、大量の並列処理を可能とする能力があるので、一旦データウェアハウスにあらゆるデータを入れたのちに、自由にデータの変換処理をする方が筋が良いように思っています。

今回はさまざまなデータソースから全てのデータをBigQueryに集約する部分を担っているStitchと、BigQueryに集約されたデータを分析しやすい形式にするためのdbtの2つについてご紹介します。

Stitchを用いたデータの集約

Stitchは、130以上のデータソース（MySQLなどのデータベースからAdwordsやSalesforceといったSaaSのデータまで幅広く対応）からデータを取得し、BigQueryやRedshift、Snowflakeなどのデータウェアハウスにデータを保存するサービスです。ELTのうち、ExtractとLoadを担当していると言えます。

似たサービスとしてはFivetranやSegment、Airbyte、Xplentyなどがあります。

ミツモアでは主にMongoDBと広告関連のデータをBigQueryに転送するためにStitchを使っています。MongoDBはスキーマレスなデータベースですが、Stitchは実際に保存されているデータからスキーマを類推しBigQueryに保存してくれるので、新しいテーブル同期したい時の作業がかなり削減されます。

データベースの転送には、logベース、keyベース、フルテーブルの3つのタイプがあります。logベースはMongoDBの場合はOpLog、MySQLの場合はbinlogなどを用いて更新差分を抽出する方式です。可能な場合この方式が一番おすすめと言えるでしょう。keyベースは特定のフィールド（updated_at など）を用いて更新差分を抽出する方式です。もちろんこのフィールドにはindexを貼るのが良いでしょう。フルテーブルはその名の通り毎回全件更新をする形式です。基本的には時系列データに対してはスケールしないので使わない方がいいでしょう。

ミツモアではもともとこの部分をembulkで実施していました。しかし利用していくにつれてデータソースの変化に対応する運用コストが高く外部のサービスを使うことに決めました。Stitchにすることで数多くのデータソースに対応することができ、後から追加する際にもほぼ工数がなく実施することができました。

dbtを用いたデータの変換

dbtはStitchとは違いデータTransformに特化したOSSです。ミツモアでは利用していませんが有償のクラウドサービスも提供しています。

BigQueryをある程度使ったことがある方は、スケジューリングクエリという機能があることをご存知かと思います。SQLを記載し、保存先テーブルを設定すると定期的に実行し保存してくれる機能です。ミツモアでもBigQuery導入初期はスケジューリングクエリを用いてデータマートのようなものを作っていました。しかしスケジュールクエリには最低限の機能しかないので実運用ではさまざまな問題がありました。

一つ目はgit管理ができないという点です。スケジューリングクエリはBigQueryのコンソールにSQLを登録するのでSQLのレビューや履歴管理できませんでした。もう一点は依存関係のあるクエリの管理が面倒な点です。AとBのスケジューリングクエリを実行したのちにCのスケジューリングクエリを実行したい場合があるのですがその場合、AとBが5分以内に終わるという想定で、CをAとBの5分後にスケジューリングするといった作業がありました。

dbtを導入することで例に挙げた問題の他にも多くの改善がなされました。dbtにはさまざまな特徴・機能がありますが実際に導入して一番便利だと感じる特徴は以下となります。

多段のデータ変換における依存性の解決
SQLをベースとしつつマクロによる拡張
テストコードによる動作保証

それぞれの詳細を説明していきたいと思います。

多段のデータ変換における依存性の解決

dbtは基本的にSQLによる宣言的なテーブル定義の連なりになっています。他のテーブルに依存し（FROM句）自分自身のテーブルの中身を定義するのでテーブル同士が依存関係になっています。dbtで定義された別のtableを用いて新たにtableを定義したい場合は以下のように記載します。

-- models/base_sales.sql
select
date
, date_trunc(date, week(monday)) as week
, date_trunc(date, month) as month
, price as sales
from raw_jobs

-- models/mid_monthly_sales.sql
select
month
, sum(sales) sales
from {{ ref('base_sales') }}

これによりmid_monthly_salesテーブルはbase_salesテーブルに依存することをdbtに教えることができます。この依存関係を知ったdbtは、実際に変換処理を実施する際にbase_salesの変換処理が完了するのを待ってmid_monthly_salesの変換を開始してくれます。これにより並列実行しつつ最速の処理時間で全体の変換を終えることができるのです。

またdbtはこのテーブルの依存関係を可視化したLineage Graphを生成してくれるのでパイプラインの全体像を把握することもできます。

f:id:meetsmore:20211218161806p:plain — ミツモアのdbtテーブルの一部

また特定のテーブルに破壊的変更が加わった際に、それに依存した下流のテーブル全部を生成し直すことも可能です。dbt cliのgraph operatorsを使うとコマンドひとつでbase_sales以下のテーブルを再生成できます。

$ dbt run -m base_sales+ --full-refresh

SQLをベースとしつつマクロによる拡張

SQLを書くだけでそれがそのままテーブルになるので非常に便利なのですが、SQLには変数だったり関数だったり分岐だったりが記載できません。dbtではninjaをベースとした拡張がされているのでDRYなSQLを書くことができます。

その中でもmacroは関数に相当する機能でミツモアでも多く利用しています。

-- macros/convert.sql
{% macro parse_integer(fieldname) %}
  IFNULL(
    CAST( {{ parse_string(fieldname) }} AS INT64 ),
    0
  )
{% endmacro %}

-- models/base_sales.sql
select
{{ parse_integer(price) }} as sales
from raw_jobs

テストコードによる動作保証

dbtではアプリケーションのユニットテストを書くようにテストコードを書くことができます。書き方は簡単で実行結果が0行になるSQLを書くだけです。

-- tests/mid_monthly_sales/sales_should_be_same_or_bigger_than_sales_notax.sql
select
id
from mid_monthly_sales
where sales < sales_notax

これにより生成したテーブルのデータに不備がないかチェックすることができます（プロジェクト外のテーブルもテスト可能）。

最後に

今回はミツモアの分析基盤を支える仕組みについて記事にしました。 Stitchは似たサービスがとてもたくさんあるので一例になるかとは思いますが、そこまでコストがかからず簡単に転送処理を任せることができています。 dbtは昨今注目度が上がってきており、Airbyteのようなdbtを組み込んだELTプラットフォームも出てきました。今後も注目していきたいと考えております。

ミツモアではDX（開発者体験）を向上させるようなツールや取り組みを現在推し進めております。現在ミツモアではデータエンジニアを含めたさまざまなエンジニア・デザイナー・PdMのポジションで採用を強化しております。ぜひWantedlyのリンクからカジュアル面談をしましょう。Meetyでもお待ちしております。

meety.net