Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.
# Configura la funzionalità Oracle UTL\_FILE su Aurora, compatibile con PostgreSQL
*Rakesh Raghav e anuradha chintha, Amazon Web Services*
## Riepilogo
Durante il tuo percorso di migrazione da Oracle ad Amazon Aurora PostgreSQL Compatible Edition sul cloud Amazon Web Services (AWS), potresti incontrare diverse sfide. Ad esempio, la migrazione di codice che si basa sull'utilità Oracle è sempre una sfida. `UTL_FILE` In Oracle PL/SQL, il `UTL_FILE` pacchetto viene utilizzato per operazioni sui file, come lettura e scrittura, insieme al sistema operativo sottostante. L'`UTL_FILE`utilità funziona sia per i sistemi server che per quelli client.
Amazon Aurora PostgreSQL Compatible è un'offerta di database gestiti. Per questo motivo, non è possibile accedere ai file sul server del database. Questo modello illustra l'integrazione tra Amazon Simple Storage Service (Amazon S3) e la compatibilità con Amazon Aurora PostgreSQL per ottenere un sottoinsieme di funzionalità. `UTL_FILE` Grazie a questa integrazione, possiamo creare e consumare file senza utilizzare strumenti o servizi di estrazione, trasformazione e caricamento (ETL) di terze parti.
Facoltativamente, puoi configurare il CloudWatch monitoraggio di Amazon e le notifiche Amazon SNS.
Consigliamo di testare a fondo questa soluzione prima di implementarla in un ambiente di produzione.
## Prerequisiti e limitazioni
**Prerequisiti**
+ Un account AWS attivo
+ Esperienza in AWS Database Migration Service (AWS DMS)
+ Esperienza nella codifica PL/pgSQL
+ Un cluster compatibile con Amazon Aurora PostgreSQL
+ Un bucket S3
**Limitazioni**
Questo modello non fornisce la funzionalità necessaria per sostituire l'utilità Oracle. `UTL_FILE` Tuttavia, i passaggi e il codice di esempio possono essere ulteriormente migliorati per raggiungere gli obiettivi di modernizzazione del database.
**Versioni del prodotto**
+ Amazon Aurora versione 11.9 compatibile con PostgreSQL
## Architecture
**Stack tecnologico Target**
+ Compatibile con Amazon Aurora PostgreSQL
+ Amazon CloudWatch
+ Amazon Simple Notification Service (Amazon SNS)
+ Simple Storage Service (Amazon S3)
**Architettura Target**
Il diagramma seguente mostra una rappresentazione di alto livello della soluzione.
1. I file vengono caricati dall'applicazione nel bucket S3.
1. L'`aws_s3`estensione accede ai dati, utilizzando PL/pgSQL, e carica i dati su Aurora PostgreSQL Compatible.
## Tools (Strumenti)
+ Compatibile con [Amazon Aurora PostgreSQL — Amazon Aurora PostgreSQL Compatible](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html) Edition è un motore di database relazionale completamente gestito, compatibile con PostgreSQL e conforme agli ACID. Combina la velocità e l'affidabilità dei database commerciali di fascia alta con l'economicità dei database open source.
+ [AWS CLI — L'](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html)AWS Command Line Interface (AWS CLI) è uno strumento unificato per gestire i servizi AWS. Con un solo strumento da scaricare e configurare, puoi controllare più servizi AWS dalla riga di comando e automatizzarli tramite script.
+ [Amazon CloudWatch](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/WhatIsCloudWatch.html): Amazon CloudWatch monitora le risorse e l'utilizzo di Amazon S3.
+ [Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html) — Amazon Simple Storage Service (Amazon S3) è uno storage per Internet. In questo modello, Amazon S3 fornisce un livello di storage per ricevere e archiviare file per il consumo e la trasmissione da e verso il cluster Aurora compatibile con PostgreSQL.
+ [aws\_s3](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html#aws_s3.table_import_from_s3) — L'estensione `aws_s3` integra la compatibilità con Amazon S3 e Aurora PostgreSQL.
+ [Amazon SNS — Amazon Simple](https://docs.aws.amazon.com/sns/latest/dg/welcome.html) Notification Service (Amazon SNS) coordina e gestisce la consegna o l'invio di messaggi tra editori e clienti. In questo modello, Amazon SNS viene utilizzato per inviare notifiche.
+ [pgAdmin](https://www.pgadmin.org/docs/) — pgAdmin è uno strumento di gestione open source per Postgres. pgAdmin 4 fornisce un'interfaccia grafica per la creazione, la manutenzione e l'utilizzo di oggetti di database.
**Codice**
Per ottenere la funzionalità richiesta, il pattern crea più funzioni con denominazione simile a. `UTL_FILE` La sezione *Informazioni aggiuntive* contiene il codice base per queste funzioni.
Nel codice, sostituiscilo `testaurorabucket` con il nome del bucket S3 di test. Sostituisci `us-east-1` con la regione AWS in cui si trova il bucket S3 di test.
## Epiche
### Integra la compatibilità con Amazon S3 e Aurora PostgreSQL
| Operazione | Description | Competenze richieste |
| --- | --- | --- |
| Configura le politiche IAM. | Crea policy AWS Identity and Access Management (IAM) che garantiscono l'accesso al bucket S3 e agli oggetti in esso contenuti. Per il codice, consulta la sezione *Informazioni aggiuntive*. | Amministratore AWS, DBA |
| Aggiungi i ruoli di accesso di Amazon S3 ad Aurora PostgreSQL. | Crea due ruoli IAM: un ruolo per l'accesso in lettura e un ruolo per l'accesso in scrittura ad Amazon S3. Collega i due ruoli al cluster compatibile con Aurora PostgreSQL: [See the AWS documentation website for more details](http://docs.aws.amazon.com/it_it/prescriptive-guidance/latest/patterns/set-up-oracle-utl_file-functionality-on-aurora-postgresql-compatible.html)
[Per ulteriori informazioni, consulta la documentazione compatibile con Aurora PostgreSQL sull'importazione [e](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PostgreSQL.S3Import.html) l'esportazione di dati su Amazon S3.](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/postgresql-s3-export.html) | Amministratore AWS, DBA |
### Configura le estensioni in Aurora, compatibile con PostgreSQL
| Operazione | Description | Competenze richieste |
| --- | --- | --- |
| Crea l'estensione aws\_commons. | L'`aws_commons`estensione è una dipendenza dell'estensione. `aws_s3` | DBA, Sviluppatore |
| Crea l'estensione aws\_s3. | L'`aws_s3`estensione interagisce con Amazon S3. | DBA, Sviluppatore |
### Convalida l'integrazione compatibile con Amazon S3 e Aurora PostgreSQL
| Operazione | Description | Competenze richieste |
| --- | --- | --- |
| Prova a importare file da Amazon S3 in Aurora PostgreSQL. | Per testare l'importazione di file in Aurora PostgreSQL compatibile, crea un file CSV di esempio e caricalo nel bucket S3. Crea una definizione di tabella basata sul file CSV e carica il file nella tabella utilizzando la funzione. `aws_s3.table_import_from_s3` | DBA, Sviluppatore |
| Prova a esportare file da Aurora PostgreSQL ad Amazon S3. | Per testare l'esportazione di file da Aurora PostgreSQL compatibile, crea una tabella di test, popolala con dati, quindi esporta i dati utilizzando la funzione. `aws_s3.query_export_to_s3` | DBA, Sviluppatore |
### Per imitare l'utilità UTL\_FILE, create funzioni wrapper
| Operazione | Description | Competenze richieste |
| --- | --- | --- |
| Crea lo schema utl\_file\_utility. | Lo schema mantiene unite le funzioni del wrapper. Per creare lo schema, esegui il comando seguente.CREATE SCHEMA utl_file_utility;
| DBA, Sviluppatore |
| Crea il tipo file\_type. | Per creare il `file_type` tipo, utilizzate il codice seguente.CREATE TYPE utl_file_utility.file_type AS (
p_path character varying(30),
p_file_name character varying
);
| DBA/Sviluppatore |
| Crea la funzione init. | La `init` funzione inizializza una variabile comune come o. `bucket` `region` Per il codice, consultate la sezione *Informazioni aggiuntive*. | DBA/Sviluppatore |
| Crea le funzioni wrapper. | Crea le funzioni `fopen` wrapper e. `put_line` `fclose` Per il codice, vedere la sezione *Informazioni aggiuntive*. | DBA, Sviluppatore |
### Prova le funzioni del wrapper
| Operazione | Description | Competenze richieste |
| --- | --- | --- |
| Prova le funzioni del wrapper in modalità scrittura. | *Per testare le funzioni del wrapper in modalità scrittura, utilizzate il codice fornito nella sezione Informazioni aggiuntive.* | DBA, Sviluppatore |
| Prova le funzioni del wrapper in modalità append. | *Per testare le funzioni del wrapper in modalità di aggiunta, utilizzate il codice fornito nella sezione Informazioni aggiuntive.* | DBA, Sviluppatore |
## Risorse correlate
+ [Integrazione con 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)
## Informazioni aggiuntive
**Configura le politiche IAM**
Crea le seguenti politiche.
|
|
| Nome policy | 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"
]
}
]
} |
**Crea la funzione init**
Per inizializzare variabili comuni, come `bucket` o`region`, create la `init` funzione utilizzando il codice seguente.
```
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$;
```
**Create le funzioni wrapper**
Crea le funzioni `fopen``put_line`, e `fclose` wrapper.
*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$;
```
*chiudere*
```
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$;
```
**Metti alla prova le tue funzioni di configurazione e wrapper**
Usa i seguenti blocchi di codice anonimi per testare la tua configurazione.
*Prova la modalità di scrittura*
Il codice seguente scrive un file denominato `s3inttest` nel 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;
$$
```
*Prova la modalità di aggiunta*
Il codice seguente aggiunge righe al `s3inttest` file creato nel test precedente.
```
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;
$$
```
**Notifiche Amazon SNS**
Facoltativamente, puoi configurare il CloudWatch monitoraggio di Amazon e le notifiche Amazon SNS sul bucket S3. Per ulteriori informazioni, consulta [Monitoraggio di Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/monitoring-overview.html) e [Configurazione delle notifiche Amazon SNS](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/US_SetupSNS.html).