Chargez BLOB des fichiers en TEXT utilisant le codage de fichiers dans Aurora Postgre -Compatible SQL

Créé par Bhanu Ganesh Gudivada (AWS) et Jeevan Shetty () AWS

Récapitulatif

Au cours de la migration, il arrive souvent que vous deviez traiter des données structurées et non structurées chargées à partir de fichiers sur un système de fichiers local. Les données peuvent également se trouver dans un jeu de caractères différent de celui de la base de données.

Ces fichiers contiennent les types de données suivants :

Le chargement d'une combinaison de ces types de données peut s'avérer difficile.

Le modèle peut être utilisé avec les bases de données Oracle sur site, les bases de données Oracle qui se trouvent sur des instances Amazon Elastic Compute Cloud (AmazonEC2) sur le cloud Amazon Web Services (AWS) et Amazon Relational Database Service (RDSAmazon) pour les bases de données Oracle. Par exemple, ce modèle utilise Amazon Aurora Postgre SQL -Compatible Edition.

Dans Oracle Database, à l'aide d'un pointeur BFILE (fichier binaire), du DBMS_LOB package et des fonctions du système Oracle, vous pouvez charger à partir d'un fichier et le convertir au format CLOB par codage de caractères. Postgre SQL ne prenant pas en charge le type de BLOB données lors de la migration vers une base de données Amazon Aurora Postgre SQL -Compatible Edition, ces fonctions doivent être converties en scripts compatibles avec Postgre. SQL

Ce modèle propose deux approches pour charger un fichier dans une seule colonne de base de données d'une base de données SQL compatible avec Amazon Aurora Postgre :

Nous vous recommandons d'utiliser Approach 1 car Aurora Postgre SQL -Compatible est directement intégré à l'aws_s3extension.

Ce modèle utilise l'exemple du chargement d'un fichier plat contenant un modèle d'e-mail, comportant des caractères multioctets et un formatage distinct, dans une base de données SQL compatible avec Amazon Aurora Postgre.

Conditions préalables et limitations

Prérequis

Limites

Versions du produit

Architecture

Pile technologique cible

Architecture cible

Approche 1 — Utilisation de aws_s3.table_import_from_s3

À partir d'un serveur sur site, un fichier contenant un modèle d'e-mail avec des caractères multioctets et un formatage personnalisé est transféré vers Amazon S3. La fonction de base de données personnalisée fournie par ce modèle utilise la aws_s3.table_import_from_s3 fonction with file_encoding pour charger des fichiers dans la base de données et renvoyer les résultats de la requête sous forme de type de TEXT données.

Approche 2 — Encodage en hexadécimal à l'extérieur de la base de données et décodage pour affichage à TEXT l'intérieur de la base de données

Un fichier provenant d'un serveur local ou d'un système de fichiers local est converti en vidage hexadécimal. Le fichier est ensuite importé dans Postgre SQL sous forme de TEXT champ.

Outils

AWSservices

Autres outils

Épopées

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:AbortMultipartUpload",
                "s3:DeleteObject",
                "s3:ListMultipartUploadParts",
                "s3:PutObject",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::<bucket-name>/*",
                "arn:aws:s3:::<bucket-name>"
            ]
        }
    ]
}

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow","Principal": {
        "Service": "rds.amazonaws.com"
      },"Action": "sts:AssumeRole"
    }
  ]
}

aws rds add-role-to-db-cluster --db-cluster-identifier aurora-postgres-cl
--feature-name s3Import --role-arn arn:aws:iam::<Account-ID>:role/aurora-s3-import-role

select load_file_into_clob('aws-s3-import-test'::text,'us-west-1'::text,'employee.salary.event.notification.email.vm'::text);

Select load_file_into_clob('aws-s3-import-test'::text,'us-west-1'::text,'employee.salary.event.notification.email.vm'::text);

xxd -p </path/file.vm> | tr -d '\n' > </path/file.hex>

xxd -p employee.salary.event.notification.email.vm | tr -d '\n' > employee.salary.event.notification.email.vm.hex

Ressources connexes

Références

Didacticiels

Informations supplémentaires

Fonction personnalisée load_file_into_clob

CREATE OR REPLACE FUNCTION load_file_into_clob(
    s3_bucket_name text,
    s3_bucket_region text,
    file_name text,
    file_delimiter character DEFAULT '&'::bpchar,
    file_encoding text DEFAULT 'UTF8'::text)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    blob_data BYTEA;
    clob_data TEXT;
    l_table_name CHARACTER VARYING(50) := 'file_upload_hex';
    l_column_name CHARACTER VARYING(50) := 'template';
    l_return_text TEXT;
    l_option_text CHARACTER VARYING(150);
    l_sql_stmt CHARACTER VARYING(500);
        
BEGIN
    
    EXECUTE format ('CREATE TEMPORARY TABLE %I (%I text, id_serial serial)', l_table_name, l_column_name);
    
    l_sql_stmt := 'select ''(format text, delimiter ''''' || file_delimiter || ''''', encoding ''''' || file_encoding ||  ''''')'' ';
    
    EXECUTE FORMAT(l_sql_stmt)
    INTO l_option_text;
    
    EXECUTE FORMAT('SELECT aws_s3.table_import_from_s3($1,$2,$6, aws_commons.create_s3_uri($3,$4,$5))')
    INTO l_return_text
    USING l_table_name, l_column_name, s3_bucket_name, file_name,s3_bucket_region,l_option_text;
    
    EXECUTE format('select array_to_string(array_agg(%I order by id_serial),E''\n'') from %I', l_column_name, l_table_name)
    INTO clob_data;
    
    drop table file_upload_hex;
    
    RETURN clob_data;
END;
$BODY$;

Modèle d'e-mail

######################################################################################
##                                                                                    ##
##    johndoe Template Type: email                                                    ##
##    File: johndoe.salary.event.notification.email.vm                                ##
##    Author: Aimée Étienne    Date 1/10/2021                                                ##
##  Purpose: Email template used by EmplmanagerEJB to inform a johndoe they         ##
##        have been given access to a salary event                                    ##
##    Template Attributes:                                                             ##
##        invitedUser - PersonDetails object for the invited user                        ##
##        salaryEvent - OfferDetails object for the event the user was given access    ##
##        buyercollege - CompDetails object for the college owning the salary event    ##
##        salaryCoordinator - PersonDetails of the salary coordinator for the event    ##
##        idp - Identity Provider of the email recipient                                ##
##        httpWebRoot - HTTP address of the server                                    ##
##                                                                                    ##
######################################################################################

$!invitedUser.firstname $!invitedUser.lastname,

Ce courriel confirme que vous avez ete invite par $!salaryCoordinator.firstname $!salaryCoordinator.lastname de $buyercollege.collegeName a participer a l'evenement "$salaryEvent.offeringtitle" sur johndoeMaster Sourcing Intelligence.

Votre nom d'utilisateur est $!invitedUser.username

Veuillez suivre le lien ci-dessous pour acceder a l'evenement.

${httpWebRoot}/myDashboard.do?idp=$!{idp}

Si vous avez oublie votre mot de passe, utilisez le lien "Mot de passe oublie" situe sur l'ecran de connexion et entrez votre nom d'utilisateur ci-dessus.

Si vous avez des questions ou des preoccupations, nous vous invitons a communiquer avec le coordonnateur de l'evenement $!salaryCoordinator.firstname $!salaryCoordinator.lastname au ${salaryCoordinator.workphone}.

*******

johndoeMaster Sourcing Intelligence est une plateforme de soumission en ligne pour les equipements, les materiaux et les services.

Si vous avez des difficultes ou des questions, envoyez un courriel a support@johndoeMaster.com pour obtenir de l'aide.