TEXT 使用 Aurora Postgre 中的檔案編碼將BLOB檔案載入 SQL- 相容

由 Bhanu Ganesh Gudivada （AWS） 和 Jeevan Shetty （AWS） 建立

環境：生產	來源：內部部署 Oracle 資料庫	目標：Aurora Postgre SQL- 相容
R 類型：重新架構	工作負載：Oracle；開放原始碼	技術：遷移；資料庫
AWS 服務：Amazon Aurora

Summary

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

這些檔案包含下列資料類型：

• 中繼資料 – 此資料說明檔案結構。

• 半結構化資料 – 這些是特定格式的文字字串，例如 JSON或 XML。您可能可以對這類資料提出聲明，例如「一律以「＜」」開頭，或「不包含任何新行字元」。

• 全文 – 此資料通常包含所有類型的字元，包括新行和引號字元。它也可能包含 UTF-8 中的多位元組字元。

• 二進位資料 – 此資料可能包含位元組或位元組組合，包括 null 和 end-of-file標記。

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

此模式可與內部部署 Oracle 資料庫、Amazon Web Services （EC2） Cloud 上 Amazon Elastic Compute Cloud （Amazon） 執行個體上的 Oracle 資料庫AWS，以及 Oracle 資料庫的 Amazon Relational Database Service （AmazonRDS） 搭配使用。例如，此模式使用 Amazon Aurora Postgre SQL-Compatible Edition。

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

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

• 方法 1 - 您可以透過使用 aws_s3擴充table_import_from_s3功能搭配 編碼選項，從 Amazon Simple Storage Service （Amazon S3） 儲存貯體匯入資料。

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

我們建議您使用方法 1，因為 Aurora Postgre SQL-Compatible 與aws_s3延伸模組直接整合。

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

先決條件和限制

先決條件

• 作用中AWS帳戶

• Amazon RDS執行個體或 Aurora Postgre SQL- 相容執行個體

• 對 SQL和 關聯式資料庫管理系統 （RDBMS） 的基本了解

• Amazon Simple Storage Service （Amazon S3） 儲存貯體。

• 了解 Oracle 和 Postgre 中的系統功能SQL

• RPM 套件 HexDump-XXD-0.1.1 （隨附於 Amazon Linux 2）

  注意：Amazon Linux 2 即將結束支援。如需詳細資訊，請參閱 Amazon Linux 2 FAQs。

限制

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

產品版本

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

架構

目標技術堆疊

• Aurora Postgre SQL- 相容

目標架構

方法 1 – 使用 aws_s3.table_import_from_s3

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

1. 檔案會傳輸至預備 S3 儲存貯體。

2. 檔案會上傳至 Amazon Aurora Postgre SQL- 相容資料庫。

3. 使用 pgAdmin 用戶端，自訂函數load_file_into_clob會部署到 Aurora 資料庫。

4. 自訂函數會在內部table_import_from_s3搭配 file_encoding 使用。使用 array_to_string和 array_agg作為輸出，從函數取得TEXT輸出。

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

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

1. 使用 xxd -p選項，將檔案轉換為命令列中的十六進位傾印。

2. 使用 \copy選項將十六進位傾印檔案上傳到 Aurora Postgre SQL-相容，然後將十六進位傾印檔案解碼為二進位檔案。

3. 編碼要傳回為 的二進位資料TEXT。

工具

AWS 服務

• Amazon Aurora Postgre SQL-Compatible Edition 是完全受管且ACID合規的關聯式資料庫引擎，可協助您設定、操作和擴展 PostgreSQL 部署。

• AWS Command Line Interface （AWS CLI） 是開放原始碼工具，可協助您透過命令列 Shell 中的命令與 AWS服務互動。

其他工具

• pgAdmin4 是 Postgre 的開放原始碼管理和開發平台SQL。pgAdmin4 可用於 Linux、Unix、mac OS 和 Windows，以管理 Postgre SQL。 

史詩

方法 1：將資料從 Amazon S3 匯入 Aurora Postgre SQL- 相容

任務	描述	所需的技能
啟動 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
為從 Amazon S3 匯入至 Aurora Postgre 的物件建立IAM角色 SQL- 相容。	使用下列程式碼建立aurora-s3-import-role以AssumeRole信任關係命名IAM的角色。 AssumeRole 允許 Aurora 代表您存取其他 AWS 服務。 { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow","Principal": { "Service": "rds.amazonaws.com" },"Action": "sts:AssumeRole" } ] }	DBA
將IAM角色與叢集建立關聯。	若要將IAM角色與 Aurora Postgre SQL-Compatible 資料庫叢集建立關聯，請執行下列AWSCLI命令。<Account-ID> 變更AWS託管 Aurora Postgre SQL-Compatible 資料庫的帳戶 ID。這可讓 Aurora Postgre SQL相容資料庫存取 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_clobSQL檔案內容複製到暫存資料表。 登入 Aurora Postgre SQL-Compatible 資料庫，並使用 pgAdmin 用戶端將其部署到資料庫結構描述中。	應用程式擁有者、 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

方法 2：將範本檔案轉換為本機 Linux 系統中的十六進位傾印

任務	描述	所需的技能
將範本檔案轉換為十六進位傾印。	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 Postgre SQL-Compatible 資料庫。 登入 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); 若要將十六進位旁路程式碼傳回為TEXT資料，請執行下列命令。 SELECT encode(hex_data::bytea, 'escape') FROM email_template_bytea;	DBA

相關資源

參考

• 使用 PostgreSQL 資料庫作為AWS資料庫遷移服務的目標

• Oracle Database 19c 至具有 PostgreSQL 相容性的 Amazon Aurora （12.4） 遷移手冊

• 建立IAM政策

• 將IAM角色與 Amazon Aurora MySQL 資料庫叢集建立關聯

• pgAdmin

教學課程

• Amazon 入門 RDS

• 從 Oracle 遷移至 Amazon Aurora

其他資訊

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 [e-mail] pour obtenir de l'aide.