コーディング規約を定めて SQL の可読性を上げる

Da Vinci Studio データ分析チームの濱口です。
現在データ分析チームで運用されている SQL のコーディング規約について紹介します。

なぜコーディング規約を定めるのか

SQL のコーディング規約を定める主な理由として、 可読性を上げるというのがあると思います。 スタイルを統一せず、書き手によって異なるスタイルで書いていると以下のような弊害が発生します。

レビューしづらい

他の人が書いた別の作業の SQL というだけでも読みづらいですが、コーディングスタイルが揃っていないとさらに読みづらくなってしまいます。 そうするとレビューにも時間がかかってしまい、他の作業に割ける時間が短くなってしまいます。

再利用しづらい

Da Vinci Studio の分析チームでは、過去に書いたクエリを GitHub のリポジトリに貯めており、他の人も再利用できるようにしています。 しかし SQL が読みづらいとバグも発生しやすくなり、かえって作業効率を悪くしてしまいます。

どんな SQL に適用すべきか

他の人が読む可能性がある SQL は全て、定めたコーディング規約に則って書くことにしました。具体的には

  • 他チームからの依頼の SQL
  • アプリケーション内で利用される SQL

などを指します。個々人のアドホックな分析に関しては、必ずしもスタイルガイドに沿わなくてもいいというルールにしました。

コーディング規約

以下で定めているコーディング規約をいくつか紹介します。
なお、今回は分析チームで主に利用されている BigQuery の利用を前提としたコーディング規約になっています。

予約語は小文字で書く

# NG
SELECT
  name,
  comment,
FROM SAMPLE.table
WHERE
  name = 'name1'

# OK
select
  name,
  comment,
from SAMPLE.table
where
  name = 'name1'

SQL の予約語は大文字で書く文化が広く浸透しており、大文字で書く人はかなり多いかと思います。 しかし上記を踏まえた上でも、小文字で書く方が良いと思った理由として

  • 予約語だけ大文字で書くのめんどくさい
  • 予約語だけ大文字で書いても可読性は変わらない

の2点でした。可読性が変わらないのであれば、予約語だけ大文字で書くメリットはないという結論に至りました。

select 句のカンマの位置は行末

# NG
select
  name
  , comment
  , date
from SAMPLE.table

# OK
select
  name,
  comment,
  date,
from SAMPLE.table

このルールは可読性というよりは、試行錯誤の効率の観点で決められました。

  • 行頭にカンマを書くと、一番上のカラムをコメントアウトの際にエラーになってしまう
  • BigQuery は select 句の最後の行にカンマがついていてもエラーにならないので、一番最後の行をコメントする際もカンマの削除が不要

の2点が主な理由です。より少ない労力で各行をコメントできる書き方を採用しました。

where 句の and と or は先頭に置く

# NG
select
  name,
  comment,
from SAMPLE.table
where
  date between date('2021-04-27') and date('2021-05-05') and
  name = 'name1' or
  comment = 'comment1'

# OK
select
  name,
  comment,
from SAMPLE.table
where
  date between date('2021-04-27') and date('2021-05-05')
  and name = 'name1'
  and comment = 'comment1'

条件が複雑で長くなっている際に and や or が行末についていると見づらいというのがありました。 先頭に書いておけば、条件のかたまりの切れ目が分かりやすく良いのではという議論がありました。

サブクエリではなく、 with 句を使う

# NG
select
  name,
  sum_price
from (
  select
    name,
    sum(price) sum_price,
  from SAMPLE.table
  where
    date between date('2021-04-27') and date('2021-05-05')
  group by name
)
where
  sum_price >= 10000

# OK
with
  t_base as (
    select
      name,
      sum(price) sum_price,
    from SAMPLE.table
    where
      date between date('2021-04-27') and date('2021-05-05')
    group by name
  )
select
  name,
  sum_price
from t_base
where sum_price >= 10000

主な理由としては

  • 一つのクエリが大きくなりすぎない
  • ネストが深くなりすぎない
  • 同じクエリを何度も再利用できる

の3点が挙がりました。可読性だけでなく、再利用性の観点からも with 句を使うようにしています。

ビフォーアフター

以下、全ての規約を適用したときのビフォーアフターの一例です。

# 適用前
SELECT
  date,
  target_name,
  SUM(price) sum_price,
  AVG(price) avg_price,
FROM (
  SELECT
    date,
    CASE
      WHEN regecp_contains(name, r'regexp1') THEN 'name1'
      WHEN regecp_contains(name, r'regexp2') THEN 'name1'
    ELSE null
  END target_name,
    price,
  FROM DataBase.table1
  WHERE date BETWEEN DATE('2021-01-01') AND DATE('2021-01-31'))
WHERE target_name IS NOT NULL
GROUP BY date, target_name
GROUP BY date, target_name

# 適用後
with
  t_base as (
    select
      date,
      case
        when regecp_contains(name, r'regexp1') then 'name1'
        when regecp_contains(name, r'regexp2') then 'name1'
        else null
      end target_name,
      price,
    from DataBase.table1
    where date between date('2021-01-01') and date('2021-01-31')
  )
select
  date,
  target_name,
  sum(price) sum_price,
  avg(price) avg_price,
from t_base
where target_name is not null
group by date, target_name
order by date, target_name

紹介した規約以外にも、インデントに関する規約などが適用されており、読みやすくなりました。 規約通りにかけているかのチェックは、現状は目視で確認しています。最低限レビューや後で読み返すときに困らない程度の可読性を保てたら良いので、そこまで厳密にはチェックしていません。

まとめ

分析チームで運用している SQL のコーディング規約についてまとめました。カラム名や with 句における命名規約なども定めていますが、それはまた別の記事で紹介しようと思います。
このコーディング規約はあくまでも一例で、状況によって規約の内容や運用方法は変わると思います。その時のチームの課題や状況に応じてアップデートしていきたいと思います。

We are hiring!!

Da Vinci Studio では一緒に働ける仲間を絶賛大大大募集中です!

データ分析に興味がある方、それ以外の開発に興味がある方、あるいは一緒に働いてみたいという方は recruit@da-vinci-studio.net までご連絡ください。

BigQuery で定数を使う方法

f:id:da-vinci-studio:20210528105644p:plain

Da Vinci Studio 分析チームの末安です。
今回は BigQuery で定数を使う方法をいくつか紹介します。

日付だけ変えて同じクエリを実行したいケースなどよくあると思いますが、変更箇所が漏れていたりして無駄に時間を消費してしまったりします。 こういう場合、定数にしておくと便利です。

まず前提として BigQuery では、定数を定義する機能はありません。( 2021年05月時点 )
ここで紹介する方法は、定数っぽくつかえる BigQuery の機能になります。

今回紹介する方法は以下の 3 つです。

  • with 句を利用する
  • UDF を利用する
  • BigQuery Scripting を利用する

ではひとつずつ説明していきます。

with 句を利用する

with 句で固定値をサブクエリとして定義することで定数っぽくする方法です。

with
  const as (
    select date('2021-04-01') st, date('2021-04-30') en
  ),

  date_array as (
      select 
        date(sequence) item,
      from unnest(generate_date_array('2021-01-01', '2021-05-01')) sequence
  )

select
  date_array.*
from date_array, const
where
  item between const.st and const.en

このように定義した const というサブクエリを CROSS JOIN することで利用します。
都度 from に書く必要があって少し面倒です。

UDF を利用する

UDF とは、ユーザー定義関数のことです。固定値を返す関数を定義することで、定数のように扱うことができます。

cloud.google.com

create temp function const()
as (
  struct(date('2021-04-01') as st, date('2021-04-30') as en)
);

with 
  date_array as (
      select 
        date(sequence) item,
      from unnest(generate_date_array('2021-01-01', '2021-05-01')) sequence
  )

select
  date_array.*
from date_array
where
  item between const().st and const().en

上記の場合では、構造体を返すようにすることで複数の定数を一つの関数で定義できるようにしています。
with 句を利用する方法と違って CROSS JOIN する必要がありません。

デメリットとしては、 JOIN する際の条件のなかで利用できないという点があります。
例えば以下のようなケースでは Subquery in join predicate should depend on one of join sides. のようなエラーがでます。

create temp function const()
as (
  struct(date('2021-04-01') as st, date('2021-04-30') as en)
);

with 
  date_array as (
      select 
        date(sequence) item,
      from unnest(['2021-01-01', '2021-02-01', '2021-04-01','2021-05-01']) sequence
  ),
  date_array2 as (
      select 
        date(sequence) item,
      from unnest(['2021-01-01', '2021-02-01', '2021-04-01','2021-05-01']) sequence
  )

select
  date_array.*
from date_array
  join date_array2 on date_array.item = date_array2.item and date_array2.item = const().st
where date_array.item between const().st and const().en

BigQuery Scripting を利用する

BigQuery Scripting では、変数やif 文やループ処理などのステートメントを特に何の設定もすることなく利用できます。この変数の宣言と代入を利用することで定数のように扱います。

cloud.google.com

declare st, en date;
set st = date('2021-04-01');
set en = date('2021-04-30');

with 
  date_array as (
      select 
        date(sequence) item,
      from unnest(generate_date_array('2021-01-01', '2021-05-01')) sequence
  )

select
  date_array.*
from date_array
where item between st and en

with 句や UDF を利用する方法と違い CROSS JOIN する必要もなく、JOIN の条件の中でも利用できます。 また他の方法よりも可読性も良く、現時点では一番良い方法だと思います。

ただし、BigQuery Scripting を使うと事前にスキャン量を計算できないので注意が必要です。

まとめ

今回は BigQuery で定数を使う方法を紹介しました。 クエリを再利用する際に有用なので、ぜひ試してみてください。

BigQuery Scripting はこれ以外でも工夫次第で色々応用できそうなので活用していきたいと思います。

We are hiring!!

Da Vinci Studio では一緒に働ける仲間を絶賛大大大募集中です! データ分析に興味がある方、それ以外の開発に興味がある方、あるいは一緒に働いてみたいという方は recruit@da-vinci-studio.net までご連絡ください。

BigQuery 用 ETL ライブラリ inbulk を公開したので紹介する

f:id:da-vinci-studio:20210528104904p:plain

Da Vinci Studio 分析チームの末安です。
今回は 2021 年 1 月に公開した ETL ライブラリ inbulk について紹介します。

github.com

inbulk でできること

  • ジョブを簡潔に定義できる
  • 差分実行できる
  • それらの処理を BigQuery 内で完結できる

ざっくり言うと、ほとんど yaml で定義できる BigQuery の API の wrapper のようなものになっています。

背景

分析チームでは BigQuery を利用することが多く、BigQuery 内のテーブルを加工して別のテーブルを作るといった作業がよく発生します。

以前まで、こういった処理は BigQuery の機能である Scheduled Query を利用して行っていましたが、利用していて幾つかの問題が見えてきました。

1. 依存関係を定義できない

一つの処理が終わった後に別のテーブルを作りたいということはよくあると思います。

Scheduled Query ではそういった依存関係は定義できないため、実行時間をずらすことでどうにか実現していましたが、最初の処理が失敗しても次の処理が実行されてしまうという問題がありました。

2. 差分実行のコストが大きい

バッチ処理においても冪等性を考慮することは重要だと思います。

これも BigQuery 上のみで実現できなくはないですが、宛先テーブルを全スキャンして新規追加分マージしてからそのテーブルを置換する必要がありました。

この方法の場合、宛先テーブルのデータ量が多いとかなりのコストがかかってしまうという問題がありました。

3. レビューしづらい

当時は BigQuery の UI 上で Scheduled Query を設定していたため、レビューがうまくできない状態でした。

この問題に関しては Terraform などで管理すれば解決できそうではありますが、こういった管理方法の場合、GitHub 上の定義と実態に差異が発生してしまうことが多いことと、他にも解決できない課題があったため別の手段を探すことにしました。

調査

1 の依存関係を定義できない問題は、ワークフローエンジンを利用することで解決できるのでそちらに移行することにしました。

差分実行とジョブ定義の実現方法についても調査しましたが、それらしきものは見つかりませんでした。

Embulk を利用すれば実現はできそうでしたが、Embulk 自体はデータ転送ツールであり、今回のケースでは BigQuery 内で完結できる処理のため、一度 BigQuery からデータをダウンロードして再び BigQuery に入れるという内部の流れが個人的に許せませんでした。

inbulk

そんなわけで、ETL ライブラリ inbulk を OSS として開発しました。

特徴

定義

このように Embulk っぽい記述でジョブを定義できます。

Embulk と違うのは、データを実行環境に一切ダウンロードしない点です。そのためジョブ実行するマシンのメモリ消費を抑えることができます。

init:
  credential-file: /.gcp/credential.json
  service: bigquery
in:
  query: |
    select
      id
    from Dataset.users
out:
  project: project
  database: Destination
  table: users
  mode: append

変数

inbulk では、事前に別のクエリを実行してその結果をクエリに組み込むことができます。 具体的には、下記のように in のセクションの vars で組み込みたいデータを指定して query のなかで ${変数名} とすることでクエリに組み込みます。

init:
  credential-file: /.gcp/credential.json
  service: bigquery
in:
  query: |
    select
      id
    from Dataset.users
    where created > '${last_modified}'
  vars:
    - name: last_modified
      database: Destination
      table: users
      mode: meta
      field: last_modified_time
out:
  project: project
  database: Destination
  table: users
  mode: append

上記の例では、last_modified という名前で宛先テーブルの最終更新日を BigQuery のメタデータテーブルから取得して利用しています。これにより差分実行が低コストで実現できます。

また、他のテーブルを参照することも可能で、vars に下記のように指定すると、宛先テーブルの最後の id を取得するようなこともできます。

  vars:
    - name: last_id
      database: Destination
      table: users
      mode: max
      field: id

マージ

マージ機能も用意しています。マージモードでは、指定したフィールドが重複する場合は、指定したオーダーで最初に来るもので置き換えます。 下記の例では、id が重複した場合、最終更新日が新しいレコードで置き換えるような設定になっています。

ただし、マージモードでは内部的には宛先テーブルを全スキャンしてリプレイスしており、宛先テーブルのデータ量が多い場合はスキャン量が多くなってしまうので注意が必要です。

init:
  credential-file: /.gcp/credential.json
  service: bigquery
in:
  query: |
    select
      id
    from Dataset.users
out:
  project: project
  database: Destination
  table: users
  mode: merge
  merge:
    order:
      - column: modified
        desc: True
    keys:
      - id

パーティション

下記の例のように、宛先テーブルのパーティションも定義することができます。

init:
  credential-file: /.gcp/credential.json
  service: bigquery
in:
  query: |
    select
      id
    from Dataset.users
out:
  project: project
  database: Destination
  table: users
  mode: append
  partition:
    mode: time
    field: created
    type: DAY

利用例

日毎に新規ユーザー数を集計して保存しておきたい

宛先テーブルの最新の created 以降に作成された未集計の users を集計する

init:
  credential-file: /.gcp/credential.json
  service: bigquery
in:
  query: |
    select
      date(created) created,
      count(distinct id) user_count,
    from Sample.users
    where
      date(created) > date('${last_created}')
      and date(created) <= date_sub(current_date, interval 1 day)
  vars:
    - name: last_created
      database: Destination
      table: new_users
      mode: max
      field: created
out:
  project: project
  database: Destination
  table: new_users
  mode: append
  partition:
    mode: time
    field: created
    type: MONTH

特定のイベントのみ整形して保存しておきたい

宛先テーブルの最終更新日から昨日までのデータを append するように定義する

init:
  credential-file: /.gcp/credential.json
  service: bigquery
in:
  query: |
    select
      timestamp_micros(event_timestamp) event_time,
      user_id,
      user_pseudo_id,
      (select value.string_value from unnest(event_params) event_param where key = 'screen_class') screen_class,
      device.category device_category,
      device.operating_system os,
      device.operating_system_version os_version,
    from Sample.events
    where
      event_name = 'screen_view'
      and date(timestamp_micros(event_timestamp)) between date('${last_modified}') and date_sub(current_date, interval 1 day)
  vars:
    - name: last_modified
      database: Destination
      table: screen_views
      mode: meta
      field: last_modified_time
out:
  project: project
  database: Destination
  table: screen_views
  mode: append
  partition:
    mode: time
    field: event_time
    type: DAY

まとめ

今回は、ETL ライブラリの inbulk について紹介しました。 inbulk を導入したことによって、課題であった差分実行とレビューのしづらさを解消することができました。

今は BigQuery 専用となっていますが、追々 Redshift などにも対応していきたいと考えています。

なにか要望などあれば気軽に Issue をあげてもらえると嬉しいです。

We are hiring!!

Da Vinci Studio では一緒に働ける仲間を絶賛大大大募集中です! データ分析に興味がある方、それ以外の開発に興味がある方、あるいは一緒に働いてみたいという方は recruit@da-vinci-studio.net までご連絡ください。

Redash の metadata だけでできるダッシュボードのアクセス解析

f:id:da-vinci-studio:20210527152952p:plain Da Vinci Studio インフラ基盤部データ分析チームの濱口です。
Da Vinci Studio のデータ分析チームでは、グループ内に溜まっている膨大なデータを日々分析・集計しています。そうして得られた学びについては、公開可能なものに限り Redash のダッシュボードとしてグループ内で確認できるようにしています。 ただ公開して終わりではなく、ダッシュボードがどれくらい見られているのかも計測して、グループ内のデータへの関心の把握やダッシュボードの改善などに繋げたいですよね。
というわけで今回は Redash 内のクエリやダッシュボードの閲覧状況を可視化した話についてまとめておこうと思います。

Redash の metadata

閲覧状況を可視化するにあたって Redash の内部データベースに貯まっている metadata を利用しました。 Redash の metadata には、登録ユーザーやユーザー権限、追加されたクエリやダッシュボードなど色々なデータが貯まっています。

Redash の metadata のテーブル一覧
Redash の metadata のテーブル一覧

events というテーブルがあり、その中にユーザーの閲覧履歴や操作履歴が記録されるようになっています。

events テーブルの詳細
events テーブルの詳細

これを利用して閲覧状況を可視化してみましょう。

Redash で Redash の metadata にクエリを投げられるようにする

metadata に今回必要そうなデータが入っていることが確認できたので、次に Redash の metadata をデータソースに接続します。 今回は docker のコンテナに立てているので、それぞれ設定した値から host, port, user, password などを入力していきます。

Redash のデータソース接続画面
Redash のデータソース接続画面

設定を完了して、以下のようにテーブル一覧が確認できたら接続完了です。

Redash のデータソース接続確認
Redash のデータソース接続確認

events テーブル

ここまできたらあとはいつものごとくクエリを書いて events テーブルのデータを集計します。

events テーブルのデータ例
events テーブルのデータ例

先ほどテーブル内容を紹介しましたが、その中の action に何の操作が行われたか、object_type に action の対象物が記録されています。ダッシュボードの閲覧であれば action = 'view' and object_type = 'dashboard' の条件で絞り込むことでデータを確認できます。ここに users テーブルや dashboards テーブルを join すれば、誰が何のダッシュボード見ているのかを集計できます。

with
    t_base_events as (
        select
            u.name user_name,
            e.action,
            d.name dashboard_name,
            e.created_at
        from events e
            join users u on e.user_id = u.id 
            join dashboards d on cast(e.object_id as integer) = d.id
        where
            action = 'view'
            and object_type = 'dashboard'
    )

select
    dashboard_name,
    count(*) view_dashboard_count
from t_base_events
group by dashboard_name
order by view_dashboard_count desc

action には他にも create, update, load_favorites, execute_query など、object_type は dashboard, query, page などが入っていて、色々な対象物の様々な操作履歴を把握できます。閲覧状況以外にも、誰がどれくらいクエリを書いているかやどれくらいダッシュボードを作成しているかもモニタリングできそうです。

ダッシュボード完成

ダッシュボードとクエリの日別閲覧状況
ダッシュボードとクエリの日別閲覧状況

大きく伸びている日は、新しくダッシュボードを追加して周知した日です。それ以外の期間では、あまり継続して見られていないことが分かります。何の情報をどういう見せ方にすれば興味を持って継続的に見てもらえるのか、まだまだ改善の余地があります。

人物、会社別の閲覧状況
人物、会社別の閲覧状況

また会社別に見てみると、直近でダッシュボードを見ている会社と見ていない会社で大きく差があることが分かりました。追加したダッシュボードに対して強く興味を示している会社は良いですが、あまり興味を示していない会社に対してはどういった情報だとより興味が惹かれるのかをもう少し深ぼりして考える必要があります。

まとめ

  • Redash の metadata には様々な使えるデータが入っている
  • events テーブルにありとあらゆるものの操作履歴が入っている
  • Redash に接続して集計して可視化すると、閲覧状況や利用状況などをモニタリングできる

分析結果を Redash のダッシュボードで公開するのはもちろんですが、公開内容が見られているかどうか、しっかり計測までできるようになりました。今後のダッシュボードの公開内容やまとめ方の改善に繋げていきたいと思います。
最後までご覧いただきありがとうございました。

We are hiring!!

Da Vinci Studio では一緒に働ける仲間を絶賛大大大募集中です! データ分析に興味がある方、それ以外の開発に興味がある方、あるいは一緒に働いてみたいという方は recruit@da-vinci-studio.net までご連絡ください。もしくは 濱口 までご連絡いただいても大丈夫です。

データ分析チームの紹介

Da Vinci Studio インフラ基盤部データ分析チームの濱口です。 今回は Da Vinci Studio のデータ分析チームについて紹介したいと思います。

Da Vinci Studio の「データ分析チーム」

Da Vinci Studio のデータ分析チームは現在は 3 名体制で、くふうグループ横断のデータ分析を担当しています。 まだ最近できたばかりの比較的新しいチームで、データ分析を通してユーザーや社会により良い価値を届けるために日々活動しています。 まずはそんなデータ分析チームのミッション・ビジョン・バリュー(MVV)について紹介したいと思います。

データ分析チームにおけるMVV

紹介の前に、チームなのにMVV?と思われた方もいらっしゃるかもしれません。その疑問の通りで Da Vinci Studio 全体としてミッション・ビジョンは掲げられているので、全てのチームがミッションを定めているわけではありません。
しかしデータ分析チームは新しくできたばかりで、実績を積み重ねるために日々多くの業務をこなしながら、手探りで進んでいる状態でした。メンバーそれぞれのやっていることがチームの目指したい理想像や目的から逸れていないかを振り返ることができるようにMVVを決めるに至りました。

ミッション

まずはミッションですが、Da Vinci Studio ではテクノロジー(エンジニアリング/デザイン)の力を引き出すというミッションを掲げています。それを踏まえた上でデータ分析チームでは

最強の玩具箱をつくる

というミッションを設定しました。
玩具箱というのは、面白い知見やアイデア、技術が溜まる場所という意味で名付けています。新しくて唯一無二、変幻自在で独創的な玩具箱を作りたいという話になり、まとめて「最強の」「玩具箱」を作ろうとなりました。

ビジョン

つづいて Da Vinci Studio では新しいスタンダードを作り出すというビジョンを掲げています。それを踏まえた上でデータ分析チームでは

最強のアトリエになる

というビジョンを設定しました。
アトリエとは、画家・美術家・工芸家・建築家などの芸術家が仕事を行うための専用の作業場、もしくはその作業場を拠点とする芸術家集団のことを言います。ここでは後者の芸術家集団のことを指しています。
新しい技術を試して積極的に取り入れながらも、しっかり作って価値を届ける最強の「アトリエ」になろうという思いが込められています。

バリュー

ここまで紹介してきたミッション、ビジョンを達成するために

  • 面白いことをやろう
  • 好奇心旺盛であろう
  • 価値を届けよう
  • 開かれたチームであろう

の 4 つのバリューを設定しました。
面白いことをやろう、好奇心旺盛であろう、価値を届けようの 3 つに関しては、他のチームでも重視されている価値観とほとんど同じです。データ分析チームとしても重要な価値観となるので明示的に掲げています。
開かれたチームであろうは、分析に興味がある人は誰でも歓迎という意図で掲げられています。色々な人の力を借りて、色々なことに挑戦して、新しくて唯一無二の玩具箱を作っていこうということですね。

今やっていること

サービス改善のための分析

くふうグループでは結婚や不動産、金融など様々なサービスが運営されており、より良い価値をユーザーに届けるためのくふうや改善が日々行われています。
データ分析チームでも、サービス改善や新たな施策の閃きなどの助けとなるように、グループに溜まっているデータや一般公開されているデータを集計・分析して、ダッシュボードにまとめて公開しています。

f:id:da-vinci-studio:20210426202351p:plain

公開しているダッシュボードの一例

自社プロダクトの開発

自社プロダクトにおけるデータの活用や分析活動も行っています。データ構造の設計からデータフローの構築・運用、機械学習部分の実装まで幅広くやっています。
ゆくゆくは機械学習などを軸にしたデータ分析主導のプロダクトも出していきたいと思っています。

社会貢献のためのデータ提供や研究

データの分析結果を外部に提供したり、公的機関との共同研究を行ったりしています。直近ではグループ会社サービス「Zaim」のデータ分析チームの一員として「コロナ禍のお財布の変化」について分析し、メディアへの情報提供協力を行い、テレビ番組で取り上げられました。

www.nhk.jp

www3.nhk.or.jp

社会的変化の把握に寄与する活動などを行うことで、ユーザーはもちろん社会全体にも価値を届けていきたいと思っています。

最後に

ざっくりとですが Da Vinci Studio のデータ分析チームの紹介でした。
これからも面白くてワクワクすることをやっていきながら、ユーザーや社会にしっかりと価値を届けていきたいと思います。最後までご覧いただきありがとうございました。

We are hiring!!

Da Vinci Studio では一緒に働ける仲間を絶賛大大大募集中です!
データ分析に興味がある方、それ以外の開発に興味がある方、あるいは一緒に働いてみたいという方は recruit@da-vinci-studio.net までご連絡ください。もしくは 濱口 までご連絡いただいても大丈夫です。

AWS CodeBuildとGitHub Actionsを使ってテストカバレッジやサマリーを計測・表示させる

Da Vinci Studioサーバー部の徳元です。今回は私が関わっているプロジェクトでJestとGo(testing)のカバレッジやサマリーをAWS CodeBuildとGitHub Actionsを使って計測・表示した話を書いていきたいと思います。

自動テストを書きながらシステム開発をしている方も多いと思いますが、ただテストを書くだけでなくテストカバレッジを日頃から意識することで色々なメリットが得られます。例えば実装者はカバレッジの上昇がはっきりとわかることでテストを書くモチベーションが上がったり、レビュワーはカバレッジファイルを見ることでテストがされていない箇所や全体的にテストの手薄な箇所を認識できたりします。

カバレッジを見るために色々なツールを使うこともできますが、導入するのは手間やコストの面から面倒という方もいらっしゃるのではないでしょうか。実はCodeBuildとGitHub Actionsを使うことによって簡単にそれが実現できるのです。

なにをやったか

私が担当しているプロジェクトではCIにAWS CodeBuild(以下 CodeBuild)を使っています。プルリクエスト(以下 PR)を作成したり、PRへコミットを追加してCIが回った後にJestとtestingのカバレッジファイルへのリンクや、それぞれの簡単なサマリーをPR上にコメントとして表示させるようにしました。

カバレッジコメントの例
※カバレッジの値は実際のものとは異なります

青文字となっている箇所をクリックするとAmazon S3(以下 S3)に置いてあるカバレッジファイルを見ることができ、カバレッジの詳細を見ることができます。

カバレッジ計測ツールを使わなかった理由

カバレッジ計測をするとなるとCodecovなどのツールを使うこともあると思いますが、先述のとおり私が担当しているプロジェクトでは以前からCodeBuildでイメージのビルドやテストを実行しており、Jestもtestingパッケージもまあまあいい感じのカバレッジファイルを吐き出してくれる機能が既にあるので、新しいツールなどは導入せず、シンプルにCodeBuildに出力させたカバレッジファイルをGitHub Actionsに表示させる方がいいと考えこの方法を採用しました。

カバレッジ表示までの大まかな流れ

PRを作成もしくはコミットしてからカバレッジが表示されるまでの大まかな流れは以下の通りです。

カバレッジ表示までの大まかな流れ

GitHubのリポジトリにPRが作成・コミット追加されたタイミングでCodeBuildとGitHub Actionsが走り、CodeBuildによってS3にアップロードされたアーティファクトを使ってサマリーコメントを作成し、PR上に表示します。

アーティファクトがアップロードされた後じゃないとコメントが作成できないので、GitHub Status APIを使ってビルドが終わったかどうかを定期的にチェックします。

ここからは、それぞれの要素について説明していきます。まずはAWS側から説明します。

アーティファクトのアップロード

CodeBuildでは、ビルド出力ファイル等をアーティファクトとしてS3にアップロードすることができます。

まず最初に、コンソールやCloud Formationなどのインフラ定義ツールで該当ビルドプロジェクトのアーティファクトのアップロード先を設定します。そのあとbuildspec.ymlにアーティファクトに関するシーケンスを追加します(参考:CodeBuild のビルド仕様に関するリファレンス)。

具体的には、テストを実行するフェーズでJestとtestingのカバレッジファイルを吐き出させておき、artifactsシーケンスでビルド環境のビルド出力アーティファクトのパスやアップロード先のパスを指定すればOKです。アーティファクトはPOST_BUILDフェーズの後にアップロードされます(参考: ビルドフェーズの移行)。

色々省略していますが、buildspec.ymlは以下のようなイメージになります。

version: 0.2
env:
  variables:
    DOCKER_COMPOSE_YML: docker-compose.ci.yml
phases:
  install:
    runtime-versions:
      docker: 18
  build:
    commands:
      - go test -coverprofile=api.out
      - go tool cover -html=api.out -o api.html
      - npm test src -- --coverage
artifacts:
  files:
    - "coverage/lcov-report/**/*"
    - "api.html"
  base-directory: $CODEBUILD_SRC_DIR
  name: ${CODEBUILD_RESOLVED_SOURCE_VERSION}

S3にアップロードしたカバレッジファイルはBucket PolicyでIP制限しています。また、ライフサイクルルールを追加し、オブジェクトに有効期限を設けています(参考:オブジェクトのライフサイクル管理)。

AWS側の設定はこれで以上です。次に、GitHub Actions側の設定について説明します。

GitHub Actionsの追加

CodeBuildでアーティファクトをアップロードさせるよう設定したら、次はGitHub Actionsでアップロードされたアーティファクトを取得したりサマリーを表示できるようにします。

GitHub Actionsは利用可能なGitHubプランであれば.github/workflows配下にYAMLファイルでワークフローを定義することによって実行することができます。

ステップは以下の三つに大きく分かれます。

  1. AWS Credentialsの設定
  2. アーティファクトアップロードの待機
  3. アーティファクトの取得・PR上への表示

AWS Credentialsの設定

まず、S3からアーティファクトを取得するためにAWSのCredential情報を設定しておきます。

予めリポジトリに設定しておいたSecretsを使って、Workflowの中でAWS CLIを使ってS3にアクセスできるようにします。

name: Display test coverage on PR
on:
  pull_request:
    types: [opened, synchronize]
jobs:
  build:
    name: Build
    runs-on: ubuntu-latest
    steps:
      - name: Configure AWS Credentials
        uses: aws-actions/configure-aws-credentials@v1
        with:
          aws-access-key-id: ${{ secrets.AWS_ACCESS_KEY_ID }}
          aws-secret-access-key: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          aws-region: ap-northeast-1

アーティファクトアップロードの待機

次に、アーティファクトアップロードのステータスを確認するために、GitHub Status APIへPollingを行います。

ここでGitHub Status APIにPOSTするために渡されているGITHUB_TOKENは、GitHubが自動的に生成したものとなります。(参考: GITHUB_TOKENでの認証

- name: Wait for artifcact upload
  id: commit-status
  timeout-minutes: 20
  run: |
    STATUS_API_URL=https://api.github.com/repos/$GITHUB_REPOSITORY/commits/${{ github.event.pull_request.head.sha }}/status
    CURRENT_STATUS=$(curl -v --url $STATUS_API_URL --header 'authorization:Bearer ${{ secrets.GITHUB_TOKEN }}' | jq -r '.state')

    echo "Current status is:$CURRENT_STATUS"

    while [ "$CURRENT_STATUS" = "pending" ]; do
      sleep 10
      CURRENT_STATUS=$(curl --url $STATUS_API_URL --header 'authorization:Bearer ${{ secrets.GITHUB_TOKEN }}' | jq -r '.state')
    done

    echo "Current status is:$CURRENT_STATUS"

    if [ "$CURRENT_STATUS" = "failure" ]; then
      echo "Commit status failed.Canceling execution"
      exit 1
    fi

ステータスを確認する方法を模索しながら実装していた当初は「このWorkflow自体が終わらないとステータスがsuccessになることはないんじゃないか」と思っていたのですが、そんなことはなく、CodeBuildが完了次第GitHub Statusはsuccessになってくれます。

アーティファクトの取得・PR上への表示

aws s3コマンドでアーティファクトの取得をし、Shell Scriptでアーティファクトのパースやサマリーの作成をし、最後にGitHub APIを使ってReview CommentをPOSTをすれば完了となります。 このパートはちょっと長くなるので、分割して説明していこうかと思います。

環境変数の設定

GitHub APIにコメントをPOSTするために必要となるトークンと、POST先のURL、そしてアーティファクトのオブジェクトパスに利用しているコミットハッシュをenvとして設定します。 下の例ではsecrets, githubコンテキストから値を取得しています。

コミットハッシュを取得する方法は他にWorkflowの中で使える環境変数を使うなど、色々な方法があると思いますので、こちらも参考にしてみるといいかもしれません。

- name: Comment on Pull Request
  env:
    TOKEN: ${{ secrets.GITHUB_TOKEN }}
    COMMENT_URL: ${{ github.event.pull_request.comments_url }}
    COMMIT_ID: ${{ github.event.pull_request.head.sha }}
  run: |
    # ここで色んな処理をさせる

アーティファクトの取得とパース

envで設定したCOMMIT_IDを含むプレフィックスを使ってアーティファクトを取得し、HTMLファイルの中から欲しい情報をパースします。

- name: Comment on Pull Request
  env:
    TOKEN: ${{ secrets.GITHUB_TOKEN }}
    COMMENT_URL: ${{ github.event.pull_request.comments_url }}
    COMMIT_ID: ${{ github.event.pull_request.head.sha }}
  run: |
    S3_KEY_PREFIX="s3://sample-bucket/base-directory/$COMMIT_ID"
    WEB_PATH="coverage/lcov-report/index.html"

    # SUMMARYの配列を取得しておく。Jestはデフォルトでカテゴリ別のカバレッジサマリーを用意してくれています
    WEB_SUMMARY=($(aws s3 cp $S3_KEY_PREFIX/$WEB_PATH - | grep strong | sed -e 's/<[^>]*>//g' | tr -d ' '))

    # APIの要素毎にテストカバレッジを計測しているケースを想定しています
    paths=(api.html repositories.html models.html graph.html interactors.html)

    for path in ${paths[@]}; do
      # testingパッケージのカバレッジファイルは各ファイルの中のカバレッジの割合しか表示してくれないので、全体の割合を算出しています
      COVERAGE_DATA=($(aws s3 cp $S3_KEY_PREFIX/api.html - | grep '<option value="file' | cut -d "(" -f2 | cut -d "%" -f1 | sed 's/\.//g' ))

      SUM=$(IFS=+; echo "$((${COVERAGE_DATA[*]}))")

      if [ $SUM == 0 ]; then
        AVERAGE=0
      else
        AVERAGE=$(($SUM/${#COVERAGE_DATA[@]}/10))
      fi
      # ここにサマリーをAppendしていくコードを書く
    done

PR上にコメントを表示する

最後に上記のパースした内容をReview Commentとして渡せるようテキストを修正し、POSTします。

- name: Comment on Pull Request
  env:
    TOKEN: ${{ secrets.GITHUB_TOKEN }}
    COMMENT_URL: ${{ github.event.pull_request.comments_url }}
    COMMIT_ID: ${{ github.event.pull_request.head.sha }}
  run: |
    WEB_PATH="coverage/lcov-report/index.html"
    OBJECT_URL="https://sample-bucket.s3-ap-northeast-1.amazonaws.com/base-directory/$COMMIT_ID"

    # ...中略...
    WEB_SUMMARY_TEXT="- Statements: ${WEB_SUMMARY[0]}\n- Branches: ${WEB_SUMMARY[1]}\n- Functions: ${WEB_SUMMARY[2]}\n- Lines: ${WEB_SUMMARY[3]}"

    for path in ${paths[@]}; do
      # ...中略...
      api_summary_texts+="- [$(echo $path | sed 's/\..*//g')]($OBJECT_URL/$path): $AVERAGE%\n"
    done

    curl -v POST $COMMENT_URL -H "Authorization:Bearer $TOKEN" -d \
    "{\"body\":\"## Coverage\n### Web\n- [Coverage File]($OBJECT_URL/$WEB_PATH)\n$WEB_SUMMARY_TEXT\n### API\n$api_summary_texts\"}"

全体像

これまで説明してきた処理をまとめると、このようになります。

- name: Comment on Pull Request
  env:
    TOKEN: ${{ secrets.GITHUB_TOKEN }}
    COMMENT_URL: ${{ github.event.pull_request.comments_url }}
    COMMIT_ID: ${{ github.event.pull_request.head.sha }}
  run: |
    S3_KEY_PREFIX="s3://sample-bucket/base-directory/$COMMIT_ID"
    WEB_PATH="coverage/lcov-report/index.html"
    WEB_SUMMARY=($(aws s3 cp $S3_KEY_PREFIX/$WEB_PATH - | grep strong | sed -e 's/<[^>]*>//g' | tr -d ' '))
    WEB_SUMMARY_TEXT="- Statements: ${WEB_SUMMARY[0]}\n- Branches: ${WEB_SUMMARY[1]}\n- Functions: ${WEB_SUMMARY[2]}\n- Lines: ${WEB_SUMMARY[3]}"

    OBJECT_URL="https://sample-bucket.s3-ap-northeast-1.amazonaws.com/base-directory/$COMMIT_ID"
    paths=(api.html repositories.html models.html graph.html interactors.html)

    for path in ${paths[@]}; do
      COVERAGE_DATA=($(aws s3 cp $S3_KEY_PREFIX/api.html - | grep '<option value="file' | cut -d "(" -f2 | cut -d "%" -f1 | sed 's/\.//g' ))
      SUM=$(IFS=+; echo "$((${COVERAGE_DATA[*]}))")

      if [ $SUM == 0 ]; then
        AVERAGE=0
      else
        AVERAGE=$(($SUM/${#COVERAGE_DATA[@]}/10))
      fi
      api_summary_texts+="- [$(echo $path | sed 's/\..*//g')]($OBJECT_URL/$path): $AVERAGE%\n"
    done

    curl -v POST $COMMENT_URL -H "Authorization:Bearer $TOKEN" -d \
    "{\"body\":\"## Coverage\n### Web\n- [Coverage File]($OBJECT_URL/$WEB_PATH)\n$WEB_SUMMARY_TEXT\n### API\n$api_summary_texts\"}"

以上で説明は終わりです。これで、冒頭にあったようなカバレッジサマリーが表示されるようになります。

さいごに

最後まで読んでいただきありがとうございました。Da Vinci Studioブログの技術記事の栄えある第一回の投稿をさせてもらいました。これからもインフラ、フロントエンドやサーバサイドの技術についてどんどん書いていきたいと思います。

開発者が成長・変化できるような組織アーキテクチャ

Da Vinci Studio 代表の吉川です。

Da Vinci Studio は、くふうカンパニーグループ内にある開発会社です。 グループ内外の受託開発の他、自社でもサービス開発に取り組んでいます。

異色なのはその成り立ちです。

くふうカンパニーグループはオウチーノ、みんなのウェディング、Zaim、RCUBEといった、もともと自社に開発組織を持ち自社で提供するサービスを持つ会社が集まりました。 そんな中で全く新規の会社として設立され、当初からグループ外の案件の受託を事業内容に入れて組織されました。

自社でしっかりとした事業体があるにも関わらず何故あえてリソースを他に割くようなグループ外の受託を始めたのでしょうか。

自立・自律する組織という考え方

前提として、くふうカンパニーはこの考え方を土台に据えています。 親会社はあくまで各事業会社を支援する立場であり、ユーザーの対面に立つ事業会社が主体的に意思決定をする。 お互い助け合いますし、グループ内での転籍・出向も積極的に行われていますが、グループ内の各社で人事制度などもバラバラです。

そんな中にある開発組織をどう設計するか。

開発者が成長・変化できるための土壌をつくる

Da Vinci Studioを考える際に一番重視したポイントがここです。

例えば会社で一つのアプリケーションのみを開発している場合、仮に個々の機能についてまかせられたとしても、全体の技術選定や設計、あるいはデザインのトンマナなどはトップに近い人間が判断することになります。トップのレベルが全体のキャップになってしまう可能性もありますし、トップのレベルが高かったとすればそれはそれで後進が最終判断をする機会が訪れない可能性もあります。 一方で判断の経験が浅いメンバーにいきなり新規事業等をまかせたとしても、開発プロセスの取捨選択がうまくできず迷走してしまっては誰もメリットがありません。

変化についても同様です。色々な案件を行う受託開発から、一つの開発に集中したいということで事業会社に転職する人もいますし、その逆もいます。 また自身のライフステージが変わったことで取り組む仕事も変えたいという人もいます。 できるだけ長く働いてほしいと考えるのはどこの会社も同じかもしれませんが、本人の志向性の変化は福利厚生だけではカバーできません。

その人のフェーズにあった開発の場が存在することが重要そうですが、どういった場があればよいのでしょうか。

開発プロセスにおける意思決定・判断ができる場を増やす

開発プロセスにおいては大小様々な粒度の意思決定が必要となります。

ひとつのメソッドにどの程度までの責務をもたせるのか、といったことも判断ですし、MVPをどのように定義するのかも判断です。 プロジェクトを進める、あるいは事業開発に近いことをやりたいのであれば、様々な案件で判断の経験を重ねることで引き出しが増えていくでしょうし、 ある特定技術の専門性を高めたい場合、その技術に絡む部分の判断を多くすることで専門性は高まるでしょう。

必ずしもすべての領域において判断ができる必要はなく、一人ひとりで見た場合は、軸となる領域は深まるように、隣接領域には幅が広がるように、 山なりの経験値を得ていくことで、より大きな判断ができるようになるはずです。

開発プロセスにおける取捨選択の精度を高める

判断とは、何かの選択肢を取捨選択することに他なりません。 取捨選択する精度が高まると、開発プロセスの時間が短縮されます。

同じアウトプットにかかる時間が短縮できれば、同じ時間内により多くの試行錯誤を追加して品質をより高めることもできますし、 逆に品質はそのままにアウトプットの数を増やすこともできます。

開発者、あるいは開発組織にとってアウトプットの品質はケースバイケースで一義的に決めるのが難しいポイントですが、 同じアウトプットにかかる時間を短縮することは一義的にプラスに働くことが期待できます。

判断力を高めるために必要なもの

判断判断言っていますが、ランダムに決めてしまえばそれは判断ではなく賭けです。

設計を行う際は、ベストプラクティスとされるものを知っていればそれを利用するべきですし、それがなくとも自身の開発経験から近しいものを組み合わせることで的はずれなことに時間を使うことはなくなるはずです。 何かエラーでハマってしまったという際も、エラーメッセージをちゃんと読むのは当然ながら、そもそもこの構成でXというエラーは起こり得ないので、可能性としてはYかZだ、という風にアタリをつければ、闇雲に色々変えて試すよりはるかに効率が良いはずです。

そのためには

  • スキルセットや知見の幅を広め、あるいは深める
  • それらを活用して判断を行う機会を増やす

ということを念頭に置かなければなりません。

幅を広げるという意味では、幅広い種類の開発案件があると良さそうです。であれば受託開発という形が向いていそうですね。 受託開発の方が新しい技術に取り組みづらい・・というイメージを持つ人もいるかもしれませんが、 自社開発で運用年数が増えると気軽にリプレイスはできませんし、例えばフロントをReactにしたいと思っても事業メリットが無いためそこにリソースを割きづらい・・・ということもあります。 新規で開発する場合はそういった縛りがありません。そのため必然的に新規が増える受託の方が新しい技術を導入しやすいと考えています。 もちろんあまりにピーキーな技術を試すのは憚られますが、それは自社サービスでも同じはずです。

ただこれは表裏一体で、長年の運用を経ることで様々な応用ケースが発生しやすいのは自社サービスです。新規の場合はミニマムで開発する場合が多く、その場合シンプルな構成で済んでしまいます。 それが時間を経ると当初のユースケースでは対応できないためフレームワークの拡張を行ったり自社で何かしら開発したりと複雑になっていきます。そういった場面でしか得られない経験もあるはずです。

受託開発と自社開発の機能を併せ持つ組織

Da Vinci Studioでは冒頭に述べたようにグループ内外の受託と自社開発を行っています。 グループ内事業の既存の年季が入ったサービスもありますし、グループ外で運用されていたシステムを引き継ぐケースもあります。 またグループ内・外においてスクラッチで開発を始めたものもあります。

ただそれらだけだとカジュアルに新規技術を試しづらかったり、あるいはディレクション等まで入る場合にある程度要求レベルも高くなるため(それが良い人ももちろんいます) 自社開発しているものでそこをカバーしています。

例えばリーダーサポートのもと様々な開発案件の実装を行い、その後受託案件で一つのプロジェクトをリードする経験を得る。 あるいはプロジェクト横断で特定の技術領域についてカバーしていく。といった様々な場ができてきます。

自身のロールを宣言する

さらにこういった経験の場を最大限に活用するため、評価制度にも少し工夫があります。

Da Vinci Studioの目標設定フォーマットは

  • 組織の中で自分が解決すべきIssue
  • そのために担うRole
  • 具体的に担えている状態

という3つから構成されており、Approveされた後に毎月これらに対応する形で実績を追加していきます。 なおこれらはGitHub上でプルリクエストを出し、上長でなく全員が見えてコメントできる形をとっています。

初めてこのフォーマットでやるメンバーは面食らう人も多いのですが、それでも出てくる自身のRoleは千差万別です。 例えばRailsについてのスキル・知識を増やすというメンバーもいれば、アプリもサーバーサイドもシームレスに開発できることを目指すメンバーもいます。 しっかりと言葉にすることで、本人だけでなく周囲もそのメンバーの志向性を認識します。 周囲が認識していることで、そのRoleに関連するアサインがされやすくなりますし、相談やトピックも集まりやすくなります。

出る杭は引っこ抜いてエースに

ここまで成長軸を考えているのは、それこそが最終的に開発組織のパフォーマンスを最大化すると考えているためです。 プロダクト開発がうまく進む場合は、組織体制やテクニック的なところももちろんあるでしょうが、やはり中心となるキーマンが存在する場合が多いと考えています。 その人が入るだけで何故かプロジェクトがうまく進む、という人を見かけたことはあるのではないでしょうか。 それはおそらくその人が高い専門性を持ち、固有の専門性を活用して判断できるからだと思います。

もちろん採用で連れてくるという手はあるのですが、そのプロダクトや組織課題にぴったりマッチする人材が見つかることは稀だと思っています。 近しい人は見つかるかもしれませんが、その場合には組織がその人を中心として多少なりとも再構成される必要があります。

つまりいずれにせよ組織は成長・変化できる土壌がなければどこかで行き詰まってしまう可能性が高いということです。

そこで、成長・変化する土壌を最大限に形成するために、あえて既存の開発組織とは別に立ち上げたのです。

実際のところ、当初は実験的な意味合いも含まれていましたが、今となっては当初の規模より3倍ほどに開発者も増え、また様々な開発組織の土壌が混じり合い始めています。 まだまだ課題もありますが、すごい開発者集団になれるように日々頑張っています。

社外へのアウトプットの場をつくる

さてさて場をつくるという意味では、社外へアウトプットしていく経験も重要ですね。 ということでDa Vinci Studio ブログが始まりました。 今後Da Vinci Studioのメンバーが得た経験をどんどんアウトプットしていきたいと思います。

これからどうぞよろしくお願いいたします。