XplentyのCTOでもあるMark Smallcombeによるブログ「Redshift Performance Tuning Techniques: View, Insert, Join, and More」について翻訳したものを紹介します。
はじめに
Amazon Redshiftは "世界最速のクラウドデータウェアハウス "と謳われています。しかし、フェラーリでも、たまにはチューンナップが必要です。慢性的な不調や謎のクラッシュを経験しているかどうかに関わらず、Redshiftにはフラストレーションや課題があります。
幸いなことに、これらの問題の大部分は解決することができます。以前の記事では、Amazon Redshiftのパフォーマンスチューニングテクニックトップ14を含む、一般的なRedshiftのベストプラクティスについて書いてきました。今回は、Redshiftの特定のアクション（Redshiftでビュー、挿入、結合、更新を実行する）の効率を向上させることに焦点を当てていきます。
Redshiftビュー パフォーマンスチューニング
データベースビューは、データベーステーブルに対するクエリの結果として、特定のデータベースのサブセットです。ビューには、データベーススキーマの設計、データの単純化や要約、複数のテーブルからの情報の結合など、さまざまな目的があります。
しかし、Redshiftのビューパフォーマンスには1つの大きな問題があります：ビューにアクセスするたびに、Redshift上ではそのビューに対応する基礎となるデータベースクエリを評価する必要があります。クエリ自体が非効率であれば、ビューへのアクセスも同様にイライラするほど遅くなります。
Redshiftビューのパフォーマンスを向上させるために、ユーザーはCREATE TABLE AS SELECT (CTAS)やマテリアライズド・ビューなど、複数のオプションを用意しています。
CREATE TABLE AS SELECT (CTAS)
SQLのCREATE TABLE AS SELECT (CTAS)文は、既存のテーブルから列をコピーし、それらの列から新しいテーブルを作成します。コマンドの正式な構文は以下の通りです。
CREATE TABLE new AS (SELECT * FROM old).
CTASはRedshiftビューのパフォーマンスを向上させるために非常に有用なツールであり、CTASによって生成されたテーブルは他のビューやテーブルと同じように使用することができます。しかし、Redshiftビューのパフォーマンスを向上させるためにCTASを使用するには、いくつかの重要な注意点があります。
- CTAS テーブルは、下のテーブルのデータが変更されてもリフレッシュされません。これは、手動でCTASテーブルをリフレッシュする必要があることを意味します。
- 関連する注意点として、手動でCTASのリフレッシュを行うには、ユーザーによるかなりの監視が必要になります。RedshiftはテーブルがCTASコマンドによって作成されたかどうかを表示しないので、ユーザーはこの情報を追跡し、いつ更新を実行するかを判断しなければなりません。 これらの理由から、多くのRedshiftユーザーは、Redshiftのビューパフォーマンスを最適化するために、新しいマテリアライズド・ビュー機能を使用することを選択しています。
マテリアライズド・ビュー
マテリアライズド・ビューは、データベースシステムではおなじみの概念ではありますが、Amazon Redshiftの新機能として2020年3月に初めて導入されました。マテリアライズド・ビューとは、CTASテーブルに似た、データベースクエリの事前計算結果を格納したデータベースオブジェクトのことです。マテリアライズド・ビューとCTASテーブルの大きな違いは、マテリアライズド・ビューはデータベースのスナップショットであり、定期的に自動的に更新されるため、効率性と管理性が向上します。
以下の例のコマンドは、Redshiftでマテリアライズド・ビューを作成する方法を示しています。
CREATE MATERIALIZED VIEW mv_name [ BACKUP { YES | NO } ] [ table_attributes ] AS query
BACKUP句は、マテリアライズド・ビューのデータがRedshiftクラスタスナップショットの一部としてバックアップされているかどうかを決定します。table_attributes句は、マテリアライズド・ビューのデータを配布する方法を指定します。
Redshiftインサート パフォーマンスチューニング
その名の通り、RedshiftのINSERTコマンドはテーブルに新しい行や行を挿入します。しかし、多くのRedshiftユーザーからは、Redshiftの挿入速度の遅さやパフォーマンスの問題についての苦情が寄せられています。このセクションのRedshift挿入パフォーマンスのヒントは、Redshiftデータウェアハウスにデータをより速く取り込むのに役立ちます。
COPYコマンド
一度に大量の情報を投入する場合、RedshiftではINSERTではなくCOPYを使用することをお勧めします。COPYコマンドは、特にRedshiftのデータを一括で挿入するために作成されました。
RedshiftのAWS公式ドキュメントによると "大量のデータをロードする際にはCOPYコマンドを使用することを強くお勧めします。個々のINSERT文を使用してテーブルにデータを投入するのは、法外に時間がかかる場合があります。"
COPYコマンドを使用すると、Amazon S3、Amazon EMR、Amazon DynamoDBに保存されているデータの行を、リモートのSSH接続を介してアップロードすることができます。COPYコマンドを使用して投入される1つの行の最大サイズは4メガバイトです。
Bulk Inserts
何らかの理由でCOPYコマンドがオプションになっていない場合でも、Bulk Insert機能を使用することでRedshiftのINSERTコマンドをより効率的にすることができます。
AWSのドキュメントでは、作業中のデータを一時的に保存するためにステージングテーブルと組み合わせてINSERTを使用することを推奨しています。
例えば、以下のコードは、既存のstudentsテーブルからすべての行をコピーして新しいステージングテーブルstudents_stageを作成しています。
create table students_stage as select * from students;
ステージングテーブルがすでに存在する場合は、別のテーブルの行でステージングテーブルを作成することもできます。以下のコードは、studentsテーブルからすべての行を取り出し、ステージングテーブルのstudents_stageにコピーしています。
insert into students_stage (select * from students);
Multi-Row Inserts
COPYではなくINSERTを使用したい場合、Multi-row Insert（https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-multi-row-inserts.html）を行うことも選択肢の一つです。
Multi-row Insertは、Redshiftの性質上、1行挿入よりも高速です。Redshiftはテーブル間で情報を転送する際にデータ圧縮を行うため、1行のデータを圧縮することは多くの行を圧縮することよりも時間がかかります。(車を起動して駐車場を探すのに時間がかかるため、1ブロック先まで運転しても意味がないのと同じように)
以下は（非常に小さな）Multi-row Insertの例です。デフォルト値は、フィールドにテーブルのデフォルトオプションが入力されることを示しています。
insert into category_stage values (default, default, default, default), (20, default, 'Country', default), (21, 'Concerts', 'Rock', default);
Redshift Joinパフォーマンスチューニング
SQL結合は遅いという悪い評判がありますが、少なくとも非正規化を使用して結合操作を完全に避けることはできます。しかし、Redshiftのジョインのパフォーマンスに関しては、正しい調整とパフォーマンスの調整によって、このステレオタイプを完全に回避できることを報告できることを嬉しく思います。
キーベースのDistribution style
Redshiftのテーブルには、テーブル内の行がクラスタ内のノードにどのように分散されるか、という4つの異なるDistribution styleのオプションがあります。
- AUTO: テーブルデータのサイズに基づいて、Redshiftが自動的に分配スタイルを選択します。
- EVEN: すべてのRedshiftノードはテーブルごとに同じ数の行を持ちます。
- KEY: 1つの列(DISTKEY列)の値に従って、ノード間で行が分散されます。
- ALL: すべてのノードは、テーブル内のすべての行のコピーを格納します。
デフォルトのオプションはAUTOで、実際にはEVEN Distribution styleを意味します。しかし、EVEN Distribution styleはRedshiftのジョインパフォーマンスには最適ではありません。なぜなら、異なるノードからのデータをこれらのノード間でやり取りしなければならないため、遅いネットワークとI/O操作が必要になるからです。
その代わりに、特定のユースケースではKEYベースのDistribution styleを使用することでRedshiftのジョインパフォーマンスを向上させることができます。KEYベースのDistribution styleでは、RedshiftはDISTKEY列に同じ値を持つ行を同じノードに配置します。これは、DISTKEY上でRedshiftのジョイン操作を実行した場合、ネットワークを介してデータを送信する必要がなく、単一のノード内で実行できることを意味します。
KEYベースのDistribution styleにも限界があることに注意してください。すべての場所でKEYベースのDistribution styleを使用すると、いくつかの不都合な結果につながります。
- DISTKEYカラムの値が均等に分散されていない場合、行はRedshiftクラスタ内のノード間で不均等に分散されます。これは "行のSKEW"と呼ばれる現象です。
- 行が不均等に分散しているため、すべてのノードにまたがるSELECT操作などのクエリは遅くなります。この操作は行数の少ないノードでより早く完了し、これらのノードは行数の多いノードを待たなければなりません。言い換えれば、クラスターはその弱いリンクと同じくらいしか強くないということです。
Cross Join
クロスジョインは一見無害に見えるかもしれませんが、Redshiftのジョインのパフォーマンスを恐ろしく遅くすることがあります。クロスジョインとは、2つのテーブルのデカルト積を用いたジョイン操作のことです。クロスジョインは実用的な用途がありますが、多くの場合、フィルタや結合条件を適用せずに2つのテーブルを結合するときに発生します。
パフォーマンスが極端に低下するため、クロスジョインは絶対に必要な場合にのみ使用してください。クロスジョインはしばしば入れ子になったループを引き起こしますが、これはRedshiftのSTL_ALERT_EVENT_LOGで入れ子になったループのアラートイベントを監視することで確認できます。
Redshift Update Performance Tuning
最後になりますが、多くのユーザーはテーブル内のデータを更新する際に、Redshiftの更新パフォーマンスを向上させたいと考えています。1つ目は、元のレコードをテーブルから削除すること、2つ目は、テーブルの各カラムに対して新しいレコードを書き込むことです。
多くの場合、データの挿入と更新の操作を組み合わせた「アップサート」を行うことで、Redshiftの更新をより速く実行することができます。
Upsert
SQL 標準では、データベースに新しいレコードを挿入および/または更新する MERGE 文を定義しています。レコードが存在しない場合、MERGE 文はレコードを挿入します。存在する場合、既存のレコードは（必要に応じて）新しい情報で更新されます。この操作は、UPSERT (update + insert) とも呼ばれます。
しかし、MERGEは公式のSQL標準に含まれていますが、この記事を書いている時点ではまだRedshiftでは実装されていません。代わりに、Redshift AWSのドキュメントでは、ステージングテーブルを使用してマージ操作を行うことを推奨しています。以下の手順で行います。
- 元のテーブルと同じスキーマを持つステージングテーブルを作成します。
- アップサート」したいデータをステージングテーブルにアップロードします。
- 元のテーブルで、ステージング・テーブルの行と同じ主キーを持つ行を探して削除します。
- 元のテーブルにステージングテーブルから新しい行を挿入します。
RedshiftのUpdateパフォーマンスで最高の結果を得るためには、以下のUpsertのガイドラインに従ってください。
- 一連のステップはすべて1つのアトミックトランザクションで実行する必要があります。これにより、プロセスの最後のステップが失敗してもデータの損失を防ぐことができます。
- 必要に応じて、アップサートが完了した後、クラスタ内のノード間のデータのDistributionをリバランスさせます。上述したように、データのDistributionが不均一だと、クエリが遅くなる可能性があります。
最後に
Redshiftのビュー、挿入、結合、更新のパフォーマンスを最適化する方法に苦戦されていますか？この記事で紹介したように以下のような様々な方法で解決することが可能です。ぜひ参考にしていただければと思います。
- Redshiftビューのパフォーマンスの低下は、CTAS (CREATE TABLE AS SELECT)コマンドとマテリアライズド・ビューを使うことで修正することができます。
- 1つずつ行を投入させるのではなく、COPYコマンド、一括挿入、または複数行挿入を使用して、一度に多くの行を投入するようにして下さい。
- クロスジョインを避け、必要に応じてKEYベースのDistribution styleに切り替えることで、Redshiftのジョインのパフォーマンスを向上させることができます。
- アトミックトランザクションでプロセス全体をラップし、操作が完了したらデータのDistributionをリバランスさせることで、"upserts "を適切に実行してください。