第8回 雲勉LT【オンライン︓中級者向け】 AthenaでS3上のデータとDynamoDBのデータを結合する

⾃⼰紹介 2 • 野崎 ⾼弘（のざき たかひろ） • アイレット株式会社 クラウドインテグレーション事業部 プロジェクト企画推進セクション インフラ技術 • インフラ構築・保守やSOCセキュリティアナリストをやってました • 現在は技術検証やドキュメント作業が主 • 趣味︓資格取得、⽝の散歩、ドラクエ、巨⼈ファン、ドラマ鑑賞 • 現在、AWSは10冠、GCPは4冠 3

本LTの⽬的 3 AthenaのFederated Query機能を利⽤してDynamoDBのデータをAthenaからクエリできるようにし、それ をS3のテーブルと結合して、QuickSightで可視化します。 様々なデータソースから、Athenaがそれらを結び付け、可視化までの橋渡しができるようになります︕

本LTの⽬的 4 ユースケースとして架空の販売情報から粗利を算出します。それぞれ別のデータソースに格納されているト ランザクションテーブルとマスターテーブルをAthenaで結合することで、粗利を算出するのが⽬的です。 1. AthenaのFederated QueryでDynamoDBのデータをクエリする 2. S3上のCSVデータをクエリする 3. 1.のDynamoDBのデータと、2.のS3上のCSVデータとを結合する 4. （時間があればQuickSightでじっくり可視化したかった） 本LTの対象者︓データ分析のことはなんとなく知っているが、まだ実際にAthenaをそれほど触ったことがな い初⼼〜中級者。AWS Certified Data Analytics – Specialty試験向け （参考元ハンズオンURL）https://aws.amazon.com/jp/blogs/news/athena-federated-query- dynamodb-quicksight/

全体像 5

Athena とは︖ 6 標準的なSQLを使ってS3のデータを直接クエリするサービス • インタラクティブなクエリを投げてデータの分析を⾏うことができるサーバレスサービス • JDBC / ODBC / API経由でBIツールやシステムと連携 • オープンソースのTrinoおよびPrestoエンジン、Apache Sparkフレームワーク上に構築 • ⼤規模データに対しても⾼速なクエリ • SQLクエリまたはPythonでMLモデルを使⽤して、 異常検出、顧客コホート分析、売上予測などの 複雑なタスクを簡素化 https://d1.awsstatic.com/products/athena/product-page-diagram_Amazon-Athena-Connectors%402x.867e3023b0e6b33862d65aa8e786cce46b88cb61.png

Athena フェデレーテッドクエリとは︖ 7 S3上のデータ以外に、多数のデータソースに保存されているデータをまたがるSQLを実⾏する機能 • リレーショナル、⾮リレーショナル、オブジェクト、およびカスタムデータソースに保存されているデー タの全体でSQLクエリを実⾏できる • データソースコネクタ(ターゲットデータソースと Athena間での変換を実⾏できるコード)を使⽤する • アクセス⽤のLambda関数を準備することで、多様な データソースへのクエリを可能 • カスタムコネクタを作成することで、独⾃のデータソース にも対応可能 IUUQTCMPHHHFODPKQFOUSZHPPHMFBQJQSJWBUFTFSWJDFDPOOFDUFYQMBJOFE

作業の流れ 8 1. Athenaワークグループを作成 2. AthenaのDynamoDBのデータソースへのコネクタを作成 3. 2.で作成したLambda関数ddbcatalogを指定してデータソースを作成 4. Glueクローラーの作成 （Demo） 5. Glueクローラーの実⾏ 6. DynamoDBのデータ（transaction）をAthenaからクエリする 7. S3のデータ（master）をAthenaからクエリする 8. S3上のデータとDynamoDBの表を結合して表⽰する 9. QuickSightで利益の時系列推移を折れ線グラフで表⽰

事前準備 9 • transaction表の⽤意（DynamoDB） DynamoDBに、transactionテーブル（売り上げトランザクションを⼀意に指すTransaction ID毎に Item ID(売れたアイテム) 、Region, Country(販売した地域)や、Sales Channel(販売チャンネル)、オーダーと出 荷⽇時、units sold(アイテムの販売個数)等が格納されている販売記録）を配置。アイテムの詳細はmaster表 にあり、transactionにはありません。 • master表の⽤意（S3） S3のathena-ws-532152701269-ap-northeast-1バケットのmasterフォルダに、master.csvファイル（各 アイテムID毎にItem Type(アイテム種名)、Unit Price(販売単価)とUnit Cost(単体コスト)が格納されている 商品マスタ表）を配置

作業⼿順 10 1. Athenaのワークグループを作成 エンジンタイプや エンジンバージョン（最新はバージョン3） を指定 クエリ結果を保存するS3の場所も指定

作業⼿順 11 2. AthenaのDynamoDBのデータソースへのコネクタを作成 AthenaDynamoDBConnectorというLambdaアプリケーション を作成します。 Spillバケット︓フェデレーテッドクエリのLambda関数の中の メモリやストレージにデータが⼊りきらなかった場合に、 ⼀時的なデータ置き場として利⽤されるバケット Athenaカタログ名︓Athenaでこのカタログに付ける名前。関数名 としても使⽤されます。

作業⼿順 12 3. 2.で作成したLambda関数ddbcatalogを 指定して、データソースを作成

作業⼿順 13 ・Glueクローラーでカタログ登録する AthenaでS3上のファイルにクエリする場合、その情報をGlueカタログに登録する必要があります。 Athenaは、テーブルや列の名前などのメタデータを保存するために、Glueデータカタログを使⽤して、S3 に保存されたデータに接続します。 （Athenaがデフォルトで利⽤している”AwsDataCatalog”はGlueデータカタログ機能を参照しています。） Glueクローラーを使って、指定したデータソースを調べて、データカタログに登録します。

作業⼿順 14 4. Glueクローラーの作成 データソースの追加 出⼒とスケジューリング

デモ 15 ここからデモも交えて実際の動きを⾒ながら説明します。 （bigint型は、RDBを操作するSQLに⽤意されたデータ型の⼀つで、8バイトの整数を格納する）

確認 16 最後に、DynamoDB上のtransaction表とS3上のmaster表をジョイン(結合)しました。 これは、2つの表を結合し、transaction表にあるUnits Sold(販売個数)を、master表のUnit Price(販売単価) やUnit Cost(単体コスト)と掛け算することで、 トランザクション当たりのトータルの売り上げ(Total Revenue＝Units Sold×Unit Price) かかったコスト(Total Cost＝Units Sold×Unit Cost) その差分である粗利(Total Profit＝Total Revenue­Total Cost) を算出するクエリです。 SQLの最後に LIMIT 10 とあり、結果のうち10⾏までが返るようになっています。

No content