この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
AWS 事業本部のイシザワです。
RDS のデータを S3 に格納する方法と、格納したデータを Athena で分析する方法を調査したので、その手順をまとめます。
用語
本記事で使う AWS Glue の用語を簡潔にまとめます。
AWS Glue Data Catalog
AWS のメタデータストアサービスです。後述する AWS Glue データベースや AWS Glue テーブルなどを格納しています。
AWS Glue データベース
後述する AWS Glue テーブルのコレクションです。
AWS Glue テーブル
リレーショナルデータベースのテーブル定義です。テーブル属性でソースデータのロケーションを指定することができます。
ソースデータの形式は構造化データだけでなく、半構造化データである JSON や XML にも対応しています。
クローラー
クローラーはデータストアとやり取りすることで AWS Glue テーブルを作成します。
データストアとして JDBC 接続可能なデータベースや S3 を指定できます。
AWS Glue ジョブ
ソースデータに接続して抽出したデータを処理し、結果を書き出すまでの一連の流れを実行するスクリプトです。
スクリプト自体は AWS Glue コードジェネレータによって自動的に作成されます。
AWS Glue 接続
データストアへの認証情報や URI などの、データストアへの接続に関する情報を格納するデータカタログオブジェクトです。
前提
構成
AWS の構成図を以下に示します。構成図中の RDS のデータを AWS Glue で S3 に格納して、Athena で分析することが今回の目的です。
Glue ジョブの実行には S3 アクセスが必要となるため S3 用のゲートウェイ型 VPC エンドポイントを作成しています。
データベース
今回使用したデータベースエンジンは Oracle SE2 で、エンジンバージョンは19.0.0.0.ru-2022-07.rur-2022-07.r1です。
データベース名はORCLで、以下のような内容のテーブルを持っています。
SQL> DESC "部署";
名前 NULL? 型
----------------------------------------- -------- ----------------------------
部署ID NOT NULL CHAR(4)
部署名 NOT NULL VARCHAR2(10)
SQL> SELECT * FROM "部署";
部署ID 部署名
------------ ------------------------------
D001 開発
D002 総務
D003 経理
SQL> DESC "従業員";
名前 NULL? 型
----------------------------------------- -------- ----------------------------
社員ID NOT NULL CHAR(5)
氏名 NOT NULL VARCHAR2(15)
年齢 NUMBER(38)
所属部署ID CHAR(4)
入社日 NOT NULL DATE
更新日時 NOT NULL TIMESTAMP(6)
SQL> SELECT * FROM "従業員";
社員ID 氏名 年齢 所属部署ID 入社日 更新日時
--------------- --------------------------------------------- ---------- ------------ -------- -------------------------
E0001 アリス 25 D001 21-04-01 22-10-27 10:39:14.000000
E0002 ボブ 30 D001 21-04-01 22-10-27 10:39:14.000000
E0003 キャロル 30 D002 22-10-01 22-10-27 10:39:14.000000
E0004 デイブ D003 22-10-01 22-10-27 10:39:14.000000手順
1. AWS Glue が引き受ける IAM ロールを作成する
AWS Glue は一時的な作業場所として S3 を使用するため、S3 へのアクセスを許可する必要があります。 また、ソースやターゲットとするデータストアが AWS サービスの場合はそのサービスへのアクセスも許可する必要があります。
そのため、以下の設定で IAM ロールを作成します。今回は簡単のため、フルアクセス権限の許可ポリシーを選択しています。
- 名前：
AWSGlueServiceRole-for-oracle-db-data-extraction - 信頼されたエンティティ：
glue.amazonaws.com - 許可ポリシー：
AWSGlueServiceRoleAmazonS3FullAccessAmazonRDSFullAccess
なお、IAM ロール名のプレフィックスは AWSGlueServiceRole とすることが必須となります。
2. クローラーとジョブを実行する ENI 用のセキュリティグループを作成する
クローラーや Glue ジョブを VPC 内で実行するときに、指定したサブネットに ENI (Elastic Network Interface) が作成されます。 この ENI 用のセキュリティグループを作成する必要があります。
このセキュリティグループは、全ての TCP ポートに対して自己参照のインバウンドルールを持つ必要があります。 つまり、以下のインバウンドルールを持つセキュリティグループを作成します。
| タイプ | プロトコル | ポート範囲 | ソース |
|---|---|---|---|
| すべての TCP | TCP | 0 ～ 65535 | 自分自身のセキュリティグループ ID |
作成したら RDS のセキュリティグループのインバウンドルールに、このセキュリティグループからのデータベースサービスへのアクセスを許可するルールを追加します。
3. AWS Glue 接続を作成する
RDS に JDBC で接続するための AWS Glue 接続 oracle-db-connection を作成します。
まず、AWS Glue コンソールに移動して、左ペインから Connections を選択し、Connections の欄にある Create connection を選択します。
接続名と接続タイプを入力します。今回は Oracle データベースに接続するので接続タイプに JDBC を選択します。
JDBC 接続に必要な情報を入力します。RDS for Oracle に接続する場合、JDBC URL はjdbc:oracle:thin://@(RDS のエンドポイント):(DB サービスのポート番号)/(データベース名)となります。
その他のデータベースエンジンに接続する際にはここを参照して適切な URL を指定します。
認証情報はここではユーザー名とパスワードを直に入力していますが、AWS Secrets Manager を利用することもできます。
ネットワークの設定は、VPC には目的の RDS のある VPC を選択し、サブネットには RDS と S3 に到達可能なサブネットを選択します。セキュリティグループには先ほど作成したセキュリティグループを選択します。
これらの設定が終わったら、右下の Create connection を押下することで AWS Glue 接続が作成されます。
4. AWS Glue データベースを作成する
Glue ジョブでデータを抽出する用のデータベースoracle-dbと Athena で分析する用のデータベースfor-athenaを作成します。
AWS Glue コンソールの左ペインの Databases を選択し、コンソールの右上の Add database を選択します。
データベース名oracle-dbを入力し、Create database を押下します。
AWS Glue データベースが作成されます。同様にしてデータベースfor-athenaも作成します。
5. クローラーを作成し、RDS からメタデータを読み出す
RDS からテーブル定義を読み出すクローラーoracle-db-crawlerを作成し、実行します。
AWS Glue コンソールの左ペインの Crawlers を選択し、コンソールの右上の Create crawler を選択します。
クローラー名oracle-db-crawlerを入力します。
まだ Glue Data テーブルを作成していないので Not yet を選択し、Data sources 欄の右上の Add a data source を選択します。
Data source には JDBC を選択し、Connection には先ほど作成した AWS Glue 接続oracle-db-connectionを選択します。
データベースORCL配下のすべてのテーブルをスキャンしたいので、Include path にはORCL/%を入力し、Add a JDBC data source を押下します。
Data sources の欄に項目が追加されているので選択します。
クローラーが引き受ける IAM ロールに、先ほど作成した IAM ロールAWSGlueServiceRole-for-oracle-db-data-extractionを選択します。
クローラーの出力先となる AWS Glue データベースに先ほど作成したoracle-dbを選択し、スケジュールに On demand を選択します。
設定内容を確認し、問題がなければ Create crawler を押下します。
作成したoracle-db-crawlerを選択し、コンソール右上の Run を押下することでクローラーを実行することができます。
AWS Glue データベースoracle-dbにテーブルが作成されているのが確認できます。テーブル名がおかしいのは、恐らく元のテーブル名が日本語であることによるものだと思われます。
従業員テーブルのスキーマを見ると、CHAR 型や VARCHAR2 型が string 型に、DATE 型が timestamp 型にマッピングされていることが確認できます。 Oracle データベースの DATE 型が日付と時刻を表すのに対し、Glue の date 型は日付のみを表すようなので、日付と時刻を表す timestamp 型にマッピングされるのは妥当に思われます。
また、カラム名の英字の大文字が小文字になっていることが確認できます。
6. AWS Glue ジョブを作成し、RDS のデータを S3 に格納する
従業員テーブルを S3 に格納する用のジョブEmployeeと部署テーブルを S3 に格納する用のジョブDeptを作成します。
ジョブの作成には AWS Glue Studio というジョブ作成用の GUI を利用します。
AWS Glue コンソールの左ペインから Jobs を選択し、Visual with a blank canvas を選択してから Create を押下します。
画面左上の Source から Oracle SQL を選択し、データソースノードを作成します。
設定項目の JDBC ソースには Data Catalog table を選択し、データベースにはoracle-dbを、テーブルには従業員テーブルを読み出した AWS Glue テーブルを選択します。
Action から Change Schema を選択し、Change Schema 変換ノードを作成します。
Change Schema 変換ノードでは、カラム名やデータ型を変更することができます。 年齢には decimal 型よりも int 型が、入社日には timestamp 型よりも date 型が妥当だと思われるのでデータ型の変換をしています。
Target から Amazon S3 を選択し、データターゲットノードを作成します。
ここではデータを Snappy で圧縮した Parquet 形式で S3 に保存します。
データカタログ更新オプションを設定することで、S3 への保存と同時に AWS Glue テーブルを作成できます。
ここではfor-athenaデータベースに、テーブル名「従業員」で作成するよう設定します。
このテーブルは Athena で分析する際に使用します。
また、ここでパーティションキーの設定をすることができます。パーティションキーを適切に作成すると、Athena で分析する際のパフォーマンスとコストが改善されます。
ジョブの詳細設定で AWS Glue が引き受ける IAM ロールの設定をします。
これらの設定が終わったら、右上の Save を押下して保存します。 その後、Run を押下することでジョブが実行されます。
ジョブDeptにも同様の設定をして実行します。こちらではデータ型の変換をせずにソースノードとターゲットノードを直接繋ぎます。
ジョブの実行が完了した後にfor-athenaデータベースの詳細を開くと、従業員テーブルと部署テーブルが作成されていることが確認できます。
それぞれのテーブルの列 Location にある S3 パスに Parquet 形式のデータが格納されています。
7. 格納したデータを Amazon Athena で分析する
AWS Glue ジョブで S3 に格納したデータを Amazon Athena で分析します。
Amazon Athena コンソールの左ペインからクエリエディタを選択します。
設定タブでクエリ結果の保存場所を設定した後に、エディタタブのデータベースにfor-athenaを選択することで、先ほど格納したデータを SQL でクエリすることができます。
S3 に格納する際にパーティションの設定をすると、設定したテーブルとカラムに上図のように「パーティション化済み」と出ます。
いくつかクエリを試してみます。
SELECT * FROM "従業員";
出力内容に問題はなさそうです。
次にテーブルの結合を行うクエリを実行します。
SELECT "氏名", "部署名" AS "所属部署" FROM "従業員" JOIN "部署" ON "従業員"."所属部署ID" = "部署"."部署ID";
テーブルの結合ができることが確認できます。 ドキュメントによると、Athena ではクエリ中のカラム名の英大文字が英小文字に変換されるため、英小文字のカラム名をクエリ中で英大文字で指定しても意図通り動作します。
最後に、NULL を扱うクエリを実行します。
SELECT "氏名" FROM "従業員" WHERE "年齢" IS NOT NULL;
年齢カラムが NULL になっているデイブが除外されていることから、NULL かどうかの判定を行うことができることが確認できます。
調査中に詰まったところ
調査中にいくつか詰まったところがあるので忘備録として残しておきます。
ジョブの実行に失敗する
セキュリティグループに自己参照のルールをつけていませんでした。また、AWS Glue が引き受ける IAM ロールの名前の接頭辞にAWSGlueServiceRoleをつけていませんでした。
ジョブの2回目以降の実行で S3 にデータが格納されない
実験中にジョブの実行で作成されたデータを一度削除してからジョブを再実行したら、ジョブは成功するのに S3 にデータが格納されませんでした。
AWS Glue にはジョブのブックマークの仕組みがあり、処理済みのデータの処理がスキップされていたようです。 ジョブの詳細設定からジョブのブックマークを無効にしたら格納されるようになりました。
Athena の2回目以降の実行で失敗する
Athena のクエリ結果の保存場所を、AWS Glue テーブルで指定されるソースデータの S3 上のロケーションの配下に設定していたことが原因でした。
ジョブに成功するのに S3 にデータが格納されない
Oracle データベースで実験用のテーブルを作成していた際に、テーブルの変更をコミットしていなかったためテーブルが空になっていました。これはひどい。
まとめ
RDS のデータを S3 に格納して、Athena で分析するまでの手順をまとめました。 本記事では RDS for Oracle を利用していますが、他の JDBC 接続するリレーショナルデータベースでも適用できるよう書いたつもりです。
最後に、オンプレミスのデータベースと接続する際の参考文献としてこの記事を挙げておきます。
4
