Redashを整理してルールを作ったので紹介します！〜SHOWROOMのデータ民主化に向けた取り組み〜

2022年10月5日 13:16

こんにちは！開発部でデータアナリストをしている松尾です。

前回、現在進めているデータの民主化に向けた取り組みをご紹介させていただきました。

今回は、その取り組みの１つであるRedashの整理について書いていきたいと思います。クエリが乱立してしまった状態からmetadataを使って整理を進め、運用ルールとRedashの検索ができるダッシュボードを作りましたので、ご紹介していきます。

※metadataを使えるようにする方法は、こちらの記事をご覧ください

SHOWROOMの分析環境を知っていただくきっかけとなったり、同じくRedashを導入されている、もしくは導入を検討されている方の今後の運用のご参考になれば幸いです！

なぜ運用ルールが必要か

Redashは手軽にSQLの実行結果をビジュアル化できるので、モニタリングや効果検証目的以外にもちょっとした調査や探索的な分析をしたい時にクエリを作る人も多く、あっという間にクエリは増えていきます。SHOWROOMでも直近1年で作られたクエリは約1,000件に上ります。

命名規則やタグ付けのルールなしで各々が自由にクエリを作り続けていると、下記のような問題が発生してしまいました。

検索性の悪化
- 同じ指標を集計しているのにタグやタイトルがバラバラだと検索で引っかかりません。検索性が悪くなると新しく作った方が早い…となり、無駄な作業工数がかかってしまいますし、同じ集計をしているクエリが複数作られてさらにクエリが増える結果に…。
正しくデータを解釈する難易度の上昇
- 命名規則がないとタイトルだけでは何を集計しているのか分からず、SQLを読まないと理解が難しいクエリも散見されるようになります。また同じ指標名がタイトルに記載されていても作った人によって若干定義が異なったりすると、どのクエリが信頼できるものなのか判断が難しくなってしまい、誤ったデータの解釈にも繋がります。

日々の意思決定に使われるRedashを正しくデータを理解して判断ができたり、見たいデータを見つけられる状態に維持することはとても重要だと思います。そのため、指標名を統一したり命名規則などを決めて共通認識を持ち、定期的に使わなくなったダッシュボードやクエリはアーカイブするなど、ルールを決めて運用することに決めました。

分類と命名規則などのルール

タグ付けによる分類

ダッシュボードとクエリを下記の通り４つに分類しています。

みんなが使い、今後も使うもの
個人で使うもので、残しておきたいもの
使わなくなったらアーカイブして良いもの
分析チームだけ使うもの（分析チームのみ）

それぞれ、下記のようなルールを決めています。

それぞれ、下記の通りタグ付けを行うことで分類しています。

タイトルやカラムの命名規則は①と④だけ適用するようにしています。

①みんなが使い、今後も使うもの
- 主にモニタリングや効果検証、必要なデータの取得などに日々使われるもの。
④分析チームだけ使うもの（分析チームのみ）
- Redashの整理で使うものや、分析チームの定例内で議題に上がった分析課題に関わるものなど。

①に分類するダッシュボードやクエリは命名規則も適用し、信頼性の高いものが分類された状態を維持していきたいため、新規で追加する時には分析チームの確認・承認が必要な形にしています。

①に新規でクエリを追加したい場合
- 分類したいメインカテゴリとサブカテゴリ、judge（承認申請）の３つのタグ付けだけしてもらい、毎日judgeタグがついたクエリの一覧を自動でSlackに飛ばすようにしています。分析チームでタイトルなどの命名の確認やそのクエリで見れる指標とapproved（承認済み）のタグ付けを行います。

Redashのmetadataに各クエリに付けられたタグのデータがあるので、下記のようにPostgreSQLを書いてjudgeタグがついたクエリを取得することができます。

SELECT
  q.name, -- クエリ名
  'https://redash.hogehoge.com/queries/' || q.id || '' AS url, -- クエリのURL
  u.name AS created_by, -- クエリの作成者
  q.tags -- クエリに付けられているタグ
FROM
  queries AS q
LEFT JOIN users AS u ON q.user_id = u.id
WHERE
  q.is_archived = false --アーカイブされていない
  AND
  q.is_draft = false --publishされている
  AND
  q.tags @> '{judge}'

ちょっとした調査や探索的な分析などで個人的に作ったクエリは、【②個人で使うもので、残しておきたいもの】と【③使わなくなったらアーカイブして良いもの】に各自分けてもらい、命名規則は気にせず自由に作れるようにすることで、クエリを作るハードルを上げないようにしています。

定期的なアーカイブ

月に１回、下記の通り整理しています。

【①みんなが使い、今後も使うもの】で使われていないものは【③ 使わなくなったらアーカイブして良いもの】に分類を変える
【③ 使わなくなったらアーカイブして良いもの】と【④分析チームだけ使うもの（分析チームのみ）】で使われていないものはアーカイブ

分類変更やアーカイブの条件は、直近半年の利用状況を集計して決めています。各ダッシュボードやクエリの利用状況もmetadataを使って集計ができ、例えば各クエリのviewやexecuteしたUU数は下記のように集計することができます。

SELECT
  q.name, -- クエリ名
  'https://redash.hogehoge.com/queries/' || q.id || '' AS url, -- クエリのURL
  -- 直近7日
  COUNT(DISTINCT CASE WHEN e.created_at + INTERVAL '9 HOUR' >= (CURRENT_DATE + INTERVAL '-7 DAYS') AND e.action = 'view' THEN e.user_id ELSE NULL END) AS view_uu_7days,
  COUNT(DISTINCT CASE WHEN e.created_at + INTERVAL '9 HOUR' >= (CURRENT_DATE + INTERVAL '-7 DAYS') AND e.action = 'execute' THEN e.user_id ELSE NULL END) AS execute_uu_7days,
  -- 直近1ヶ月
  COUNT(DISTINCT CASE WHEN e.created_at + INTERVAL '9 HOUR' >= (CURRENT_DATE + INTERVAL '-1 MONTH') AND e.action = 'view' THEN e.user_id ELSE NULL END) AS view_uu_1month,
  COUNT(DISTINCT CASE WHEN e.created_at + INTERVAL '9 HOUR' >= (CURRENT_DATE + INTERVAL '-1 MONTH') AND e.action = 'execute' THEN e.user_id ELSE NULL END) AS execute_uu_1month,
  -- 直近3ヶ月
  COUNT(DISTINCT CASE WHEN e.created_at + INTERVAL '9 HOUR' >= (CURRENT_DATE + INTERVAL '-3 MONTHS') AND e.action = 'view' THEN e.user_id ELSE NULL END) AS view_uu_3months,
  COUNT(DISTINCT CASE WHEN e.created_at + INTERVAL '9 HOUR' >= (CURRENT_DATE + INTERVAL '-3 MONTHS') AND e.action = 'execute' THEN e.user_id ELSE NULL END) AS execute_uu_3months
FROM
  events e
LEFT JOIN queries q ON CAST(e.object_id AS INTEGER) = q.id
WHERE
  e.action IN ('view','execute')
  AND
  e.object_type = 'query'
  AND
  q.is_archived = false
GROUP BY 1,2

アーカイブ条件が定まっていれば、UPDATE文とクエリのRefreshスケジュールの設定を使ってアーカイブを自動化することもできます。

命名規則

指標・カラム名の統一
- 定義を理解して正しくデータを解釈できるよう、タイトルやタグに記入する指標名やカラム名を統一することにしました。各指標名とその定義、カラム名の一覧をGoogleスプレッドシートに記入してBigQueryに取り込み、Redashでも見れるようにしています。

タイトル
- ダッシュボード名
  - 月別/週別/日別それぞれ作成した場合やパラメータ指定が必要なダッシュボードの場合、タイトルに記入することだけ守ってもらい、他は細かいルールはなく各自何が見れるダッシュボードかなるべく分かるタイトルにしてもらっています。
- クエリ名
  - 少し複雑ですが、タイトルだけで何が見れるクエリか分かるように下記の通り命名してもらっています。
  - 【①】②_③（④）：⑤ ※⑥
    - ①集計対象 / ②集計した指標名 / ③グルーピング / ④集計対象期間・単位 / ⑤パラメーター指定 / ⑥追記事項あれば
    - <例>
      - 配信ライバー数（週別）→ 週別に配信があったライバー数が見れる
      - 【バーチャル】配信ライバー数（週別）→ 週別に配信があったバーチャルジャンルのライバー数が見れる
      - 配信ライバー数_ジャンル別（月別）→ 月別に配信があったライバー数がジャンル別に見れる

Redashの検索ができるダッシュボードを作ってみた

前述の通り【①みんなが使い、今後も使うもの】に分類されたダッシュボードやクエリは命名規則も適用し、信頼性の高いものが分類されるようになっています。分類のためにつけたタグを使って、①に分類されたダッシュボードとクエリのみ検索できるダッシュボードを作ってみました。

カテゴリ別に該当ダッシュボードやクエリの一覧が見れるようにすることで、各部門やプロジェクトでどのようなデータを日々見ているか分かりやすくなり、データを探したい時も目的に合った信頼できるクエリを探せるようになっています。ダッシュボード・クエリの検索にもmetadataを使っています。

【[Sample-A] ダッシュボード検索】のクエリに使っているPostgreSQL

SELECT
  d.tags[1] AS main_category__filter, -- 1つ目のタグがメインカテゴリ
  d.tags[2] AS sub_category, -- 2つ目のタグがサブカテゴリ
  d.name AS dashboard_name,
  'https://redash.hogehoge.com/dashboards/' || d.id || '' AS url,
  u.name AS created_by,
  d.created_at
FROM
  dashboards AS d
LEFT JOIN users AS u ON d.user_id = u.id
WHERE
  d.is_archived = false
  AND
  d.is_draft = false
  AND
  d.tags[1] IS NOT NULL
  AND
  d.tags[1] NOT IN ('分析チーム管理','user')
ORDER BY 1,2,3

【[Sample-B] クエリのメインカテゴリとクエリ一覧へのリンク】のクエリに使っているPostgreSQL

WITH
add_description AS (
  SELECT
    'hogehoge' AS main_category,
    'hogehoge' AS description
  UNION ALL
  SELECT
    'hogefuga' AS main_category,
    'hogefuga' AS description
  UNION ALL
  ...
)

SELECT
  q.main_category,
  '<a href="https://redash.hogehoge.com/queries/[クエリ検索のID]?p_main_category=' || q.main_category ||'">クエリ一覧</a>' as url,
  d.description,
  q.sub_category
FROM (
  SELECT
    tags[1] AS main_category,
    STRING_AGG(DISTINCT tags[2],' / ' ORDER BY tags[2]) AS sub_category
  FROM
    queries
  WHERE
    is_archived = false
    AND
    is_draft = false
    AND
    tags[3:] @> '{approved}' -- 3つ目以降のタグにapprovedを含む
  GROUP BY 1
) AS q
LEFT JOIN add_description AS d USING (main_category)
ORDER BY 1

【[Sample-B] クエリのメインカテゴリとクエリ一覧へのリンク】のクエリ一覧のリンク先は以下のようなクエリになっており、リンクから飛ぶとメインカテゴリが自動で指定されるようになっています。検索窓に記入した指標を集計しているクエリに絞ることもできます。

【[Sample-C] クエリ検索】のクエリに使っているPostgreSQL

WITH
approved_queries AS (
  SELECT
    id,
    name,
    url,
    main_category,
    sub_category,
    STRING_AGG(query_tag,' / ' ORDER BY query_tag) AS query_tags
  FROM (
    SELECT
      id,
      name,
      'https://redash.hogehoge.com/queries/'  || id || '' AS url,
      tags[1] AS main_category, -- 1つ目のタグがメインカテゴリ
      tags[2] AS sub_category, -- 2つ目のタグがサブカテゴリ
      unnest(tags[3:]) AS query_tag -- 3つ目以降のタグがそのクエリで集計されている指標
    FROM
      queries
    WHERE
      is_archived = false
      AND
      is_draft = false
      AND
      tags[3:] @> '{approved}'
  ) q
  WHERE
    query_tag <> 'approved' -- approvedタグは表示しない
  GROUP BY 1,2,3,4,5
)

SELECT
  q.sub_category AS "sub_category::multi-filter",
  q.name,
  q.url,
  q.query_tags,
  STRING_AGG(DISTINCT d.name,' / ') AS dashboard_name
FROM
  approved_queries AS q
LEFT JOIN visualizations AS v ON q.id = v.query_id
LEFT JOIN widgets AS w ON v.id = w.visualization_id
LEFT JOIN dashboards AS d ON w.dashboard_id = d.id
WHERE
  q.main_category = '{{ main_category }}' -- パラメータ指定
GROUP BY 1,2,3,4
ORDER BY 1,2

さいごに

最後までお読みいただき、ありがとうございます！！

Redash導入の段階で分類するカテゴリや命名規則を決めるのは難しいですが、クエリの整理はクエリが多いほど大変になるので、なるべく早い段階で最低限でもルールを決めておくことをオススメします！

SHOWROOMではダッシュボードやクエリがかなり増えてしまった状態から整理をしたため、特にクエリは何から手をつけたらいいか分かりませんでした。しかしmetadataが本当に便利で、ダッシュボードは直近のviewを集計してマージできそうなものはviewUU数が多いダッシュボードにマージしていったり、クエリはダッシュボードで使われているクエリやその他直近のview・executeのUU数が多いクエリをよく使われているものとして、該当するクエリの一覧を取得して前述の【①みんなが使い、今後も使うもの】に分類していきました。

この記事を読んで下さったRedashユーザーの皆さまも同じような課題に直面し、何かしらルールを決めて運用されているのではないかと思います。SHOWROOMでもより良い運用ルールを模索しながら引き続き運用していきます。

データに関わる様々な取り組みに一緒に挑戦して下さるデータエンジニアも現在募集中です！

SHOWROOM株式会社では、エンジニア職を絶賛募集中です。
・ライブ配信サービス「SHOWROOM」
・バーティカルシアターアプリ「smash.」

以下の求人内容をご確認の上、ご興味あればご応募ください。
心よりご応募お待ちしております。

■求人は以下になります
SHOWROOM株式会社採用情報

■その他SHOWROOM株式会社の情報
・ note（代表前田取材記事です！）
・社員インタビュー記事
・ SHOWROOM
・ smash.
・ SHOWROOM最新記事一覧（PRTimes）