翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。
Aurora PostgreSQL-Compatible で Oracle UTL_FILE 機能をセットアップする
作成者: Rakesh Raghav (AWS) と anuradha chintha (AWS)
概要
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拡張機能は PL/pgSQL を使用してデータにアクセスし、そのデータを Aurora PostgreSQL-Compatible にアップロードします。
ツール
Amazon Aurora PostgreSQL-Compatible – Amazon Aurora PostgreSQL-Compatible エディションは、フルマネージド型で PostgreSQL 互換の ACID 準拠リレーショナルデータベースエンジンです。ハイエンドの商用データベースのスピードおよび信頼性と、オープンソースデータベースのシンプルさとコスト効率を併せ持っています。
CLI – AWS コマンドラインインターフェイス (AWS CLI) は、AWS のサービスを管理するための統合ツールです。ダウンロードおよび構成用の単一のツールのみを使用して、コマンドラインから複数の AWS サービスを制御し、スクリプトを使用してこれらを自動化することができます。
Amazon CloudWatch — Amazon CloudWatch は Amazon S3 のリソースと使用状況をモニタリングします。
「Amazon S3」— Amazon Simple Storage Service (Amazon S3)は、インターネット用のストレージです。このパターンでは、Amazon S3 は Aurora PostgreSQL-Compatible クラスターとの間で使用および送信するファイルを受信および保存するためのストレージレイヤーを提供します。
_s3 —
aws_s3拡張機能は Amazon S3 と Aurora PostgreSQL-Compatible を統合します。「Amazon SNS」 — Amazon Simple Notification Service (Amazon SNS)は、パブリッシャーやクライアントの間のメッセージ配信や送信を調整および管理します。このパターンでは、Amazon SNS を使用して通知を送信します。
pgAdmin
— pgAdmin は Postgres 用のオープンソース管理ツールです。pgAdmin 4 は、データベースオブジェクトを作成、管理、および使用するためのグラフィカルインターフェイスを提供します。
コード
必要な機能を実現するために、このパターンは UTL_FILE に類似した名前の関数を複数作成します。「追加情報」セクションには、これらの関数のコードベースが含まれています。
コードでは、testaurorabucket をテストの S3 バケットの名前に置き換えます。us-east-1 については、テストの S3 バケットがある AWS リージョンに置き換えます。
エピック
| タスク | 説明 | 必要なスキル |
|---|---|---|
| IAM ポリシーを設定する。 | S3 バケットとその中のオブジェクトへのアクセス権を 付与する AWS Identity and Access Management (IAM) ポリシーを作成します。コードについては、「追加情報」セクションを参照してください。 | AWS 管理者、DBA |
| Amazon S3 アクセスロールを Aurora PostgreSQL に追加します。 | 2 つの IAM ロールを作成します。1 つは Amazon S3 への読み取りアクセス用で、もう 1 つは書き込みアクセス用です。2 つのロールを Aurora PostgreSQL-Compatible クラスターにアタッチします。
詳細については、Amazon S3 へのデータのインポートとエクスポートに関する「Aurora PostgreSQL-Compatible ドキュメント」を参照してください。 | AWS 管理者、DBA |
| タスク | 説明 | 必要なスキル |
|---|---|---|
| aws_commons 拡張機能を作成します。 |
| DBA、開発者 |
| aws_s3 拡張機能を作成します。 |
| DBA、開発者 |
| タスク | 説明 | 必要なスキル |
|---|---|---|
| Amazon S3 から Aurora PostgreSQL へのデータインポートをテストする | Aurora PostgreSQL-Compatible へのファイルのインポートをテストするには、サンプル CSV ファイルを作成して S3 バケットにアップロードします。CSV ファイルに基づいてテーブル定義を作成し、 | DBA、開発者 |
| Aurora PostgreSQL から Amazon S3 へのファイルのエクスポートをテストする。 | Aurora PostgreSQL-Compatible からのファイルのエクスポートをテストするには、テストテーブルを作成してデータを入力し、 | DBA、開発者 |
| タスク | 説明 | 必要なスキル |
|---|---|---|
| utl_file_utility スキーマを作成します。 | このスキーマはラッパー関数をまとめて維持します。スキーマを作成するには、次のコマンドを実行します。 | DBA、開発者 |
| file_type タイプを作成します。 |
| DBA/開発者 |
| init 関数を作成します。 |
| DBA/開発者 |
| ラッパー関数を作成する。 | ラッパー関数 | DBA、開発者 |
| タスク | 説明 | 必要なスキル |
|---|---|---|
| ラッパー関数を書き込みモードでテストします。 | ラッパー関数を書き込みモードでテストするには、「追加情報」セクションに記載されているコードを使用してください。 | DBA、開発者 |
| アペンドモードでラッパー関数をテストします。 | アペンドモードでラッパー関数をテストするには、「追加情報」セクションに記載されているコードを使用してください。 | DBA、開発者 |
関連リソース
追加情報
IAM ポリシーを設定する
次のポリシーを作成します。
ポリシー名 | JSON |
S3IntRead | |
S3IntWrite | |
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_line
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 をモニタリングする」と「Amazon SNS 通知のセットアップ」を参照してください。
