300万テーブルのデータ流通を支えるエンジニアリング #GoogleCloud #GoogleCloudDay / 20230523

300 万テーブルのデータ流通を
支えるエンジニアリング
NE株式会社　熱田亮
株式会社風音屋　横山翔

View Slide

スピーカー自己紹介
カリフォルニア州立大学CS卒業。米スタートアップ入社
後、独自プロトコル等担当。帰国後、電力システムや事前
登録者数50万規模のゲーム開発に携わる。Hamee株式
会社にSREとして入社後、データ基盤をBigQueryへ移行、
構築。分社化後はNE株式会社にて年間流通額1兆円規模
のデータを社内外へ活用促進するためデータマネジメント
部のデータエンジニアとして勤務。
熱田亮
NE株式会社
データマネジメント部
Data Engineer

View Slide

スピーカー自己紹介
リクルートやメルカリにてデータ活用を推進後、外資ITを経
て、風音屋を創業。広告配信最適化や営業インセンティブ
設計など、データを駆使した業務改善を得意とする。コミュ
ニティ活動では Developers Summit や Data
Engineering Study の運営に携わり、データ基盤やダッ
シュボード構築について情報発信している。主な著書に
『データマネジメントが30分でわかる本』『実践的データ基
盤への処方箋』がある。
横山翔
株式会社風音屋
代表取締役

View Slide

● スモールチームで、300万テーブル規模のデータ基盤を構築・運用し、
社内・社外にデータを提供しています。
● 「社内外へのデータ提供を検討したい」と言われたテックリードの方々が
スケーラブルな仕組みやデータ流通を実現するヒントになればと思います。
プラクティスの
開拓が必要
（横山パート）
スケーラブルな仕組みが必要（熱田パート）
SaaSの標準機能で済ませられる
このセッションの概要
収集移行
モデリング・
加工
社外提供
社内提供
小規模
大規模
公開事例が
少ない領域
公開事例が
多い領域

View Slide

目次
● プロジェクト概要
● データ収集から社外提供まで
○ データ収集
○ データ移行
○ モデリング・加工
○ 社内への提供
○ 社外への提供
● まとめ

View Slide

プロジェクト概要

View Slide

会社概要
● NE株式会社（エヌイー株式会社）は EC Attractions「NEXT ENGINE」を
中核に、EC SaaS 事業、EC コンサルティング事業、ふるさと納税支援事業を
提供し、全てのコマースを支えることを目指して活動
● スマホケース「iFace」などを制作販売している Hamee株式会社から
2022 年 8 月に分社して出来た会社

View Slide

ネクストエンジンとは
● NE株式会社が提供する一元管理ツール
● 2021 年度の年間流通総額が
1 兆円を突破
● ビジネス拡大施策として
データ提供・販売を進めている

View Slide

背景・課題
● データ基盤として Treasure Data を使っていたが
想定以上のコストが掛かっていた
● 旧データ基盤のパフォーマンスの問題があった
● SQL に癖があり、社内で浸透しなかった
● 契約が年末に切れるとのことでデータ基盤移行プロジェクト発足

View Slide

プロジェクト体制
以下５人チーム
● 執行役員
● Analytics Engineer（PL）
● Analytics Engineer（Unit の MGR 兼任）
● Data Engineer（当時 SRE）
● 外部アドバイザー（風音屋）

View Slide

新データ基盤構成
これを全て一年以内に構築する
Facebook

View Slide

収集から提供まで
● 収集
● 移行
● モデリング・加工
● 社内提供
● 社外提供・ビジネス開拓

View Slide

データ収集
● 収集
● 移行
● 社内提供

View Slide

データ収集
Facebook

View Slide

データ収集の流れ
PFとMFと呼ばれる2つのシステムについて、それぞれ適した方法でデータを取得
● スナップショットから復元しているケース
● シャードで別れているケース（300万テーブル）

View Slide

スナップショットからの取得
■ 処理の流れ
１. 最新のスナップショットからRDSインスタンスを復元
２. 復元したRDSからBigQueryへデータ送信
３. 復元したRDSを削除
Fargate
Event
Bridge
RDS
ECR

View Slide

シャードからの取得
24 シャード・約 300 万テーブルから日次でデータ取得する連携システム
1. EventBridge で毎日 0 時に実行し、Fargate コンテナを 24 並列起動
2. 300万テーブルからデータを取得
3. 取得したデータをBigQuery へ流し込む

View Slide

ECR
Fargate
Event
Bridge
CloudWatch
Logs
Containers

View Slide

ECR
Fargate
Event
Bridge
CloudWatch
Logs
Containers
1. EventBridge によるコンテナ起動

View Slide

ECR
Fargate
Event
Bridge
CloudWatch
Logs
Containers
2. 連携システムによりデータを取得

View Slide

ECR
Fargate
Event
Bridge
CloudWatch
Logs
Containers
3. BigQuery へ流し込む

View Slide

ETL SaaS もいくつか検討したが...
● 流石に 300 万テーブルに耐えるサービスは存在しなかった
● trocco を試してみたが「300 万テーブルの転送がそれぞれ
1 分かかった場合、転送にかかる時間は 5 万時間となり、
月換算だと 150 万時間となる」といわれ断念
● Datastream を試してみたが連携できるのは 1 万テーブルまでだったため断念
○ 1 シャード約 12 万テーブル

View Slide

データ移行
● 収集
● 移行
● 社内提供

View Slide

データ移行時の課題
① シャードから約 15 年分の全データを取得しなければならなかった
② 旧データ基盤（TD）にあるデータを全てバックアップする必要があった
③ 旧データ基盤（TD）上の移行対象の SQL は計数千行
① 〜 ③ のデータ整合性確認をどうするのか

View Slide

① 全データ取得
● 不正データや Communication Link Failure などエラーが多数発生し連携に失敗
○ 一つずつ原因を特定して修正
● データ取得には負荷と IO 課金が大きな額発生（SRE と協力）
○ スナップショットから復元したシャードから取得することで負荷を回避
○ 社内調整
● DateTime が 9 時間ずれることで集計結果が旧データ基盤と一致しない
○ 全取得する際に復元したシャードのパラメータグループのタイムゾーンがUTC
○ 取得しなおし

View Slide

15 年分全データ流し込み
（通称：過去データインポート）

View Slide

② 旧データ基盤からのバックアップの課題
● 数千テーブル 100 TB 以上が旧データ基盤（Treasure Data）に存在していた
● 都合上、旧データ基盤の集計データも引き続き使用するため旧データ基盤の解約直前
である年末にバックアップを実施しなければならなかった

View Slide

Cloud Run による
100 並列バックアップ取得
Treasure Data

View Slide

③ SQL 移行と整合性確認の課題
● とにかく SQL の量が膨大だった
● Treasure Data 記法から、BigQuery の書き方に全て変更しなければならなかった
● 旧データ基盤も値が間違っていることがあった
● 関係者全員から理解を得るには「なぜ集計結果がズレているのか」を
全て洗い出さなければならなかった

View Slide

● 気合と根性
● スプレッドシートによる整合性確認（ 2 ヶ月）
● ① ~ ③ 全てに対し実施
整合性確認

View Slide

データ移行のまとめ
● シャードから約 15 年分の全データ流し込み
● 旧データ基盤（TD）にあるデータを Cloud Run で 100 並列バックアップ取得
● 旧データ基盤（TD）に存在した合計数千行にもなる SQL の移行
● スプレッドシートで 2 ヶ月間データ整合性確認

View Slide

モデリング・加工 > データ構成
● 収集
● 移行
● 社内提供

View Slide

モデリング・加工
Facebook

View Slide

複数パターンの構成案を検討

View Slide

最終的に採用した構成

View Slide

Data Lake (DL)
● 概要
○ 生データを保存
○ ソースの種類毎にデータセット作成
○ 機密性の観点から DM からの参照は許可しない
● ソースの種類
○ GA4
○ Marketo
○ プロダクトデータ
● 連携方法
○ BQ 連携システム（Embulk）
○ Airbyte
○ BigQuery Data Transfer Service

View Slide

Data Ware House (DWH)
● cleansing
○ クレンジング済みデータ
○ 機密情報は取得する際にマスキング
● cleansing_secret
○ マスキング前の機密情報
○ IAM 権限で強く縛っている
● snapshot
○ ログや履歴など
○ 推移などに利用
● fact
○ イベント毎にレコードが新規作成されるもの

View Slide

Data Mart (DM)
● role_<ロール名>
○ ロール毎にデータセットを作成
● service_<サービス名>
○ 外部システムから参照する
● monitoring
○ 監視用テーブル配置
● widetable
○ 広く共通で使われるデータ
○ 一番利用率が高い

View Slide

Data Mart (DM)
● public_common
○ 外部の複数企業から参照される共通テーブルを配置
● public_<企業ID>_<企業名>
○ 1 企業毎に参照される
● workshop
○ 利用者が各自で自由に作成できる

View Slide

View Slide

モデリング・加工 > 技術スタック
● 収集
● 移行
● 社内提供

View Slide

技術スタック
● 言語: Python（自動化する際に使用）
● データ加工ツール: dbt（ELT におけるT「変換」の役割を担う）
● テンプレートエンジン: Jinja

View Slide

SQLを実行してデータを加工

View Slide

View Slide

Jinja とは
● テンプレートエンジン
● dbt ではSQL の動的生成に使っている
● テキストに対して、次のような書き方ができる

View Slide

list には test1, test2, test3 という文字列がリストの要素として入っている
{% for data in list %}
・{{ data }}
{% endfor %}
list には test1, test2, test3 という文字列がリストの要素として入っている
・test1
・test2
・test3
test.tpl
test.txt

View Slide

モデリング・加工 > Cleansing
● 収集
● 移行
● 社内提供

View Slide

Cleansing 処理
● UTC を JST に変換
● 文字列になっている日付を DateTime 型に変換
● 重複削除

View Slide

Cleansing の課題
膨大なテーブル、カラムを一つ一つ Cleansing するための SQL を
書くことは開発、保守運用、将来的な変更を考慮すると現実的ではない

View Slide

SQL Generator 処理の流れ
1. データベースの information_schema からテーブル、カラムのメタデータを
取得し、CSV 形式で保存
2. masking, partitioning, casting 対象のテーブル、カラムを
それぞれ CSV 形式で保存
3. それらを一つの CSV に結合する
4. その結合した CSV の情報を元にテンプレートエンジンで SQL 生成

View Slide

input_casting.csv input_partitioning.csv input_masking.csv
input_delete_duplicate.csv
CSV
結合

View Slide

結合後のCSV

View Slide

テンプレート(jinja) SQL自動生成

View Slide

全テーブル SQL 自動生成

View Slide

開発したことによって何が得られたか
● 作業の簡略化
● 千人分のアウトプットが出せる
● 気軽にリアーキが可能

View Slide

1つのP.R.を出すのに20分〜

View Slide

これでデータ基盤移行プロジェクトは完了

View Slide

BigQuery へ移行したことで何が得られたか

View Slide

基盤移行の成果
● コスト削減
● 集計速度数十倍高速化

View Slide

社内提供 > 利用促進
● 収集
● 移行
● 社内提供

View Slide

社内へのデータ提供
Facebook

View Slide

社内提供における課題
● 価値に気付けない
● 認知されていない
● 難しそうなイメージ
● 安全に使えるのか不安
データ基盤を作って終わり、では決してない。

View Slide

「認知されていない」課題解決
● データ基盤の「存在」と「価値」を都度伝える
● BigQuery 勉強会開催により基盤に対する「壁」を取り除く
● help チャンネル運用で「信用」を積み重ねる
● 他部署に対する「リスペクト」を常に忘れないこと

View Slide

社内勉強会の開催
● テーマ
○ 移行プロジェクトの説明
○ BigQuery の利用案内
○ Connected Sheet の紹介
● 頻度
○ 四半期毎に実施
● 対象者
○ エンジニアか否かは問わず
○ データ基盤に興味のある方

View Slide

社内勉強会の開催
データ利用者とのコミュニケーション機会として機能
● 移行プロジェクトの状況報告
● 旧基盤からの移行作業の依頼

View Slide

Connected Sheet

View Slide

Connected Sheet
接続
BigQuery Spreadsheet

View Slide

Looker Studio

View Slide

「安全に使えるのか不安」課題解決：IAM管理
● 最小権限の付与
○ プロジェクトレベルでの権限設定は基本使用しない
● リソース単位で厳密に設定
○ データセット、バケットなど
○ Resource / Role のマトリクスで権限管理
● 定期的な棚卸し

View Slide

課題解決：IAM管理
Resource / Role Data Sales Enginneer
role_xxxx R - R
public_xxxx - - -
widetables R R R
workshop WS WS WS
- : 閲覧禁止
R: 閲覧者
WW: 編集者（弱）
WS: 編集者（強）

View Slide

基盤利用率の向上
● 社内全体のデータ基盤利用率が向上
■ エンジニアは自動化率測定に使用
■ セールスはデータ基盤に価値を感じ自ら SQL を学び始めている
■ コンサルはダッシュボードからインサイトを得て社外提供

View Slide

社内提供 > 商品カテゴリー
● 収集
● 移行
● 社内提供

View Slide

社内データ提供時に挙がった課題
● 商品がカテゴリーに分類できない
● ネクストエンジンでは「商品分類」が任意項目となっており、
一部のモールのカテゴリデータが存在しない
● このままだとデータ分析で最初に見たくなるであろう
「このカテゴリーの売れ行きが良い・悪い」を見ることができない

View Slide

商品カテゴリー推定モデルを構築するため
ChatGPT でアノテーションの自動化をして

View Slide

Vertex AI / AutoML を用いて学習・予測させる

View Slide

ChatGPTによるアノテーション自動化

View Slide

View Slide

データセット作成学習・モデル構築
Vertex AI / AutoML による学習・予測

View Slide

Vertex AI / AutoML による学習・予測

View Slide

しかし、、、
● 予測にかかるコストが想定以上に高かった
○ 数十億レコードある流通額テーブル全てに対して分類させてしまうと、膨大なお金が
かかることが発覚
● Tensorflow モデルをエクスポートして CloudRun で並列実行できないか検証
○ text classification はエクスポートの対象外だった
● AutoML 自体は機械学習の PoC をする分には非常に良い選択肢

View Slide

社内提供 > BigQuery ML による
異常検出
● 収集
● 移行
● 社内提供

View Slide

異常検出システム構成

View Slide

BigQuery ML による異常検出
学習
予測

View Slide

BigQuery ML による異常検出

View Slide

実際に何があったか
● ２０２３年１月２３日：大寒波到来
● ヤマト運輸配送遅延
● moreネクストエンジンが提供している「配送遅延時のお知らせ方法に関
する記事」にアクセスが集中

View Slide

異常検出まとめ
● BigQuery ML によりたった数行のSQLで異常検出モデルが構築可能に
● 毎回Google Search Console を見て確認する必要がなくなった

View Slide

社外提供・ビジネス開拓
● 収集
● 移行
● 社内提供

View Slide

データ提供によるビジネス機会の開拓
● 以前より利用者のデータ分析を支援する機能やコンサルティングを提供
● 分析ツールの多様化/進化に伴ってローデータ連携のビジネス機会が拡大

View Slide

社外データ提供の課題
● 公開事例が少なく、ベストプラクティスが定まっているとは言えない
● テクノロジー面では「n人のデータ配信者」と「m人のデータ受信者」が
柔軟にデータを連携する「データメッシュ」が参考になりそう…？
● マーケティング面ではデータ取引/流通の専門サイトが参考になりそう…？

View Slide

社外データ提供における技術要求
① 低コスト、少労力で検証したい
② 流出防止・権限管理を徹底したい
③ 変更容易性を担保したい
（より良いプラクティスが分かったらデータ構成や仕組みを切り替えたい）
という 3 つの要求を満たせる仕組みが必要

View Slide

① 低コスト、少労力で検証したい　→　Analytics Hub で実現可能
② 流出防止・権限管理を徹底したい

View Slide

社外データ提供のプラットフォーム比較
現在のシステム構成や既存取引先へのデータ連携の観点で比較検討し
Google Cloud に完結すると最速でスモールスタートを実現できると判断
NE（データ提供）観点データ利用者観点
Google Cloud
(例：Analytics Hub, GCS)
◯
・データ基盤が BigQuery なのですぐに使える
◯
・Google アカウント利用中ならすぐに使える
Amazon Web Service
(例：S3)
◯
・プロダクトが AWS なのですぐに使える
△
・AWS アカウントを作ってもらう必要がある
・プロダクト間の自動連携には有力な選択肢
Microsoft 365 / Azure
(例：SharePoint)
△
・新規にデータ連携の仕組みが必要
◯
・Microsoft アカウント利用中ならすぐに使える
Snowflake Marketplace
△
・新規に契約が必要
・新規にデータ連携の仕組みが必要
△
・Snowflake アカウントを作ってもらう必要がある
・NE ユーザー企業の Snowflake 導入が増えたり、
　海外企業とやり取りするときには有力な選択肢
その他、
データ販売会社を経由した提供
△
・新規に契約が必要
△
・データ提供先を広げるときには有力な選択肢
・技術面の選択肢が柔軟（ロックイン回避が可能）

View Slide

Google Cloud による社外データ提供の選択肢
● 新しい仕組みや機能を導入せずに、1 人の相手に暫定対応するだけなら
GCS > データセット分離 & IAM > Analytics Hub > BI ツール経由での提供
● スケーラブルな仕組みにする、かつ相手が Google Cloud を利用中の場合
Analytics Hub > データセット分離 & IAM > GCS > BI ツール経由での提供
→ 試してみて簡単に使えたので Analytics Hub を採用！

View Slide

社外データ提供の選択肢（詳細）
風音屋アドバイザー陣とのディスカッション内容

View Slide

② 流出防止・権限管理を徹底したい　→　Analytics Hub & IAM で実現可能

View Slide

データ受け渡しのシステム構成
● 提供者はデータマートで渡して、利用者はデータレイクで受け取る
● 提供者：ユースケースの 1 つ（CS向けにリストを作るのと同じ）
● 利用者：データソースの 1 つ（気象庁から天候データを取得するのと同じ）
● データ管理の責務を分けて、各自で IAM による権限管理（既存運用と同じ）
ネクストエンジンの
Google Cloud プロジェクト
データ
マート
Hamee の
Google Cloud プロジェクト
データ
レイク
Analytics
Hub

View Slide

NE/Hamee間のデータ連携の考え方
①1つの基盤を共有し、グループ会社や部署で管理を分ける、と考える
● hameeのGoogleCloudプロジェクトでジョブを発行
● NEのGoogleCloudプロジェクトのデータマートで加工済みデータを提供
②別の会社が管理する別の基盤だと考える（こちらを採用）
● hameeのGoogleCloudプロジェクトでジョブを発行
● NEのGoogleCloudプロジェクトのデータマートで加工済みデータを提供

View Slide

③ 変更容易性を担保したい　→　スケーラブルなスクリプトを作成

View Slide

グループ会社へのデータ提供の流れ
相手
（Hamee）
自社
（NE）
提供希望のテーブル・カラム一覧を
スプレッドシートで渡す
Analytics Hub でデータを提供
SQL Generator でCSVファイルを読み取り
Hamee専用データマートを自動作成

View Slide

1. 受け取ったCSVを適切な場所に配置
2. 独自の設定ファイルを会社毎に設定
3. 実行
社外提供用SQL Generator

View Slide

3. 実行
public_xxxxxxxx_hamee.csv

View Slide

3. 実行
output_dir: 出力先ディレクトリ

View Slide

3. 実行
templates:
:
file: 入力となるテンプレートを指定
params: 入力するパラーメーター

View Slide

3. 実行
python main.py

View Slide

Hamee 専用テーブル作成 SQL自
動生成

View Slide

グループ会社へのデータ提供の流れ
相手
（Hamee）
自社
（NE）
提供希望のテーブル・カラム一覧を
スプレッドシートで渡す
Analytics Hub でデータを提供
SQL Generator でCSVファイルを読み取り
Hamee専用データマートを自動作成

View Slide

View Slide

NEデータセットが利用可能に

View Slide

変更容易性へのアプローチ
【提供者視点】Analytics Hub やデータマートの自動生成スクリプトで
社外へのデータ提供の仕組みを爆速で実現できる
→ すぐに作り直せる、置き換えれば OK
【利用者視点】互換性担保については Web API のベストプラクティス
（例：v1, v2 といった命名でのバージョニング）を適用できる
→ 今回は省略、課題が顕在化したら v2 への切り替えを検討する

View Slide

③ 変更容易性を担保したい　→　スケーラブルなスクリプトを作成
という 3 つの要求を満たせる仕組みが必要　→　OK！

View Slide

社外データ提供の課題
● 公開事例が少なく、ベストプラクティスが定まっているとは言えない
● テクノロジー面では「n人のデータ配信者」と「m人のデータ受信者」が
柔軟にデータを連携する「データメッシュ」が参考になりそう…？
● マーケティング面ではデータ取引/流通の専門サイトが参考になりそう…？
（株）日本データ取引所の
延川裕樹様のご支援のもと
最適なアプローチを模索

View Slide

データ取引・流通の市場トレンド
データ提供の概況をリサーチして勝ち筋を模索
1. 認知 2. マッチング
✕
アンチパターン
○
成功事例
プラットフォームへの公開だけでは自然に利用されない
（仕掛けが必要）
【コンテンツマーケ/分析事例公開】
海外にはデータ販売のために
ホワイトペーパー作成を請け負う
データ分析会社が存在
【BizDev/業務提携】
国内だとセコム、シャープ、
KDDIの業務提携＆データ連携による
見守りサービスが代表的

View Slide

データ取引・流通へのアプローチ
● 認知
○ 大学の研究室と共同研究やインターンの座組みを擦り合わせ中
○ 最終的には分析事例として公開できるところを目指したい
→横山が今春より某国立大の研究員に就任してPJを開始
● マッチング
○ グループ会社（Hamee環境）にAnalyticsHub経由で提供済み
○ ネクストエンジン利用中のクライアントにも同様にデータ提供可能
→現在NEのコンサルタントチームがPJを推進中

View Slide

まとめ

View Slide

● スモールチームで、300万テーブル規模のデータ基盤を構築・運用し、
社内・社外にデータを提供しています。
● 「社内外へのデータ提供を検討したい」と言われたテックリードの方々が
スケーラブルな仕組みやデータ流通を実現するヒントになればと思います。
プラクティスの
開拓が必要
（横山パート）
スケーラブルな仕組みが必要（熱田パート）
SaaSの標準機能で済ませられる
このセッションの概要（再掲）
収集移行
モデリング・
加工
社外提供
社内提供
小規模
大規模
公開事例が
少ない領域
公開事例が
多い領域

View Slide

データ提供を支えるテクノロジー
● BigQuery, Analytics Hub, Cloud IAM による社内外へのデータ提供
● Connected Sheet, Looker Studio によるデータ分析の民主化
● Vertex AI / AutoML, BigQuery ML, ChatGPT による機械学習の
スモールスタート
● Cloud Run, Amazon Event Bridge, AWS Fargate, dbt による
スケーラブルなデータ収集・移行・加工・モデリング

View Slide

ご清聴ありがとうございました

View Slide

300万テーブルのデータ流通を支えるエンジニアリング #GoogleCloud #GoogleCloudDay / 20230523

300万テーブルのデータ流通を支えるエンジニアリング #GoogleCloud #GoogleCloudDay / 20230523

風音屋 (Kazaneya)
PRO

More Decks by 風音屋 (Kazaneya)

Other Decks in Technology

Featured

Transcript

300万テーブルのデータ流通を支えるエンジニアリング #GoogleCloud #GoogleCloudDay / 20230523

300万テーブルのデータ流通を支えるエンジニアリング #GoogleCloud #GoogleCloudDay / 20230523

風音屋 (Kazaneya) PRO

More Decks by 風音屋 (Kazaneya)

Other Decks in Technology

Featured

Transcript

風音屋 (Kazaneya)
PRO