翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。

# Aurora PostgreSQL 互換のファイルエンコーディングを使用して BLOB ファイルを TEXT にロード
BLOB ファイルを Aurora PostgreSQL 互換にロード

*Amazon Web Services、Bhanu Ganesh Gudivada および Jeevan Shetty*

## 概要


多くの場合、移行中に、ローカルファイルシステムのファイルからロードされた非構造化データや構造化データを処理しなければならない場合があります。データがデータベースの文字セットと異なる文字セットになっている場合もあります。

これらのファイルには以下の種類のデータが格納されています。
+ **メタデータ** – このデータはファイル構造を記述します。
+ **半構造化データ** – JSON や XML などの特定の形式のテキスト文字列です。このようなデータについて、「常に '<' で始まる」や「改行文字を一切含まない」などのアサーションができる場合があります。
+ **全文** – このデータには通常、改行文字や引用符を含むあらゆる種類の文字が含まれます。UTF-8 のマルチバイト文字で構成されている場合もあります。
+ **バイナリデータ** – このデータには、バイト、または NULL とファイル末尾マーカーを含むバイトの組み合わせが含まれる場合があります。

これらの種類のデータが混在してロードするのは難しい場合があります。

このパターンでは、オンプレミスの Oracle データベース、Amazon Web Services (AWS) クラウドの Amazon Elastic Compute Cloud (Amazon EC2) インスタンスにある Oracle データベース、および Oracle データベースの Amazon Relational Database Service (Amazon RDS) で使用できます。例として、このパターンでは Amazon Aurora PostgreSQL 互換エディションを使用しています。

Oracle データベースでは、`BFILE` (バイナリファイル) ポインタ、`DBMS_LOB` パッケージ、および Oracle システム関数を使用して、ファイルからロードし、文字エンコーディングを使用して CLOB に変換できます。PostgreSQL は Amazon Aurora PostgreSQL 互換エディションデータベースへの移行時に BLOB データ型が適用されないため、これらの関数は PostgreSQL 互換のスクリプトに変換する必要があります。

このパターンでは、Amazon Aurora PostgreSQL 互換データベースの 1 つのデータベース列にファイルをロードする方法が 2 つあります。
+ 方法 1 —エンコードオプション付けの `aws_s3` 拡張機能の `table_import_from_s3` 関数を使用して、 Amazon Simple Storage Service (Amazon S3) バケットからデータをインポートします。
+ 方法 2 – データベースの外部で 16 進数にエンコードし、次にデータベース内で `TEXT` を表示するようにデコードします。

Aurora PostgreSQL 互換は、`aws_s3` の拡張機能つ直接統合されているため、アプローチ 1 を使用することを推薦します。

このパターンでは、マルチバイト文字と独自の形式を持つ E メールテンプレートを含むフラットファイルを Amazon Aurora PostgreSQL 互換データベースにロードする例を使用します。

## 前提条件と制限事項


**前提条件**
+ アクティブなAWS アカウント
+ Amazon RDS インスタンスまたは Aurora PostgreSQL 互換インスタンス
+ SQL およびリレーショナルデータベース管理システム (RDBMS) の基本的な理解
+ 1 つの Amazon Simple Storage Service (Amazon S3) バケット
+ OracleとPostgreSQLのシステム機能に関する知識
+ RPM Package HexDump-XXD-0.1.1 (Amazon Linux 2 に含まれています)
**注記**  
Amazon Linux 2 のサポートは間もなく終了します。詳細については、「[Amazon Linux 2 のよくある質問](https://aws.amazon.com/amazon-linux-2/faqs/)」を参照してください。

**制限事項**
+ `TEXT` データ型の場合、保存できる最長の文字列は約 1 GB です。

**製品バージョン**
+ Auroraには [Amazon Aurora PostgreSQL 更新](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Updates.html) に記載されている PostgreSQL バージョンが適用されます。

## アーキテクチャ


**ターゲットテクノロジースタック**
+ Aurora PostgreSQL 互換

**ターゲット アーキテクチャ**

*アプローチ 1 – aws\$1s\$1s3.table\$1import\$1from\$1s3 を使用*

オンプレミスサーバーから、マルチバイト文字とカスタムフォーマットの E メールテンプレートを含むファイルが Amazon S3 に転送されます。このパターンで提供されるカスタムデータベース関数は、`file_encoding` 付けの `aws_s3.table_import_from_s3` 関数を使用して、ファイルをデータベースにロードし、クエリ結果を `TEXT` データ型として返します。

![\[オンプレミスサーバーから Aurora データベースの TEXT 出力までの 4 ステップのプロセス。\]](http://docs.aws.amazon.com/ja_jp/prescriptive-guidance/latest/patterns/images/pattern-img/cbf63cac-dcea-4e18-ab4f-c4f6296f60e7/images/9c46b385-e8a0-4e50-b856-d522c44d79e3.png)


1. ファイルはステージング S3 バケットに転送されます。

1. ファイルは Amazon Aurora PostgreSQL 互換データベースにアップロードされます。

1. pgAdmin クライアントを使用して、カスタム関数 `load_file_into_clob` が Aurora データベースにデプロイされます。

1. カスタム関数が内部的に file\$1encoding `table_import_from_s3` と組み合わせて使用します。この関数からの出力は、`array_to_string` と `array_agg` を使用して、`TEXT` 出力として取得されます。

方法 2 – データベースの外部では 16 進数にエンコードし、データベース内の TEXT を表示するにはデコードします。

オンプレミスサーバーまたはローカルファイルシステムのファイルは 16 進ダンプに変換されます。次に、ファイルが `TEXT` フィールドとして、 PostgreSQL にインポートされます。

![\[16 進数ダンプを使用した 3 ステップのプロセス。\]](http://docs.aws.amazon.com/ja_jp/prescriptive-guidance/latest/patterns/images/pattern-img/cbf63cac-dcea-4e18-ab4f-c4f6296f60e7/images/563038ca-f890-4874-85df-d0f82d99800a.png)


1. `xxd -p` オプションを使用して、コマンドラインでファイルを 16 進数ダンプに変換します。

1. `\copy` オプションを使用して、 16 進数ダンプファイルを Aurora PostgreSQL 互換にアップロードし、16 進数ダンプファイルをバイナリにデコードします。

1. バイナリデータを `TEXT` エンコードしてとして返します。

## ツール


**AWS サービス**
+ [Amazon Aurora PostgreSQL 互換版](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html)は、PostgreSQL デプロイのセットアップ、運用、スケーリングを支援するフルマネージド型のリレーショナルデータベースエンジンです。
+ 「[AWS コマンドラインインターフェイス (AWS CLI)](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html)」は、オープンソースのツールであり、コマンドラインシェルのコマンドを使用して AWS サービスとやり取りすることができます。

**その他のツール**
+ [PgAdmin4](https://www.pgadmin.org/) は PostgreSQL のオープンソースの管理および開発プラットフォームです。PgAdmin4 は Linux、UNIX、Mac OS、および Windows で使用して PostgreSQL を管理できます。  

## エピック


### アプローチ 1：Amazon S3 からの Aurora PostgreSQL 互換のデータをインポート



| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| EC2 インスタンスの起動 | インスタンスを起動する手順については、[インスタンスを起動する](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/LaunchingAndUsingInstances.html) を参照してください。 | DBA | 
| PostgreSQL クライアント pgAdmin ツールをインストールします。 | [pgAdmin](https://www.pgadmin.org/download/) をダウンロードし、インストールします。 | DBA | 
| IAM ポリシーを作成します。 | ファイルが保存される S3 バケットへのアクセスを許可する `aurora-s3-access-pol` と名前付けられたAWS アイデンティティアクセス管理(IAM) ポリシーを作成します。次のコードを使用して、S3 バケットの名で `<bucket-name>` を置き換わります。<pre>{<br />    "Version": "2012-10-17",		 	 	 <br />    "Statement": [<br />        {<br />            "Effect": "Allow",<br />            "Action": [<br />                "s3:GetObject",<br />                "s3:AbortMultipartUpload",<br />                "s3:DeleteObject",<br />                "s3:ListMultipartUploadParts",<br />                "s3:PutObject",<br />                "s3:ListBucket"<br />            ],<br />            "Resource": [<br />                "arn:aws:s3:::<bucket-name>/*",<br />                "arn:aws:s3:::<bucket-name>"<br />            ]<br />        }<br />    ]<br />}</pre> | DBA | 
| Amazon S3 から Aurora PostgreSQL 互換にオブジェクトをインポートするための IAM ロールを作成します。 | 次のコードを使用して、[AssumeRole](https://docs.amazonaws.cn/en_us/STS/latest/APIReference/API_AssumeRole.html) 信頼関係を持ち、`aurora-s3-import-role` と名前付けられた IAM ロールを作成します。`AssumeRole` は、ユーザーの代わりに Aurora が他の AWS のサービスにアクセスすることを許可します。<pre>{<br />  "Version": "2012-10-17",		 	 	 <br />  "Statement": [<br />    {<br />      "Effect": "Allow","Principal": {<br />        "Service": "rds.amazonaws.com"<br />      },"Action": "sts:AssumeRole"<br />    }<br />  ]<br />}<br /></pre> | DBA | 
| IAM ロールをクラスターに関連付けます。 | IAM ロールを Aurora PostgreSQL 互換データベースクラスターに関連付けるには、次の AWS CLI コマンドを実行します。Aurora PostgreSQL 互換データベースをホストする AWS アカウントの ID を `<Account-ID>` に変更します。これにより、Aurora PostgreSQL 互換データベースが S3 バケットにアクセスできるようになります。<pre>aws rds add-role-to-db-cluster --db-cluster-identifier aurora-postgres-cl<br />--feature-name s3Import --role-arn arn:aws:iam::<Account-ID>:role/aurora-s3-import-role</pre> | DBA | 
| このファイルを Amazon S3 にアップロードします。 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/prescriptive-guidance/latest/patterns/load-blob-files-into-text-by-using-file-encoding-in-aurora-postgresql-compatible.html) | DBA、アプリ所有者 | 
| カスタム関数をデプロイします。 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/prescriptive-guidance/latest/patterns/load-blob-files-into-text-by-using-file-encoding-in-aurora-postgresql-compatible.html) | アプリ所有者、DBA | 
| データをデータベースにインポートするために、カスタム関数を実行します。 | 次の SQL コマンドを実行し、山括弧内の項目を適切な値に置き換えます。<pre>select load_file_into_clob('aws-s3-import-test'::text,'us-west-1'::text,'employee.salary.event.notification.email.vm'::text);</pre>コマンドを実行する前に、山括弧内の項目を、次の例に示されているように、適切な値に置き換わります。<pre>Select load_file_into_clob('aws-s3-import-test'::text,'us-west-1'::text,'employee.salary.event.notification.email.vm'::text);</pre>このコマンドは Amazon S3 からファイルをロードし、`TEXT` として出力を返します。 | アプリ所有者、DBA | 

### アプローチ 2：テンプレートファイルをローカル Linux システムの 16 進数ダンプに変換



| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| テンプレートファイルを 16 進数ダンプに変換します。 | Hexdump ユーティリティは、バイナリファイルの内容を 16 進数、10 進数、8 進数、または ASCII 形式で表示します。`hexdump` コマンドが `util-linux` パッケージの一部で、、Linux ディストリビューションにプリインストールされています。Hexdump RPM パッケージは Amazon Linux 2 の一部でもあります。(: Amazon Linux 2 のサポートは間もなく終了します。詳細については、「[Amazon Linux 2 のよくある質問](https://aws.amazon.com/amazon-linux-2/faqs/)」を参照してください。）ファイルの内容を 16 進数ダンプに変換するには、次のシェルコマンドを実行します。<pre>xxd -p </path/file.vm> | tr -d '\n' > </path/file.hex></pre>パスとファイルを次の例に示されているように、適切な値に置き換わります。<pre>xxd -p employee.salary.event.notification.email.vm | tr -d '\n' > employee.salary.event.notification.email.vm.hex</pre> | DBA | 
| hexdump ファイルをデータベーススキーマにロードします。 | 次のコマンドを使用して、hexdump ファイルを Aurora PostgreSQL 互換データベースにロードします。[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/prescriptive-guidance/latest/patterns/load-blob-files-into-text-by-using-file-encoding-in-aurora-postgresql-compatible.html) | DBA | 

## 関連リソース


**リファレンス**
+ [PostgreSQL データベースを AWS Database Migration Service のソースとして使用する](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html)
+ [Oracle データベース 19c から PostgreSQL 互換 (12.4) 対応 Amazon Aurora への移行プレイブック](https://d1.awsstatic.com/whitepapers/Migration/oracle-database-amazon-aurora-postgresql-migration-playbook-12.4.pdf)
+ [IAM ポリシーの作成](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_create.html)
+ [IAM ロールと Amazon Aurora MySQL DB クラスターの関連付け](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.Authorizing.IAM.AddRoleToDBCluster.html)
+ [pgAdmin](https://www.pgadmin.org/)

**チュートリアル**
+ [Amazon RDS の開始方法](https://aws.amazon.com/rds/getting-started/)
+ [ Oracle から Amazon Aurora への移行](https://aws.amazon.com/getting-started/projects/migrate-oracle-to-amazon-aurora/)

## 追加情報


**load\$1file\$1into\$1clob カスタム関数**

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

**E メールテンプレート**

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