GCP で構築する これからの変化に対応出来る データ分析基盤の作り方 株式会社リクルートテクノロジーズ ITI本部 DP部　山田 雄、白鳥 昇治、佐伯 嘉康 2020/03/31 Google Cloud Data Platform Day #2

山田　雄(Yamada Yu) @nii_yan データエンジニア (データ基盤の開発・運用) AWS/GCP/BigData/Mail/Beer/Yakisoba... Photo Speaker

データ分析基盤の　　 　デザインパターン

80%

80% 基盤エンジニアが運用に割いている割合

なぜ運用にそれほど時間がかかってしまうのか？

Scalability

90% を意識した基盤設計が必要

Microservices

データ ソース データ取 得 データ加 工 データ保 存 データ分 析 BI 施策 個別エンハンスが出来るように、それぞれの過程を分けて 作っておく。 データ保管場所だけ変える、データ取得方式だけ変えるなど が出来るように。

データ ソース データ取 得 データ加 工 データ保 存 データ分 析 BI 施策 マネージドでスケーラブル なサービスを疎結合させる Cloud Dataflow Cloud Dataprep Cloud Data Fusion BigQuery Data Transfer Cloud Storage BigQuery Cloud AutoML

分析ユーザー主体

データ ソース データ取 得 データ加 工 データ保 存 データ分 析 BI 施策 データ取得移行の過程を基盤エンジニアでなく、分析ユーザ が出来るような基盤を作成する。 新規データソースの追加や、新規クエリの定期実行登録な ど、エンジニアを介さずユーザが出来るように

ちょっと番外編

今日のデータ連携遅くない？ DWH に繋がらないんだけど!!! なんで、そんな事も出来ないの？？ 基盤にそんなコストかけてROI は大丈夫？？

基盤は一度出来ると使えるのが当たり前に なり、エンジニアは責められる事があっても 褒められる事はなくなる

データ活用をすすめていく上で、 基盤エンジニアの モチベーション コントロール はとても重要です！

リクルートのETL における課題 ● SPOF・スケール限界 ● ETL シェルの運用工数増大 ● インフラコスト増 ● 同じ様なETL の乱立

リプレースを決意

白鳥 昇治 (Shirotori Shoji) @irotoris データエンジニア (開発・運用) 　 　→IBM Netezza 　　→Oracle Exadata 　　→Amazon Redshift 　　→TreasureData 　　　→Google BigQuery Photo Speaker

概要 Cloud Pub/Sub Cloud BigQuery データベース ストレージ 機械学習基盤 分析者 Other SaaS / API 事業システム raw data meta data

リクルートのETL 要件 環境 ● On-Premises / AWS / GCP データソース ● Oracle / MySQL / PostgreSQL / S3 / GCS / Salesforce / Kintone / Adobe Analytics / etc. ETL ジョブ ● 並列度制御 　= DB 同時接続数を制御したい ● 優先度管理 　= データ鮮度を保ちたいジョブがある ● 差分更新連携 = ETL 時間を短くしたい

Job Queue Cloud Pub/Sub アーキテクチャ DB Cloud SQL API / App Kubernetes Engine Dedicated Interconnect Data Lake Cloud Storage BigQuery ETL Scheduler CronJob Configure Dispatcher Pod Create Job Cloud Pub/Sub Job State / Metadata Workflow Job Job Queue Cloud Pub/Sub Workflow JobWorkflow Job Cloud Load Balancing Cloud VPN Cloud Armor Cloud NAT Notify Job Queue Cloud Pub/Sub ETL Kubernetes Engine

Job Queue Cloud Pub/Sub アーキテクチャ DB Cloud SQL API / App Kubernetes Engine Dedicated Interconnect Data Lake Cloud Storage BigQuery ETL Scheduler CronJob Configure Dispatcher Pod Create Job Cloud Pub/Sub Job State / Metadata Workflow Job Job Queue Cloud Pub/Sub Workflow JobWorkflow Job Cloud Load Balancing Cloud VPN Cloud Armor Cloud NAT Notify Job Queue Cloud Pub/Sub ETL Kubernetes Engine どういう思想で作ったか話します！ ● ETL ジョブ on Google Kubernetes Engine (GKE) ● データ更新方式 ● ETL ジョブ実行管理 ● ネットワークの話 ● API / WebApp

Job Queue Cloud Pub/Sub ETL on GKE DB Cloud SQL API / App Kubernetes Engine Dedicated Interconnect Data Lake Cloud Storage BigQuery ETL Scheduler CronJob Configure Dispatcher Pod Create Job Cloud Pub/Sub Job State / Metadata Job Queue Cloud Pub/Sub Cloud Load Balancing Cloud VPN Cloud Armor Cloud NAT Notify Job Queue Cloud Pub/Sub Workflow JobWorkflow JobWorkflow Job

ETL on GKE ● ETL 処理をコンテナでパーツ化・着脱しやすく ● ワークフロー管理ジョブ（独自実装）でコンテナ実行を制御 ● オーケストレーションとリトライをKubernetes に任せる ● Google Cloud Storage をハブにしてinput / output の処理を分割 Workflow Job parse config Job embulk Job bq load Job BigQuery Cloud Storage notify Job Cloud Pub/Sub csv メタデータとともに ジョブ成功・失敗を通知 create_namespace_job()

ETL = embulk + cloudsdk ● DB/File→GCS へのInput 処理はembulk を採用 ○ 多種多様なデータソースに対応するinput プラグイン ○ Filter やgsub でクレンジング処理 / 型変換を記述 ● GCS → BigQuery にはcloudsdk(bq load) を採用 ○ 扱いが簡単、ハマりにくい Service B BigQuery Cloud Storage csv filter/gsub Service A BigQuery Service C BigQuery bq load bq query 型変換とクレンジング処 理が統一される Service A Service B Service C

データ更新方式 4つ 1. 全レコードを洗い替え 2. 全レコードを連携日付のパーティションとして積上げ 3. 更新日付キー + 主キーによる差分更新 4. 日付パーティションによる差分更新

データ更新方式 4つ 全レコードを洗い替え テーブル CREATE OR REPLACE

データ更新方式 4つ 全レコードを連携日付のパーティションとして積上げ → 更新のあるテーブルの日付断面で履歴を検索したい場合 2020-01-01 2019-12-31 2019-12-30 ... 取り込み時間パーティション テーブル _PARTITIONTIME=2020-01-01を WRITE_TRUNCATE

データ更新方式 4つ 更新日付キー + 主キーによる差分更新 → 予約、在庫などのトランザクション テーブル ①差分 レコード テーブル ②更新前 テーブル 更新後 テーブル PK/更新日付キー による差分抽出 PK/更新日付キーによる Upsert 差分 レコード

データ更新方式 4つ 日付パーティションによる差分更新 → レコード更新のないログテーブルなどで利用 2020-01-01 2019-12-31 2019-12-30 ... 2020-01-01 パーティション抽出 取り込み時間パーティション テーブル _PARTITIONTIME=2020-01-01を WRITE_TRUNCATE 2020-01-01

ETL ジョブ実行管理 Dedicated Interconnect Data Lake Cloud Storage BigQuery Cloud Pub/Sub Cloud Load Balancing Cloud VPN Cloud Armor Cloud NAT Notify Job Queue Cloud Pub/Sub DB Cloud SQL API / App Kubernetes Engine ETL Scheduler CronJob Configure Dispatcher Pod Create Job Job State / Metadata Workflow Job Job Queue Cloud Pub/Sub Workflow JobWorkflow Job Job Queue Cloud Pub/Sub

Job Queue Cloud Pub/Sub ETL ジョブ実行管理 DB Cloud SQL API / App Kubernetes Engine ETL Scheduler CronJob Configure Dispatcher Pod Create Job Job State / Metadata Workflow Job Job Queue Cloud Pub/Sub Workflow JobWorkflow Job Job Queue Cloud Pub/Sub 1. ユーザーによって設定されたETL ジョブはk8s Cronjob に変換される 2. ジョブキューとしてPubSub を優先度別に用意 3. ディスパッチャーPod が優先度順にジョブキューとジョブステータスをみて、 並列数が設定より多くなければk8s Job を起動 4. ジョブステータスはDB 管理。ディスパッチャーやk8s Job から書き込まれる 1 2 3 4 ETL 要件のおさらい ・並列度制御：DB 同時接続数を制御したい ・優先度管理：データ鮮度を保ちたいジョブがある

ネットワークの話 Cloud Load Balancing Cloud Armor Notify Job Queue Cloud Pub/Sub DB Cloud SQL API / App Kubernetes Engine ETL Scheduler CronJob Configure Dispatcher Pod Create Job Job State / Metadata Job Queue Cloud Pub/Sub Job Queue Cloud Pub/Sub Dedicated Interconnect Data Lake Cloud Storage BigQuery Cloud Pub/Sub Cloud VPN Cloud NAT Workflow JobWorkflow JobWorkflow Job

ネットワークの話 ● 専用線とCloud VPN はネットワークが密結合になってしまうため、プライベート IP の被りやIP 枯 渇が問題になる。 ● ネットワーク密結合な VPC をジョブ実行のVPC と分割し、内部NAT サーバーをGoogle Compute Engine で構築。これでGKE のノードはプライベート IPを気にしなくてよい。 ● しかし接続先のVPCが追加されるたびに NATサーバーのIP変換を管理しないといけない Dedicated Interconnect Data Lake Cloud Storage BigQuery Cloud Pub/Sub Workflow Job Cloud VPN Cloud NAT VPC for Compute VPC for Connection NAT Server

Job Queue Cloud Pub/Sub API / WebApp Dedicated Interconnect Data Lake Cloud Storage BigQuery ETL Scheduler CronJob Configure Dispatcher Pod Create Job Cloud Pub/Sub Job State / Metadata Workflow Job Job Queue Cloud Pub/Sub Workflow JobWorkflow Job Cloud VPN Cloud NAT Notify Job Queue Cloud Pub/Sub DB Cloud SQL API / App Kubernetes Engine Cloud Load Balancing Cloud Armor

API / WebApp ETL 設定の抽象化 + API / WebUI でセルフサービス化 - ジョブの設定・実行・停止を利用者で実施できるように - 小難しい差分更新やembulk の設定をラップ - インターフェースを変えなければバックエンドは後から変更が可 能

API / WebApp DBA データ利用者 データエンジニア これ以上DB に負荷 かけないで！ すみません :bow: すみません :bow: 朝 HH:MM:SS までにデー タが必要なんです！ ETL + DWH データベース レポート

API / WebApp DBA データ利用者 これ以上DB に負荷 かけないで！ 朝 HH:MM:SS までにデー タが必要なんです！ ETL + DWH データベース レポート ETL Platform HTTP/2 400 Bad Request

Worker Job Datasource kind: DataSource/v1 groupId: 1 id: 57 name: mysql type: mysql params: hostname: mysql.xxx.xxx port: 3306 username: etl_user password: xxxxxxxxxxxxx kind: DataSource/v1 groupId: 1 id: 60 name: biqquery type: biqquery params: {} kind: Worker/v1 groupId: 1 id: 25 name: worker25 concurrency: 10 queues: 3 kind: job/v1 groupId: 1 id: 392 name: merge-table-load copyType: merge priority: 1 schedule: 0 0 * * * enabled: true dstParams: dataSourceId: 60 type: bigquery project: project dataset: dataset table: table srcParams: dataSourceId: 57 type: mysql database: dbName table: table options: mergeKeyColumns: - user_id - group_id updateDateColumn: updated_at workerId: 25 tags: - x2 # embulk-job.yaml in: xx out: xx --- # bq load command command : - bq - load - ... # Cronjob schedule: 0 0 * * * ... # Job resources: limit: cpu: xxx ... API Resources System Resources

ETL 基盤 まとめ ● ETL on Google Kubernetes Engine (GKE) ○ 処理を組み替えやすいようにコンテナと embulk で ● データ連携方式 ○ 4種類の方式でさまざまなデータ活用ユースケースに対応 ● ETL ジョブ実行管理 ○ サービスに負荷を与えないような安全な制御 ● ネットワークの話 ○ 専用線/VPN はプライベートIP を意識せざるを得ない ● API / WebApp ○ ETL 設定・管理のセルフサービス化

Speaker 佐伯　嘉康(Saeki Yoshiyasu) @laclefyoshi データエンジニア (データ基盤の開発・運用) Next’19 Tokyo にて発表:: hacci: リクルート各サービスログをリアルタイム処理・分析するた めのデータパイプライン

Garuda こぼれ話 ● コスト節約で頑張った話 ● ログが悩ましい話 ● BigQuery の仕様に振り回された話 ● 数値型で苦しめられている話

コストの話（今のところ） ● ほぼ GCE と GCS ○ GCE は GKE 所属のノード群 ■ ETL ジョブ数で大きく変動 ○ GCS は収集した生データ ● その他使用している PubSub などはこ の 2 つに比べて小さい ● BigQuery は Garuda 外のためなし

（コストに大きな影響を与えた）監視 ログ = アプリケーションの標準出力・標準エラー経由 メトリクス = kube-state-metrics からの情報 ETL クラスタ Web クラスタ ログ + メトリクス ログ + メトリクス

SD カスタム メトリクス増大 Stackdriver 標準の GKE Monitoring では Job 監視観点 をカバーできなかったため kube-state-metrics + prometheus-to-sd でメトリクスを拡充した

SD カスタム メトリクス量の制限に挑戦 ● コストの読みが不十分だった ○ Pricing Calculator はちゃんと使いましょう ● 急いでメトリクス対象を Job に絞る ○ kube-state-metrics には WL/BL がある ● それでも減りづらい ○ 日に日に Job は増え続けるから ● そしてカスタム メトリクスを使うのをやめた

（現在の）監視 ETLクラスタ Web クラスタ ログ ログ メトリクス メトリクス ログ = アプリケーションの標準出力・標準エラー経由 メトリクス = kube-state-metrics からの情報

ストレージ コストも無視できない ● GCS コストを下げるためにも色々頑張った ○ Embulk の出力は CSV ■ JSON → 出力サイズが大きすぎる ■ AVRO → Datetime 型未対応 ● さらに GKE のストレージ（ノード/Persistent Volume）の節約も ○ Embulk の出力は「直」GCS ○ embulk-output-command で gsutil cp - gs://bucket/file.csv ■ チェックサム計算なし（とりあえず問題は起こってない）

ログの話 ● 実装コンポーネント多数 ● その結果ログ書式がバラバラ 致命的ではない！ 後回し！

BigQuery ロード仕様の話 https://cloud.google.com/bigquery/quotas

CSV ロードの制限と Garuda での対応 ● bq load に --allow_quoted_newlines オプションがある場合、 非圧縮形式でも 4GB がファイルサイズ上限となる可能性がある ○ ドキュメントに書いてない（サポート問い合わせで発覚） ● Embulk ではサイズによるファイル分割出力はできない ○ 出力スレッド数の調整で分割はできるが ○ embulk-output-command で gsutil の前に 1 つプログラムを挟ん だ ■ 4GB を超える前 & CSV レコードがちゃんと終了した時に gsutil プロセスを閉じて、新たに作る

CSV ロードの制限と Garuda での対応 ● load に --allow_quoted_newlines オプションがある場合、 非圧縮形式でも 4GB がファイルサイズ上限となる ○ ドキュメントに書いてない ● Embulk ではサイズによるファイル分割出力はできない ○ 出力スレッド数の調整で分割はできるが ○ embulk-output-command で gsutil の前に 1 つプログラムを挟ん だ ■ 4GB を超える前 & CSV レコードがちゃんと終了した時に gsutil プロセスを閉じて、新たに作る 標準 出力 CSV プログラム gsutil cp - gs://bucket/file-00.csv にパイプ出力 条件1. CSV レコードが完結していること（セル内改行対応） 条件2. トータル 4GB を超えていないこと 条件2.1. 4GB を超える前に gsutil プロセスを閉じ、再作成 gsutil cp - gs://bucket/file-01.csv にパイプ出力

数値型はこわい 制限1. Oracle の NUMBER はでかい（最大38桁） https://docs.oracle.com/cd/E18283_01/server.112/e17110/limits001.htm 制限2. BigQuery の NUMERIC カラムは Tableau から参照できない https://kb.tableau.com/articles/issue/numeric-type-bigquery-fields-missing-in-tableau-desktop 制限3. Java（Embulk）のLong.MAX_VALUE はBigQuery のINT64相当 https://docs.oracle.com/javase/10/docs/api/java/lang/Long.html#MAX_VALUE どうしろと（STRING?）

● コスト節約で頑張った話 ○ 見積もりは正しく行いましょう ● ログが悩ましい話 ○ 見た目の問題なだけあって、解決のための時間を取るタイ ミングが難しい ● BigQuery の仕様に振り回された話 ○ サポートに聞くのが一番 ● 数値型で苦しめられている話 ○ ほんとどうしろと こぼれ話まとめ

この基盤で実現出来たこと ● マネージドなサービス・OSS を使い、開発工数削減 ● 必要なリソースを必要な分だけを追求し、圧倒的なコストダウン ● API/WebUI によるセルフ化で運用工数削減 ● Data Platform Day の登壇 まとめ

募集 We are hiring!

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