20200125-DB-ETL

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