Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris. # Siapkan fungsionalitas Oracle UTL\$1FILE pada Aurora PostgreSQL yang kompatibel *Rakesh Raghav dan anuradha chintha, Amazon Web Services* ## Ringkasan Sebagai bagian dari perjalanan migrasi Anda dari Oracle ke Amazon Aurora PostgreSQL Compatible Edition di Amazon Web Services (AWS) Cloud, Anda mungkin menghadapi beberapa tantangan. Misalnya, memigrasikan kode yang bergantung pada `UTL_FILE` utilitas Oracle selalu menjadi tantangan. Di Oracle PL/SQL, `UTL_FILE` paket ini digunakan untuk operasi file, seperti baca dan tulis, bersama dengan sistem operasi yang mendasarinya. `UTL_FILE`Utilitas ini berfungsi untuk sistem server dan mesin klien.  Amazon Aurora PostgreSQL kompatibel adalah penawaran database terkelola. Karena itu, tidak mungkin mengakses file di server database. Pola ini memandu Anda melalui integrasi Amazon Simple Storage Service (Amazon S3) dan Amazon Aurora PostgreSQL yang kompatibel untuk mencapai subset fungsionalitas. `UTL_FILE` Dengan menggunakan integrasi ini, kita dapat membuat dan menggunakan file tanpa menggunakan alat atau layanan ekstrak, transformasi, dan muat (ETL) pihak ketiga. Secara opsional, Anda dapat mengatur CloudWatch pemantauan Amazon dan notifikasi Amazon SNS. Kami merekomendasikan pengujian solusi ini secara menyeluruh sebelum menerapkannya di lingkungan produksi. ## Prasyarat dan batasan **Prasyarat** + Akun AWS yang aktif + Keahlian AWS Database Migration Service (AWS DMS) + Keahlian dalam PL/pgSQL pengkodean + Cluster yang kompatibel dengan Amazon Aurora PostgreSQL + Ember S3 **Batasan** Pola ini tidak menyediakan fungsionalitas untuk bertindak sebagai pengganti `UTL_FILE` utilitas Oracle. Namun, langkah-langkah dan kode sampel dapat ditingkatkan lebih lanjut untuk mencapai tujuan modernisasi database Anda. **Versi produk** + Amazon Aurora PostgreSQL Edisi 11.9 yang kompatibel ## Arsitektur **Tumpukan teknologi target** + Kompatibel dengan Amazon Aurora PostgreSQL + Amazon CloudWatch + Amazon Simple Notification Service (Amazon SNS) + Amazon S3 **Arsitektur target** Diagram berikut menunjukkan representasi tingkat tinggi dari solusi. ![\[File data diunggah ke bucket S3, diproses menggunakan ekstensi aws_s3, dan dikirim ke instance Aurora.\]](http://docs.aws.amazon.com/id_id/prescriptive-guidance/latest/patterns/images/pattern-img/3aeecd46-1f87-41f9-a9cd-f8181f92e83f/images/4a6c5f5c-58fb-4355-b243-d09a15c1cec6.png) 1. File diunggah dari aplikasi ke dalam ember S3. 1. `aws_s3`Ekstensi mengakses data, menggunakan PL/PGSQL, dan mengunggah data ke Aurora PostgreSQL kompatibel. ## Alat + Kompatibel dengan [Amazon Aurora PostgreSQL — Amazon Aurora PostgreSQL Compatible](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html) Edition adalah mesin basis data relasional yang dikelola sepenuhnya, kompatibel dengan PostgreSQL, dan sesuai dengan Asam. Ini menggabungkan kecepatan dan keandalan database komersial kelas atas dengan efektivitas biaya database sumber terbuka. + [AWS CLI](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html) — AWS Command Line Interface (AWS CLI) Command Line Interface (AWS CLI) adalah alat terpadu untuk mengelola layanan AWS Anda. Dengan hanya satu alat untuk mengunduh dan mengonfigurasi, Anda dapat mengontrol beberapa layanan AWS dari baris perintah dan mengotomatiskannya melalui skrip. + [Amazon CloudWatch](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/WhatIsCloudWatch.html) — Amazon CloudWatch memonitor sumber daya dan penggunaan Amazon S3. + [Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html) - Amazon Simple Storage Service (Amazon S3) Simple Storage Service adalah penyimpanan untuk internet. Dalam pola ini, Amazon S3 menyediakan lapisan penyimpanan untuk menerima dan menyimpan file untuk konsumsi dan transmisi ke dan dari cluster Aurora PostgreSQL yang kompatibel. + [aws\$1s3 - Ekstensi `aws_s3` mengintegrasikan Amazon S3](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html#aws_s3.table_import_from_s3) dan Aurora PostgreSQL kompatibel. + [Amazon SNS](https://docs.aws.amazon.com/sns/latest/dg/welcome.html) - Amazon Simple Notification Service (Amazon SNS) mengoordinasikan dan mengelola pengiriman atau pengiriman pesan antara penerbit dan klien. Dalam pola ini, Amazon SNS digunakan untuk mengirim notifikasi. + [pgAdmin](https://www.pgadmin.org/docs/) - pgAdmin adalah alat manajemen sumber terbuka untuk Postgres. pgAdmin 4 menyediakan antarmuka grafis untuk membuat, memelihara, dan menggunakan objek database. **Kode** Untuk mencapai fungsionalitas yang diperlukan, pola membuat beberapa fungsi dengan penamaan yang mirip dengan`UTL_FILE`. Bagian *Informasi tambahan* berisi basis kode untuk fungsi-fungsi ini. Dalam kode, ganti `testaurorabucket` dengan nama bucket S3 pengujian Anda. Ganti `us-east-1` dengan Wilayah AWS tempat bucket S3 pengujian Anda berada. ## Epik ### Integrasikan Amazon S3 dan Aurora PostgreSQL yang kompatibel | Tugas | Deskripsi | Keterampilan yang dibutuhkan | | --- | --- | --- | | Siapkan kebijakan IAM. | Buat kebijakan AWS Identity and Access Management (IAM) yang memberikan akses ke bucket S3 dan objek di dalamnya. Untuk kode, lihat bagian *Informasi tambahan*. | Administrator AWS, DBA | | Tambahkan peran akses Amazon S3 ke Aurora PostgreSQL. | Buat dua peran IAM: satu peran untuk dibaca dan satu peran untuk akses tulis ke Amazon S3. Lampirkan dua peran ke cluster yang kompatibel dengan Aurora PostgreSQL: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/id_id/prescriptive-guidance/latest/patterns/set-up-oracle-utl_file-functionality-on-aurora-postgresql-compatible.html)[Untuk informasi selengkapnya, lihat dokumentasi yang kompatibel dengan Aurora PostgreSQL tentang [mengimpor dan mengekspor](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PostgreSQL.S3Import.html) data ke Amazon S3.](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/postgresql-s3-export.html) | Administrator AWS, DBA | ### Siapkan ekstensi di Aurora PostgreSQL yang kompatibel | Tugas | Deskripsi | Keterampilan yang dibutuhkan | | --- | --- | --- | | Buat ekstensi aws\$1commons. | `aws_commons`Ekstensi adalah ketergantungan `aws_s3` ekstensi. | DBA, Pengembang | | Buat ekstensi aws\$1s3. | `aws_s3`Ekstensi berinteraksi dengan Amazon S3. | DBA, Pengembang | ### Validasi integrasi yang kompatibel dengan Amazon S3 dan Aurora PostgreSQL | Tugas | Deskripsi | Keterampilan yang dibutuhkan | | --- | --- | --- | | Uji mengimpor file dari Amazon S3 ke Aurora PostgreSQL. | Untuk menguji mengimpor file ke Aurora PostgreSQL yang kompatibel, buat contoh file CSV dan unggah ke bucket S3. Buat definisi tabel berdasarkan file CSV, dan muat file ke dalam tabel dengan menggunakan `aws_s3.table_import_from_s3` fungsi. | DBA, Pengembang | | Uji ekspor file dari Aurora PostgreSQL ke Amazon S3. | Untuk menguji mengekspor file dari Aurora PostgreSQL yang kompatibel, buat tabel uji, isi dengan data, lalu ekspor data dengan menggunakan fungsi. `aws_s3.query_export_to_s3` | DBA, Pengembang | ### Untuk meniru utilitas UTL\$1FILE, buat fungsi pembungkus | Tugas | Deskripsi | Keterampilan yang dibutuhkan | | --- | --- | --- | | Buat skema utl\$1file\$1utility. | Skema menjaga fungsi pembungkus bersama-sama. Untuk membuat skema, jalankan perintah berikut.
CREATE SCHEMA utl_file_utility;
| DBA, Pengembang | | Buat tipe file\$1type. | Untuk membuat `file_type` tipe, gunakan kode berikut.
CREATE TYPE utl_file_utility.file_type AS (
    p_path character varying(30),
    p_file_name character varying
);


| DBA/Pengembang | | Buat fungsi init. | `init`Fungsi menginisialisasi variabel umum seperti `bucket` atau`region`. Untuk kode, lihat bagian *Informasi tambahan*. | DBA/Pengembang | | Buat fungsi pembungkus. | Buat fungsi pembungkus`fopen`,`put_line`, dan`fclose`. Untuk kode, lihat bagian *Informasi tambahan*. | DBA, Pengembang | ### Uji fungsi pembungkus | Tugas | Deskripsi | Keterampilan yang dibutuhkan | | --- | --- | --- | | Uji fungsi pembungkus dalam mode tulis. | Untuk menguji fungsi pembungkus dalam mode tulis, gunakan kode yang disediakan di bagian *Informasi tambahan*. | DBA, Pengembang | | Uji fungsi pembungkus dalam mode append. | Untuk menguji fungsi pembungkus dalam mode append, gunakan kode yang disediakan di bagian *Informasi tambahan*. | DBA, Pengembang | ## Sumber daya terkait + [Integrasi 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) ## Informasi tambahan **Menyiapkan kebijakan IAM** Buat kebijakan berikut. | | | Nama kebijakan | JSON | | --- |--- | | S3 IntRead | 
{
    "Version": "2012-10-17",		 	 	 
    "Statement": [
        {
            "Sid": "S3integrationtest",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
         "arn:aws:s3:::testaurorabucket/*",
         "arn:aws:s3:::testaurorabucket"
            ]
        }
    ]
}
| | S3 IntWrite | 
{
    "Version": "2012-10-17",		 	 	 
    "Statement": [
        {
            "Sid": "S3integrationtest",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",                
                "s3:ListBucket"
            ],
            "Resource": [                "arn:aws:s3:::testaurorabucket/*",                "arn:aws:s3:::testaurorabucket"
            ]
        }
    ]
}
| **Buat fungsi init** Untuk menginisialisasi variabel umum, seperti `bucket` atau`region`, buat `init` fungsi dengan menggunakan kode berikut. ``` 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$; ``` **Buat fungsi pembungkus** Buat fungsi`fopen`,`put_line`, dan `fclose` pembungkus. *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$; ``` **Uji fungsi pengaturan dan pembungkus Anda** Gunakan blok kode anonim berikut untuk menguji pengaturan Anda. *Uji mode tulis* Kode berikut menulis file bernama `s3inttest` dalam bucket S3. ``` 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; $$ ``` *Uji mode append* Kode berikut menambahkan baris ke `s3inttest` file yang dibuat dalam tes sebelumnya. ``` 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; $$ ``` **Pemberitahuan Amazon SNS** Secara opsional, Anda dapat mengatur CloudWatch pemantauan Amazon dan notifikasi Amazon SNS di bucket S3. Untuk informasi selengkapnya, lihat [Memantau Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/monitoring-overview.html) dan [Menyiapkan Pemberitahuan Amazon SNS](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/US_SetupSNS.html).