Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra. # Configuration de la fonctionnalité Oracle UTL\$1FILE sur Aurora compatible avec PostgreSQL *Rakesh Raghav et Anuradha Chintha, Amazon Web Services* ## Résumé Dans le cadre de votre migration d'Oracle vers l'édition compatible avec Amazon Aurora PostgreSQL sur le cloud Amazon Web Services (AWS), vous pouvez rencontrer de nombreux défis. Par exemple, la migration de code qui repose sur l'`UTL_FILE`utilitaire Oracle représente toujours un défi. Dans Oracle PL/SQL, le `UTL_FILE` package est utilisé pour les opérations sur les fichiers, telles que la lecture et l'écriture, conjointement avec le système d'exploitation sous-jacent. L'`UTL_FILE`utilitaire fonctionne à la fois pour les serveurs et les ordinateurs clients.  Amazon Aurora PostgreSQL compatible est une offre de base de données gérée. De ce fait, il n'est pas possible d'accéder aux fichiers sur le serveur de base de données. Ce modèle vous explique comment intégrer Amazon Simple Storage Service (Amazon S3) et Amazon Aurora PostgreSQL compatible pour obtenir un sous-ensemble de fonctionnalités. `UTL_FILE` Grâce à cette intégration, nous pouvons créer et consommer des fichiers sans utiliser d'outils ou de services tiers d'extraction, de transformation et de chargement (ETL). Vous pouvez éventuellement configurer la CloudWatch surveillance Amazon et les notifications Amazon SNS. Nous vous recommandons de tester minutieusement cette solution avant de l'implémenter dans un environnement de production. ## Conditions préalables et limitations **Conditions préalables** + Un compte AWS actif + Expertise du service de migration de base de données AWS (AWS DMS) + Expertise en PL/pgSQL codage + Un cluster compatible avec Amazon Aurora PostgreSQL + Compartiment S3 **Limites** Ce modèle ne fournit pas les fonctionnalités nécessaires pour remplacer l'`UTL_FILE`utilitaire Oracle. Toutefois, les étapes et les exemples de code peuvent être encore améliorés pour atteindre les objectifs de modernisation de votre base de données. **Versions du produit** + Édition 11.9 compatible avec Amazon Aurora PostgreSQL ## Architecture **Pile technologique cible** + Compatible avec Amazon Aurora PostgreSQL + Amazon CloudWatch + Amazon Simple Notification Service (Amazon SNS) + Amazon S3 **Architecture cible** Le schéma suivant montre une représentation de haut niveau de la solution. ![\[Les fichiers de données sont chargés dans un compartiment S3, traités à l'aide de l'extension aws_s3 et envoyés à l'instance Aurora.\]](http://docs.aws.amazon.com/fr_fr/prescriptive-guidance/latest/patterns/images/pattern-img/3aeecd46-1f87-41f9-a9cd-f8181f92e83f/images/4a6c5f5c-58fb-4355-b243-d09a15c1cec6.png) 1. Les fichiers sont chargés depuis l'application dans le compartiment S3. 1. L'`aws_s3`extension accède aux données à l'aide de PL/pgSQL et les télécharge sur Aurora PostgreSQL compatible. ## Outils + Compatible avec [Amazon Aurora PostgreSQL — L'édition compatible avec Amazon](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html) Aurora PostgreSQL est un moteur de base de données relationnelle entièrement géré, compatible avec PostgreSQL et conforme à l'ACID. Il associe la rapidité et la fiabilité des bases de données commerciales haut de gamme à la rentabilité des bases de données open source. + [AWS CLI](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html) — L'interface de ligne de commande AWS (AWS CLI) est un outil unifié permettant de gérer vos services AWS. Avec un seul outil à télécharger et à configurer, vous pouvez contrôler plusieurs services AWS depuis la ligne de commande et les automatiser par le biais de scripts. + [Amazon CloudWatch](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/WhatIsCloudWatch.html) — Amazon CloudWatch surveille les ressources et l'utilisation d'Amazon S3. + [Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html) — Amazon Simple Storage Service (Amazon S3) est un service de stockage pour Internet. Dans ce modèle, Amazon S3 fournit une couche de stockage pour recevoir et stocker des fichiers destinés à être consommés et transmis vers et depuis le cluster compatible Aurora PostgreSQL. + [aws\$1s3](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html#aws_s3.table_import_from_s3) — L'`aws_s3`extension intègre la compatibilité avec Amazon S3 et Aurora PostgreSQL. + [Amazon SNS —](https://docs.aws.amazon.com/sns/latest/dg/welcome.html) Amazon Simple Notification Service (Amazon SNS) coordonne et gère la distribution ou l'envoi de messages entre les éditeurs et les clients. Dans ce modèle, Amazon SNS est utilisé pour envoyer des notifications. + [pgAdmin](https://www.pgadmin.org/docs/) — pgAdmin est un outil de gestion open source pour Postgres. pgAdmin 4 fournit une interface graphique pour créer, gérer et utiliser des objets de base de données. **Code** Pour obtenir les fonctionnalités requises, le modèle crée plusieurs fonctions avec un nom similaire à`UTL_FILE`. La section *Informations supplémentaires* contient le code de base de ces fonctions. Dans le code, remplacez `testaurorabucket` par le nom de votre compartiment S3 de test. `us-east-1`Remplacez-le par la région AWS dans laquelle se trouve votre compartiment S3 de test. ## Épopées ### Intégrez la compatibilité avec Amazon S3 et Aurora PostgreSQL | Sous-tâche | Description | Compétences requises | | --- | --- | --- | | Configurez des politiques IAM. | Créez des politiques AWS Identity and Access Management (IAM) qui accordent l'accès au compartiment S3 et aux objets qu'il contient. Pour le code, consultez la section *Informations supplémentaires*. | Administrateur AWS, DBA | | Ajoutez des rôles d'accès Amazon S3 à Aurora PostgreSQL. | Créez deux rôles IAM : un rôle pour l'accès en lecture et un rôle pour l'accès en écriture à Amazon S3. Associez les deux rôles au cluster compatible avec Aurora PostgreSQL : [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/prescriptive-guidance/latest/patterns/set-up-oracle-utl_file-functionality-on-aurora-postgresql-compatible.html)Pour plus d'informations, consultez la documentation compatible avec Aurora PostgreSQL sur l'[importation et l'](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PostgreSQL.S3Import.html)[exportation de données vers Amazon S3](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/postgresql-s3-export.html). | Administrateur AWS, DBA | ### Configurer les extensions dans Aurora PostgreSQL compatible | Sous-tâche | Description | Compétences requises | | --- | --- | --- | | Créez l'extension aws\$1commons. | L'`aws_commons`extension est une dépendance de l'`aws_s3`extension. | DBA, Développeur | | Créez l'extension aws\$1s3. | L'`aws_s3`extension interagit avec Amazon S3. | DBA, Développeur | ### Validez l'intégration compatible avec Amazon S3 et Aurora PostgreSQL | Sous-tâche | Description | Compétences requises | | --- | --- | --- | | Testez l'importation de fichiers depuis Amazon S3 vers Aurora PostgreSQL. | Pour tester l'importation de fichiers dans un environnement compatible avec Aurora PostgreSQL, créez un exemple de fichier CSV et chargez-le dans le compartiment S3. Créez une définition de table basée sur le fichier CSV et chargez le fichier dans le tableau à l'aide de la `aws_s3.table_import_from_s3` fonction. | DBA, Développeur | | Testez l'exportation de fichiers depuis Aurora PostgreSQL vers Amazon S3. | Pour tester l'exportation de fichiers depuis une version compatible avec Aurora PostgreSQL, créez une table de test, remplissez-la de données, puis exportez les données à l'aide de la fonction. `aws_s3.query_export_to_s3` | DBA, Développeur | ### Pour imiter l'utilitaire UTL\$1FILE, créez des fonctions wrapper | Sous-tâche | Description | Compétences requises | | --- | --- | --- | | Créez le schéma utl\$1file\$1utility. | Le schéma maintient les fonctions du wrapper ensemble. Pour créer le schéma, exécutez la commande suivante.
CREATE SCHEMA utl_file_utility;
| DBA, Développeur | | Créez le type file\$1type. | Pour créer le `file_type` type, utilisez le code suivant.
CREATE TYPE utl_file_utility.file_type AS (
    p_path character varying(30),
    p_file_name character varying
);


| DBA/Développeur | | Créez la fonction d'initialisation. | La `init` fonction initialise une variable courante telle que `bucket` ou`region`. Pour le code, consultez la section *Informations supplémentaires*. | DBA/Développeur | | Créez les fonctions du wrapper. | Créez les fonctions du wrapper `fopen``put_line`, et. `fclose` Pour le code, consultez la section *Informations supplémentaires*. | DBA, Développeur | ### Testez les fonctions du wrapper | Sous-tâche | Description | Compétences requises | | --- | --- | --- | | Testez les fonctions du wrapper en mode écriture. | Pour tester les fonctions du wrapper en mode écriture, utilisez le code fourni dans la section *Informations supplémentaires*. | DBA, Développeur | | Testez les fonctions du wrapper en mode ajout. | Pour tester les fonctions du wrapper en mode ajout, utilisez le code fourni dans la section *Informations supplémentaires*. | DBA, Développeur | ## Ressources connexes + [Intégration avec 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) ## Informations supplémentaires **Configurer des politiques IAM** Créez les politiques suivantes. | | | Nom de la politique | 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"
            ]
        }
    ]
}
| **Création de la fonction d'initialisation** Pour initialiser des variables courantes, telles que `bucket` ou`region`, créez la `init` fonction à l'aide du code suivant. ``` 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$; ``` **Création des fonctions du wrapper** Créez les fonctions `fopen``put_line`, et `fclose` wrapper. *fouvrir* ``` 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$; ``` *fermer* ``` 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$; ``` **Testez votre configuration et les fonctions du wrapper** Utilisez les blocs de code anonymes suivants pour tester votre configuration. *Tester le mode d'écriture* Le code suivant écrit un fichier nommé `s3inttest` dans le compartiment 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; $$ ``` *Testez le mode d'ajout* Le code suivant ajoute des lignes au `s3inttest` fichier créé lors du test précédent. ``` 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; $$ ``` **Notifications Amazon SNS** Vous pouvez éventuellement configurer la CloudWatch surveillance Amazon et les notifications Amazon SNS sur le compartiment S3. Pour plus d'informations, consultez [Surveillance d'Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/monitoring-overview.html) et [Configuration des notifications Amazon SNS.](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/US_SetupSNS.html)