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

12665 ワード

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サンプルを動かしてみた。を参考に 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つ目のバケット（入力用）の「オブジェクト・イベントの出力」ボックスにチェックを入れます。

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への接続パスワードを格納するシークレット作成します。

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

1.ボールトの作成

OCI Webコンソールから[セキュリティ] > [ボールト] で「ボールトの作成」を選択

2.キーの作成

作成したボールトに対して「キーの作成」からキーを作成します。

3.DBユーザのパスワードのシークレットを作成

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

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

ファンクションのデプロイ

ファンクション・アプリケーションの作成

Webコンソールまたは、CLIでアプリケーションを作成

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

ファンクションのデプロイ

すべてのファイルは Githubより取得可能です。

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 コンソール > アプリケーション統合 > イベント・サービスに移動

[ルールの作成］をクリック
- 表示名・説明を入力
- ルール条件に以下3条件を入力
  - イベント・タイプ , サービス名 = Object Storage, イベント・タイプ = Object - Create
  - 属性, 属性名 = compartmentName, 属性値 = <コンパートメント名>
  - 属性, 属性名 = bucketName, 属性値 = <入力バケット名>
- アクション
  - アクション・タイプ = ファンクション
  - ファンクション・コンパートメント = <コンパートメント名>
  - 作成した、ファンクション・アプリケーション、ファンクションを指定

動作確認

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へのロード自動化ができた。

参考情報

Author And Source

この問題について([OCI] Oracle FunctionsからAutonomous DBに接続してPLSQL ストアドプロシージャを実行してみた。), 我々は、より多くの情報をここで見つけました https://qiita.com/kenwatan/items/7b8058f1b1b9c3efb06a

著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。

Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .