1 RDB x DMS x QuickSightで 基幹DBのデータを可視化した話 - がんばれ QuickSight - 2021/03/01 BigData-JAWS 勉強会#16 JAWS-UG 千葉⽀部 ⼭⼝正徳

2 名前：⼭⼝ 正徳 JAWS-UG千葉、Fin-JAWS コアメンバー JAWS DAYS 2021 実⾏委員⻑ 所属：フォージビジョン株式会社 2020 Japan APN Ambassador ⾃⼰紹介 好きなAWSサービス：ECS Fargate, Lambda 仕事：AWS事業管理、コンテナに関わる案件推進

3 https://aws.amazon.com/jp/builders-flash/202103/jawsdays-yamaguchi-interview/

4 1. 背景とアーキテクチャ 2. 直⾯した課題と解決⽅法 3. ここができるともっと良くなる、QuickSight︕ お話しすること

5 1.背景の説明

③将来的には、データレイク化の構想 ②BIツールアクセス先であるDB負荷の軽減 （DB⾃体のリプレイスは⾏わない） ①BIツールの刷新 １. 背景 ⇒ ユーザ端末から直接DB参照しているため スケールしない。 ⇒ BI⽤のデータリポジトリを作成 ⇒ データをS3へ蓄積し、今後他データ蓄積も視野 6 ユーザ端末内の BIツール ① ②、③ DB ミドルウェア

１. アーキテクチャ 7 ｚ Glueジョブ リアルタイムで DBデータ差分抽出 S3 （⽣データ⽤） QuickSight DMS S3 （分析データ⽤） DBデータ差分 をCSV出⼒ Athena SQLクエリ 分析データを 更新 差分CSVの 取り込み データ抽出 分析／可視化 データ編集 将来的には 他RDB等とも連携 DB ミドルウェア BIユーザ APIでデータセット更新 現⾏DB環境 Lambda DB負荷を最⼩限にするため、 連携テーブルは絞り込み コスト⾯を意識し、 ジョブを分けすぎない Athenaの多重度や S3負荷も意識し、 API実⾏間隔も考慮

8 2. 直⾯した課題と解決⽅法

9 ２. 直⾯した課題と解決⽅法 プロジェクトを進める上で直⾯した課題と、その課題をどのように 回避したかを説明していきます。 このあたり 現⾏DB、DMS、Glueジョブと⾊々あるのですが、 なぜか普段あまり語られることが多くないQuickSight 関連に 絞ってお話しします。

10 ２. 直⾯した課題と解決⽅法 ① SPICE のデータ更新間隔への挑戦 ② ⼤⼈数へのダッシュボード共有⽅法 ③ ダッシュボードのデータ更新⽇時をユーザに⾒せたい ④ ピボットのフィールドは20 列制限内での表現

11 要件︓ 『QuickSightに、1時間未満の間隔で、最新の分析データを インポートすること』 ここの話 ２. SPICE のデータ更新間隔への挑戦

２. SPICE のデータ更新間隔への挑戦 ■背景︓ ・ 今回はSPICEを利⽤ ・ 予めAthenaを介しSQLクエリで分析データを抽出し、SPICE内にインポートするつくり ・ SPICE に取り込む場合、QuickSight上での表⽰データはSPICEへインポート時点と なるため、定期的にSPICE内のデータ最新化が必要 ※可能な限り、早いサイクルでSPICEのデータ最新化したい QuickSight Athena SQL SQL結果 をインポート SPICE S3 （分析データ⽤） ⾼速に 可視化 × 毎次SQLクエリ実⾏は 性能⾯で難 SPICEとは ・QuickSight に内蔵されたインメモリ型のデータベース ・分析データをSPICE にインポートすることで、SPICE内の インポートデータに対し分析/可視化を可能。そのため⾼速。

13 ■解決⽅法︓ ・QuickSightの各データセットに対しCreateIngestion API（※）を発⾏することで、 任意タイミングでSPICE内データを⼿動更新できる。更に⾃動更新とも組み合わせる。 ※CreateIngestion APIとは https://docs.aws.amazon.com/ja_jp/quicksight/latest/APIReference/API_CreateIngestion.html ■課題︓ ・QuickSight 標準機能では、SPICEのデータ⾃動更新間隔が最短1時間 (Enterprise版) 注意事項: ⼿動での SPICE 更新の実⾏回数制限について ・現状⼿動での SPICE 更新の実⾏回数には制限があり、24時間につき 32 回 まで。 ・QuickSight標準機能での⾃動更新は本回数には含まれないため、⾃動更新（最短1時間に1回）と ⼿動更新（CreateIngestion）を組み合わせることで、最⼤24 時間につき 56 回の更新が可能。 ２. SPICE のデータ更新間隔への挑戦

14 要件︓ 『権限変更の運⽤作業を、カスタマイズせずに運⽤⼯数を落とすこと』 ここの話 ２.⼤⼈数へのダッシュボード⼀括共有⽅法

15 ■背景︓ • QucikSightは、ダッシュボードを共有する際に、どのユーザに公開するか範囲指定が必要 • 今回の案件では、将来的にユーザ数が300⼈以上になること、ダッシュボード数が 増加する計画があった ■課題︓ • ダッシュボード追加、組織異動等（ユーザの権限変更）時の運⽤として、 都度コンソール画⾯でユーザ単位でダッシュボード公開範囲を変更するのは 運⽤負荷も⾼く、管理も煩雑になる可能性がある ２.⼤⼈数へのダッシュボード⼀括共有⽅法

16 ■解決⽅法︓ • 実はQuickSightのユーザに対し、グループ設定が可能 ※ QuickSightのGUIコンソールではグループ作成画⾯は存在しない • ユーザをグループに所属させ、グループに対しダッシュボードを共有することによって、 そのユーザーグループに含まれる全てのユーザに共有可能 • グループ⾃体の管理は、現時点ではAWS CLI(API) を使う必要があり 分類 アクション QuickSightコンソール AWS CLI ユーザ ユーザの作成、削除 ○ ○ ユーザ指定でダッシュボード共有 ○ ○ グループ グループの作成、削除 × ○ ユーザーをグループへ追加、削除 × ○ グループ指定でダッシュボード共有 ○ ○ ユーザA ユーザB ユーザC ダッシュボード１ ダッシュボード２ Group1 Group2 × ２.⼤⼈数へのダッシュボード⼀括共有⽅法

17 要件︓ 『表⽰しているQuickSight データが、いつ最新化したのかをユーザ が確認できること』 ここの話 ２.ダッシュボードのデータ更新⽇時をユーザに⾒せたい

18 ■背景︓ • SPICEを利⽤する場合、データは定期的に最新化する必要あり • ⼀般ユーザ（閲覧者権限を前提）はQuickSight ダッシュボード上でデータの 最新⽇時がわからないと業務に影響を及ぼす可能性がある ■課題︓ • ⼀般ユーザから⾒ると、QuickSight上のデータはいつ取り込まれたデータであるかを、 明⽰的には確認できない（特に問題が発⽣し、データ鮮度が落ちた場合の懸念） ※ちなみに、作成者以上の権限である場合は、データセットへの権限を付与すれば ダッシュボード右上の「データ」にて最終更新⽇時を確認可能 ２.ダッシュボードのデータ更新⽇時をユーザに⾒せたい

19 ■解決⽅法︓ • SPICEのデータ⼿動更新処理のプログラム内で、更新⽇時をS3上にファイルとして出⼒ • ダッシュボード上でユーザが視認可能な箇所に、対象S3ファイルの中⾝を表⽰ ｚ QuickSight S3 （分析データ⽤） Athena SQLクエリ 分析データ 更新 API実⾏ Lambda 更新 ⽇時 分析 データ群 ⽣成 QucikSight ダッシュボード 取得した更新⽇時 を表⽰ ２.ダッシュボードのデータ更新⽇時をユーザに⾒せたい

20 要件︓ 『ピボットのフィールド数上限に負けずに、お客様の要望する ダッシュボードを表現すること︕』 ここの話 ２.ピボットのフィールド20制限内での表現

21 ■背景︓ • ピボットテーブルのフィールドには、QuickSightの仕様上、⾏/列ともに上限20の 制約がある。 ■課題︓ • お客様が現⾏で確認されているBIをそのまま移⾏すると、⾏が21フィールド以上 となり再現できない。 ２.ピボットのフィールド20制限内での表現

22 ■解決⽅法︓ • 上限緩和はできないので、データ表⽰の⽅法を変えて対応するしかない 具体的には・・・ • ⽤途など状況を上、⼀部データをAthena側のSQLで結合してフィールド数を節約 例えば、住所3 + 住所2 + 住所1 ＝ 住所として結合 • 集計が必要ないデータなどは、ピボッドテーブルではなく、テーブル(⾏/列の制限なし)を 代替実装することで対応する ２.ピボットのフィールド20制限内での表現

23 3.ここができるともっと良くなる、 QuickSight︕

24 ◆機能上の制約︓ • 現時点では合計値は、ピボットテーブルでは最下部に表⽰される。 • 最上部、最下部を任意に選択できると使い勝⼿がよくなって嬉しい。 ３. 「合計値をピボットテーブルの最上部に表⽰したい」 最下部までスクロールしないと 合計が確認できない。 データ量が多い場合つらい

25 ◆機能上の制約︓ • QuickSight コンソールへのアクセス元 IP アドレスからアクセスを制限は不可。 ※もし実現する場合の現状の代替︓ ①QuickSight 操作⽤のIAM ロールを⽤意し、ロール切り替えに IP 制限 ユーザをIAMユーザとして払い出し、マネジメントコンソール経由でIAM ロール切り替えにてアクセス ②SSOの仕組みを作り、IdP 側で制御 外部 IdP による SAML 2.0 を利⽤した SSO を構築し、IdP 側で IP 制限を⾏う ３. 「アクセス元のIP制限をしたい︕」

26 ◆機能上の制約︓ • 現時点において CloudWatch メトリクス、アラームなどを⽤いて、 SPICEの空きサイズ閾値を超えた際にアラートを出せない。 • SPICE 容量を取得するために利⽤可能なAPI 、メトリクスがないため。 ※ SPICEは⾃動拡張ではなく、容量を購⼊型のため、運⽤としてSPICE空き容量の注視が必要 ３. 「SPICEの容量監視をするインターフェースがほしい︕」

27 ◆機能上の制約︓ • 検索画⾯にて、“ダッシュボード”、”分析”、”データセット”を指定し、絞り込み検索ができない。 （ 横断的に検索されるため、数が多くなった場合、結果が⾒づらい場合がある） ※ 代替として、検索結果のURLを強引にentityTypeを指定し絞ることは可能。 例︓デフォルト︓ https://us-east-1.quicksight.aws.amazon.com/sn/search?keyword=XXX&entityType=ALL 変更後︓ entityType=DASHBOARD entityType=ANALYSIS entityType=DATASET ３. 「検索のGUIインターフェースに機能追加してほしい︕」

28 本編はここまで

JAWS DAYS 2021 re:Connect 2021年3⽉20⽇(⼟) 10:00〜18:00 オンラインにて開催 #jawsdays2021 #jawsug https://jawsdays2021.jaws-ug.jp/

JAWS DAYS 2021 - re:Connect - とは ・AWSユーザのエキスパートからノウハウがつまったセッション ・エンタープライズ企業からスタートアップまでのAWSユーザ事例 ・初⼼者向けセッションとハンズオン ・みんなで楽しめる参加型コンテンツ と、盛りだくさんのオンラインコミュニティイベントです。 普通の勉強会では聞けない、ここだけの話も聞けるかも︕︖

イベント開催前も楽しんじゃおう︕ JAWS DAYS 2021 - re:Connect - オリジナルグッズがもらえる 応援キャンペーン開催中︕(詳しくは jawsdays note で検索)

申し込んだら絶対参加︕とは⾔いません。 「とりあえず申し込んでみようかな」 という軽い気持ちで⼤丈夫です。 みなさん、オンライン会場でお会いしましょう︕

JAWS DAYS 2021 re:Connect 2021年3⽉20⽇(⼟) 10:00〜18:00 オンラインにて開催 #jawsdays2021 #jawsug https://jawsdays2021.jaws-ug.jp/