Muat file BLOB ke TEXT dengan menggunakan pengkodean file di Aurora PostgreSQL yang kompatibel

Bhanu Ganesh Gudivada dan Jeevan Shetty, Amazon Web Services

Ringkasan

Seringkali selama migrasi, ada kasus di mana Anda harus memproses data tidak terstruktur dan terstruktur yang dimuat dari file pada sistem file lokal. Data mungkin juga berada dalam kumpulan karakter yang berbeda dari kumpulan karakter database.

File-file ini menyimpan jenis data berikut:

Memuat campuran jenis data ini bisa menjadi tantangan.

Pola ini dapat digunakan dengan database Oracle lokal, database Oracle yang ada di instans Amazon Elastic Compute Cloud (Amazon) di Amazon Web Services (AWS EC2) Cloud, dan Amazon Relational Database Service (Amazon RDS) untuk database Oracle. Sebagai contoh, pola ini menggunakan Amazon Aurora PostgreSQL Compatible Edition.

Di Oracle Database, dengan bantuan pointer BFILE (file biner), DBMS_LOB paket, dan fungsi sistem Oracle, Anda dapat memuat dari file dan mengkonversi ke CLOB dengan pengkodean karakter. Karena PostgreSQL tidak mendukung tipe data BLOB saat bermigrasi ke database Amazon Aurora PostgreSQL Edisi yang kompatibel dengan PostgreSQL, fungsi ini harus dikonversi ke skrip yang kompatibel dengan PostgreSQL.

Pola ini menyediakan dua pendekatan untuk memuat file ke dalam kolom database tunggal dalam database yang kompatibel dengan Amazon Aurora PostgreSQL:

Sebaiknya gunakan Pendekatan 1 karena Aurora PostgreSQL kompatibel memiliki integrasi langsung dengan ekstensi. aws_s3

Pola ini menggunakan contoh memuat file datar yang berisi template email, yang memiliki karakter multibyte dan format berbeda, ke dalam database Amazon Aurora PostgreSQL yang kompatibel.

Prasyarat dan batasan

Prasyarat

Batasan

Versi produk

Arsitektur

Tumpukan teknologi target

Arsitektur target

Pendekatan 1 - Menggunakan aws_s3.table_import_from_s3

Dari server lokal, file yang berisi templat email dengan karakter multibyte dan pemformatan kustom ditransfer ke Amazon S3. Fungsi database kustom yang disediakan oleh pola ini menggunakan aws_s3.table_import_from_s3 fungsi dengan file_encoding untuk memuat file ke dalam database dan mengembalikan hasil query sebagai tipe TEXT data.

Pendekatan 2 - Pengkodean ke heksadesimal di luar database dan decoding untuk melihat TEXT di dalam database

File dari server lokal atau sistem file lokal diubah menjadi hex dump. Kemudian file tersebut diimpor ke PostgreSQL sebagai bidang. TEXT

Alat

Layanan AWS

Alat lainnya

Epik

{
    "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

Sumber daya terkait

Referensi

Tutorial

Informasi tambahan

load_file_into_clob fungsi kustom

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$;

Template email

######################################################################################
##                                                                                    ##
##    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.