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

# SQL Server から PostgreSQL に移行する際に、PII データに SHA1 ハッシュを実装する
<a name="implement-sha1-hashing-for-pii-data-when-migrating-from-sql-server-to-postgresql"></a>

*Amazon Web Services、Rajkumar Raghuwanshi および Jagadish Kantubugata*

## 概要
<a name="implement-sha1-hashing-for-pii-data-when-migrating-from-sql-server-to-postgresql-summary"></a>

このパターンでは、SQL Server から Amazon RDS for PostgreSQL または Amazon Aurora PostgreSQL 互換に移行する際に、E メールアドレスにセキュアハッシュアルゴリズム 1 (SHA1) ハッシュを実装する方法について説明します。E メールアドレスは、*個人を特定できる情報* (PII) の一例です。PII は、直接閲覧した場合、または他の関連データと組み合わせた場合に、個人の身元を合理的に推測するために使用できる情報です。

このパターンでは、さまざまなデータベースの照合順序と文字エンコーディングにわたって一貫したハッシュ値を維持するという課題について説明し、PostgreSQL 関数とトリガーを使用したソリューションを提供します。このパターンは SHA1 ハッシュに重点を置いていますが、PostgreSQL の `pgcrypto` モジュールでサポートされている他のハッシュアルゴリズムにも適応できます。機密データを扱う場合には、ハッシュ戦略のセキュリティ上の影響を常に考慮し、セキュリティエキスパートに相談してください。

## 前提条件と制限事項
<a name="implement-sha1-hashing-for-pii-data-when-migrating-from-sql-server-to-postgresql-prereqs"></a>

**前提条件**
+ アクティブな AWS アカウント
+ ソース SQL Server データベース
+ ターゲット PostgreSQL データベース (Amazon RDS for PostgreSQL または Aurora PostgreSQL 互換)
+ PL/PgSQL コーディングの専門知識

**制限事項**
+ このパターンでは、ユースケースに基づいてデータベースレベルの照合順序を変更する必要があります。
+ 大規模なデータセットに対するパフォーマンスの影響は評価されていません。
+ 一部の AWS のサービス は、すべてで利用できるわけではありません AWS リージョン。利用可能なリージョンについては、「[AWS サービス (リージョン別)](https://aws.amazon.com/about-aws/global-infrastructure/regional-product-services/)」を参照してください。特定のエンドポイントについては、[「サービスエンドポイントとクォータ](https://docs.aws.amazon.com/general/latest/gr/aws-service-information.html)」を参照して、サービスのリンクを選択してください。

**製品バージョン**
+ Microsoft SQL Server 2012 以降

## アーキテクチャ
<a name="implement-sha1-hashing-for-pii-data-when-migrating-from-sql-server-to-postgresql-architecture"></a>

**ソーステクノロジースタック **
+ SQL Server
+ 特定のランタイムライブラリまたは .NET Framework の最小バージョンが必要です。

**ターゲットテクノロジースタック**
+ [PostgreSQL]
+ `pgcrypto` エクステンション

**自動化とスケール**
+ メンテナンスを容易にするために、ハッシュ関数をストアドプロシージャとして実装することを検討します。
+ 大規模なデータセットの場合は、パフォーマンスを評価し、バッチ処理またはインデックス作成戦略を検討します。

## ツール
<a name="implement-sha1-hashing-for-pii-data-when-migrating-from-sql-server-to-postgresql-tools"></a>

**AWS のサービス**
+ [Amazon Aurora PostgreSQL 互換](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html)は、PostgreSQL デプロイのセットアップ、運用、スケーリングを支援するフルマネージド型の ACID 互換リレーショナルデータベースエンジンです。
+ [AWS Database Migration Service (AWS DMS)](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) を使用すると、データストアを に移行する AWS クラウド か、クラウドとオンプレミスのセットアップの組み合わせ間で移行できます。
+ [Amazon Relational Database Service Amazon RDS for PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) を使用すると、 AWS クラウドで PostgreSQL リレーショナルデータベースのセットアップ、運用、スケーリングができます。
+ [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) は、ソースデータベーススキーマとカスタムコードの大部分をターゲットデータベースと互換性のある形式に自動的に変換することで、異種データベースの移行をサポートします。

**その他のツール**
+ 「[pgAdmin](https://www.pgadmin.org/)」 は PostgreSQL 用のオープンソース管理ツールです。データベースオブジェクトの作成、管理、使用を支援するグラフィカルインターフェイスを提供します。
+ [SQL Server Management Studio (SSMS)](https://learn.microsoft.com/en-us/ssms/sql-server-management-studio-ssms) は、SQL Server インフラストラクチャを管理するための統合環境です。

## ベストプラクティス
<a name="implement-sha1-hashing-for-pii-data-when-migrating-from-sql-server-to-postgresql-best-practices"></a>
+ ターゲットデータベース側で特殊文字を処理するには、適切な照合順序設定を使用します。
+ 非 ASCII 文字のアドレスなど、さまざまな E メールアドレスで徹底的にテストします。
+ アプリケーションレイヤーとデータベースレイヤーの間で大文字と小文字の処理の一貫性を維持します。
+ ハッシュ値を使用してクエリのパフォーマンスをベンチマークします。

## エピック
<a name="implement-sha1-hashing-for-pii-data-when-migrating-from-sql-server-to-postgresql-epics"></a>

### ソースハッシュの実装を分析する
<a name="analyze-source-hashing-implementation"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| SQL Server コードを確認します。 | SHA1 ハッシュを生成する SQL Server コードを確認するには、以下を行います。+ SHA1 ハッシュの既存の SQL Server の実装を分析します。<br />+ ハッシュ生成に使用される正確な方法を特定します。<br />+ 入力パラメータと出力形式を文書化します。<br />+ データ型の変換を確認します。<br />+ 照合順序設定とそれらの影響を調べます。 | データエンジニア、DBA、アプリケーション開発者 | 
| ハッシュアルゴリズムとデータ変換を文書化します。 | ハッシュアルゴリズムとデータ変換を正確に文書化するには、以下を行います。+ ハッシュプロセスに関する詳細な技術ドキュメントを作成します。<br />+ ステップバイステップの変換ロジックを文書化します。<br />+ 入出力形式とデータ型を指定します。<br />+ エッジケースと特殊文字の処理を含めます。 | アプリケーション開発者、データエンジニア、DBA | 

### PostgreSQL ハッシュ関数を作成する
<a name="create-postgresql-hashing-function"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| `pgcrypto` エクステンションを作成します。 | `pgcrypto` エクステンションを作成するには、`pgAdmin/psql` を使用して次のコマンドを実行します。<pre>CREATE EXTENSION pgcrypto;</pre> | DBA、データエンジニア | 
| PostgreSQL 関数を実装します。 | SQL Server のハッシュロジックをレプリケートするには、次の PostgreSQL 関数を実装します。大まかに言うと、この関数では次のステップを使用します。1. オプションで、入力を大文字に変換します。<br />2. 入力の SHA1 ハッシュを作成します。<br />3. このハッシュの最後の 10 バイト (80 ビット) を取得します。<br />4. これらのバイトを 64 ビットの整数に変換します。<pre>CREATE OR REPLACE FUNCTION utility.hex_to_bigint ( <br />     par_val character varying, <br />     par_upper character varying DEFAULT 'lower'::character varying) <br />RETURNS bigint <br />LANGUAGE 'plpgsql' <br />AS $BODY$ <br />DECLARE <br />    retnumber bigint; <br />    digest_bytes bytea;<br />BEGIN <br />    if lower(par_upper) = 'upper' <br />    then <br />        digest_bytes := digest(upper(par_val), 'sha1');<br />    else <br />        digest_bytes := digest((par_val), 'sha1');<br />    end if; <br />    retnumber := ('x' || encode(substring(digest_bytes, length(digest_bytes)-10+1), 'hex'))::bit(64)::bigint; <br />    RETURN retnumber; <br />END; <br />$BODY$;</pre> | データエンジニア、DBA、アプリケーション開発者 | 
| 関数をテストします。 | 関数をテストするには、SQL Server のサンプルデータを使用して、一致するハッシュ値を検証します。次のコマンドを実行します。<pre>select 'alejandro_rosalez@example.com' as Email, utility.hex_to_bigint('alejandro_rosalez@example.com','upper') as HashValue;<br /><br />--OUTPUT<br />/*<br />email 	        hashvalue<br />"alejandro_rosalez@example.com"	451397011176045063<br />*/<br /></pre> | アプリ開発者、DBA、データエンジニア | 

### 自動ハッシュのトリガーを実装する
<a name="implement-triggers-for-automatic-hashing"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| 関連するテーブルでトリガーを作成します。 | 挿入または更新時にハッシュ値を自動的に生成するためのトリガーを、関連するテーブルで作成するには、次のコマンドを実行します。<pre>CREATE OR REPLACE FUNCTION update_email_hash() <br />RETURNS TRIGGER <br />AS $$ <br />BEGIN <br />    NEW.email_hash = utility.hex_to_bigint(NEW.email, 'upper'); <br />    RETURN NEW; <br />END; <br />$$ LANGUAGE plpgsql;</pre><pre>CREATE TRIGGER email_hash_trigger BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_email_hash();</pre> | アプリケーション開発者、データエンジニア、DBA | 

### 既存のデータを移行する
<a name="migrate-existing-data"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| 移行スクリプトを開発するか、 を使用します AWS DMS。 | 移行スクリプトを作成するか AWS DMS 、 を使用して既存のデータのハッシュ値 (`BIGINT`ソースシステムに として保存されているハッシュ値を含む) を入力します。以下のタスクを実行します。+ ハッシュ値を使用してデータ転送用の移行スクリプトを作成します。<br />+ 適切な変換ルールを使用して AWS DMS タスクを設定します。<br />+ でソースエンドポイントとターゲットエンドポイントを設定します AWS DMS。<br />+ エラー処理とログ記録メカニズムを実装します。<br />+ 大規模なデータセットのバッチ処理戦略を設計します。<br />+ データ検証のための検証クエリを作成します。 | データエンジニア、アプリケーション開発者、DBA | 
| 新しい PostgreSQL ハッシュ関数を使用します。 | 新しい PostgreSQL ハッシュ関数を使用して一貫性を確保するには、以下を行います。+ ハッシュの一貫性を検証するための検証手順を実装します。<br />+ ソースシステムとターゲットシステム間の比較スクリプトを作成します。<br />+ ハッシュ値の検証のための自動テストをセットアップします。<br />+ 不一致と解決手順を文書化します。 | アプリ開発者、DBA、DevOps エンジニア | 

### アプリケーションクエリを更新する
<a name="update-application-queries"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| アプリケーションクエリを特定します。 | ハッシュ値を使用するアプリケーションクエリを特定するには、以下を行います。+ ハッシュ値を使用して、クエリのアプリケーションコードベースを分析します。<br />+ ハッシュ操作を参照するストアドプロシージャと関数を確認します。<br />+ クエリのパフォーマンスメトリクスと実行計画を文書化します。<br />+ ハッシュベースのルックアップにおける依存関係を特定します。<br />+ 影響を受けるアプリケーションコンポーネントをマッピングします。 | アプリ開発者、DBA、データエンジニア | 
| クエリを変更します。 | 必要に応じて、新しい PostgreSQL ハッシュ関数を使用するようにクエリを変更します。以下の操作を実行します。+ PostgreSQL ハッシュ関数を使用するように既存のクエリをリファクタリングします。<br />+ ストアドプロシージャと関数を更新します。<br />+ 新しいクエリパターンを実装してテストします。<br />+ 変更されたクエリをパフォーマンスのために最適化します。 | アプリ開発者、DBA、データエンジニア | 

### テストと検証
<a name="test-and-validate"></a>


| タスク | 説明 | 必要なスキル | 
| --- | --- | --- | 
| テストを実行します。 | 本番データのサブセットを使用して徹底的なテストを実行するには、以下を行います。+ データサブセットの検証用のテストプランを作成します。<br />+ 本番データの代表的なサンプルを抽出します。<br />+ 適切な構成でテスト環境をセットアップします。<br />+ データ読み込みと変換のテストを実行します。<br />+ ボリュームとストレスのテストを実行します。 | アプリケーション開発者、データエンジニア、DBA | 
| ハッシュ値が一致することを検証します。 | SQL Server と PostgreSQL 間でハッシュ値が一致することを検証するには、以下を行います。+ ハッシュ値の比較スクリプトを作成します。<br />+ ハッシュマッチングの検証レポートを作成します。<br />+ 自動検証手順を実装します。<br />+ 見つかった不一致をすべて文書化します。<br />+ ハッシュの不一致を分析して解決します。 | アプリケーション開発者、データエンジニア、DBA | 
| アプリケーションの機能を検証します。 | 移行されたデータと新しいハッシュ実装を使用してアプリケーションの機能を検証するには、以下を行います。+ エンドツーエンドのアプリケーションテストを実行します。<br />+ ハッシュ化されたデータを使用して、すべてのアプリケーション機能を検証します。<br />+ 新しい実装で、アプリケーションのパフォーマンスをテストします。<br />+ API 統合と依存関係を検証します。 | アプリ開発者、DBA、データエンジニア | 

## トラブルシューティング
<a name="implement-sha1-hashing-for-pii-data-when-migrating-from-sql-server-to-postgresql-troubleshooting"></a>


| 問題 | ソリューション | 
| --- | --- | 
| ハッシュ値が一致しません。 | ソースとターゲット間の文字エンコードと照合順序を検証します。詳細については、「[Manage collation changes in PostgreSQL on Amazon Aurora and Amazon RDS](https://aws.amazon.com/blogs/database/manage-collation-changes-in-postgresql-on-amazon-aurora-and-amazon-rds/)」(AWS ブログ) を参照してください。 | 

## 関連リソース
<a name="implement-sha1-hashing-for-pii-data-when-migrating-from-sql-server-to-postgresql-resources"></a>

**AWS ブログ**
+ [Amazon Aurora および Amazon RDS で PostgreSQL の照合順序の変更を管理する](https://aws.amazon.com/blogs/database/manage-collation-changes-in-postgresql-on-amazon-aurora-and-amazon-rds/)
+ [Migrate SQL Server to Amazon Aurora PostgreSQL using best practices and lessons learned from the field](https://aws.amazon.com/blogs/database/migrate-sql-server-to-amazon-aurora-postgresql-using-best-practices-and-lessons-learned-from-the-field/)

**その他のリソース**
+ [PostgreSQL pgcrypto モジュール](https://www.postgresql.org/docs/current/pgcrypto.html) (PostgreSQL ドキュメント)
+ [PostgreSQL トリガー関数](https://www.postgresql.org/docs/current/plpgsql-trigger.html) (PostgreSQL ドキュメント)
+ [SQL Server HASHBYTES 関数](https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql) (Microsoft ドキュメント)