これは一連の投稿のXNUMX番目です。 スキーマ変換を自動化するための多数の新機能を共有できることをうれしく思います。 既存のスクリプト、レポート、およびアプリケーションへの投資を維持します。 クエリのパフォーマンスを高速化します。 レガシーデータウェアハウスからへの移行を簡素化する可能性があります Amazonレッドシフト.

Amazon Redshiftは、主要なクラウドデータウェアハウスです。 データから新しい洞察を簡単に得ることができるデータウェアハウスは他にありません。 Amazon Redshiftを使用すると、標準SQLを使用して、データウェアハウス、運用データストア、およびデータレイク全体でエクサバイトのデータをクエリできます。 次のような他のAWSサービスを統合することもできます アマゾンEMR, アマゾンアテナ, アマゾンセージメーカー, AWSグルー, AWSレイクフォーメーション, アマゾンキネシス AWSクラウドのすべての分析機能を使用します。

これまで、データウェアハウスをAWSに移行することは複雑な作業であり、かなりの手作業が必要でした。 構文の違いを手動で修正し、独自の機能を置き換えるコードを挿入し、新しいプラットフォームでクエリとレポートのパフォーマンスを手動で調整する必要があります。

従来のワークロードは、AmazonRedshiftなどの最新のデータベースでは直接サポートされていない非ANSI独自の機能に依存している場合があります。 たとえば、多くのTeradataアプリケーションはSETテーブルを使用します。これにより、完全な行の一意性が強制されます。テーブル内に、すべての属性値が同一のXNUMXつの行を含めることはできません。

Amazon Redshiftユーザーの場合、SETセマンティクスを実装することはできますが、ネイティブデータベース機能に依存することはできません。 この投稿のデザインパターンを使用して、SQLコードでSETセマンティクスをエミュレートできます。 または、ワークロードをAmazon Redshiftに移行する場合は、 AWSスキーマ変換ツール （AWS SCT）コード変換の一部としてデザインパターンを自動的に適用します。

この投稿では、SQLデザインパターンについて説明し、そのパフォーマンスを分析し、AWSSCTがデータウェアハウスの移行の一部としてこれを自動化する方法を示します。 まず、TeradataでSETテーブルがどのように動作するかを理解することから始めましょう。

TeradataSETテーブル

一見すると、SETテーブルは、すべての列に主キーが定義されているテーブルに似ているように見えます。 ただし、従来の主キーとは意味上の重要な違いがいくつかあります。 Teradataの次のテーブル定義を検討してください。

CREATE SET TABLE testschema.sales_by_month ( sales_dt DATE
, amount DECIMAL(8,2)
);

次のように、テーブルにXNUMX行のデータを入力します。

select * from testschema.sales_by_month order by sales_dt; *** Query completed. 4 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/02 200.00
22/01/03 300.00
22/01/04 400.00

テーブルにUNIQUEPRIMARYINDEX（主キーと同様）を定義していないことに注意してください。 ここで、既存の行の複製である新しい行をテーブルに挿入しようとすると、挿入は失敗します。

INSERT IGNORE INTO testschema.sales_by_month values (20220101, 100); *** Failure 2802 Duplicate row error in testschema.sales_by_month. Statement# 1, Info =0 *** Total elapsed time was 1 second.

同様に、既存の行を更新して別の行の複製になるようにしようとすると、更新は失敗します。

UPDATE testschema.sales_by_month SET sales_dt = 20220101, amount = 100
WHERE sales_dt = 20220104 and amount = 400; *** Failure 2802 Duplicate row error in testschema.sales_by_month. Statement# 1, Info =0 *** Total elapsed time was 1 second.

つまり、単純なINSERT-VALUEステートメントとUPDATEステートメントは、TeradataSETテーブルに重複する行を導入すると失敗します。

この規則には注目すべき例外があります。 ターゲットテーブルと同じ属性を持つ次のステージングテーブルについて考えてみます。

CREATE MULTISET TABLE testschema.sales_by_month_stg ( sales_dt DATE
, amount DECIMAL(8,2)
);

ステージングテーブルはMULTISETテーブルであり、重複する行を受け入れます。 ステージングテーブルにXNUMXつの行を入力します。 最初の行は、ターゲットテーブルの行の複製です。 XNUMX行目とXNUMX行目は互いに重複していますが、ターゲット行を重複させないでください。

select * from testschema.sales_by_month_stg; *** Query completed. 3 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/05 500.00
22/01/05 500.00

これで、ステージングデータがターゲットテーブル（SETテーブル）に正常に挿入されました。

INSERT IGNORE INTO testschema.sales_by_month (sales_dt, amount)
SELECT sales_dt, amount FROM testschema.sales_by_month_stg; *** Insert completed. One row added. *** Total elapsed time was 1 second.

ターゲットテーブルを調べると、（2022-01-05、500）の単一の行が挿入され、（2022-01-01、100）の重複行が破棄されていることがわかります。 基本的に、Teradataは、INSERT-SELECTステートメントを実行するときに、重複する行をサイレントに破棄します。 これには、ステージングテーブルにある重複と、ステージングテーブルとターゲットテーブルの間で共有される重複が含まれます。

select * from testschema.sales_by_month order by sales_dt; *** Query completed. 6 rows found. 2 columns returned. *** Total elapsed time was 1 second. sales_dt amount
-------- ----------
22/01/01 100.00
22/01/02 200.00
22/01/03 300.00
22/01/03 200.00
22/01/04 400.00
22/01/05 500.00

基本的に、SETテーブルは、実行されている操作のタイプに応じて異なる動作をします。 INSERT-VALUEまたはUPDATE操作は、ターゲットに重複する行を導入すると失敗します。 ステージングテーブルに重複する行が含まれている場合、または重複する行がステージングテーブルとテーブルテーブルの間で共有されている場合、INSERT-SELECT操作で障害が発生することはありません。

この投稿では、INSERT-VALUEまたはUPDATEステートメントを変換する方法については詳しく説明しません。 これらのステートメントは通常、XNUMX行または数行を含み、INSERT-SELECTステートメントよりもパフォーマンスの点で影響が少なくなります。 INSERT-VALUEまたはUPDATEステートメントの場合、作成中のXNUMXつまたは複数の行を実体化し、そのセットをターゲット表に結合して重複をチェックできます。

挿入-選択

この投稿の残りの部分では、INSERT-SELECTステートメントを注意深く分析します。 お客様から、INSERT-SELECT操作はSETテーブルに対するINSERTワークロードの最大78％を占める可能性があるとのことです。 次の形式のステートメントに関係します。

INSERT into <target table> SELECT * FROM <staging table>

ステージングテーブルのスキーマは、列ごとにターゲットテーブルと同じです。 前に述べたように、重複する行はXNUMXつの異なる状況で表示される可能性があります。

• ステージングテーブルは設定されていません-一意です。つまり、ステージングデータにXNUMXつ以上の完全な行の重複があります
• ステージングテーブルに行xがあり、ターゲットテーブルに同じ行xがあります

Amazon Redshiftはマルチセットテーブルセマンティクスをサポートしているため、ステージングテーブルに重複が含まれている可能性があります（最初にリストした状況）。 したがって、自動化では両方のケースに対処する必要があります。どちらもAmazonRedshiftテーブルに重複を導入する可能性があるためです。

この分析に基づいて、次のアルゴリズムを実装しました。

• マイナス –これは、SQLMINUSを使用してフルセットのロジック重複排除を実装します。 MINUSは、ステージングテーブルが設定されていない場合や、ステージングテーブルとターゲットテーブルの共通部分が空でない場合を含め、すべての場合に機能します。 MINUSには、NULLとNULLの比較を克服するためにNULL値が特別な比較ロジックを必要としないという利点もあります。 MINUSの構文は次のとおりです。

  INSERT IGNORE INTO <target table> (<column list>)
  SELECT <column list> FROM <staging table> MINUS
  SELECT <column list> FROM <target table>;

• マイナス-最小-最大 –これは、ステージテーブルの値に基づいてターゲットテーブルスキャンを制限するフィルターを組み込んだMINUSの最適化です。 最小/最大フィルターを使用すると、クエリエンジンはテーブルスキャン中に多数のブロックをスキップできます。 見る ソートキーの操作 のガイドをご参照ください。

  INSERT IGNORE INTO <target table>(<column list>)
  SELECT <column list> FROM <staging table> MINUS
  SELECT <column list> FROM <target table>
  WHERE <target table>.<sort key> >= (SELECT MIN(<sort key>) FROM <staging table>) AND <target table>).<sort key> <= (SELECT MAX(<sort key>) FROM <staging table>)
  );

他のアルゴリズムも検討しましたが、それらを使用することはお勧めしません。 たとえば、GROUP BYを実行してステージングテーブルの重複を排除できますが、MINUS演算子を使用する場合は、この手順は不要です。 左（または右）外部結合を実行して、ステージングテーブルとターゲットテーブルの間で共有されている重複を見つけることもできますが、NULL=NULL条件を説明するために追加のロジックが必要です。

性能

AmazonRedshiftでMINUSおよびMINUS-MIN-MAXアルゴリズムをテストしました。 6つのAmazonRedshiftクラスターでアルゴリズムを実行しました。 最初の構成は、3.4xra12xlargeノードで構成されていました。 3.4つ目は、12xra96xlargeノードで構成されていました。 各ノードには、XNUMX個のCPUとXNUMXGBのメモリが含まれていました。

データの移動を最小限に抑えるために、同じソートキーと配布キーを使用してステージテーブルとターゲットテーブルを作成しました。 同じターゲットデータセットを両方のクラスターにロードしました。 ターゲットデータセットは、1.1億行のデータで構成されていました。 次に、20万行単位で200万行から20億行の範囲のステージングデータセットを作成しました。

次のグラフは、結果を示しています。

テストデータは人為的に生成されたものであり、分布キー値にいくらかのスキューが存在していました。 これは、パフォーマンスの直線性からのわずかな逸脱に現れます。

ただし、基本的なMINUSアルゴリズム（オレンジ色の線または青色の線をそれ自体と比較）よりもMINUS-MIN-MAXアルゴリズムによって得られるパフォーマンスの向上を確認できます。 Amazon RedshiftでSETテーブルを実装している場合は、MINUS-MIN-MAXを使用することをお勧めします。これは、このアルゴリズムがシンプルで読みやすいコードと優れたパフォーマンスの幸せな収束を提供するためです。

オートメーション

すべてのAmazonRedshiftテーブルは重複行を許可します。つまり、デフォルトではMULTISETテーブルです。 TeradataワークロードをAmazonRedshiftで実行するように変換する場合は、データベースの外部でSETセマンティクスを適用する必要があります。

AWSSCTがSETテーブルに対して動作するSQLコードを自動的に変換することを喜ばしく思います。 AWS SCTは、SETテーブルをロードするINSERT-SELECTをリライトして、上記のリライトパターンを組み込みます。

これがどのように機能するか見てみましょう。 Teradataに次のターゲットテーブル定義があるとします。

CREATE SET TABLE testschema.fact ( id bigint NOT NULL
, se_sporting_event_id INTEGER NOT NULL
, se_sport_type_name VARCHAR(15) NOT NULL
, se_home_team_id INTEGER NOT NULL
, se_away_team_id INTEGER NOT NULL
, se_location_id INTEGER NOT NULL
, se_start_date_time DATE NOT NULL
, se_sold_out INTEGER DEFAULT 0 NOT NULL
, stype_sport_type_name varchar(15) NOT NULL
, stype_short_name varchar(10) NOT NULL
, stype_long_name varchar(60) NOT NULL
, stype_description varchar(120)
, sd_sport_type_name varchar(15) NOT NULL
, sd_sport_league_short_name varchar(10) NOT NULL
, sd_short_name varchar(10) NOT NULL
, sd_long_name varchar(60)
, sd_description varchar(120)
, sht_id INTEGER NOT NULL
, sht_name varchar(30) NOT NULL
, sht_abbreviated_name varchar(10)
, sht_home_field_id INTEGER , sht_sport_type_name varchar(15) NOT NULL
, sht_sport_league_short_name varchar(10) NOT NULL
, sht_sport_division_short_name varchar(10)
, sat_id INTEGER NOT NULL
, sat_name varchar(30) NOT NULL
, sat_abbreviated_name varchar(10)
, sat_home_field_id INTEGER , sat_sport_type_name varchar(15) NOT NULL
, sat_sport_league_short_name varchar(10) NOT NULL
, sat_sport_division_short_name varchar(10)
, sl_id INTEGER NOT NULL
, sl_name varchar(60) NOT NULL
, sl_city varchar(60) NOT NULL
, sl_seating_capacity INTEGER
, sl_levels INTEGER
, sl_sections INTEGER
, seat_sport_location_id INTEGER
, seat_seat_level INTEGER
, seat_seat_section VARCHAR(15)
, seat_seat_row VARCHAR(10)
, seat_seat VARCHAR(10)
, seat_seat_type VARCHAR(15)
, pb_id INTEGER NOT NULL
, pb_full_name varchar(60) NOT NULL
, pb_last_name varchar(30)
, pb_first_name varchar(30)
, ps_id INTEGER NOT NULL
, ps_full_name varchar(60) NOT NULL
, ps_last_name varchar(30)
, ps_first_name varchar(30)
)
PRIMARY INDEX(id)
;

ステージテーブルは、TeradataでMULTISETテーブルとして作成されることを除いて、ターゲットテーブルと同じです。

次に、ステージテーブルからファクトテーブルをロードするプロシージャを作成します。 プロシージャには、単一のINSERT-SELECTステートメントが含まれています。

REPLACE PROCEDURE testschema.insert_select() BEGIN INSERT IGNORE INTO testschema.test_fact SELECT * FROM testschema.test_stg;
END;

ここで、AWS SCTを使用して、TeradataストアドプロシージャをAmazonRedshiftに変換します。 まず、ソースデータベースツリーでストアドプロシージャを選択し、次に右クリックして選択します スキーマを変換する.

AWS SCTは、MINUS-MIN-MAXリライトパターンを使用して、ストアドプロシージャ（および埋め込みINSERT-SELECT）を変換します。

以上です！ 現在、AWS SCTはINSERT-SELECTのリライトのみを実行します。これは、これらのステートメントがETLワークロードによって頻繁に使用され、パフォーマンスに最も影響を与えるためです。 使用した例はストアドプロシージャに埋め込まれていますが、AWS SCTを使用して、同じステートメントがBTEQスクリプト、マクロ、またはアプリケーションプログラムにある場合はそれらを変換することもできます。 ダウンロード AWSSCTの最新バージョン 試してみてください！

まとめ

この投稿では、AmazonRedshiftでSETテーブルのセマンティクスを実装する方法を示しました。 説明したデザインパターンを使用して、SETセマンティクスを必要とする新しいアプリケーションを開発できます。 または、既存のTeradataワークロードを変換する場合は、AWS SCTを使用してINSERT-SELECTステートメントを自動的に変換し、SETテーブルのセマンティクスを保持することができます。

このシリーズの次の記事でまもなく戻ってきます。 TeradataからAmazonRedshiftへの移行の自動化の詳細については、もう一度確認してください。 それまでの間、詳細についてはこちらをご覧ください Amazonレッドシフト & AWS SCT。 幸せな移行！

著者について

マイケル・ス AWS DatabaseMigrationServiceチームのプリンシパルデータベースエンジニアです。 彼は、顧客がデータベースワークロードをAWSクラウドに移行するのに役立つ製品とサービスを構築しています。

ポーホン博士、 は、AW​​SProfessionalServicesのModernDataArchitecture Global Specialty Practice（GSP）のプリンシパルデータアーキテクトです。 彼は、顧客が革新的なソリューションを採用し、大規模なMPPデータウェアハウスからAWSの最新のデータアーキテクチャに移行するのを支援することに情熱を注いでいます。

• コインスマート。 ヨーロッパで最高のビットコインと暗号通貨取引所。
• Platoblockchain。 Web3メタバースインテリジェンス。 知識の増幅。 出入り自由。
• CryptoHawk。 アルトコインレーダー。 無料トライアル。
• ソース：https：//aws.amazon.com/blogs/big-data/part-5-accelerate-your-data-warehouse-migration-to-amazon-redshift/