はじめに
Amazon S3内に蓄積してあるParqetファイルをAutonomous Databaseから直接参照したいというご要望をいただいたので、外部表を使用して実現する方法を検証してみました。
1. IAMユーザ作成とポリシーの割り当て（AWS）
Autonomous DatabaseがAmazon S3にアクセスするために使用するユーザを作成します。
Autonomous DatabaseからAmazon S3を含むAWSリソースにアクセスする際、ARNを用いたロールベース認証が利用できますが、扱うファイルがParqet、Avro、ORCといったBigdataドライバーを使用するファイルタイプの場合は残念ながらARNを用いたロールベース認証を利用できません。（2022/11/18現在）
AWSのコンソールから「IAM」→「ユーザ」に移動します。
「ユーザの追加」をクリックします。
ユーザ名（ここではadbuser）を入力し、AWS 認証情報タイプを選択セクションの「アクセスキー - プログラムによるアクセス」のチェックをオンにします。
入力が完了したら「次のステップ」をクリックします。
「既存のポリシーを直接アタッチ」を選択し、適切なポリシーを選択します。
ここでは[AmazonS3FullAccess」を選択しました。
選択が完了したら「次のステップ」をクリックします。
必要に応じてタグを入力し、「次のステップ」をクリックします。
確認画面が表示されるので、問題がなければ「ユーザーの作成」をクリックします。
ユーザの作成が完了すると、アクセスキーID、シークレットアクセスキーが表示されるので、メモしておきます。
2. クレデンシャルの作成
Database ActionsやSQL*PlusからAutonomous Databaseに接続します。
DBMS_CLOUD.CREATE_CREDENTIALプロシージャを使用して、Amazon S3にアクセスするためのクレデンシャルを作成します。
各パラメータは以下のように指定します。
| パラメータ | 値 |
|---|---|
| credential_name | 作成するクレデンシャルの名前（ここではMY_S3_CRED） |
| username | 1.で作成したユーザのアクセスキーID |
| username | 1.で作成したユーザのシークレットアクセスキー |
SQL> BEGIN
2 DBMS_CLOUD.CREATE_CREDENTIAL(
3 credential_name => 'MY_S3_CRED',
4 username => 'XXXXXXXXXXXXXXXXXXXX',
5 password => 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
3. クレデンシャルの確認
外部表を作成する前に、2.で作成したクレデンシャルが利用できるかを確認ために、バケット内のオブジェクトの一覧を取得するDBMS_CLOUD.LIST_OBJECTSファンクションを使用してみます。
各パラメータは以下のように指定します。
| パラメータ | 値 |
|---|---|
| credential_name | 使用するクレデンシャルの名前（ここではMY_S3_CRED） |
| location_uri | アクセスするバケットのURI |
SQL> SELECT object_name FROM DBMS_CLOUD.LIST_OBJECTS(
2 credential_name => 'MY_S3_CRED',
3 location_uri => 'https://s3.ap-northeast-1.amazonaws.com/parquet-files-on-s3/');
OBJECT_NAME
--------------------------------------------------------------------------------
fhv_tripdata_2022-01.parquet
fhv_tripdata_2022-02.parquet
fhv_tripdata_2022-03.parquet
fhv_tripdata_2022-04.parquet
fhv_tripdata_2022-05.parquet
fhv_tripdata_2022-06.parquet
fhv_tripdata_2022-07.parquet
fhv_tripdata_2022-08.parquet
8 rows selected.
SQL>
バケット内のオブジェクトの一覧が取得できたので、クレデンシャルが問題なく利用できることが確認できました。
4. 外部表の作成
クレデンシャルに問題がないことが確認できたので、DBMS_CLOUD.CREATE_EXTERNAL_TABLEプロシージャを使用して、Amazon S3上にあるParquetファイルを元にした外部表を作成します。
各パラメータは以下のように指定します。
| パラメータ | 値 |
|---|---|
| table_name | 作成する表の名前（ここではtrip_dataa_2022） |
| credential_name | 使用するクレデンシャルの名前（ここではMY_S3_CRED） |
| file_uri_list | 外部表で使用するファイルのURI |
| format | Parquetファイルの場合は、json_object('type' value 'parquet')で固定 |
テキストファイル（CSV等）を元にした外部表を作成する場合と異なり、列リストやファイル内のフィールドリストを指定していません。
SQL> BEGIN
2 DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
3 table_name =>'trip_data_2022',
4 credential_name =>'MY_S3_CRED',
5 file_uri_list =>'https://s3.ap-northeast-1.amazonaws.com/parquet-files-on-s3/fhv_tripdata_2022-*.parquet',
6 format => json_object('type' value 'parquet')
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
外部表が作成できたので、DBMS_CLOUD.VALIDATE_EXTERNAL_TABLEプロシージャを用いて外部表を検証します。
SQL> BEGIN
2 DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
3 table_name =>'trip_data_2022',
4 rowcount => 10);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
問題なく検証が完了しました。
5. 外部表を使用したS3上のParquetファイルの参照
descコマンドでtrip_data_2002表の表定義を確認してみます。
SQL> desc trip_data_2022
Name Null? Type
----------------------------------------- -------- ----------------------------
DISPATCHING_BASE_NUM VARCHAR2(4000)
PICKUP_DATETIME TIMESTAMP(6)
DROPOFF_DATETIME TIMESTAMP(6)
PULOCATIONID BINARY_DOUBLE
DOLOCATIONID BINARY_DOUBLE
AFFILIATED_BASE_NUMBER VARCHAR2(4000)
SQL>
外部表作成時に列リストやファイル内のフィールドリストを指定しませんでしたが、Parquetファイル内のデータ項目が表の各列にマップされ、Parquetファイル内のデータのデータ型が自動的にOracleのデータ型にマップされていることがわかります。
Parquetのデータ型からOracleのデータ型へのマッピングの詳細はについては、こちらのマニュアルに記載があります。
作成した外部表内のレコード数を確認してみます。
SQL> SELECT COUNT(*) FROM trip_data_2022;
COUNT(*)
----------
9784656
次に、外部表内のデータを確認してみます。
SQL> set pagesize 100
SQL> SELECT * FROM trip_data_2022 WHERE rownum < 5;
DISPATCHING_BASE_NUM
--------------------------------------------------------------------------------
PICKUP_DATETIME
---------------------------------------------------------------------------
DROPOFF_DATETIME
---------------------------------------------------------------------------
PULOCATIONID DOLOCATIONID
------------ ------------
AFFILIATED_BASE_NUMBER
--------------------------------------------------------------------------------
B00014
01-FEB-22 12.15.00.000000 AM
01-FEB-22 12.31.00.000000 AM
B00014
B00021
01-FEB-22 12.08.40.000000 AM
01-FEB-22 12.42.54.000000 AM
9.5E+001 1.73E+002
B00021
B00021
01-FEB-22 12.45.26.000000 AM
01-FEB-22 01.04.37.000000 AM
1.92E+002 7.0E+001
B00021
B00021
01-FEB-22 12.50.39.000000 AM
01-FEB-22 01.29.06.000000 AM
1.73E+002 1.47E+002
B00021
SQL>
外部表に対してSELECT文を実行することで、Parquetファイル内のデータを参照できることが確認できました。
以上で、外部表を作成することで、Autonomous DatabaseからAmazon S3上にあるParquetファイル内のデータを直接参照できることがわかりました。
参考資料
・DBMS_CLOUD.CREATE_CREDENTIALプロシージャ
・DBMS_CLOUD.LIST_OBJECTSファンクション
・Avro、ORCまたはParquetファイル用のCREATE_EXTERNAL_TABLEプロシージャ
・DBMS_CLOUD.VALIDATE_EXTERNAL_TABLEプロシージャ
・DBMS_CLOUD Avro、ORCおよびParquetのサポート