[OCI] Oracle FunctionsからAutonomous DBに接続してPLSQL ストアドプロシージャを実行してみた。

Object StorageにCSVファイルがUploadされた際にイベント・サービスがOracle Functionsをキックし、Autonomous Databaseにロードするサンプルを動かしてみた。
Autonomous DatabaseへのロードはDBMS_CLOUD.COPY_DATAを呼び出すPL/SQLストアドプロシージャを使用しています。
Databaseの接続には、Oracle Clientを使用し、パスワードの管理にはOCIシークレットを使用し、FunctionはPythonで記載しています。

※ Oracle Functionsとは、Oracle Cloud Infrastructureで提供されるFn Projectのマネージドサービス
※ OCIシークレットとは、パスワードなどリソースにアクセスするために使用する資格情報を安全に一元的に管理できます。

# 作業ステップ

- Oracle Functions実行環境の整備
- Object Storage バケットの作成
- Autonomous Databaseの作成
    - スキーマ、表、プロシージャの作成
- OCIシークレットの作成
- Functionsの作成（デプロイ）
- イベント・ルールの作成
- 動作確認

## Oracle Functions実行環境の整備
Oracle Functionsが利用できるように以下の作業を実施

- 仮想クラウド・ネットワークの作成
- IAMポリシーの作成
- OCIレジストリの作成
- [Cloud Shellを使ってOracle Functionsサンプルを動かしてみた。](https://qiita.com/kenwatan/items/d8fd2426e93a1cfb65f6) を参考に fn shell のセットアップを実施。
- 動的グループの作成
    - ファンクションが他の OCI サービスを使用するために、ポリシーを割り当てる動的グループを作成
    - 一致ルール ALL{resource.type='fnfunc', resource.compartment.id='コンパートメントOCID'}

## Object Storage バケットの作成
Object Storageに2つのバケットを作成
1つ目のバケットは、Autonomous DatabaseにロードするCSVファイルの配置
2つ目のバケットは、処理が完了したファイルの移動場所
例えば「input-bucket」と「processed-bucket」を作成
1つ目のバケット（入力用）の「オブジェクト・イベントの出力」ボックスにチェックを入れます。
![図1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/88f4bfb9-71a8-cb72-b235-38cd79443b4d.jpeg)


#### IAMポリシーの作成または更新
2 つのバケット内のオブジェクトを管理できるように、動的グループにポリシーを割り当て

```
Allow dynamic-group <dynamic-group-name> to manage objects in compartment <compartment-name> where target.bucket.name=‘<input-bucket-name>’
Allow dynamic-group <dynamic-group-name> to manage objects in compartment <compartment-name> where target.bucket.name=‘<processed-bucket-name>’
```

## Autonomous Databaseの作成
接続するAutonomous Databaseを作成
作成したAutonomous DatabaseのOCIDをコピー

#### スキーマオブジェクトの作成
ロード対象の表の作成とロードプロシージャの作成(スキーマ作成・ロール付与は省略)

```DDL.sql
CREATE TABLE funtest
( region varchar2(10),
  col1 number(4),
  col2 number(4),
  col3 number(4)
);

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => '<OCI UserNAME>',
    password => '<Auth Token>'
  );
END;
/

create or replace procedure csvload (file_name in varchar2)
is
BEGIN
 DBMS_CLOUD.COPY_DATA(
    table_name =>'FUNTEST',
    credential_name =>'DEF_CRED_NAME',
    file_uri_list =>file_name,
    format => json_object('delimiter' value ',', 'skipheaders' value '1')
 );
END;
```

####IAMポリシーの作成または更新
Autonomous Databaseの接続クレデンシャルを入手できるように、動的グループにポリシーを割り当て

```
Allow dynamic-group <dynamic-group-name> to use autonomous-databases in compartment <compartment-name> where request.permission='AUTONOMOUS_DATABASE_CONTENT_READ'
```

## DB Userパスワードを格納するOCIシークレットの作成
####シークレット操作用のポリシーの割り当て
以下のサービスレベルポリシーを適用

```
allow service VaultSecret to use vaults in tenancy
allow service VaultSecret to use keys in tenancy
```

####IAMポリシーの作成または更新
シークレットを読み取れるように、動的グループにポリシーを割り当て

```
allow dynamic-group <dynamic-group-name>  to read secret-family in compartment <compartment-name>

allow service objectstorage-<region-name> to manage object-family in compartment <compartment-name>
	例）allow service objectstorage-us-ashburn-1 to manage object-family in compartment <compartment-name>
```

####ボールト、キー、シークレット作成
Autonomous Databaseへの接続パスワードを格納するシークレット作成します。

1. ボールトの作成
2. キーの作成
3. DBユーザのパスワードのシークレットを作成

##### 1.ボールトの作成
OCI Webコンソールから[セキュリティ] > [ボールト] で「ボールトの作成」を選択
![図3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/202142d1-11f3-2b55-c2f9-af99b180c729.png)


##### 2.キーの作成
作成したボールトに対して「キーの作成」からキーを作成します。
![図4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/c8c9b60b-22d3-dd09-a149-3703e7652c5d.png)

##### 3.DBユーザのパスワードのシークレットを作成
![図5.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/f4cfd6f4-001e-1bcd-697f-3beea2dd46f5.jpeg)

- 名前・説明を入力
- 暗号化キー：作成したキー
- シークレット・タイプ：プレーンテキスト
- シークレット・コンテンツ：データベースユーザのパスワード

作成したシークレットのOCIDを記録します。

## ファンクションのデプロイ
#### ファンクション・アプリケーションの作成
Webコンソールまたは、CLIでアプリケーションを作成

```
fn create myapp adb-python-load --annotation oracle.com/oci/subnetIds='["ocid1.subnet.oc1...."]'
```

#### ファンクションのデプロイ
すべてのファイルは [Github](https://github.com/kenwatan/functions_adb_python_load)より取得可能です。

```
fn deploy --app <AP名>
```

ファンクションの動作

- シークレットの取得
- Autonomous Databaseの接続クレデンシャルをダウンロード
- Autonomous Databaseに接続し、ストアドプロシージャを実行（ DBMS_CLOUD.COPY_DATA ）
- ロードしたCSVファイルを作業済みバケットに移動


シークレット取得部分

```
import oci
import base64
import sys

def read_secret_value(secret_client, secret_id):
    response = secret_client.get_secret_bundle(secret_id)
    base64_Secret_content = response.data.secret_bundle_content.content
    base64_secret_bytes = base64_Secret_content.encode('ascii')
    base64_message_bytes = base64.b64decode(base64_secret_bytes)
    secret_content = base64_message_bytes.decode('ascii')
    return secret_content
```

Handler主要部分部分

```
secret_contents = read_secret_value(secret_client, secret_id)

# Create the DB Session Pool
dbpool = cx_Oracle.SessionPool(dbuser, dbpwd, dbsvc, min=1, max=1, encoding="UTF-8", nencoding="UTF-8")

    with dbpool.acquire() as dbconnection:
        with dbconnection.cursor() as dbcursor:
            dbcursor.callproc('csvload',[source_name])
    move_object(signer, namespace, input_bucket, processed_bucket, object_name)
```

#### ファンクションの構成変数のセット
リージョン名、Object Storageバケット名、シークレットOCID、ADBスキーマ、接続サービス名、ADB OCID

```
fn config function <app-name> <function-name> region-name <region名>
fn config function <app-name> <function-name> input-bucket <input bucket name>
fn config function <app-name> <function-name> processed-bucket <processed bucket name>
fn config function <app-name> <function-name> password_id <DB Password Secret OCID>
fn config function <app-name> <function-name> DBUSER <DB user name>
fn config function <app-name> <function-name> DBSVC <DB schema>
fn config function <app-name> <function-name> ADB_OCID <Autonomous DB OCID>
```

## イベント・ルールの作成
Object Storage(入力バケット)にファイルをアップロードしたタイミングでファンクションをトリガーするイベントを設定
OCI コンソール > アプリケーション統合 > イベント・サービス に移動
![図6.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/6f57345f-059c-58ec-7821-e443804e85a3.jpeg)

- [ルールの作成］をクリック
    - 表示名・説明を入力
    - ルール条件に以下3条件を入力
        - イベント・タイプ , サービス名 = Object Storage, イベント・タイプ = Object - Create
        - 属性, 属性名 = compartmentName, 属性値 = <コンパートメント名>
        - 属性, 属性名 = bucketName, 属性値 = <入力バケット名>
    - アクション
        - アクション・タイプ = ファンクション
        - ファンクション・コンパートメント = <コンパートメント名>
        - 作成した、ファンクション・アプリケーション、ファンクションを指定
![図7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/2bf54254-ee0f-4ca3-fd66-7bdee31aa5eb.png)

# 動作確認
input-bucket に file1.csvをアップロード

```functions.log
  INFO: DB wallet has to be generated from ADB  ocid1.autonomousdatabase.oc1.iad.aaaa
  INFO: DB wallet dir content = ['keystore.jks', 'ewallet.p12', 'sqlnet.ora', 'ojdbc.properties', 'truststore.jks', 'tnsnames.ora', 'cwallet.sso', 'README']
  INFO: DB pool created
  INFO - Event ID 63062746-0a2d-2eff-d928-927e982b2d5e received
  INFO - Namespace: XXXXXXX7
  INFO - Object name: file1.csv
  INFO - Bucket name: input-bucket
  INFO - Object file1.csv is read
  INFO - Source name: https://objectstorage.us-ashburn-1.oraclecloud.com/n/XXXXXXX7/b/input-bucket/o/file1.csv
  INFO: CSV LOAD executed in 1.149079079999865 sec
  INFO - Object file1.csv moved to Bucket processed-bucket
  INFO - request execution completed
```

#### ロードの確認
Autonomous Databaseにアクセスし、「FUNTEST」表を検索

```
select * funtest;

"REGION" "COL1"  "COL2"  "COL3"  
"APAC"  " 4" 	" 5" 	" 6" 
"APAC"  "16"    "17"    "18"
"AMER"  "22"    "23"    "24"
"AMER"  "13"    "14"    "15"
```

# おわりに
イベント・サービスとOracle Functionsを使って、Autonomous Databaseに接続し、PL/SQLストアドプロシージャを実行することでObject StorageにUploadされたファイルのAutonomous Databaseへのロード自動化ができた。

# 参考情報

- [Oracle Functionsことはじめ](https://oracle-japan.github.io/paasdocs/documents/faas/oraclefunctions/handson/getting-started/)
- [Autonomous Database ハンズオンラボ（ADB HOL）](https://community.oracle.com/docs/DOC-1034727)
- [はじめてのAutonomous Databaseへのデータロード（Object Storage経由の場合）](https://qiita.com/mikika/items/5323651cb5826a5b495e)
- [Automatically load data from Object Storage into Autonomous Data Warehouse](https://github.com/oracle/oracle-functions-samples/tree/master/oci-load-file-into-adw-python)
- [Cloud Shellを使ってOracle Functionsサンプルを動かしてみた。](https://qiita.com/kenwatan/items/d8fd2426e93a1cfb65f6)
- [[AutonomousDB] Autonomous DatabaseにJSONドキュメントを格納してみた。（ORDS+SODA)](https://qiita.com/kenwatan/items/db9134e1c9bf49315a79)
- [[OCI] OCIシークレットを使ってOracle FunctionsからAutonomous DBに接続してみた。](https://qiita.com/kenwatan/items/5867a06ef6a00749dcf0)
- [Perform PL/SQL Operations with cx_Oracle](https://blogs.oracle.com/oraclemagazine/perform-plsql-operations-with-cx_oracle)