背景

heyは3社が統合されて出来た会社なのですが、もともとEC、決済、予約の各事業が会社として独立していたというのもあり、社内のデータは統合されていませんでした。

これの何が問題かというと

KPIの算出定義を一元管理できず、数値の正しさに信頼を持てないため施策のPDCAが適切に回せない
KPIを再利用できず、余計な仕事が増える
事業の数値が見えにくいため、異常や周期性、トレンドを発見することが難しい

などといったことが起きます。

実際、heyは3事業を持っていることから横断的な分析が難しく、事業成績の視認性や顧客のUXなどにおいて課題がありました。

しかし逆にデータ基盤を整備することによって組織としてより効果的な戦略を設計でき、自分たちの生産性を上げ、より多くの顧客に更に大きな価値を届けることができます。

このデータカルチャーを社内に定着させて事業をもっと伸ばせるよう、heyのデータチームは日々泥臭く格闘しています。

heyの持つデータ基盤について

heyのデータ基盤はBigQueryを中心に構成されています。

BigQuery内ではデータレイク、データウェアハウス、データマートの3層に分かれてデータを管理しており、それぞれの役割としては以下の通りになっています。

種類	役割	説明	データセット名
データレイク	生データ	RDSのスナップショットなど	source_* データソース名と1対1に対応する source_ec_mongoなど
データウェアハウス	加工済みデータ	KPI定義マスキングしたりID名を整えたりnull処理したり	warehouse_* ビジネスドメインに1対1に対応する warehouse_paymentsなど
データマート	表示用データ	BIツールなどでグラフ化する	mart_* 表示する指標に1対1に対応する mart_ec_dashboardなど

先述の通りheyはEC事業と決済事業、予約事業を持っていて、それぞれアプリケーションとして独立しているためデータがバラバラに存在しており、横断的な分析ができるように全てのデータをBigQueryに集約させるようにしています。

そしてこのBigQueryにあるデータを共通のBIツール(Metabaseを使っています)で参照できるようにしています。

つまり以下のような構成となっています。

f:id:komi1230:20210430121402p:plain — heyのデータの流れ

各データソースからデータを持ってくる部分についてはGKEでDigdag+Embulkを動かしているのですが、ここの運用についてはまた別の記事でチームメンバーに書いてもらおうと思います。

データエンジニアとデータアナリストの責任範囲

データエンジニアとデータアナリストは職務としてはデータ関係のことをやるという点で共通ですが、データエンジニアは割とエンジニアリング寄り、データアナリストはビジネス寄りというのが差分だと思います。

では、この両者の具体的な境目はなんだろうと考えたとき、ちょうどデータウェアハウスとデータマートの間にあるのではないかと考えられます。

というのも、データウェアハウスはマスキングといった機械的・定常的な操作によって構成されるのに対し、データマートは入り組んだビジネスロジックを反映させたもので、データマートの作成をデータエンジニアがやるのは少しメンテナンスコストが高いです。

そこでデータウェアハウスの作成までをデータエンジニアが担当、データマートの作成をデータアナリストがやるようにしました。

こうすることによってデータマートの作成、変更についてはデータエンジニアにお願いすることなくデータアナリストだけで完結するようになり、作業がより効率化されます。

CIを使う

データマートの作成をデータアナリストに任せるといっても、アクセス権限の管理など少々エンジニアリングが必要となってきます。

そこで今回CIをフル活用することによってこの部分を完全に自動化させました。

具体的に実装した機能として、データアナリストが管理しているリポジトリでbigquery/以下のディレクトリにあるSQLファイルを全てデータマートに反映されるようにしました。

例えば、以下のようにSQLファイルを配置したとします。

bigquery/
└── hoge/
    ├── foo/
    │   └── bar.sql
    └── piyo.sql

このとき、CIが起動するとmart_hoge_fooというデータセットとmart_hogeというデータセットが作られ、それぞれの中にbarというビューとpiyoというビューが作られます。

データウェアハウスは定期的に更新されるので、それに対応するべく基本的にデータマートはビューで作成しますが、データ量が自然と多くなってくるものについてはビューではなくテーブルで保存したいというケースもあり、その際はファイル名にtable_というprefixをつければテーブルが作られるようになってます。

それでは具体的にコードを眺めていこうと思います。

データセットを作成する

今回はCIとしてGitHub Actionsを利用しました。

それではデータセットの作成について、ディレクトリ構成をfindコマンドで見てsedでデータセット名を構成します。

for dataset_name in $(find bigquery -type d | tail -n +2 | sed -e 's/^bigquery\//mart_/g' -e 's/\//_/g')
do
  bq --location asia-northeast1 mk --force --dataset $dataset_name
done

こうすることで$dataset_nameにはmart_hoge_fooのような文字列が入ります。

ひょっとしたらすでにデータセットが存在している可能性もあるのでbqコマンドを使ってすでに存在するデータセットについては処理をパスするというお利口なやり方もあったのでしょうが、用途としてはこれが簡便なので--forceオプションで乗り切りました。

アクセス権限を構成する

先述の通りheyの持つデータ基盤はデータレイクとデータウェアハウス、データマートの3層があり、匿名化加工がなされたデータウェアハウスとデータマートについては社内で誰でもデータ分析ができるようにアクセス権限を設定しているため、アクセス権限はデータウェアハウスからコピーできます。

データセットのアクセス権限を操作する方法としては一度bq show [dataset name]コマンドでデータセットのメタ情報をtmp.jsonのようなファイルに保存し、中身を書き換えた上でbq update --source tmp.json [dataset name]でメタ情報を更新するというやり方が必要です。

ここはやや複雑な作業が必要ですが、jqとシェルスクリプトで完結させます。

for dataset_name in $(find bigquery -type d | tail -n +2 | sed -e 's/^bigquery\//mart_/g' -e 's/\//_/g')
  do
    data=$(bq show --format=prettyjson $GCP_PROJECT:$TABLE_NAME | jq -r '.access' | tr -d '\n')
    pattern=".access|=${data}"
    bq show --format=prettyjson $GCP_PROJECT:$dataset_name | jq "$pattern" > tmp.json
    bq update --source tmp.json $GCP_PROJECT:$dataset_name
  done

jqコマンドは引数として{key}|={value}を入れることによってJSONの値を書き換えることができ、これによってメタ情報のアップデートができます。

bashだと改行コードが入っているとコマンドがそこで完結している扱いになってしまうため、取得したデータセットのメタ情報のJSONをtr -d '\n'で改行コードを取り除いているのがミソです。

ビューとテーブルを作る

最後に.sqlという拡張子がついたファイル名をfindでとってきて、sedなどで拡張子を取り除いたりtable_といったprefixを取り除いてテーブル名(ビュー名)を作り、それでbqコマンドを走らせます。

for f in $(find bigquery -type f -name *.sql)
do
  dataset_name=$(echo $f | sed -e 's/bigquery\//mart_/g' -e 's/\/\w*.sql//g' -e 's/\//_/g')
  view_name=$(echo $f | sed -e "s/.sql//g" -e "s/.*\///g" )
  if [[ view_name =~ table_ ]]
    then
      table_name=$(echo $view_name | sed 's/table_//g')
      bq query --replace --use_legacy_sql=false --destination_table ${dataset_name}.${table_name} < $f
    else
      bq mk --force --use_legacy_sql=false --view "`cat $f`" ${dataset_name}.${view_name}
  fi
done

成果物

最終的なコードは以下の通りになりました。

name: Make Data Mart

on:
  schedule:
    - cron: '0 0 * * *'
  push:
    paths:
      - "bigquery/**"
    branches:
      - main
  workflow_dispatch:

jobs:
  make_view:
    name: Make Data Mart
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2

      - name: Set up Cloud SDK
        uses: google-github-actions/setup-gcloud@master
        with:
          project_id: $GCP_PROJECT
          service_account_key: ${{ secrets.GCP_SA_KEY }}
          export_default_credentials: true

      - name: Make datasets
        run: |
          for dataset_name in $(find bigquery -type d | tail -n +2 | sed -e 's/^bigquery\//mart_/g' -e 's/\//_/g')
          do
            bq --location asia-northeast1 mk --force --dataset $dataset_name
          done
      
      - name: Define access control
        run: |
          for dataset_name in $(find bigquery -type d | tail -n +2 | sed -e 's/^bigquery\//mart_/g' -e 's/\//_/g')
          do
            data=$(bq show --format=prettyjson $GCP_PROJECT:warehouse_ec | jq -r '.access' | tr -d '\n')
            pattern=".access|=${data}"
            bq show --format=prettyjson $GCP_PROJECT:$dataset_name | jq "$pattern" > tmp.json
            bq update --source tmp.json $GCP_PROJECT:$dataset_name
          done
      
      - name: Make view
        run: |
          for f in $(find bigquery -type f -name *.sql)
          do
            dataset_name=$(echo $f | sed -e 's/bigquery\//mart_/g' -e 's/\/\w*.sql//g' -e 's/\//_/g')
            view_name=$(echo $f | sed -e "s/.sql//g" -e "s/.*\///g" )
            if [[ view_name =~ table_ ]]
              then
                table_name=$(echo $view_name | sed 's/table_//g')
                bq query --replace --use_legacy_sql=false --destination_table ${dataset_name}.${table_name} < $f
              else
                bq mk --force --use_legacy_sql=false --view "`cat $f`" ${dataset_name}.${view_name}
            fi
          done