Upgrade to Pro — share decks privately, control downloads, hide ads and more …

trocco第5回ユーザー会_troccoとAmazon Redshiftで挑んだコンテンツマーケティングの分析基盤構築

CUEBiC Inc.
September 05, 2023
420

trocco第5回ユーザー会_troccoとAmazon Redshiftで挑んだコンテンツマーケティングの分析基盤構築

CUEBiC Inc.

September 05, 2023
Tweet

More Decks by CUEBiC Inc.

Transcript

  1. 株式会社キュービックとは? 2 株式会社キュービック /CUEBiC Inc. 社名 事業 設立 資本金 拠点

    2006 年 10 月 24 日 31,000,000円 人員 約 300 名(単体)※インターンを含む 約 484 名(連結) ※2022年3月現在 デジタルメディア事業、集客支援事業 ほか 東京、福岡
  2. 自己紹介 5 株式会社キュービック Tech Lead/データエンジニア 尾﨑 勇太(おざき ゆうた) 覚え方:尾崎豊(おざきゆたか)と一字違い 1990年和歌山県白浜町生まれ

    生息地:千葉県松戸市 スキルセット 1. マネジメント/品質管理/データ分析 2. マイナスからゼロ、ゼロイチ 3. サーバーサイド(WEB/アプリ開発) @waichang111 経歴の詳細 はてなブログ
  3. 既存のアーキテクチャ 14 広告/ASP アーキテクチャ CUEBiC Analytics RDS Tableau データ抽出/整形/蓄積 データ分析

    データ出力 独自の社内集計基盤システムCUEBiC Analyticsに広告と成果のレポートを取り込み 集計結果を保存し、Tableau上で事業部のメディア担当者が分析できる状態に出力
  4. 既存のアーキテクチャのそれぞれの役割 15 広告/成果データの ・データ設定 ・データインポート ・データ集計 ・データエクスポート データの保持 ・集計設定データ ・広告/成果の生データ

    ・広告/成果の集計データ データの加工 ・広告データ ・データの加工 データ分析 ・集計データの加工 ・ビジュアライズ CUEBiC Analytics RDS Tableau
  5. 既存のアーキテクチャ 16 広告/ASP アーキテクチャ CUEBiC Analytics RDS Tableau データ抽出/整形/蓄積 データ分析

    データ出力 事業成長に伴い、広告/成果の説明変数が増え、集計方法も複雑化 CUEBiC Analyticsの老朽化し、事業の要求に耐えられなくなってきた・・・・ ・集計がずれている ・出力が遅い ・参照されない 個別実装が増え スプシの魔改造化・・・
  6. 既存のアーキテクチャ 17 広告/ASP アーキテクチャ CUEBiC Analytics RDS Tableau データ抽出/整形/蓄積 データ分析

    データ出力 DX戦略としてCUEBiC Analyticsを解体し、ローコード化により業務効率化を測るとともに 広告/成果以外のデータの分析軸も取りうる基盤へのリプレイスを実施
  7. リニューアル後のデータ分析環境でのそれぞれの役割 19 CUEBiC Analytics 広告データ/成果データ ・データ抽出 ・データ転送 ・データ整形 データの蓄積 データの加工

    データの集計 データ分析 ・集計データの加工 ・ビジュアライズ 集計設定 Tableauのデータを連携 ・集計設定データ保存 ・その他マスタ保存 Oasis Tableau
  8. 実現したアーキテクチャ 20 広告/ASP アーキテクチャ データ抽出 集計設定 集計ロジック データ蓄積 データ分析 Oasis

    Tableau Tableau troccoをフロントとして配置してDWHとしてAmazon Redshiftを選定。 troccoの転送設定とデータマート機能により大幅に処理の簡素化に成功
  9. 実現したアーキテクチャ 21 広告/ASP アーキテクチャ データ抽出 集計設定 集計ロジック データ蓄積 データ分析 Oasis

    Tableau Tableau troccoの強みを活かしてデータマート機能でAmazon Redshiftの ストアドプロシージャーを呼び出し、集計処理をSQLで完結させた
  10. 直近の切実な悩み 27 広告/ASP アーキテクチャ データ抽出 集計設定 集計ロジック データ蓄積 データ分析 Oasis

    Tableau Tableau troccoの弱みとして転送データが引き継げないことにより一時的なデータ設定の間口として Oasisを用意し、Redshiftのストアドプロシージャによる集計処理との分離が必要となった
  11. 直近の切実な悩み 28 アーキテクチャ データ抽出 集計設定 集計ロジック データ蓄積 Oasis Oasisからtrocco APIでの転送設定を起動とACTIVE

    RECORDからRedshiftのストアドプロシー ジャの呼び出しが発生しており、Rails側に責務がじわじわ寄っている trocco API ストアドプロ シージャ
  12. Troccoへの期待 34 広告/ASP アーキテクチャ データ抽出 集計設定 集計ロジック データ蓄積 データ分析 Oasis

    Tableau Tableau troccoのデータマート機能で設定したシンクジョブのAPI化やワークフローAPIのカスタム変数 の追加などにより、更なるローコード化が進む見込み
  13. Redshiftの構成 Redshift クライアントから SQL クエリを受け付けコンパイルし、 コンピュートノードに配信 コンピュートノードは高速ローカル SSD ・キャッシュを 利用しており、該当データがキャッシュに存在しない場合は

    マネージドストレージからブロック単位でデータ読み取りを実行 複数のコンピュートノードでこの処理を並列実行するため、 大量データを高速に処理することが可能 Amazon Redshift Serverless の概要 今までのAmazon Redshift(RA3 インスタンス)より引用
  14. Federated Queryとは? いわゆる、RedshiftからPostgreSQLに対してデータベースリンクする機能(RDS,Aurora) 以下のような外部スキーマを定義することで、 接続が可能になります CREATE EXTERNAL SCHEMA apg FROM

    POSTGRES DATABASE 'database-1' SCHEMA 'myschema' URI 'endpoint to aurora hostname' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-Secre tsManager-RO' SECRET_ARN 'arn:aws:secretsmanager:us-west-2:12345678901 2:secret:federation/test/dataplane-apg-creds-Yb VKQw'; ※事前に適切なセキュリティグループ/ロールの設定が必要です 45 1.Federated Queryへの活用

  15. 検証結果:サマリー サービス 広告 成果 比較項目 Redshift Redshift Serverless Redshift Redshift

    Serverless 収集 24秒 2分25秒 39秒 3分49秒 集計 15秒 43秒 2分7秒 6分9秒 同一条件下ではRedshiftの方が優位な結果となった 要因 立ち上げ時のデータベース接続のオーバーヘッド 60 3.パフォーマンス比較
  16. Q3.Example
 73 アーキテクチャ Tableau データ収集 データ分析 BigQueryに蓄積されたGA4データをtroccoのデータマート機能を使用して整形したデータを Amazon Auroraに退避し、TableauからカスタムSQLで参照する データ整形

    Auroraをデータソースと して参照し、カスタム SQLでデータを取得 データマート機能でBigQueryのGA4蓄 積データを整形して、BigQueryに整形 後のデータをパーティションで作成 BigQueryデータを Amazon Auroraに転送 troccoワークフロー