在 Aurora PostgreSQL 相容中使用檔案編碼將 BLOB 檔案載入 TEXT

Bhanu Ganesh Gudivada 和 Jeevan Shetty，Amazon Web Services

Summary

通常在遷移期間，在某些情況下，您必須處理從本機檔案系統的檔案載入的非結構化和結構化資料。資料也可能位於與資料庫字元集不同的字元集中。

這些檔案會保存以下類型的資料：

中繼資料 – 此資料說明檔案結構。
半結構化資料 – 這些是特定格式的文字字串，例如 JSON 或 XML。您可以對這類資料提出聲明，例如「一律以「＜」」或「不包含任何換行字元」。
全文 – 此資料通常包含所有類型的字元，包括換行字元和引號字元。它也可能由 UTF-8 中的多位元組字元組成。
二進位資料 – 此資料可能包含位元組或位元組組合，包括 null 和end-of-file標記。

載入這些資料類型的混合可能是一項挑戰。

此模式可與現場部署 Oracle 資料庫、Amazon Web Services (AWS) 雲端上 Amazon Elastic Compute Cloud (Amazon EC2) 執行個體上的 Oracle 資料庫，以及 Oracle 資料庫的 Amazon Relational Database Service (Amazon RDS) 搭配使用。例如，此模式使用 Amazon Aurora PostgreSQL 相容版本。

在 Oracle Database 中，透過 BFILE（二進位檔案）指標、DBMS_LOB套件和 Oracle 系統函數的協助，您可以從檔案載入並使用字元編碼轉換為 CLOB。由於 PostgreSQL 在遷移至 Amazon Aurora PostgreSQL 相容版本資料庫時不支援 BLOB 資料類型，因此這些函數必須轉換為 PostgreSQL 相容指令碼。

此模式提供兩種方法，可將檔案載入 Amazon Aurora PostgreSQL 相容資料庫中的單一資料庫資料欄：

方法 1 – 您可以使用 aws_s3延伸的 table_import_from_s3函數搭配編碼選項，從 Amazon Simple Storage Service (Amazon S3) 儲存貯體匯入資料。
方法 2 – 您在資料庫外部編碼為十六進位，然後解碼以在TEXT資料庫中檢視。

建議使用方法 1，因為 Aurora PostgreSQL 相容與 aws_s3 延伸模組直接整合。

此模式使用範例，將包含多位元組字元和不同格式之電子郵件範本的平面檔案載入 Amazon Aurora PostgreSQL 相容資料庫。

先決條件和限制

先決條件

作用中的 AWS 帳戶
Amazon RDS 執行個體或 Aurora PostgreSQL 相容執行個體
對 SQL 和關聯式資料庫管理系統 (RDBMS) 的基本了解
Amazon Simple Storage Service (Amazon S3) 儲存貯體。
Oracle 和 PostgreSQL 中的系統函數知識
RPM 套件 HexDump-XXD-0.1.1 （隨附於 Amazon Linux 2)
注意
Amazon Linux 2 即將終止支援。如需詳細資訊，請參閱 Amazon Linux 2 FAQs。

限制

對於 TEXT資料類型，可以存放的最長字元字串約為 1 GB。

產品版本

Aurora 支援 Amazon Aurora PostgreSQL 更新中列出的 PostgreSQL 版本。 PostgreSQL

架構

目標技術堆疊

Aurora PostgreSQL 相容

目標架構

方法 1 – 使用 aws_s3.table_import_from_s3

從內部部署伺服器，包含多位元組字元和自訂格式的電子郵件範本的檔案會傳輸至 Amazon S3。此模式提供的自訂資料庫函數使用 aws_s3.table_import_from_s3函數搭配 file_encoding 將檔案載入資料庫，並以TEXT資料類型傳回查詢結果。

檔案會傳輸至預備 S3 儲存貯體。
檔案會上傳至 Amazon Aurora PostgreSQL 相容資料庫。
使用 pgAdmin 用戶端，自訂函數load_file_into_clob會部署到 Aurora 資料庫。
自訂函數會在內部table_import_from_s3搭配 file_encoding 使用。函數的輸出是透過使用 array_to_string和 array_agg做為TEXT輸出來取得。

方法 2 – 在資料庫外部編碼為十六進位，並解碼以檢視資料庫中的 TEXT

來自內部部署伺服器或本機檔案系統的檔案會轉換為十六進位傾印。然後，檔案會匯入 PostgreSQL 做為TEXT欄位。

使用 xxd -p選項，將檔案轉換為命令列中的十六進位傾印。
使用 \copy選項將十六進位傾印檔案上傳至 Aurora PostgreSQL 相容，然後將十六進位傾印檔案解碼為二進位檔案。
將二進位資料編碼為傳回為 TEXT。

工具

AWS 服務

Amazon Aurora PostgreSQL 相容版本是完全受管的 ACID 相容關聯式資料庫引擎，可協助您設定、操作和擴展 PostgreSQL 部署。
AWS Command Line Interface (AWS CLI) 是一種開放原始碼工具，可協助您透過命令列 shell 中的命令與 AWS 服務互動。

其他工具

pgAdmin4 是 PostgreSQL 的開放原始碼管理和開發平台。pgAdmin4 可用於 Linux、Unix、mac OS 和 Windows 來管理 PostgreSQL。

史詩

任務	描述	所需的技能
啟動 EC2 執行個體。	如需啟動執行個體的指示，請參閱啟動執行個體。	DBA
安裝 PostgreSQL 用戶端 pgAdmin 工具。	下載並安裝 pgAdmin。	DBA
建立 IAM 政策。	建立名為的 AWS Identity and Access Management (IAM) 政策`aurora-s3-access-pol`，授予儲存檔案的 S3 儲存貯體存取權。使用下列程式碼，`<bucket-name>`將取代為 S3 儲存貯體的名稱。 `{ "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>" ] } ] }`	DBA
建立 IAM 角色，以將物件從 Amazon S3 匯入 Aurora PostgreSQL 相容。	使用以下程式碼建立名為的 IAM 角色`aurora-s3-import-role`與 AssumeRole 信任關係。 `AssumeRole` 允許 Aurora 代表您存取其他 AWS 服務。 `{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow","Principal": { "Service": "rds.amazonaws.com" },"Action": "sts:AssumeRole" } ] }`	DBA
將 IAM 角色與叢集建立關聯。	若要將 IAM 角色與 Aurora PostgreSQL 相容資料庫叢集建立關聯，請執行下列 AWS CLI 命令。`<Account-ID>` 變更為託管 Aurora PostgreSQL 相容資料庫的 AWS 帳戶 ID。這可讓 Aurora PostgreSQL 相容資料庫存取 S3 儲存貯體。 `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`	DBA
將範例上傳至 Amazon S3。	在此模式的其他資訊區段中，將電子郵件範本程式碼複製到名為的檔案`salary.event.notification.email.vm`。上傳至檔案至 S3 儲存貯體。	DBA、應用程式擁有者
部署自訂函數。	從其他資訊區段中，將自訂函數 `load_file_into_clob` SQL 檔案內容複製到暫時資料表。使用 pgAdmin 用戶端登入 Aurora PostgreSQL 相容資料庫，並將其部署至資料庫結構描述。	應用程式擁有者、DBA
執行自訂函數，將資料匯入資料庫。	執行下列 SQL 命令，將角括號中的項目取代為適當的值。 `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);` 命令會從 Amazon S3 載入檔案，並將輸出傳回為 `TEXT`。	應用程式擁有者、DBA

任務描述所需的技能

任務	描述	所需的技能
將範本檔案轉換為十六進位傾印。	注意 Hexdump 公用程式會以十六進位、小數、八進位或 ASCII 顯示二進位檔案的內容。`hexdump` 命令是 `util-linux`套件的一部分，並預先安裝在 Linux 發行版本中。Hexdump RPM 套件也是 Amazon Linux 2 的一部分。(：Amazon Linux 2 即將結束支援。如需詳細資訊，請參閱 Amazon Linux 2 FAQs。) 若要將檔案內容轉換為十六進位傾印，請執行下列 shell 命令。 `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`	DBA
將 hexdump 檔案載入資料庫結構描述。	使用下列命令將 hexdump 檔案載入 Aurora PostgreSQL 相容資料庫。登入 Aurora PostgreSQL 資料庫，並建立名為的新資料表`email_template_hex`。 `CREATE TABLE email_template_hex(hex_data TEXT);` 使用下列命令，將檔案從本機檔案系統載入資料庫結構描述。 `\copy email_template_hex FROM '/path/file.hex';` 將路徑取代為本機檔案系統上的位置。 `\copy email_template_hex FROM '/tmp/employee.salary.event.notification.email.vm.hex';` 建立另一個名為的資料表`email_template_bytea`。 `CREATE TABLE email_template_bytea(hex_data bytea);` 將資料從 `email_template_hex`插入 `email_template_bytea`。 `INSERT INTO email_template_bytea (hex_data) (SELECT decode(hex_data, 'hex') FROM email_template_hex limit 1);` 若要將十六進位 bytea 程式碼傳回為`TEXT`資料，請執行下列命令。 `SELECT encode(hex_data::bytea, 'escape') FROM email_template_bytea;`	DBA

將範本檔案轉換為十六進位傾印。

注意

Hexdump 公用程式會以十六進位、小數、八進位或 ASCII 顯示二進位檔案的內容。hexdump 命令是 util-linux套件的一部分，並預先安裝在 Linux 發行版本中。Hexdump RPM 套件也是 Amazon Linux 2 的一部分。(：Amazon Linux 2 即將結束支援。如需詳細資訊，請參閱 Amazon Linux 2 FAQs。)

若要將檔案內容轉換為十六進位傾印，請執行下列 shell 命令。


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

DBA

將 hexdump 檔案載入資料庫結構描述。

使用下列命令將 hexdump 檔案載入 Aurora PostgreSQL 相容資料庫。

登入 Aurora PostgreSQL 資料庫，並建立名為的新資料表email_template_hex。
```
CREATE TABLE email_template_hex(hex_data TEXT);
```

使用下列命令，將檔案從本機檔案系統載入資料庫結構描述。


\copy email_template_hex FROM '/path/file.hex';

將路徑取代為本機檔案系統上的位置。


\copy email_template_hex FROM '/tmp/employee.salary.event.notification.email.vm.hex';

建立另一個名為的資料表email_template_bytea。
```
CREATE TABLE email_template_bytea(hex_data bytea);
```

將資料從 email_template_hex插入 email_template_bytea。


INSERT INTO email_template_bytea (hex_data)

(SELECT decode(hex_data, 'hex') FROM email_template_hex limit 1);

若要將十六進位 bytea 程式碼傳回為TEXT資料，請執行下列命令。
```
SELECT encode(hex_data::bytea, 'escape') FROM email_template_bytea;
```

DBA

其他資訊

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

電子郵件範本


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

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

從 Amazon RDS for Oracle 逐步遷移至 Amazon RDS for PostgreSQL

使用 AWS SCT 和將 Amazon RDS for Oracle 遷移至 Amazon RDS for PostgreSQL AWS DMS

在 Aurora PostgreSQL 相容中使用檔案編碼將 BLOB 檔案載入 TEXT

Summary

先決條件和限制

注意

架構

工具

史詩

注意

相關資源

其他資訊