在 Aurora BLOB Postgre 中使用文件编码将文件加载到TEXT中-兼容 SQL

由 Bhanu Ganesh Gudivada (AWS) 和 Jeevan Shetty () 创作 AWS

Summary

通常，在迁移过程中，您必须处理从本地文件系统上的文件加载的、非结构化和结构化数据。数据也可采用与数据库字符集不同的字符集。

这些文件包含以下类型数据：

混合加载这些类型的数据，可能是一项挑战。

该模式可用于本地 Oracle 数据库、亚马逊弹性计算云 (AmazonEC2) 实例上的亚马逊云服务 (AWS) 云上的 Oracle 数据库和适用于 Oracle 数据库的亚马逊关系数据库服务 (亚马逊RDS)。例如，这种模式使用的是亚马逊 Aurora Postgre SQL 兼容版。

在 Oracle 数据库中，借助 BFILE （二进制文件）指针、 DBMS_LOB 软件包和 Oracle 系统函数，您可以从文件加载并转换为CLOB使用字符编码。由于 Postgre 在迁移到亚马逊 Aurora Postgre SQL 兼容版数据库时SQL不支持该BLOB数据类型，因此必须将这些函数转换为兼容 Post SQL gre 的脚本。

此模式提供了两种将文件加载到SQL兼容 Amazon Aurora Postgre 的数据库中的单个数据库列的方法：

我们建议使用方法 1，因为 Aurora Postgre SQL-Compatible 已与扩展程序直接集成。 aws_s3

此模式使用将包含电子邮件模板的平面文件加载到兼容 Amazon Aurora Postgr SQL e 的数据库中的示例，该模板具有多字节字符和不同的格式。

先决条件和限制

先决条件

限制

产品版本

架构

目标技术堆栈

目标架构

方法 1 — 使用 aws_s3.table_import_from_s3

将包含多字节字符和自定义格式的电子邮件模板文件从本地服务器传输至 Amazon S3。本文提供的自定义数据库函数使用带 file_encoding 的 aws_s3.table_import_from_s3 函数将文件加载至数据库，并将查询结果以 TEXT 数据类型的形式返回。

方法 2 — 在数据库外部编码为十六进制，然后解码以在数据库内部查看 TEXT

来自本地服务器或本地文件系统的文件将转换至十六进制转储。然后，该文件将SQL作为 TEXT 字段导入 Postgre。

工具

AWS服务

其他工具

操作说明

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

相关资源

参考

教程

其他信息

load_file_into_clob custom function

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

电子邮件模板

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