20200125-DB-ETL
by
yuina
Link
Embed
Share
Beginning
This slide
Copy link URL
Copy link URL
Copy iframe embed code
Copy iframe embed code
Copy javascript embed code
Copy javascript embed code
Share
Tweet
Share
Tweet
Slide 1
Slide 1 text
第28回 中国地方DB勉強会 in 岡山 マイクロサービスをBIで可視化する
Slide 2
Slide 2 text
Copyright © PREVENT Inc. All rights reserved. 自己紹介 和田 菜月 (ゆいな) / @yuina1056 株式会社 PREVENT バックエンドエンジニア兼なんでも屋さん Ruby on Rails / Go / PostgreSQL DB勉強会 in 愛知 (主催) しがないラジオ sp.61a,b (ゲスト出演) #成し遂げたいam ep15,16 (ゲスト出演)
Slide 3
Slide 3 text
Copyright © PREVENT Inc. All rights reserved. 自己紹介 和田 菜月 (ゆいな) / @yuina1056 株式会社 PREVENT バックエンドエンジニア兼なんでも屋さん Ruby on Rails / Go / PostgreSQL DB勉強会 in 愛知 (主催) しがないラジオ sp.61a,b (ゲスト出演) #成し遂げたいam ep15,16 (ゲスト出演)
Slide 4
Slide 4 text
Copyright © PREVENT Inc. All rights reserved. 自己紹介
Slide 5
Slide 5 text
Copyright © PREVENT Inc. All rights reserved. 自己紹介 和田 菜月 (ゆいな) / @yuina1056 株式会社 PREVENT バックエンドエンジニア兼なんでも屋さん Ruby on Rails / Go / PostgreSQL DB勉強会 in 愛知 (主催) しがないラジオ sp.61a,b (ゲスト出演) #成し遂げたいam ep15,16 (ゲスト出演)
Slide 6
Slide 6 text
Copyright © PREVENT Inc. All rights reserved. 自己紹介 和田 菜月 (ゆいな) / @yuina1056 株式会社 PREVENT バックエンドエンジニア兼なんでも屋さん Ruby on Rails / Go / PostgreSQL DB勉強会 in 愛知 (主催) しがないラジオ sp.61a,b (ゲスト出演) #成し遂げたいam ep15,16 (ゲスト出演) ダムはいいぞ!!!
Slide 7
Slide 7 text
Copyright © PREVENT Inc. All rights reserved. 自己紹介 丸山ダム(岐阜)
Slide 8
Slide 8 text
Copyright © PREVENT Inc. All rights reserved. ある日のこと…
Slide 9
Slide 9 text
Copyright © PREVENT Inc. All rights reserved. ある日のこと… 社内に存在する情報を分析して 経営戦略を立てたい。
Slide 10
Slide 10 text
Copyright © PREVENT Inc. All rights reserved. ある日のこと… 社内に存在する情報を分析して 経営戦略を立てたい。 それならBIツール作りましょう!!!
Slide 11
Slide 11 text
Copyright © PREVENT Inc. All rights reserved. システム構成を考える
Slide 12
Slide 12 text
Copyright © PREVENT Inc. All rights reserved. システム構成を考える データソースとして PostgreSQL繋げられるじゃん!!! Google Data Studioいいじゃん!!!
Slide 13
Slide 13 text
Copyright © PREVENT Inc. All rights reserved. システム構成予定 顧客管理システム Mystar-API その他システム BI用DB GoogleDataStudio 1PTUHSF42-
Slide 14
Slide 14 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ 最初に思いついたこと・・・
Slide 15
Slide 15 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ 最初に思いついたこと・・・ FDWでBI用のDBに繋げばいいじゃん!!!!!
Slide 16
Slide 16 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ しかし問題が・・・
Slide 17
Slide 17 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ しかし問題が・・・ • 顧客管理システム、Mystar-APIには 個人情報、要配慮個人情報が含まれている。 →BIツールはGoogleDataStudioを使用するため 外部ツールに個人情報、要配慮個人情報を渡したくない。 • そのままFDWしてしまった場合、DBのリレーションを知っていないと 誰にでも分かるようには可視化できない →該当システム開発者orDB管理者への依存が強い • 今後、データサイエンスチームへ解析データセットとして渡したい →使用しているテーブルを非正規化した形で持たせておきたい
Slide 18
Slide 18 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ どうしたか?
Slide 19
Slide 19 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ どうしたか? ETL処理基盤の実装
Slide 20
Slide 20 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ 各システムへのDBアクセスは全てETL基盤から実施 顧客管理システム Mystar-API その他システム ETL基盤 BI用DB
Slide 21
Slide 21 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ 各システムへのDBアクセスは全てETL基盤から実施 顧客管理システム PostgreSQL Mystar-API mongoDB その他システム PostgreSQL ETL基盤 BI用DB #*༻%#ͱͷ %#-*/, ௨ৗଓ
Slide 22
Slide 22 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ RDBをDBLINKで接続した理由
Slide 23
Slide 23 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ RDBをDBLINKで接続した理由 • Golangで実装したフルスクラッチのETL基盤 →使用ライブラリの都合上DBのコネクションを複数保持できない • ETL処理でデータを取得するタイミング以外は他システムと 接続している必要はない
Slide 24
Slide 24 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ ETL基盤で行っていること
Slide 25
Slide 25 text
Copyright © PREVENT Inc. All rights reserved. 各システムをBI用DBに繋ぐ ETL基盤で行っていること • 個人情報に当たる箇所の削除 →該当するカラムはDBからデータを取得するタイミングで対象から外す • JOINによるテーブルの非正規化 →BIで表示するために必要なデータを元のDBベースでJOIN 場合によってはBI用DBのテーブルにJOINして別テーブルへ • DB内データのクレンジング →stringで保持している日付データを本来のDate型、Datetime型へ statusの情報を日本語に変換 etc…
Slide 26
Slide 26 text
Copyright © PREVENT Inc. All rights reserved. 課題 • データ更新をかけるタイミングでupsertがうまく動かない →元システムで入力内容を消去する等null更新をした時にupsertが うまく動作しない… • 仕組み上、BIのリアルタイム更新が行えない。 →FDWでやればリアルタイム更新はできるが、個人情報どうする?
Slide 27
Slide 27 text
Copyright © PREVENT Inc. All rights reserved. まとめ • ETL基盤自体をスクラッチで実装することはめったに無い →とても良い経験になった • もっと良い方法がありそうな気がする →知見がある方と情報交換したい
Slide 28
Slide 28 text
Copyright © PREVENT Inc. All rights reserved. おわり