As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá. # Configure a funcionalidade Oracle UTL\$1FILE no Aurora compatível com PostgreSQL *Rakesh Raghav e Anuradha Chintha, Amazon Web Services* ## Resumo Como parte de sua jornada de migração da Oracle para Amazon Aurora Edição Compatível com PostgreSQL na nuvem da Amazon Web Services (AWS), você pode encontrar vários desafios. Por exemplo, migrar código que depende do utilitário `UTL_FILE` do Oracle é sempre um desafio. No Oracle PL/SQL, o pacote `UTL_FILE` é usado para operações de arquivo, como leitura e gravação, em conjunto com o sistema operacional subjacente. O utilitário `UTL_FILE` funciona tanto para sistemas de servidores quanto para máquinas clientes.  O Amazon Aurora compatível com PostgreSQL é uma oferta de banco de dados gerenciado. Por causa disso, não é possível acessar arquivos no servidor do banco de dados. Esse padrão orienta você na integração do Amazon Simple Storage Service (Amazon S3) e o Amazon Aurora compatível com PostgreSQL para obter um subconjunto da funcionalidade `UTL_FILE`. Usando essa integração, podemos criar e consumir arquivos sem usar ferramentas ou serviços de extração, transformação e carregamento (ETL) de terceiros. Opcionalmente, você pode configurar o CloudWatch monitoramento da Amazon e as notificações do Amazon SNS. Recomendamos testar minuciosamente essa solução antes de implementá-la em um ambiente de produção. ## Pré-requisitos e limitações **Pré-requisitos ** + Uma conta AWS ativa + AWS Database Migration Service (AWS DMS) + Experiência em PL/pgSQL codificação + Um cluster Amazon Aurora compatível com PostgreSQL + Um bucket do S3 **Limitações ** Esse padrão não fornece a funcionalidade para atuar como um substituto para o utilitário `UTL_FILE` do Oracle. No entanto, as etapas e o código de amostra podem ser aprimorados ainda mais para atingir suas metas de modernização do banco de dados. **Versões do produto** + Amazon Aurora compatível com PostgreSQL Edição 11.9 ## Arquitetura **Pilha de tecnologias de destino** + Amazon Aurora compatível com PostgreSQL + Amazon CloudWatch + Amazon Simple Notification Service (Amazon SNS) + Amazon S3 **Arquitetura de destino** O diagrama a seguir mostra uma representação de alto nível da solução. ![\[Os arquivos de dados são enviados para um bucket do S3, processados usando a extensão aws_s3 e enviados para a instância do Aurora.\]](http://docs.aws.amazon.com/pt_br/prescriptive-guidance/latest/patterns/images/pattern-img/3aeecd46-1f87-41f9-a9cd-f8181f92e83f/images/4a6c5f5c-58fb-4355-b243-d09a15c1cec6.png) 1. Os arquivos são enviados do aplicativo para o bucket do S3. 1. A extensão `aws_s3` acessa os dados, usando PL/pgSQL, e carrega os dados para o Aurora compatível com PostgreSQL. ## Ferramentas + [Amazon Aurora compatível com PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html): Amazon Aurora Edição Compatível com PostgreSQL é um mecanismo de banco de dados relacional totalmente gerenciado, compatível com PostgreSQL e compatível com ACID. Ele combina a velocidade e a confiabilidade dos bancos de dados comerciais de ponta com a relação custo-benefício dos bancos de dados de código aberto. + [AWS CLI](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html): o AWS Command Line Interface (AWS CLI) é uma ferramenta unificada para gerenciar os serviços da AWS. Com apenas uma ferramenta para fazer o download e configurar, você poderá controlar vários serviços da AWS pela linha de comando e automatizá-los usando scripts. + [Amazon CloudWatch — A](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/WhatIsCloudWatch.html) Amazon CloudWatch monitora os recursos e o uso do Amazon S3. + [Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html): o Amazon Simple Storage Service (Amazon S3) serve como armazenamento para a internet. Nesse padrão, o Amazon S3 fornece uma camada de armazenamento para receber e armazenar arquivos para consumo e transmissão de e para o cluster compatível com o Aurora PostgreSQL. + [aws\$1s3](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html#aws_s3.table_import_from_s3): a extensão `aws_s3` integra o Amazon S3 e o Aurora compatível com PostgreSQL. + [Amazon SNS](https://docs.aws.amazon.com/sns/latest/dg/welcome.html): o Amazon Simple Notification Service (Amazon SNS) coordena e gerencia a entrega ou envio de mensagens entre publicadores e clientes. Nesse padrão, o Amazon SNS é usado para enviar notificações. + [pgAdmin](https://www.pgadmin.org/docs/): o pgAdmin é uma ferramenta de gerenciamento de código aberto para o Postgres. O pgAdmin 4 fornece uma interface gráfica para criar, manter e usar objetos de banco de dados. **Código ** Para obter a funcionalidade necessária, o padrão cria várias funções com nomenclatura semelhante a `UTL_FILE`. A seção *Informações adicionais* contém a base de código para essas funções. No código, substitua `testaurorabucket` pelo nome do bucket do S3 de teste. Substitua `us-east-1` pela região da AWS em que está localizado o bucket do S3 de teste. ## Épicos ### Integre o Amazon S3 e o Aurora compatível com PostgreSQL | Tarefa | Description | Habilidades necessárias | | --- | --- | --- | | Configurar políticas do IAM. | Crie políticas do AWS Identity and Access Management (IAM) que concedam acesso ao bucket S3 e aos objetos nele contidos. Para obter o código, consulte a seção *Informações adicionais*. | Administrador da AWS, DBA | | Adicione perfis de acesso do Amazon S3 ao Aurora PostgreSQL. | Crie dois perfis do IAM: um para leitura e outro para acesso de gravação ao Amazon S3. Anexe os dois perfis ao cluster compatível com o Aurora PostgreSQL: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/prescriptive-guidance/latest/patterns/set-up-oracle-utl_file-functionality-on-aurora-postgresql-compatible.html)Para obter mais informações, consulte a documentação do Aurora compatível com PostgreSQL sobre [importação](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PostgreSQL.S3Import.html) e [exportação](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/postgresql-s3-export.html) de dados para o Amazon S3. | Administrador da AWS, DBA | ### Configure as extensões no Aurora compatível com PostgreSQL | Tarefa | Description | Habilidades necessárias | | --- | --- | --- | | Crie a extensão aws\$1commons. | A extensão `aws_commons` é uma dependência da extensão `aws_s3`. | DBA, Desenvolvedor | | Crie a extensão aws\$1s3. | A extensão `aws_s3` interage com o Amazon S3. | DBA, Desenvolvedor | ### Valide a integração do Amazon S3 e do Aurora compatível com PostgreSQL | Tarefa | Description | Habilidades necessárias | | --- | --- | --- | | Teste a importação de arquivos do Amazon S3 para o Aurora PostgreSQL. | Para testar a importação de arquivos para o Aurora compatível com PostgreSQL, crie um arquivo CSV de amostra e carregue-o no bucket do S3. Crie uma definição de tabela com base no arquivo CSV e carregue o arquivo na tabela usando a função `aws_s3.table_import_from_s3`. | DBA, Desenvolvedor | | Teste a exportação de arquivos do Aurora PostgreSQL para o Amazon S3. | Para testar a exportação de arquivos do Aurora compatível com PostgreSQL, crie uma tabela de teste, preencha-a com dados e, em seguida, exporte os dados usando a função `aws_s3.query_export_to_s3`. | DBA, Desenvolvedor | ### Para imitar o utilitário UTL\$1FILE, crie funções de encapsulamento | Tarefa | Description | Habilidades necessárias | | --- | --- | --- | | Crie o esquema utl\$1file\$1utility. | O esquema mantém as funções de encapsulamento juntas. Para criar o esquema, execute o seguinte comando.
CREATE SCHEMA utl_file_utility;
| DBA, Desenvolvedor | | Crie o tipo file\$1type. | Para criar o tipo `file_type`, use o código a seguir.
CREATE TYPE utl_file_utility.file_type AS (
    p_path character varying(30),
    p_file_name character varying
);


| DBA/Desenvolvedor | | Crie a função init. | A função `init` inicializa uma variável comum, como `bucket` ou `region`. Para obter o código, consulte a seção *Informações adicionais*. | DBA/Desenvolvedor | | Crie as funções de encapsulamento. | Crie as funções de encapsulamento `fopen`, `put_line` e `fclose`. Para obter o código, consulte a seção *Informações adicionais*. | DBA, Desenvolvedor | ### Teste as funções de encapsulamento | Tarefa | Description | Habilidades necessárias | | --- | --- | --- | | Teste as funções de encapsulamento no modo de gravação. | Para testar as funções de encapsulamento no modo de gravação, use o código fornecido na seção *Informações adicionais*. | DBA, Desenvolvedor | | Teste as funções de encapsulamento no modo de acréscimo. | Para testar as funções de encapsulamento no modo de acréscimo, use o código fornecido na seção *Informações adicionais*. | DBA, Desenvolvedor | ## Recursos relacionados + [Integração do 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) ## Mais informações **Configurar políticas do IAM** Crie as políticas a seguir. | | | Nome da política | 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"
            ]
        }
    ]
}
| **Crie a função init** Para inicializar variáveis comuns, como `bucket` ou `region`, crie a função `init` usando o código a seguir. ``` 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$; ``` **Criar as funções de encapsulamento** Crie as funções `fopen`, `put_line` e `fclose` de encapsulamento *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$; ``` **Teste suas funções de configuração e encapsulamento** Use os seguintes blocos de código anônimo para testar sua configuração. *Teste o modo de gravação* O código a seguir grava um arquivo chamado `s3inttest` no bucket do 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; $$ ``` *Teste o modo de acréscimo* O código a seguir acrescenta linhas ao arquivo `s3inttest` que foi criado no teste anterior. ``` 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; $$ ``` **Notificações do Amazon SNS** Opcionalmente, você pode configurar o CloudWatch monitoramento da Amazon e as notificações do Amazon SNS no bucket do S3. Para obter mais informações, consulte [Monitoramento do Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/monitoring-overview.html) e [Configuração das notificações do Amazon SNS](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/US_SetupSNS.html).