はじめに

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のサポート