ハウテレビジョンブログ

『外資就活ドットコム』『Liiga』『Mond』を開発している株式会社ハウテレビジョンのブログです。

ハウテレビジョンの事業を支えるデータ基盤

データエンジニアリング

この記事はHowtelevision Advent Calendar 2023 14日目の記事です。 昨日は (id:sumire_how)さんの「ハウテレビジョンのロジカルコミュニケーション研修、特に"刑事の思考法"について」でした。

qiita.com

プラットフォームエンジニアリングチームに所属している縄司(id:nawajie)です。現在は、データエンジニアリング業務を中心としてBIツールの刷新や基盤の改善、データの民主化とデータに関連した課題に幅広く取り組んでいます。

今日は私のチームで取り組んでいる3本の矢について話していこうと思います。

1. データ基盤の効率化

目標は、様々な分析課題を解決する柔軟性と管理されたデータの整合性・品質の両方を備えた基盤を作ることです。昨年は、外資就活のデータ基盤改善を始め、この目標に取り組みました。

昨年の取り組み

  • データ基盤アーキテクチャの定義
  • 外資就活ドットコムのデータ基盤改善
  • DBTの導入
  • Tableauの導入

詳細は以下のリンクからご覧ください。

blog.howtelevision.co.jp

昨年の取り組みにより、データ品質が向上しました。RedashやSlackでも同じ数値を使って意思決定が可能になり、データチームがコードを管理することで変更や修正に迅速に対応できるようになりました。

今年の取り組みについて

外資就活からLiiga、またはLiigaから外資就活へと、プロダクトを超えたデータ分析の需要が高まっています。これに対応するため、プロダクトを横断したデータ連携を可能にするデータ基盤の構築が必要だと感じました。

プロダクト全体で統一された基盤を作成するために、アーキテクチャの再構築を行いました。

  • 各プロダクトのRDSをBigQueryへ転送しデータを一元管理
  • ダッシュボードをTableauからLooker Studio(無料版)へ移行
  • Looker StudioのダッシュボードをSlackへ通知またはNotionに埋め込み
  • 重要指標はDBTで管理
  • Redashでアドホック分析
  • デイリー更新データ(キャッシュデータ)をRedisへ統一

これらを着手する前に現状の基盤内の状態を精査しました。その過程で問題点と修正が必要なものがいくつか見つかりました。

課題と対応

Redash内に不必要なスケジュールクエリが多い

約5000個のクエリがあり、そのうち約600個がスケジュール設定されています。しかし、その半分は現在全く使用していないクエリです。RedashはAWSで運用しているため、管理コストがかかります。また、データコネクタでBigQueryに接続しているため、クエリの実行にもコストが発生します。最低でも月額70ドル以上の無駄な支出が発生していることが明らかになりました。

したがって、まずはRedash内のクエリを整理し、運用フローを見直すことから始めます。

GCPのコスト

具体的には、「Billing」に記載されている費用の分析だけでなく、各プロジェクトのテーブルのストレージ費用とクエリ実行によるAnalysis費用を算出し、その要因を解析しました。BigQueryではこれらの費用をクエリで確認できますが、Analysis費用については90日間の履歴しか見ることができません。「Billing」で概要を見ることができますが、費用の詳細な内訳を分析するためには、課金データのエクスポートが必要なので設定を行いました。

分析の結果、驚くべきことにこの四半期に料金が急激に増加していました。図の青色部分が主な要因で、私たちが使用しているVertex AIの管理にミスがあり、コストが大幅に増加していました。このタイミングで発見できてよかったと思います。

また、BigQuery内にも不要なテーブルが存在し、そのストレージ費用で約$100/月の支出があることがわかりました。ストレージ費用とAnalysis費用は以下のクエリを例に算出しています。

# ストレージ計算
SELECT DISTINCT
  DATE(creation_time, "Asia/Tokyo") AS jst_creation_date,
  DATE(storage_last_modified_time, "Asia/Tokyo") AS jst_last_modified,
  project_id,
  table_schema,
  table_name,
  active_logical_bytes / POW(1024, 3) AS active_logical_gb,
  long_term_logical_bytes / POW(1024, 3) AS long_term_logical_gb
FROM `region-us.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT`
ORDER BY 4, 1

# Analysis計算
SELECT
  FORMAT_DATE("%Y-%m", DATE(creation_time, "Asia/Tokyo")) AS month,
  user_email,
  SUM(total_bytes_processed) / POW(1024, 4) AS total_execute_tb,
FROM `region-US`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
GROUP BY 1, 2
ORDER BY 1, 2;
テーブルの状態改善

RDSにはログデータを格納しているテーブルや現在使用していないテーブルが複数存在しています。また、テーブルやカラムの意味がわかりにくく、混乱を招くことがあります。これらはコストと管理の観点から改善が必要でした。詳細は後述します。

TableauからLooker Studio(無料版)への移行

ダッシュボードをTableauからLooker Studio(無料版)へ移行した理由は、Tableauへのデータ接続に負荷が大きく、テーブルやデータが整理されておらずデータ分析チームの依頼が増えたからです。自身で分析できる環境と多機能なダッシュボードを目指して導入しましたが、現状では費用対効果が低いと判断し、Looker Studioへの移行を決定しました。

Airbyteのアップグレード

データ転送ツールとしてOSSのAirbyteを使用していますが、古いバージョンv0.35.5-alphaを使っています。最新バージョンはv0.50.35で、大幅に遅れていました。最新バージョンでは多くの改善があるため、アップグレードを試みました。

これらの課題を一つずつ解決し、基盤改善に向けて進行中です。

2. データの見える化

テーブルの状態改善

現状では、RDSとBigQueryのテーブル構造がブラックボックス化されており、分析者やエンジニアでさえも、一部のテーブルの内容が理解できない状況があります。

この問題を解決するために取り組むべき課題を以下に列挙します。

RDS
  • 不要なテーブルの削除
  • メタデータの管理
  • 外部制約キーの適用
  • ログテーブルの整理
BigQuery
  • 不要なテーブルの削除
  • テーブルのメタデータの整理
  • データセットの構成
  • ログテーブルの整理

これらの課題に取り組む際の流れは以下の3ステップです。

  1. テーブルの洗い出しと整理
  2. 関連テーブルとコードの調査
  3. 改善作業

これらの課題に取り組むためには、関連性を調査しながら、一つ一つのテーブルを詳細に確認する必要があります。これは時間がかかる作業です。

特に、2番目のステップが重要で、関連するコードが残っている状態でテーブルを削除すると、予期せぬエラーの原因となる可能性があります。また、ログテーブルの一部をDWHに直接Insertする変更方針については、リアルタイムで大量のデータが流入するため、移行作業には注意が必要です。

外部制約キーはデータの整合性を保つために重要ですが、不適切な使用はパフォーマンスの低下を引き起こす可能性があります。

作業効率を上げる方法はまだ見つかっていませんが、その他のタスクの合間や、根気強く取り組んでいます。(笑)

データカタログの導入

テーブルの状態改善が完了したら、データの所在を整理するドキュメントが必要だと考えています。

これまではNotionを使用して管理しようと試みていましたが、テーブルの更新毎にアップデートしたり、リレーションを管理するのは大変です。

そこで、テーブルのドキュメンテーションツールであるSchemaSpyを導入することを考えています。SchemaSpyは、テーブルの構成をHTML形式で出力し、リレーションやメタ情報、ER図を表示するだけでなく、テーブル検索機能も備えているため、非常に便利です。

以下に、公式のサンプルがあります。

schemaspy.org

3. データの民主化

最近、データドリブンやDXが話題となっていますが、データの活用はエンジニアやデータサイエンティストだけでなく、マーケティングや営業の方々にも必要です。

専門的な知識がない人々に対して、理解しやすい形でデータを提供したり、データを分析して示唆を出すことがデータサイエンティストの醍醐味だと言えます。しかし、人手不足で手が回らない場合や有用な提案が出せない場合もあります。そのため、SQLや分析ツールの習得を促し、サービスDBから自分で分析できる人数を増やす方が、意思決定の速度を上げ、必要なデータを得られる可能性が高まります。

当社内で自主的にSQLを学び業務に活かしている素晴らしい事例があります！

blog.howtelevision.co.jp

そこで、主に非エンジニアを対象にしたSQLハンズオンを社内で展開することしました。年末・年始に実施し、その感想や成果をまとめる予定です。

まとめ

これらの取り組みを通じて、データ基盤の効率化、データの可視化、そしてデータの民主化を推進しています。これらは全て、より高質なサービスを提供するための基盤を強化することを目指しています。データはビジネスの中心であり、それを最大限に活用することで、私たちの事業をさらに前進させることができます。今後もこの取り組みを続け、私たちのデータ基盤が常に最適な状態であるように努力を続けます。