翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。 # Aurora PostgreSQL-Compatible で Oracle UTL\$1FILE 機能をセットアップする *Rakesh Raghav と anuradha chintha、Amazon Web Services* ## 概要 Oracle から Amazon Web Services (AWS) クラウド上の Amazon Aurora PostgreSQL-Compatible エディションへの移行の一環として、複数の課題に直面する場合があります。たとえば、Oracle の `UTL_FILE` ユーティリティに依存するコードの移行は常に課題です。Oracle PL/SQL では、`UTL_FILE` パッケージは基盤となるオペレーティングシステムと連携して、読み取りや書き込みなどのファイル操作に使用されます。この `UTL_FILE` ユーティリティは、サーバーマシンシステムとクライアントマシンシステムの両方で動作します。  Amazon Aurora PostgreSQL-Compatible は、マネージドデータベースサービスです。このため、データベースサーバー上のファイルにアクセスすることはできません。このパターンでは、Amazon Simple Storage Service (Amazon S3) と Amazon Aurora PostgreSQL-Compatible を統合して、`UTL_FILE` 機能のサブセットを実現する手順を示しています。この統合により、サードパーティの抽出、変換、ロード (ETL) ツールやサービスを使用せずにファイルを作成して利用できます。 オプションで Amazon CloudWatch によるモニタリングおよび Amazon SNS 通知を設定できます。 本稼働環境に実装する前に、このソリューションを徹底的にテストすることをお勧めします。 ## 前提条件と制限事項 **前提条件** + アクティブなAWS アカウント + AWS Database Migration Service (AWS DMS) 専門知識 + PL/pgSQL コーディングに関する専門知識 + Amazon Aurora PostgreSQL-Compatible クラスター + S3 バケット **制限事項** このパターンには Oracle `UTL_FILE` ユーティリティの代わりとなる機能はありません。ただし、手順とサンプルコードをさらに拡張して、データベースのモダナイゼーション目標を達成することはできます。 **製品バージョン** + Amazon Aurora PostgreSQL-Compatible エディション 11.9 ## アーキテクチャ **ターゲットテクノロジースタック** + Amazon Aurora PostgreSQL-Compatible + Amazon CloudWatch + Amazon Simple Notification Service (Amazon SNS) + Amazon S3 **ターゲットアーキテクチャ** 次の図はソリューションの概要を示しています。 ![\[データファイルは S3 バケットにアップロードされ、aws_s3 拡張機能を使用して処理された後、Aurora インスタンスに送信されます。\]](http://docs.aws.amazon.com/ja_jp/prescriptive-guidance/latest/patterns/images/pattern-img/3aeecd46-1f87-41f9-a9cd-f8181f92e83f/images/4a6c5f5c-58fb-4355-b243-d09a15c1cec6.png) 1. ファイルはアプリケーションから S3 バケットにアップロードされます。 1. `aws_s3` 拡張機能は PL/pgSQL を使用してデータにアクセスし、そのデータを Aurora PostgreSQL-Compatible にアップロードします。 ## ツール + [Amazon Aurora PostgreSQL-Compatible](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html) – Amazon Aurora PostgreSQL-Compatible エディションは、フルマネージド型で PostgreSQL 互換の ACID 準拠リレーショナルデータベースエンジンです。ハイエンドの商用データベースのスピードおよび信頼性と、オープンソースデータベースのシンプルさとコスト効率を併せ持っています。 + [ CLI](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html) – AWS コマンドラインインターフェイス (AWS CLI) は、AWS のサービスを管理するための統合ツールです。ダウンロードおよび構成用の単一のツールのみを使用して、コマンドラインから複数の AWS サービスを制御し、スクリプトを使用してこれらを自動化することができます。 + [Amazon CloudWatch](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/WhatIsCloudWatch.html) — Amazon CloudWatch は Amazon S3 のリソースと使用状況をモニタリングします。 + 「[Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html)」— Amazon Simple Storage Service (Amazon S3)は、インターネット用のストレージです。このパターンでは、Amazon S3 は Aurora PostgreSQL-Compatible クラスターとの間で使用および送信するファイルを受信および保存するためのストレージレイヤーを提供します。 + [\$1s3](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html#aws_s3.table_import_from_s3) — `aws_s3` 拡張機能は Amazon S3 と Aurora PostgreSQL-Compatible を統合します。 + 「[Amazon SNS](https://docs.aws.amazon.com/sns/latest/dg/welcome.html)」 — Amazon Simple Notification Service (Amazon SNS)は、パブリッシャーやクライアントの間のメッセージ配信や送信を調整および管理します。このパターンでは、Amazon SNS を使用して通知を送信します。 + [pgAdmin](https://www.pgadmin.org/docs/) — pgAdmin は Postgres 用のオープンソース管理ツールです。pgAdmin 4 は、データベースオブジェクトを作成、管理、および使用するためのグラフィカルインターフェイスを提供します。 **Code** 必要な機能を実現するために、このパターンは `UTL_FILE` に類似した名前の関数を複数作成します。「*追加情報*」セクションには、これらの関数のコードベースが含まれています。 コードでは、`testaurorabucket` をテストの S3 バケットの名前に置き換えます。`us-east-1` については、テストの S3 バケットがある AWS リージョンに置き換えます。 ## エピック ### Amazon S3 と Aurora PostgreSQL-Compatible を統合する | タスク | 説明 | 必要なスキル | | --- | --- | --- | | IAM ポリシーを設定する。 | S3 バケットとその中のオブジェクトへのアクセス権を 付与する AWS Identity and Access Management (IAM) ポリシーを作成します。コードについては、「*追加情報*」セクションを参照してください。 | AWS 管理者、データベース管理者 | | Amazon S3 アクセスロールを Aurora PostgreSQL に追加します。 | 2 つの IAM ロールを作成します。1 つは Amazon S3 への読み取りアクセス用で、もう 1 つは書き込みアクセス用です。2 つのロールを Aurora PostgreSQL-Compatible クラスターにアタッチします。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/prescriptive-guidance/latest/patterns/set-up-oracle-utl_file-functionality-on-aurora-postgresql-compatible.html)詳細については、Amazon S3 へのデータの[インポート](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PostgreSQL.S3Import.html)と[エクスポート](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/postgresql-s3-export.html)に関する「Aurora PostgreSQL-Compatible ドキュメント」を参照してください。 | AWS 管理者、データベース管理者 | ### Aurora PostgreSQL-Compatible の拡張機能をセットアップする | タスク | 説明 | 必要なスキル | | --- | --- | --- | | aws\$1commons 拡張機能を作成します。 | `aws_commons` 拡張機能は、`aws_s3` 拡張機能の依存関係です。 | DBA、開発者 | | aws\$1s3 拡張機能を作成します。 | `aws_s3` 拡張機能は Amazon S3 と相互作用します。 | DBA、開発者 | ### Amazon S3 と Aurora PostgreSQL-Compatible の統合を検証する | タスク | 説明 | 必要なスキル | | --- | --- | --- | | Amazon S3 から Aurora PostgreSQL へのデータインポートをテストする | Aurora PostgreSQL-Compatible へのファイルのインポートをテストするには、サンプル CSV ファイルを作成して S3 バケットにアップロードします。CSV ファイルに基づいてテーブル定義を作成し、`aws_s3.table_import_from_s3` 関数を使用してファイルをテーブルに読み込みます。 | DBA、開発者 | | Aurora PostgreSQL から Amazon S3 へのファイルのエクスポートをテストする。 | Aurora PostgreSQL-Compatible からのファイルのエクスポートをテストするには、テストテーブルを作成してデータを入力し、`aws_s3.query_export_to_s3` 関数を使用してデータをエクスポートします。 | DBA、開発者 | ### UTL\$1FILE ユーティリティを模倣するためにラッパー関数を作成するには | タスク | 説明 | 必要なスキル | | --- | --- | --- | | utl\$1file\$1utility スキーマを作成します。 | このスキーマはラッパー関数をまとめて維持します。スキーマを作成するには、次のコマンドを実行します。
CREATE SCHEMA utl_file_utility;
| DBA、開発者 | | file\$1type タイプを作成します。 | `file_type` タイプを作成数には、以下のコードを使用します。
CREATE TYPE utl_file_utility.file_type AS (
    p_path character varying(30),
    p_file_name character varying
);


| DBA/開発者 | | init 関数を作成します。 | `init` 関数は、`bucket` や `region` などの共通変数を初期化します。コードについては、「*追加情報*」セクションを参照してください。 | DBA/開発者 | | ラッパー関数を作成する。 | ラッパー関数 `fopen`、`put_line`、および `fclose` を作成します。コードについては、「*追加情報*」セクションを参照してください。 | DBA、開発者 | ### ラッパー関数をテストする | タスク | 説明 | 必要なスキル | | --- | --- | --- | | ラッパー関数を書き込みモードでテストします。 | ラッパー関数を書き込みモードでテストするには、「*追加情報*」セクションに記載されているコードを使用してください。 | DBA、開発者 | | アペンドモードでラッパー関数をテストします。 | アペンドモードでラッパー関数をテストするには、「*追加情報*」セクションに記載されているコードを使用してください。 | DBA、開発者 | ## 関連リソース + 「[Amazon S3 統合](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PostgreSQL.S3Import.html)」 + [Amazon S3](https://aws.amazon.com/s3/) + [Aurora](https://aws.amazon.com/rds/aurora/?nc2=h_ql_prod_db_aa&aurora-whats-new.sort-by=item.additionalFields.postDateTime&aurora-whats-new.sort-order=desc) + [Amazon CloudWatch](https://aws.amazon.com/cloudwatch/) + [Amazon SNS](https://aws.amazon.com/sns/?nc2=h_ql_prod_ap_sns&whats-new-cards.sort-by=item.additionalFields.postDateTime&whats-new-cards.sort-order=desc) ## 追加情報 **IAM ポリシーを設定する** 次のポリシーを作成します。 | | | ポリシー名 | JSON | | --- |--- | | S3IntRead | 
{
    "Version": "2012-10-17",		 	 	 
    "Statement": [
        {
            "Sid": "S3integrationtest",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
         "arn:aws:s3:::testaurorabucket/*",
         "arn:aws:s3:::testaurorabucket"
            ]
        }
    ]
}
| | S3IntWrite | 
{
    "Version": "2012-10-17",		 	 	 
    "Statement": [
        {
            "Sid": "S3integrationtest",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",                
                "s3:ListBucket"
            ],
            "Resource": [                "arn:aws:s3:::testaurorabucket/*",                "arn:aws:s3:::testaurorabucket"
            ]
        }
    ]
}
| **init 関数を作成する** `bucket` や `region` などの共通変数を初期化するには、次のコードを使用して `init` 関数を作成します。 ``` CREATE OR REPLACE FUNCTION utl_file_utility.init(     )     RETURNS void     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ BEGIN       perform set_config       ( format( '%s.%s','UTL_FILE_UTILITY', 'region' )       , 'us-east-1'::text       , false );       perform set_config       ( format( '%s.%s','UTL_FILE_UTILITY', 's3bucket' )       , 'testaurorabucket'::text       , false ); END; $BODY$; ``` **ラッパー関数を作成する** ラッパー関数 `fopen`、`put_line`、`fclose` を作成します。 *fopen* ``` CREATE OR REPLACE FUNCTION utl_file_utility.fopen(     p_file_name character varying,     p_path character varying,     p_mode character DEFAULT 'W'::bpchar,     OUT p_file_type utl_file_utility.file_type)     RETURNS utl_file_utility.file_type     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ declare     v_sql character varying;     v_cnt_stat integer;     v_cnt integer;     v_tabname character varying;     v_filewithpath character varying;     v_region character varying;     v_bucket character varying; BEGIN     /*initialize common variable */     PERFORM utl_file_utility.init();     v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );     v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );         /* set tabname*/     v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );     v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;     raise notice 'v_bucket %, v_filewithpath % , v_region %', v_bucket,v_filewithpath, v_region;         /* APPEND MODE HANDLING; RETURN EXISTING FILE DETAILS IF PRESENT ELSE CREATE AN EMPTY FILE */     IF p_mode = 'A' THEN         v_sql := concat_ws('','create temp table if not exists ', v_tabname,' (col1 text)');         execute v_sql;         begin         PERFORM aws_s3.table_import_from_s3             ( v_tabname,             '',               'DELIMITER AS ''#''',             aws_commons.create_s3_uri             (     v_bucket,                 v_filewithpath ,                 v_region)             );         exception             when others then              raise notice 'File load issue ,%',sqlerrm;              raise;         end;         execute concat_ws('','select count(*) from ',v_tabname) into v_cnt;         IF v_cnt > 0         then             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;         else                     PERFORM aws_s3.query_export_to_s3('select ''''',                             aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)                                           );             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;                 end if;         v_sql := concat_ws('','drop table ', v_tabname);                 execute v_sql;                 ELSEIF p_mode = 'W' THEN             PERFORM aws_s3.query_export_to_s3('select ''''',                             aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)                                           );             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;     END IF;         EXCEPTION         when others then             p_file_type.p_path := p_path;             p_file_type.p_file_name := p_file_name;             raise notice 'fopenerror,%',sqlerrm;             raise; END; $BODY$; ``` *put\$1line* ``` CREATE OR REPLACE FUNCTION utl_file_utility.put_line(     p_file_name character varying,     p_path character varying,     p_line text,     p_flag character DEFAULT 'W'::bpchar)     RETURNS boolean     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ /************************************************************************** * Write line, p_line in windows format to file, p_fp - with carriage return * added before new line. **************************************************************************/ declare     v_sql varchar;     v_ins_sql varchar;     v_cnt INTEGER;     v_filewithpath character varying;     v_tabname  character varying;     v_bucket character varying;     v_region character varying;     BEGIN  PERFORM utl_file_utility.init(); /* check if temp table already exist */  v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );  v_sql := concat_ws('','select count(1) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace where n.nspname like ''pg_temp_%'''                          ,' AND pg_catalog.pg_table_is_visible(c.oid) AND Upper(relname) = Upper( '''                          ,  v_tabname ,''' ) ');    execute v_sql into v_cnt;     IF v_cnt = 0 THEN          v_sql := concat_ws('','create temp table ',v_tabname,' (col text)');         execute v_sql;         /* CHECK IF APPEND MODE */         IF upper(p_flag) = 'A' THEN             PERFORM utl_file_utility.init();                                     v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );             v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );                         /* set tabname*/                         v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;                                     begin                PERFORM aws_s3.table_import_from_s3                      ( v_tabname,                           '',                          'DELIMITER AS ''#''',                         aws_commons.create_s3_uri                            ( v_bucket,                                v_filewithpath,                                v_region    )                     );             exception                 when others then                     raise notice  'Error Message : %',sqlerrm;                     raise;             end;             END IF;         END IF;     /* INSERT INTO TEMP TABLE */                   v_ins_sql := concat_ws('','insert into ',v_tabname,' values(''',p_line,''')');     execute v_ins_sql;     RETURN TRUE;     exception             when others then                 raise notice  'Error Message : %',sqlerrm;                 raise; END; $BODY$; ``` *fclose* ``` CREATE OR REPLACE FUNCTION utl_file_utility.fclose(     p_file_name character varying,     p_path character varying)     RETURNS boolean     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ DECLARE     v_filewithpath character varying;     v_bucket character varying;     v_region character varying;     v_tabname character varying; v_sql character varying; BEGIN       PERFORM utl_file_utility.init();       v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );     v_bucket :=  current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );     v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );     v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;     raise notice 'v_bucket %, v_filewithpath % , v_region %', v_bucket,v_filewithpath, v_region ;         /* exporting to s3 */     perform aws_s3.query_export_to_s3         (concat_ws('','select * from ',v_tabname,'  order by ctid asc'),             aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)         );    v_sql := concat_ws('','drop table ', v_tabname);     execute v_sql;        RETURN TRUE; EXCEPTION        when others then      raise notice 'error fclose %',sqlerrm;      RAISE; END; $BODY$; ``` **設定とラッパー関数をテストする** 次の匿名コードブロックを使用して、設定をテストします。 *書き込みモードをテストする* 次のコードは、S3 バケットの `s3inttest` という名前のファイルを記述します。 ``` do $$ declare l_file_name varchar := 's3inttest' ; l_path varchar := 'integration_test' ; l_mode char(1) := 'W'; l_fs utl_file_utility.file_type ; l_status boolean; begin select * from utl_file_utility.fopen( l_file_name, l_path , l_mode ) into l_fs ; raise notice 'fopen : l_fs : %', l_fs; select * from utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket: for test purpose', l_mode ) into l_status ; raise notice 'put_line : l_status %', l_status; select * from utl_file_utility.fclose( l_file_name , l_path ) into l_status ; raise notice 'fclose : l_status %', l_status; end; $$ ``` *アペンドモードをテストする* 次のコードは、前のテストで作成した `s3inttest` ファイルに行を追加します。 ``` do $$ declare l_file_name varchar := 's3inttest' ; l_path varchar := 'integration_test' ; l_mode char(1) := 'A'; l_fs utl_file_utility.file_type ; l_status boolean; begin select * from utl_file_utility.fopen( l_file_name, l_path , l_mode ) into l_fs ; raise notice 'fopen : l_fs : %', l_fs; select * from utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket: for test purpose : append 1', l_mode ) into l_status ; raise notice 'put_line : l_status %', l_status; select * from utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket : for test purpose : append 2', l_mode ) into l_status ; raise notice 'put_line : l_status %', l_status; select * from utl_file_utility.fclose( l_file_name , l_path ) into l_status ; raise notice 'fclose : l_status %', l_status; end; $$ ``` **Amazon SNSの通知** オプションで Amazon CloudWatch によるモニタリングと Amazon SNS 通知を S3 バケットに設定できます。詳細については、「[Amazon S3 をモニタリングする](https://docs.aws.amazon.com/AmazonS3/latest/userguide/monitoring-overview.html)」と「[Amazon SNS 通知のセットアップ](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/US_SetupSNS.html)」を参照してください。