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

20200125-DB-ETL

yuina
January 25, 2020

 20200125-DB-ETL

yuina

January 25, 2020
Tweet

More Decks by yuina

Other Decks in Technology

Transcript

  1. 第28回 中国地方DB勉強会 in 岡山
    マイクロサービスをBIで可視化する

    View Slide

  2. Copyright © PREVENT Inc. All rights reserved.
    自己紹介
    和田 菜月 (ゆいな) / @yuina1056
    株式会社 PREVENT
    バックエンドエンジニア兼なんでも屋さん
    Ruby on Rails / Go / PostgreSQL
    DB勉強会 in 愛知 (主催)
    しがないラジオ sp.61a,b (ゲスト出演)
    #成し遂げたいam ep15,16 (ゲスト出演)

    View Slide

  3. Copyright © PREVENT Inc. All rights reserved.
    自己紹介
    和田 菜月 (ゆいな) / @yuina1056
    株式会社 PREVENT
    バックエンドエンジニア兼なんでも屋さん
    Ruby on Rails / Go / PostgreSQL
    DB勉強会 in 愛知 (主催)
    しがないラジオ sp.61a,b (ゲスト出演)
    #成し遂げたいam ep15,16 (ゲスト出演)

    View Slide

  4. Copyright © PREVENT Inc. All rights reserved.
    自己紹介

    View Slide

  5. Copyright © PREVENT Inc. All rights reserved.
    自己紹介
    和田 菜月 (ゆいな) / @yuina1056
    株式会社 PREVENT
    バックエンドエンジニア兼なんでも屋さん
    Ruby on Rails / Go / PostgreSQL
    DB勉強会 in 愛知 (主催)
    しがないラジオ sp.61a,b (ゲスト出演)
    #成し遂げたいam ep15,16 (ゲスト出演)

    View Slide

  6. Copyright © PREVENT Inc. All rights reserved.
    自己紹介
    和田 菜月 (ゆいな) / @yuina1056
    株式会社 PREVENT
    バックエンドエンジニア兼なんでも屋さん
    Ruby on Rails / Go / PostgreSQL
    DB勉強会 in 愛知 (主催)
    しがないラジオ sp.61a,b (ゲスト出演)
    #成し遂げたいam ep15,16 (ゲスト出演)
    ダムはいいぞ!!!

    View Slide

  7. Copyright © PREVENT Inc. All rights reserved.
    自己紹介
    丸山ダム(岐阜)

    View Slide

  8. Copyright © PREVENT Inc. All rights reserved.
    ある日のこと…

    View Slide

  9. Copyright © PREVENT Inc. All rights reserved.
    ある日のこと…
    社内に存在する情報を分析して
    経営戦略を立てたい。

    View Slide

  10. Copyright © PREVENT Inc. All rights reserved.
    ある日のこと…
    社内に存在する情報を分析して
    経営戦略を立てたい。
    それならBIツール作りましょう!!!

    View Slide

  11. Copyright © PREVENT Inc. All rights reserved.
    システム構成を考える

    View Slide

  12. Copyright © PREVENT Inc. All rights reserved.
    システム構成を考える
    データソースとして
    PostgreSQL繋げられるじゃん!!!
    Google Data Studioいいじゃん!!!

    View Slide

  13. Copyright © PREVENT Inc. All rights reserved.
    システム構成予定
    顧客管理システム Mystar-API
    その他システム
    BI用DB
    GoogleDataStudio
    1PTUHSF42-

    View Slide

  14. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    最初に思いついたこと・・・

    View Slide

  15. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    最初に思いついたこと・・・
    FDWでBI用のDBに繋げばいいじゃん!!!!!

    View Slide

  16. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    しかし問題が・・・

    View Slide

  17. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    しかし問題が・・・
    • 顧客管理システム、Mystar-APIには

    個人情報、要配慮個人情報が含まれている。

    →BIツールはGoogleDataStudioを使用するため

    外部ツールに個人情報、要配慮個人情報を渡したくない。
    • そのままFDWしてしまった場合、DBのリレーションを知っていないと

    誰にでも分かるようには可視化できない

    →該当システム開発者orDB管理者への依存が強い
    • 今後、データサイエンスチームへ解析データセットとして渡したい

    →使用しているテーブルを非正規化した形で持たせておきたい

    View Slide

  18. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    どうしたか?

    View Slide

  19. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    どうしたか?
    ETL処理基盤の実装

    View Slide

  20. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    各システムへのDBアクセスは全てETL基盤から実施
    顧客管理システム Mystar-API
    その他システム
    ETL基盤
    BI用DB

    View Slide

  21. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    各システムへのDBアクセスは全てETL基盤から実施
    顧客管理システム
    PostgreSQL
    Mystar-API
    mongoDB
    その他システム
    PostgreSQL
    ETL基盤
    BI用DB
    #*༻%#ͱͷ
    %#-*/,
    ௨ৗ઀ଓ

    View Slide

  22. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    RDBをDBLINKで接続した理由

    View Slide

  23. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    RDBをDBLINKで接続した理由
    • Golangで実装したフルスクラッチのETL基盤

    →使用ライブラリの都合上DBのコネクションを複数保持できない
    • ETL処理でデータを取得するタイミング以外は他システムと

    接続している必要はない

    View Slide

  24. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    ETL基盤で行っていること

    View Slide

  25. Copyright © PREVENT Inc. All rights reserved.
    各システムをBI用DBに繋ぐ
    ETL基盤で行っていること
    • 個人情報に当たる箇所の削除

    →該当するカラムはDBからデータを取得するタイミングで対象から外す
    • JOINによるテーブルの非正規化

    →BIで表示するために必要なデータを元のDBベースでJOIN

    場合によってはBI用DBのテーブルにJOINして別テーブルへ
    • DB内データのクレンジング

    →stringで保持している日付データを本来のDate型、Datetime型へ

    statusの情報を日本語に変換 etc…

    View Slide

  26. Copyright © PREVENT Inc. All rights reserved.
    課題
    • データ更新をかけるタイミングでupsertがうまく動かない

    →元システムで入力内容を消去する等null更新をした時にupsertが

    うまく動作しない…
    • 仕組み上、BIのリアルタイム更新が行えない。

    →FDWでやればリアルタイム更新はできるが、個人情報どうする?

    View Slide

  27. Copyright © PREVENT Inc. All rights reserved.
    まとめ
    • ETL基盤自体をスクラッチで実装することはめったに無い

    →とても良い経験になった
    • もっと良い方法がありそうな気がする

    →知見がある方と情報交換したい

    View Slide

  28. Copyright © PREVENT Inc. All rights reserved.
    おわり

    View Slide