背景
- ダンボールワンがラクスルグループに加わり、データ分析基盤を整備したのでその事例紹介
- ラクスルグループの中で、はじめて dbt を導入してとても良かったので、その部分を中心に紹介
- 整備前はサービスDBの Read Replica に Redash から接続してデータ分析していた
- Redash で SQL を書いて分析できるメンバーが限られるため、SQL を書けないビジネス側メンバーも自分で分析できる環境が欲しかった（利用者は30名程度）
データ分析基盤構成
Data Warehouse
- Data Warehouse は BigQuery を採用
- ラクスルグループでは Snowflake を採用しているケースもあるが、今回は Googleスプレッドシート や Googleデータポータル といった BI環境との接続性を重視
BI
- BI環境は Googleスプレッドシートのコネクテッドシートをメインに、Redash や データポータル利用とコスパ重視
ELT
Extract & Load
- Aurora PostgreSQL to BigQuery の Extract & Load は内製
- Aurora MySQL to BigQuery を OUTFILE S3 で行う内製ツールがすでにあったので、PostgreSQL向け対応のみで済むため
- その他の Extract & Load 部分は trocco を採用
- ラクスルですでに導入した環境があったため
Transform
- Transform 部分（Lake から Warehouse/Mart を作る部分）は dbt を採用
- ラクスルでも dbt 導入を検討していたため検証もかねて導入
- 今回導入して非常に良かったため、ラクスルグループ全体に展開していく予定
- ラクスルでも dbt 導入を検討していたため検証もかねて導入
ワークフロー
- ワークフロー管理はJenkis環境が既にあったのでとりあえずそこを利用中
- もう少し複雑になってきたら別のワークフロー環境を構築予定
dbt を導入してみて
Good
-
SQLの知識があればデータパイプラインを構築できる
-
SQLで定義したモデルはrefと呼ばれるテーブル間の関係性を自動的に把握できるようになる仕組みで記述することで、DAG（有向非巡回グラフ）を考慮したデプロイを実行できるため、モデルを反映させるための細かなワークフローを組む必要がない
select orders.id, orders.status, sum(case when payments.payment_method = 'bank_transfer' then payments.amount else 0 end) as bank_transfer_amount, sum(case when payments.payment_method = 'credit_card' then payments.amount else 0 end) as credit_card_amount, sum(case when payments.payment_method = 'gift_card' then payments.amount else 0 end) as gift_card_amount, sum(amount) as total_amount from {{ ref('base_orders') }} as orders left join {{ ref('base_payments') }} as payments on payments.order_id = orders.id -
データリネージがわかりやすく可視化される
-
SQL内に軽量なテンプレート言語であるJinjaを使うことができるため、制御構文（ifやforなど）を使用することで SQL を簡潔にできる
select order_id, sum(case when payment_method = 'bank_transfer' then amount end) as bank_transfer_amount, sum(case when payment_method = 'credit_card' then amount end) as credit_card_amount, sum(case when payment_method = 'gift_card' then amount end) as gift_card_amount, sum(amount) as total_amount from app_data.payments group by 1{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %} select order_id, {% for payment_method in payment_methods %} sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount, {% endfor %} sum(amount) as total_amount from app_data.payments group by 1 -
モデルによって生成された結果にアサーションを行うことでSQLのテストが可能
- 基本的なテストはモデルのYAMLに定義するだけで実行可能
version: 2 models: - name: orders columns: - name: order_id tests: - unique - not_null - name: status tests: - accepted_values: values: ['placed', 'shipped', 'completed', 'returned'] - name: customer_id tests: - relationships: to: ref('customers') field: id-
not null：nullが含まれていないか- BigQueryでは not null 制約がつけれないので重宝
-
unique：ユニークな値になっているか- BigQueryではPKやUKがつけれないので重宝
-
relationships：別のモデルで対応する値を持っているか -
accepted_values：指定したリストの中にある値か - 上記以外にも以下のような様々なテストを実行することが可能
- 他モデルとの行数一致
- JOINミス等で行落ち、行膨れしてないかのチェックに重宝
- 導出属性の整合性チェック
- 他モデルとの行数一致
-
環境構築・実行が容易
- dbt Cloud環境（$50/user/month）もあるが、dbt-core（CLI）で無料利用可能
- dbt-core（CLI）でも以下の感じでサーバーレスで容易に実行可能
-
gcloud builds submitでコンテナイメージをビルド -
gcloud run deploy dbt-serviceで Cloud Run にデプロイ - Cloud Run の エンドポイントを叩いて dbt run 又は dbt test を実行
-
-
BigQueryの固有処理にも対応しているものがある
- 承認済みビューの作成
- ポリシータグの付与
More
- データディクショナリのカラムの日本語検索ができない
- これが致命的なこともあり、データディクショナリとしてはdbtは使わず、 Data Catalog を使っている
- BigQueryの固有処理には対応できていない部分があることもあり、dbt とは別に前後のジョブ実行がある
- UDFの依存関係は管理できないのでコード記載順序で担保
- 外部ソース（Googleスプレッドシート, GCS）のリンクテーブル作成ができないので、内製スクリプトを事前実行している
- その後 こちら で対応されたので内製スクリプトから移行済
- データセットのアクセス制御ができないので、事後処理で内製スクリプトを実行している
- terraform も使っているがデータセットやテーブルは dbt で生成しているため権限設定は後続でやりたい
- 非正規化を多様したワイドテーブルのデータモデルとしたこともあり（詳細後述）、BigQueryのメタデータ更新をdbtのYAML管理するのが面倒なため、GoogleスプレッドシートをINPUTにして更新する内製スクリプトを事後実行している
工夫した点
データモデリング
- ディメンショナルモデリング（スタースキーマ/スノーフレークスキーマ）や Data Vault ではなくワイドテーブルを採用
- 非エンジニアが分析する上でJOINが一つのハードルとなるが、分析に必要なデータを予め1つのテーブルにJOINしておくことでその問題を解消
- 冗長的に持つことによる性能や金額の問題はもうない
- SQLを書かずにGoogleスプレッドシートでコネクティッドシートを利用してBigQueryの1つのテーブルに接続してピボット分析することで大抵の分析ができるようにしている
- 顧客、注文、注文商品という3つのテーブルがある場合、注文には顧客を包含、注文商品には注文を包含することで、分析する際はどの単位で分析するかによってテーブルを1つ選択し、それを見るだけで大抵の分析ができるようにしている
- 顧客には初回注文日や合計売上等の良く利用するサマリデータも保持している
- 顧客、注文、注文商品という3つのテーブルがある場合、注文には顧客を包含、注文商品には注文を包含することで、分析する際はどの単位で分析するかによってテーブルを1つ選択し、それを見るだけで大抵の分析ができるようにしている
- 大量カラムによる構造が良くわからなくなるデメリットは、BigQueryのStruct型を活用して構造を可視化することで軽減
- lake と warehouse/mart の間に component層を設け、component層は正規化されたテーブルで作成し、 warehouse/mart 生成時に非正規化している
- SQL書ける人向けにはcomponent層もアクセス可能にしている
- dbt では staging レイヤーの利用 が推奨されているが、現時点ではレイヤー分けは必要最小限（DRYであればOK）の方針としている
- 今後処理が複雑化してきた場合にはレイヤー分けを見直すかも
自己解決可能な情報整備
データ分析基盤の構築運用体制が潤沢ではないこともあり、構築後のQA対応等の工数を極力減らすべく、なるべく自己解決できるように情報を整備
導入資料
- 入門編とSQL編の2つのドキュメントを作成し、ハンズオン形式の講座を開催
- 入門編では概念ERDを用意してERDの見方から説明
- GoogleスプレッドシートからBigQueryに接続してピボット分析する実践的な演習問題を複数用意
- 録画動画を残し、後から参画した人はその動画を見てもらう
データカタログ
- GCP Data Catalog を日本語で検索することで、そのデータがどのテーブル、カラムにあるか分かるようにした
- テーブルとカラムの説明はメンテナンス容易性を重視して Googleスプレッドシート管理
- 非正規化を多用しているため、説明もリンク先参照としたいが dbt の yaml ではできないが、Googleスプレッドシートなら容易
- Googleスプレッドシートであれば Github 操作しない人でもメンテナンス可能
- GoogleスプレッドシートをINPUTにBigQueryのメタデータを更新する処理を内製
- BigQueryのメタデータに入っていることで参照は Data Catalog が利用可能
- warehouse/mart のテーブル名、カラム名は命名規約に沿った一貫性をもたせることで、何のデータかのわかりやすさと、検索容易性を高める
- 表記揺れが起きやすい用語はNGワードを明確化
- カラムの説明には日本語名、データソースのテーブル名.カラム名、加工内容を記載し、サービス側のデータ構造の知識がある人が何のデータか warehouse/mart の処理内容を確認せずに、description だけで大抵は理解できるようにした
Google Group での権限管理
- Basic と Pro の2種類の Google Group を用意し、ダンボールワンでの管理責任者に Google Group の管理者となってメンバー管理できるようにした
- Basic は warehouse と mart のみ閲覧可能
- Pro は warehouse と mart に加えて component と lake も閲覧可能
SQL Linter でSQL記述を統一
- SQLFluff が BigQuery, dbt に対応していたので採用
参考
dbt
データモデリング
Discussion
今は対応されているので、こちら利用に変更しよう