[OCI]Autonomous Database:Amazon S3上にあるParquetファイルに対する外部表を作成してデータを参照してみた

# はじめに
Amazon S3内に蓄積してあるParqetファイルをAutonomous Databaseから直接参照したいというご要望をいただいたので、外部表を使用して実現する方法を検証してみました。

# 1. IAMユーザ作成とポリシーの割り当て（AWS）

Autonomous DatabaseがAmazon S3にアクセスするために使用するユーザを作成します。


*Autonomous DatabaseからAmazon S3を含むAWSリソースにアクセスする際、[ARNを用いたロールベース認証](https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/amazon-resource-names-about.html#GUID-3FBB7930-1648-4E5E-8DCF-5493D449AEE6)が利用できますが、扱うファイルがParqet、Avro、ORCといったBigdataドライバーを使用するファイルタイプの場合は残念ながらARNを用いたロールベース認証を利用できません。（2022/11/18現在）*

AWSのコンソールから「IAM」→「ユーザ」に移動します。

「ユーザの追加」をクリックします。
![スクリーンショット 2022-11-18 14.21.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/2b7bfda8-619c-cdbe-55c5-0201e54757cf.png)
ユーザ名（ここではadbuser）を入力し、AWS 認証情報タイプを選択セクションの「アクセスキー - プログラムによるアクセス」のチェックをオンにします。
入力が完了したら「次のステップ」をクリックします。
![スクリーンショット 2022-11-18 11.34.56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/a9ec4866-5c19-898a-8f5a-9014dbf01d52.png)
「既存のポリシーを直接アタッチ」を選択し、適切なポリシーを選択します。
ここでは[AmazonS3FullAccess」を選択しました。
選択が完了したら「次のステップ」をクリックします。
![スクリーンショット 2022-11-18 11.35.56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/dac93836-9152-75fe-615d-0a55892e7098.png)
必要に応じてタグを入力し、「次のステップ」をクリックします。
![スクリーンショット 2022-11-18 11.36.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/cef67fbf-e759-86b5-558c-031e32e0ad5e.png)
確認画面が表示されるので、問題がなければ「ユーザーの作成」をクリックします。
![スクリーンショット 2022-11-18 11.36.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/906a0f78-249e-44ff-0526-8b96066f99a2.png)
ユーザの作成が完了すると、アクセスキーID、シークレットアクセスキーが表示されるので、メモしておきます。
![スクリーンショット 2022-11-18 11.36.43.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/2617f4d7-43e1-0e0b-a6f8-543235ada0fc.png)

# 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のデータ型へのマッピングの詳細はについては、こちらのマニュアルに記載があります。

https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/data-type-mapping-parquet.html#GUID-58CC798B-A154-4C1F-840B-541CB5CFA3DF

作成した外部表内のレコード数を確認してみます。

```
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プロシージャ](https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/dbms-cloud-subprograms.html#GUID-D23C5CEF-770C-436D-B856-4C6C318FA045)
・[DBMS_CLOUD.LIST_OBJECTSファンクション](https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/dbms-cloud-subprograms.html#GUID-D23C5CEF-770C-436D-B856-4C6C318FA045)
・[Avro、ORCまたはParquetファイル用のCREATE_EXTERNAL_TABLEプロシージャ](https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/dbms-cloud-subprograms.html#GUID-D23C5CEF-770C-436D-B856-4C6C318FA045)
・[DBMS_CLOUD.VALIDATE_EXTERNAL_TABLEプロシージャ](https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/dbms-cloud-subprograms.html#GUID-604173CA-1428-486D-99F2-54BE7F8E3B75)
・[DBMS_CLOUD Avro、ORCおよびParquetのサポート](https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/dbms-cloud-avro-orc-parquet-options.html#GUID-88CEADD7-A20E-4A91-A961-F5F9EE935BB5)