オンプレミスRDBのデータをAWSクラウド上の分析基盤に取り込む手法の整理

DB TECH SHOWCASE 2023 © 2024, Amazon Web Services, Inc.
or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 © 2024, Amazon Web Services, Inc. or its affiliates. All rights reserved. Amazon Confidential and Trademark. オンプレミスRDBのデータを AWSクラウド上の分析基盤に取り込む手法の整理 - データの抽出、保存形態、必要となる前処理 - 下佐粉昭（しもさこあきら） H1 2 シニアアナリティクススペシャリストソリューションアーキテクト Amazon Web Services Japan

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 本セッションの内容クラウド上に分析基盤を作り、活用していくうえで、初期の段階で課題になるのが「データレイク」への「データ取り込み」です。本セッションでは以下を説明します。 • なぜデータレイクを作るのか？ • データが無いと活用が進まないが、どのようにすればデータソース（RDBMS等）からデータを取り出せるか？ • RDBMSから取り出したデータはデータレイク上でどのように配置、更新するべきか？どのようにして性能を担保するか？ 2

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 自己紹介下佐粉昭（しもさこあきら）アマゾンウェブサービスジャパンシニアアナリティクススペシャリストソリューションアーキテクト専門：データレイク、データウェアハウス書籍：「AWSではじめるデータレイク」「即戦力のDB2管理術」等週刊AWS：毎週のAWSアップデートをサマリして発信 @simosako

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 © 2024, Amazon Web Services, Inc. or its affiliates. All rights reserved. Amazon Confidential and Trademark. データレイク構築の狙いと構成

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 狙い：蓄積と処理系を分離することで、迅速に変更できるITインフラを実現するライフスパンの異なる「データ」と「処理系」を分離する • データを一か所に蓄積 • 「正」のデータが明確に/データを捨てない → 新たな要件に対応できる環境処理系が容易に切り替え可能 • システム間の調整が不要に → 新技術に対応可能な環境蓄積分析技術システムシステムシステム分析技術分析技術処理系 5

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 データレイクを中心とした分析環境 • 生データをデータレイクに集め、将来的なニーズに対応 • 分析や可視化といった活用部分は取り替え可能な構成 • DWHを使う場合、下図「分析」のところに入る収集データレイク（蓄積）分析応用・可視化データを収集し、データレイクへ格納生データ全期間分析 API 応用・可視化 6 DWH、全文検索エンジン etc. DWH、全文検索エンジン etc.

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 データレイクに必要な機能狭義のデータレイクはデータの保存場所 …しかし、それだけでは不足する機能が１）そのままでは利用に向かない生データの整形（前処理）最低限必要な整形とパフォーマンス面の最適化２）どのようなデータが置いてあるのかの管理（カタログ）多数のデータの情報を一元管理する仕組み 7

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 データレイクを中心とした分析環境 on クラウド 8 データレイクにカタログと前処理を持ち、活用を促進する収集データレイク処理系応用・可視化データを収集し、データレイクへ格納処理系応用・可視化カタログ前処理（ETL）蓄積生データ整形最適化済マート本セッションのカバー範囲

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 Amazon S3 データ分析コンテンツ配信 Amazon S3 を中心としたAWSのサービス構成機械学習ストリームデータ BI Amazon Personalize Amazon Rekognition Amazon SageMaker AWS Glue Amazon Redshift Amazon Athena Amazon QuickSight AWS Glue Databrew Amazon EMR Amazon Kinesis AWS IoT Analytics AWS IoT Core Amazon CloudFront AWS Elemental MediaLive Amazon Managed Service for Apache Flink Amazon Glacier ETL データレイク管理 AWS Lake Formation データ転送 AWS Backup AWS Transfer Family バックアップ＆アーカイブ AWS Snowball Edge Amazon MSK Amazon Bedrock

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 AWSのアナリティクスサービスデータレイク上の大規模データに、SQLで高速処理を実現 Amazon Redshift Serverless Amazon EMR Serverless AWS Glue 概要サーバーレス DWH サーバーレス Hive/Spark環境サーバーレス ETL/カタログ標準・デファクトスタンダード技術 SQL標準 Hive/Spark （デファクト） Spark （デファクト）分散処理〇〇〇データレイク(S3) 連携 S3上のデータを外部表として認識し、 SQLで処理 S3上のデータに透過的にアクセスし、 Spark SQLやHiveでアクセス S3上のデータに透過的にアクセスし、 Spark SQLやPython でロジックを記載

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 © 2024, Amazon Web Services, Inc. or its affiliates. All rights reserved. Amazon Confidential and Trademark. データ取り込み部分の考え方

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 データ取り込み部分の重要性と考え方稼働序盤の課題：データの量・バリエーションが少ない • データが少ないデータレイクは活用されづらい • 少ないと使われない → データが増えないの負の連鎖 • 「効率よく」データレイク上のデータを増やすためには、だれにとっての「効率」なのかを明確にするデータの出し手＆データレイクチーム双方にとって効率が良い環境の実現がポイント

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 データ取り込みを充実させる際の考え方 • データオーナー（出し手）側の課題 • 既存システムに追加の負担をかけることはできない • 通常業務で忙しく、作業工数が確保できない • データレイクチーム側の課題 • データソース毎に個別に連携インフラを構築していたら、時間・コストがかかるし、運用も大変この実情は変えるのが困難（徐々に対応する）こちらは技術で対応可能方針） • データソースに追加の負担をかけないよう、あるがままでデータを取得 • ソースのバリエーションが増えても運用負荷が高くならない仕組みづくり

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 「あるがままで」データを受け取る [原則] データの出し手にフォーマット変換や整形を要求しない • 既存インフラ・仕組をできるだけ活かした形でデータを受領する仕組みを検討する • 整形は受け取った側で – クラウドを活用して安く・速く例) • デイリーバックアップのファイルがあるなら、それをそのままもらう • 既存の連携基盤があるなら、そこから取得 • データベース側に連携機能があるなら、それを活用

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 データレイクチームの負担をどう減らすか？ • AWSサービスを活用して運用・構築負担を減らす • AWS Transfer for FTP/SFTP : FTP/SFTPアクセスでS3に連携 • AWS DataSync : オンプレミスNFS等のデータをAWSに同期 • Amazon AppFlow : SaaSとのデータ連携をノーコードで実現 • AWS Database Migration Service (DMS) : RDBのマイグレーション • 3rd パーティーツールや、SaaS、OSSの活用 • AWSは多くのISVと協力しており、多様なツールが導入可能

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 Amazon RDS, Amazon Aurora は S3 への出力をサポート 16 Export Snapshot Export Cluster Export SQLの結果を S3 に出力 Snapshot(バックアップ)から S3 に出力ストレージ内の表をS3に出力最新データをSELECTで加工して出力 Snapshot時点の表全体を出力最新の表全体を出力 RDS / Aurora のCPUやIOを消費 (Read Replicaを利用可能) RDS / Aurora に追加負荷がかからない Aurora に追加負荷がかからない CSV, Textフォーマット等 Apache Parquet フォーマット Apache Parquet フォーマットサポート： • RDS for MySQL, MariaDB, PostgreSQL • Aurora MySQL, PostgreSQL サポート： • RDS for MySQL, MariaDB, PostgreSQL • Aurora MySQL, PostgreSQL サポート： • Aurora MySQL, PostgreSQL 補足1: RDS for Oracle ではディレクトリオブジェクトに保存されたCSV等のデータをrdsadmin_s3_tasks.upload_to_s3で S3 に出力可能です補足2: RDS for SQL Server では、RDS内に設定されたD:¥S3¥ディレクトリ内のデータをrds_upload_to_s3で S3 に出力可能です

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 補足: Zero-ETL Integration with Amazon Redshift を利用したデータ連携 17 データソースからETL不要でRedshiftに連携ニアリアルタイムの分析を簡単で安全な方法で提供 Amazon Aurora 等 Amazon Redshift Transactional Applications Analytics Analyst Zero-ETL integration ※この場合、データレイクを通さずにDWHに連携されるので、データレイクへの保存はRedshift の S3 連携機能で別途実施する必要がある • Amazon Aurora MySQL zero-ETL integration with Amazon Redshift (一般提供開始) • Amazon Aurora PostgreSQL zero-ETL integration with Amazon Redshift (Preview) • Amazon RDS for MySQL zero-ETL integration with Amazon Redshift (Preview) • Amazon DynamoDB zero-ETL integration with Amazon Redshift (Preview)

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 © 2024, Amazon Web Services, Inc. or its affiliates. All rights reserved. Amazon Confidential and Trademark. RDBのデータを取り込むための手法

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 データソースの更新をどう反映させるかソースの更新をどのようにデータレイクへ反映するかは検討しておくべきポイント基本的には、分析するためのデータは「履歴」であるべき（過去も今も記録する）手法を検討する際のポイント • データサイズ • 差分の抽出方法 • 更新の反映方法 • 保存フォーマット 19

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 データサイズソースのデータサイズはもっとも重要なファクター • 全体サイズと、日々の更新サイズ • データソースとAWS間のネットワーク帯域も確認全体サイズが小さい場合、手法の選択肢が多くなる • 全部毎回コピーしても良いデータサイズが巨大な場合、どう差分を抽出するか？の検討が必要に 20

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 差分抽出の方法 1/2 • 更新時刻を判断できる列がある場合は、そこを条件に SELECTするもしくはその列をつけるよう改修(とても難しい) • 判断列が無い場合は規約（XXか月前までは更新される可能性）の有無を確認し、差分抽出範囲を抑えられないかを検討 • それも不明な場合はCDC（更新ログ転送）で検討（CDCのパフォーマンスボトルネックやトラブル時の対応について、考慮が必要） => AWS DMS や、各種商用ソフトウェア 21 Tran ID Price Last update 0011 100 2023-11-22 11:22 0012 200 2023-11-22 11:23 : 0030 105 2023-11-24 21:40

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 差分抽出の方法 2/2 どのタイミングで更新差分を取得するか？データの更新頻度にあわせた取得頻度の検討マスターのように更新頻度が少ないものを毎日取得する必要はないとはいえ、データソース側で更新頻度を確定できない場合も多いビジネス的な意味で整合性が取れたデータを取得するビジネス的な整合性がとれていない/未完了のデータは分析に利用しづらい例：船での運搬記録表- 到着日時は船が到着後にしか分からないが、到着前のデータは分析にはあまり意味がない（場合が多い） 22

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 更新差分をCDCで抽出する AWS DMS を利用した差分抽出の場合 AWS Database Migration Service (DMS) はデータソース（RDB）のトランザクションログから差分を抽出し、 S3 に差分を出力することが可能以下のような更新差分ファイルが S3 にPUTされるので、これ適切にS3内にマージする後処理が必要 I,101,Smith,Bob,4-Jun-14,New York U,101,Smith,Bob,8-Oct-15,Los Angeles U,101,Smith,Bob,13-Mar-17,Dallas D,101,Smith,Bob,13-Mar-17,Dallas I=Insert, U=Update, D=Delete を表すフラグ列参照: https://docs.aws.amazon.com/ja_jp/dms/latest/userguide/CHAP_Target.S3.html 23

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 更新の反映 – マスターマスターは変更頻度が低く、サイズも小さい事が多い（差分で取らなくても良い）版管理のイメージでデータレイク上に保持する例：2023年度製造番号マスターデータレイク上の表現方法は、 ①別データ（別表）として分ける ②1表に入れ、バージョン番号を列に持つ 24 2021年度製造番号ファイル.csv 2022年度製造番号ファイル.csv 2023年度製造番号ファイル.csv ①別表として保存 ②バージョン番号を列に持つ、1表に保存 Parts ID Price Version x110 100 2020 x120 200 2020 : x110 105 2021

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 更新の反映 – トランザクションデータ (1/2) トランザクションタイプのデータの場合は、一般的にデータサイズが大きく、更新も多いデータソース側がInsert (追記)だけか、Update/Deleteもあるかを確認データソース側の更新がInsert (追記)のみの場合：データレイク側でも新ファイルをバケットに追加するだけで良い 25 Amazon S3 (Bucket) file-2023-12-01.csv file-2023-12-02.csv file-2023-12-03.csv

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 更新の反映 – トランザクションデータ (2/2) Update/Deleteがある場合、データレイク上の表現として追記のみで表現できないか？を検討例）更新日時列をつけた形で追記する。最新断面は Athena 等で必要なデータだけ抽出するVIEWで対応データレイク上に差分を反映させる(Update/Delete)場合、洗い替えが必要になる • 全体を読んで、部分更新して、全体出力 • 実装自体は難しくない(例：AWS Glue + Spark ならSpark SQLで書ける)が、性能とコストが要件を満たせるかが検討ポイント • パーティショニングで処理対象のファイルを限定することで性能が維持しやすくなる 26

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 トランザクション可能なデータレイクとOTF データレイクに対する、より高度なニーズの増加部分更新・削除は、既存のファイルから該当行を探して処理する必要があり、効率的に行うには工夫が必要タイムトラベルクエリや、スキーマエボリューション等も「S3に存在するオブジェクトをそのまま読む」だけでは実現が難しいストレージ(S3)と処理系(Amazon EMR, AWS Glue 等)との間に差分データ、メタデータを管理するレイヤーを置くことで高度な操作を可能にする技術＝＞ Open Table Format (OTF) 27

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 OTFレイヤーを挟むことで高度なニーズに対応する 28 Amazon S3 その他オブジェクトストレージ等 Open Table Format レイヤー Open Table Format でのデータ読み書きメタデータの管理、効率的なデータ操作等分散処理エンジンレイヤーデータレイヤー

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 Open Table Format：検討事項 • 適したユースケース • データセットが大きく、部分的な更新が発生する（小さい場合は全体書き換えでも問題ない） • タイムトラベルクエリ、スキーマエボリューション等、OTF無しでは実現困難な要件への対応 • OTFの読み書きをサポートするサービスを利用可能なケース • Apache IcebergをサポートするAWSサービスの一例：Amazon Athena , Amazon EMR , AWS Glue, Amazon Redshift • 注意点 • OTFによる追加のオーバーヘッド • （更新が高速な場合があるとはいえ）汎用RDBのような更新速度が出るものではありません • 更新が繰り返された後のパフォーマンス調整 • Apache Icebergの場合：AthenaからOPTIMIZE/VACUUMの実施、AWS Glue Catalog の自動compaction等 29

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 © 2024, Amazon Web Services, Inc. or its affiliates. All rights reserved. Amazon Confidential and Trademark. データ整備の基本戦略と性能の最適化取り込んだデータを活用可能にする

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 基本戦略戦略１：オリジナルデータ（≒生データ）は残す後から変えたくなった際にやり直せる戦略２：活用を促進できるように前処理する検討ポイント：前処理はどこまでやるべきか？データレイクは、「みんなが使うものを共有する」一部の人しか使わないものはデータレイク外部で検討する 31

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 基本戦略（続き）戦略１：オリジナルデータは残す戦略２：活用を促進できるように前処理する例）3段階ぐらいのバケット構成を初期案とし、検討を進めるオリジナルデータ（生データ）必須の前処理とクエリ最適化特定分析用データ（データマート等） 32

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 2つ目のバケットでは、どのフォーマットで保存するかフォーマットはオープンであることを重視する多様なアプリケーション・サービスからアクセス可能であることデファクトスタンダードを重視（絶対ではない） 33 構造特徴注意点 CSV/TSV 構造化テキスト広く普及、シンプル大規模時の処理速度圧縮やパーティショニングで対応 JSON/XML 準構造化テキスト広く普及、構造が柔軟 CSVより性能が出づらい SQLで処理しづらい Parquet 構造化バイナリ Hadoopとともに普及列単位の読み取りが高速レガシーツールでは未対応オープンなファイルフォーマットの例：＊非構造化データ以外

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 2つ目のバケットで行う必須の前処理ビジネス的な前処理（最低限必要な前処理）＝＞ジョイン可能にする • 文字コードの変換：UTF-8等に統合を検討 • IDの統一：システムで異なるIDを発行している場合等の対応 • 日付、表現の統一：日付の表現方式、タイムゾーン、大文字小文字等パフォーマンス面の最適化（必要に応じて）＝＞クエリ性能の向上 • （次スライド） 34

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 性能向上のための前処理パラメータチューニングやインデックス作成ではなく、ファイルの配置方法を工夫することで処理性能を向上させる 1. 読む量を減らす • 圧縮 / パーティショニング / カラムナフォーマットの採用 2. 並列で読む • ファイルの分割 • Splittable (分割可能) な保存形式の採用参考資料：「Top 10 Performance Tuning Tips for Amazon Athena」 https://aws.amazon.com/jp/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/ 35

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 圧縮とファイル分割 • 圧縮により（一般的には）性能向上と利用コストの削減を実現圧縮データを展開するための時間より、データを読み込むまでの時間の方がはるかに長い • 適切なファイル分割はアクセスの並列性を高めるために重要 100MB～200MB程度（一般にオブジェクトストレージでは小さいファイルが大量にあると逆に遅くなる） • ファイルサイズが大きくなる場合、Splittableなフォーマットの利用も検討 36

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 Splittableなフォーマット分割可能＝分散処理で性能を発揮しやすい Splittableな圧縮フォーマット：bzip2、LZO、無圧縮 Splittableなカラムナフォーマット： • 例：Parquetの場合はRow groupの単位でSplit可能 • （各種OTFは、保存フォーマットにParquetを選択することが可能）利用するサービスがサポートしている組み合わせかは要確認 37

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 パーティショニング列の値によって、データの保存先を変える事で読む範囲を限定する一般的にはHiveパーティショニング形式（右）が推奨パーティショニング列は、要件次第だが一般的には日時の列が効果的例） SELECT * FROM t1 WHERE year = 1989 なら /year=1989/ prefix以下のみを読めば良い ※カタログ側でパーティショニングを認識することで、ユーザにはyearが列のように見えます（後述） 38

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 補足：データカタログへの記録テーブル情報 (名前、URL、登録日時等) テーブルプロパティ (オブジェクトのフォーマットに合わせた入出力形式やバージョン等）テーブルスキーマ(表定義）テーブルパーティションパーティションが認識されている「sample- data/location=US/year=2023/month=08/day=06・・・」 AWS Glue のデータカタログには以下のように登録され、各種 AWSサービスから参照可能になります一種の表のようにアクセスが可能です 39

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 まとめ - 作って終わりにせず、継続的な改善を繰り返す • データの分析・活用の速度をあげるためのデータレイク • データ取り込みは「あるがまま」受け取る • AWSサービスの機能を活用して作りこみを少なく • 基本戦略 • 戦略１：オリジナルデータ（≒生データ）は残す • 戦略２：活用を促進できるように前処理する • 更新反映をどうするかはデータソースごとに検討（サイズが重要） 40

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 おしらせ AWS re:Invent Recap - ソリューション編 2024年2月開催世界中の AWS ユーザーが集まり、ベストプラクティスや最新情報を学ぶための年次カンファレンス『AWS re:Invent』 AWS re:Invent Recap ソリューション編では、AWS re:Invent 2023 で発表された多くのアップデートを各ソリューションごとに内容を厳選し、日本のお客様向けに AWS のエキスパートがご紹介します。業務で必要となる分野の最新情報キャッチアップに是非お役立てください 41 https://pages.awscloud.com/japan-reinvent-recap-solution-reg.html

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 アンケート＆お問い合わせ先受講後アンケートのご記入よろしくお願いいたします！ 42 アンケートお問い合わせ先 Amazon RDSのスペシャリストは、お客様のご質問にお答えすることができます。また、お客様の組織をどのようにサポートできるかについてご相談いただくことも可能です。

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 内容についての注意点 • 本資料では2023年12月7日時点のサービス内容および価格についてご説明しています。最新の情報は AWS公式ウェブサイト(http://aws.amazon.com)にてご確認ください。 • 資料作成には十分注意しておりますが、資料内の価格とAWS公式ウェブサイト記載の価格に相違があった場合、AWS公式ウェブサイトの価格を優先とさせていただきます。 • 価格は税抜表記となっています。日本居住者のお客様には別途消費税をご請求させていただきます。 • AWS does not offer binding price quotes. AWS pricing is publicly available and is subject to change in accordance with the AWS Customer Agreement available at http://aws.amazon.com/agreement/. Any pricing information included in this document is provided only as an estimate of usage charges for AWS services based on certain information that you have provided. Monthly charges will be based on your actual use of AWS services, and may vary from the estimates provided.

or its affiliates. All rights reserved. Amazon Confidential and Trademark. #dbts2023 © 2024, Amazon Web Services, Inc. or its affiliates. All rights reserved. Amazon Confidential and Trademark. Thank you!

オンプレミスRDBのデータをAWSクラウド上の分析基盤に取り込む手法の整理

オンプレミスRDBのデータをAWSクラウド上の分析基盤に取り込む手法の整理

More Decks by Akira Shimosako

Other Decks in Technology

Featured

Transcript