Amazon Redshift Spectrum 12 のベストプラクティス

2019/7/22 に一部内容を更新しました．

Amazon Redshift Spectrum を使うことで、Amazon S3 に置かれたデータに対して Amazon Redshift の SQL クエリを走らせることができます。つまり Redshift Spectrum によって、データウェアハウスのローカルディスク内に保存されたデータ以外に対しても、Redshift の分析を拡張できるようになるのです。S3 の “データレイク” に貯まった大量のデータに対して、面倒で時間のかかる抽出・変換・ロード（ETL）処理を行うことなく、クエリを投げることができます。Redshift Spectrum は洗練されたクエリ最適化を用いて、数千ものノードにまでスケールして高速に処理を行います。

このブログポストでは、Redshift Spectrum の 10 の重要なベストプラクティスについて、いくつかのカテゴリにわけてご紹介します。

このガイドラインは、Redshift をお使いのお客さまとの多くのやりとりや、直接的なプロジェクトに基づいて作られています。

Amazon Redshift vs. Amazon Athena

AWS のお客さまは、私たちによく「Amazon Athena と Amazon Redshift Spectrum について、どう使い分けをすればよいのでしょうか？」と尋ねます。

Amazon Athena の利用シーン

Athena は S3 に置かれたデータに対して、SQL によるインタラクティブなアドホッククエリを投げるといったユースケースに向いています。Athena はサーバーレスアーキテクチャなので、クエリを投げるためにクラスタを立ち上げる必要はありません。クエリごとにスキャンした S3 のデータ量に基づいて料金が発生します。データを圧縮、パーティショニング、または列指向フォーマットに変換することにより、費用を節約しつつ優れたパフォーマンスを得ることができます。JDBC に対応しているすべての BI ツールや SQL クライアントから Athena を利用することができます。さらに簡単な可視化であれば Amazon QuickSight を利用することもできます。

Amazon Redshift の利用シーン

大規模な構造化データに対しては、Redshift の使用をおすすめします。Redshift Spectrum により、データを貯める場所、データのフォーマット、そして演算能力をより柔軟に活用することができます。Redshift Spectrum を使うことで、Redshift クラスターのスケーリングについて悩む必要はなくなります。ストレージとコンピュートを分離し、それぞれを独立して拡張することができるようになります。同じ S3 データレイクに対して複数の Redshift クラスターを立ち上げることで、クエリの同時実行数を増やすことができます。Redshift Spectrum は数千インスタンスにまで、自動的に拡張します。そのためテラバイト、ペタバイトそして、エクサバイトのデータに対してさえも、クエリは高速に動作します。

テスト環境の構築

Redshift Spectrum を利用するための前提条件や、開始までのステップについては、Amazon Redshift Spectrum の開始方法をご覧ください。

このブログポストでご紹介するベストプラクティスを検証するにあたって、どのようなデータでもお使いになることができます。ただひとつ大事な要件として、一番大きなテーブルについて、同じデータを 3 つのフォーマット（CSV、パーティション分けされていない Parquet フォーマット、パーティション分けされた Parquet フォーマット）に変換して S3 にファイルとしておく必要があります。どのようにファイルフォーマットを変換するかについては、このブログポストの範囲外となりますので、以下のリソースを参照してください。

外部スキーマの作成

メタデータストアとして、Athena のデータカタログを使い、以下に示すように “Spectrum” という名前の外部スキーマを作成します。

create external schema spectrum 
from data catalog 
database 'spectrumdb' 
iam_role 'arn:aws:iam::<AWS_ACCOUNT_ID>:role/aod-redshift-role'
create external database if not exists;

Redshift クラスターと S3 上のデータファイルは同じ AWS リージョンに置かれていなければいけません。また Redshift クラスターに、Athena のデータカタログと S3 上のファイルに対するアクセス権を与える必要があります。クラスターに対して、適切な IAM ロール（例えば aod-redshift-role）をアタッチします。より詳細な情報は、ステップ 1. Amazon Redshift 用の IAM ロールを作成するを参照してください。

外部テーブルの定義

例えば、パーティション分けされた parquet ファイルや CSV ファイルの Redshift Spectrum 外部テーブルは、以下のように定義されます。

CREATE  external table spectrum.LINEITEM_PART_PARQ ( 
 L_ORDERKEY BIGINT,
 L_PARTKEY BIGINT,
 L_SUPPKEY BIGINT,
 L_LINENUMBER INT,
 L_QUANTITY DECIMAL(12,2),
 L_EXTENDEDPRICE DECIMAL(12,2),
 L_DISCOUNT DECIMAL(12,2),
 L_TAX DECIMAL(12,2),
 L_RETURNFLAG VARCHAR(128),
 L_LINESTATUS VARCHAR(128),
 L_COMMITDATE VARCHAR(128),
 L_RECEIPTDATE VARCHAR(128),
 L_SHIPINSTRUCT VARCHAR(128),
 L_SHIPMODE VARCHAR(128),
 L_COMMENT VARCHAR(128))
partitioned by (L_SHIPDATE VARCHAR(128))
stored as PARQUET
location 's3://<your-bucket>/<xyz>/lineitem_partition/'
;

CREATE  external table spectrum.LINEITEM_CSV ( 
 L_ORDERKEY BIGINT,
 L_PARTKEY INT,
 L_SUPPKEY INT,
 L_LINENUMBER INT,
 L_QUANTITY DECIMAL(12,2),
 L_EXTENDEDPRICE DECIMAL(12,2),
 L_DISCOUNT DECIMAL(12,2),
 L_TAX DECIMAL(12,2),
 L_RETURNFLAG VARCHAR(128),
 L_LINESTATUS VARCHAR(128),
 L_SHIPDATE VARCHAR(128) ,
 L_COMMITDATE VARCHAR(128),
 L_RECEIPTDATE VARCHAR(128),
 L_SHIPINSTRUCT VARCHAR(128),
 L_SHIPMODE VARCHAR(128),
 L_COMMENT VARCHAR(128))
row format delimited
fields terminated by '|'
stored as textfile
location 's3://<your-bucket>/<xyz>/lineitem_csv/'

クエリの実行

つまり Redshift Spectrum は、S3 に置かれたデータにクエリを投げるために、外部テーブルを用います。Redshift テーブルと同様に、 SELECT 文で外部テーブルにクエリを投げられます。外部テーブルは読み込み専用であり、書き込むことはできません。

まず、Athena や（Amazon EMR などの）Apache Hive メタストアに構築されているデータカタログを参照するために、Redshift Spectrum で外部スキーマを作成します。続いて外部スキーマに対して外部テーブルを作成します。Redshift 内にテーブルを作成・ロードしないようにするために、外部テーブルに対して SELECT 文を発行する際には、必ず “外部スキーマ.外部テーブル” と記述する必要があります。

外部スキーマは、外部のデータカタログを参照します。そのため Redshift クラスターに対して、IAM によって S3 と Athena へのアクセス権を付与する必要があります。

Redshift Spectrum のテストを始めるにあたっては、まず以下のクエリを実行するとよいでしょう。

QUERY 1:

SELECT l_returnflag,
       l_linestatus,
       sum(l_quantity) as sum_qty,
       sum(l_extendedprice) as sum_base_price,
       sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
       sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
       avg(l_quantity) as avg_qty,
       avg(l_extendedprice) as avg_price,
       avg(l_discount) as avg_disc,
       count(*) as count_order
FROM lineitem
WHERE l_shipdate <= '1998-09-01'
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

このクエリは 1 つのテーブルだけにアクセスしており、Redshift Spectrum 層によって追加されるコンピューティングを活用できます。

QUERY 2:

SELECT  l_orderkey,
       sum(l_extendedprice * (1 - l_discount)) as revenue,
       o_orderdate,
       o_shippriority
FROM	customer, orders, lineitem
WHERE	c_mktsegment = 'BUILDING'
       AND c_custkey = o_custkey
       AND l_orderkey = o_orderkey
       AND o_orderdate < date '1995-03-15'
       AND l_shipdate > date '1995-03-15'
GROUP BY l_orderkey, o_orderdate, o_shippriority
ORDER BY revenue desc, o_orderdate
LIMIT 20;

このクエリは 3 つのテーブルを結合しており、Redshift Spectrum と素の Redshift のパフォーマンスを比較するのに向いています。

並行性に関するベストプラクティス

これらのおすすめプラクティスは、Redshift Spectrum による並行ワークロードのパフォーマンスを最適化するのに役立ちます。

1. Amazon Redshift Spectrum をスキャンインテンシブな並行ワークロード改善に活用する

Redshift Spectrum は、利用している Redshift クラスターとは独立した専用のサーバー群にあります。フィルター処理や集約処理といった、多くのコンピュートインテンシブな処理を Redshift Spectrum 層で行うことで、クエリが使用する Redshift クラスターの処理キャパシティは大きく削減されます。加えて Amazon Redshift Spectrum は賢くスケールします。MPP（Massively Parallel Processing）の利点を活かすために、Redshift Spectrum はクエリで必要な量に応じて、最大数千のインスタンスで処理を行います。

スキャンや集約インテンシブなワークロードを並行で実行するようなユースケースでは、平均的な Redshift Spectrum のパフォーマンスは、素の Redshift を上回ります。

MPP システムで最もリソースインテンシブな処理は、データロードのプロセスです。これはアクティブな分析クエリによって、コンピュートリソースだけでなく、MVCC（Multi Version Concurrency Control）によるテーブルロックによる競合が引き起こされるためです。対照的に S3 に追加され、新しいパーティションとしてメタデータも更新されたファイルを、外部テーブルを通じて Redshift Spectrum で認識する形をとることで、こうしたデータ更新のワークロードを Redshift クラスターの外に追いやることができるようになります。これはクエリの同時実行性能に対して、大きなプラスの効果をもたらします。

2. 同時実行性をスケールさせるために、複数のオンデマンド Amazon Redshift クラスターを使用する

複数の Redshift クラスターが、Redshift Spectrum 経由で S3 に保存されたデータにアクセスすることにより、並行ワークロードのパフォーマンスを改善させることができます。Redshift を使うお客さまの一般的なユースケースには、季節的なスパイクや、非常に同時実行クエリ数の多いワークロードがあります。Redshift Spectrum が登場する前は、増加する同時実行クエリ数に対処するために、お客さまはスナップショットからの復元によって、複数の “読み取り専用” Redshift クラスターを立ち上げる必要がありました。このやり方をとった場合、数百テラバイトにもおよぶデータを含む巨大な Redshift クラスターをお持ちのお客さまだと、クラスターの復元に非常に長い時間がかかるため、データレイテンシーの問題が生じてしまいました。

Redshift Spectrum の登場により、最も大きなテーブルを S3 に移動させ、Redshift クラスターには小さなサイズのデータのみを置く、という形を取れるようになりました。クラスターで保持するデータ量が減ったことにより、複数の “読み取り専用” Redshift クラスターを従来よりはるかに高速に立ち上げられるようになり、季節性のあるスパイク的なクエリワークロードに対応できるようになりました（図 1）。コストを削減するためには、ジョブが終わったらすぐに “オンデマンド” な Redshift クラスターをシャットダウンしてください。

Figure 1: 複数の “読み取り専用” Amazon Redshift クラスターからの共有 Redshift Spectrum 層へのアクセス

pgbouncer-rr をお使いいただくことで、複数の Redshift クラスターを立ち上げてクエリのルーティングを行う処理を簡単に行うことができます。詳細については以下のブログ記事を参照してください。

Query Routing and Rewrite: Introducing pgbouncer-rr for Amazon Redshift and PostgreSQL.

ストレージのベストプラクティス

ストレージの最適化を考える際には、あらゆるステップで I/O を削減することを考える必要があります。列指向フォーマットを使用する、圧縮によって各ストレージブロックにより多くのデータを詰め込む、データのパーティショニングをサポートしているファイルフォーマットを使用する、といったことが挙げられます。Redshift Spectrum でサポートされているファイルフォーマットは、CSV、TSV、Parquet、Sequence、そして RCFile です。また圧縮に関しては、Gzip、Snappy、BZ2 がサポートされています。

3. パフォーマンス向上とコスト削減のために Apache Parquet ファイルを使用する

Apache Parquet は、Apache Hadoop エコシステムのあらゆるプロジェクトで、データ処理フレームワークやデータモデル、プログラミング言語に依らず利用可能な列指向フォーマットです。詳細については、Apache Prquet の公式サイトをご覧ください。

Redshift Spectrum は、クエリ実行のために必要なカラムを含んだファイルだけを、S3 から読み出します。Redshift Spectrum はプレディケイトプッシュダウン（プレディケイトフィルタリングとも呼ばれます）をサポートしています（訳注：プレディケイトプッシュダウンとは、WHERE 句や GROUP BY 句などの処理を効率的に行うための手法です。例えば GROUP BY に対するプレディケイトプッシュダウンでは、各リーダーで読み込んだデータについて、全データで GROUP BY を行う前に、各ワーカー内であらかじめ GROUP BY をしておき、その結果を集約ワーカーに転送する、といったプロセスをとります。各ワーカーで先に集約を行うことでデータの転送コストが下がり、結果的にパフォーマンスが向上します。このように、クエリの実行パイプラインの最後で行う処理を、効率化のためにあらかじめ各プロセスでおこなっておく（= プッシュダウン）のが、プレディケイトプッシュダウンの役割となります）。

Redshift Spectrum は、クエリ単位で S3 からスキャンしたデータ量ごとに課金されます。Parquet フォーマットは列指向フォーマットでデータを保持するため、Redshift Spectrum は不必要なカラムを除いた形でスキャンを行うことができます。例として、CSV 形式のテキストファイルと、パーティション分けされた Parquet ファイルのクエリパフォーマンスの違いをみてみるとよいでしょう。

Parquet ファイルの利用によって、パフォーマンスが向上するだけでなく、パーティション分けされていない行指向の CSV ファイルよりはるかにコスト効率がよくなることが、さまざまな検証によって示されています。

SVL_S3QUERY_SUMMARY テーブルを調べることで、パーティション分けされた Parquet ファイルを使う際の、S3 に関するさまざまな興味深いメトリクスを確認することができます。

select * from SVL_S3QUERY_SUMMARY where query=<Query-ID>;

s3_scanned_rows と s3query_returned_rows という 2 つのメトリクスに、特に注目してみましょう。CSV ファイルを処理するときと比べて、Redshift Spectrum から Redshift クラスターに送られるデータ総量が驚異的に削減されていることがわかります。

4. 頻繁に使われるカラムをパーティションとした Parquet ファイルをつくる

パーティションとして利用する最適なカラムを決める際には、以下の点に注目しましょう

フィルタとして頻繁に使われるカラムは、パーティションカラムの良い候補と考えられます
過度に細かいパーティショニングは、パーティションの情報を取得するのに時間を要します。一方で、必要なパーティションの選択により、S3 からスキャンするデータを減らすのに役立ちます
実際のパフォーマンスは、ファイルの置き方、クエリのパターン、ファイルサイズの分布、ひとつのパーティションに含まれるファイル数、対象となるパーティション数などによって異なります
パーティションカカラムの偏りを避けましょう
ファイルサイズの分布ができる限り均等になるようにしましょう。つまり、すべて 256MB の Parquet ファイル 10 個の方が、1GB 超のファイル 1 個と 256MB のファイル 6 個より望ましいということです

パーティションによる枝借りが素晴らしい効果を発揮するのを確認するために、2 つの外部テーブルを Parquet ファイルで作成してみましょう。ひとつはパーティション分けされていないもの、もうひとつは日単位でパーティション分けされているものです。

パーティション分けされた外部テーブルは、そうでないものより 2-4 倍程度高速にスキャンできます。

“パーティションの枝借り” の効果をどのように確認すれば良いでしょうか。以下に示す SQL を用いることで、パーティションの枝借りの効果を分析することができます。クエリが数個のパーティションにのみアクセスするような場合には、期待通りの効果が得られていることを確認することができます。

SELECT query,
	segment,
	max(assigned_partitions) as total_partitions,
	max(qualified_partitions) as qualified_partitions 
FROM svl_s3partition 
WHERE query=<Query-ID>
GROUP BY 1,2;

クラスター設定のベストプラクティス

5. 正しい Redshift のクラスター設定により Redshift Spectrum のパフォーマンスを最適化する

Redshift Spectrum クエリの同時実行性能は、以下の 2 つのレベルで制御することが可能です

クエリレベル（クエリごと 1 スライスにつき最大 10 の同時実行数）
- いくつのクエリが同時に実行されているかによって、同時実行数が変わる
- 割りあてられた同時実行数によって、S3 をスキャンするスレッド数が制限される
ノードレベル（ノード状で動作するすべての S3 をスキャンするクエリに適用される。ノードタイプによって数が異なる）
- より大きなノードタイプを選択するほど、上限数も高くなる

ファイル総数 <= クエリごとの同時実行性能 (例えば 10) * クラスターのスライス数、といった簡単な計算ができます。。ただしクラスターのノード数を増やしても、必ずしもパフォーマンスが向上するとは限りません。最適なクラスターのノード数は、以下のようにして決めてください。まず Redshift Spectrum の外部テーブルに、いくつのファイルが含まれているかを確認してください。続いてクラスターのサイズを大きくしていって（クラスターに含まれるスライス数を増やすということです）、ノード数が増えてもパフォーマンスがこれ以上伸びなくなるというポイントを探してください。そのときのノードタイプにおける、最適な Redshift のクラスターサイズは、それ以上のパフォーマンス向上が起こらなくなるところです。

クエリパフォーマンスのベストプラクティス

S3 に対するクエリのパフォーマンスを改善するための、簡単な方法をいくつかご紹介します。

6. Redshift Spectrum でスキャン・集約インテンシブなクエリを実行する

Query 1 のように結合処理が含まれないクエリでは、物理的な I/O のコストがスキャン速度の大半を占めます。これらのクエリでは、Redshift Spectrum は素の Redshift よりも高速に動作します。その一方で複数の結合処理が含まれる Query 2 のようなクエリでは、ローカルストレージ上できちんと最適化されている素の Redshift テーブルの方が、当然よいパフォーマンスを発揮します。

7. プレディケイトプッシュダウンによって S3 に対するクエリのパフォーマンスを改善する

Redshift Spectrum 層で行われる処理（S3 に対するスキャン、カラム抽出、フィルタリング、集約）は Redshift クラスターとは独立して行われます。もちろん Redshift クラスターのリソースを消費することはありません。

SQL に含まれるある種の処理は Redshift Spectrum 層にプッシュダウンすることができ、可能な場合は常にその恩恵を受けることができます。例えば以下のような処理が挙げられます。

GROUP BY 句と、いくつかの文字列関数
イコール条件や LIKE のようなパターンマッチング条件
COUNT、SUM、AVG、MIN、MAX、その他の一般的な集約関数
regex_replace やその他の関数

DISTINCT や ORDER BY といった演算子は、Redshift Spectrum にプッシュダウンすることができないため、Amazon Redshift で実行されます。可能ならば、これらの処理の利用を最小化するか、もしくは利用を避けるのが望ましいです。

次に示す 2 つのクエリを実行してみると、両者の間でパフォーマンスが大きく異なることがわかります。なぜでしょうか？

Select MIN(L_SHIPDATE), MAX(L_SHIPDATE), count(*)
	from spectrum.LINEITEM_NPART_PARQ;

Select MIN(DATE(L_SHIPDATE)), MAX(DATE(L_SHIPDATE)), count(*)
        from spectrum.LINEITEM_NPART_PARQ;

最初のクエリの実行計画では、S3 データに対する集約処理が Redshift Spectrum にプッシュダウンされ、集約された結果のみが Redshift に送られます。

一方、2 つめのクエリの実行計画では、Redshift Spectrum が DATE 型を標準のデータ型としてサポートしていないことや、DATE 変換関数をサポートしていないことによって、Redshift Spectrum 層でS3 データに対する集約処理がまったく行われません。結果として S3 から読み出された大量のデータが、Redshift 上で変換処理を行うために送られてくることになります。

また、SVL_S3QUERY_SUMMARY システムビュー（の s3query_returned_rows カラム）に対して、2 つの SQL ステートメントの詳細を確認するクエリを投げることもできます。クエリの結果をみると、Redshift Spectrum から Redshift に送られる行数が両者で大きく異なっていことがわかるでしょう。

8. DISTINCT を GROUP BY で置き換える

GROUP BY や MIN/MAX/COUNT といった演算子は、Redshift Spectrum 層に処理をプッシュダウンすることができます。その他の DISTINCT や ORDER BY のような SQL 演算子は、プッシュダウンできません。一般的に、Redshift Spectrum 層にプッシュダウン可能なすべての演算子は、Redshift Spectrum が動作するパワフルなインフラにより、プッシュダウンによるパフォーマンスの向上が期待できます。

例えば、以下の 2 つの機能的に等価な SQL ステートメントを試してみましょう。

SELECT DISTINCT l_returnflag,
        l_linestatus 
FROM 	spectrum.LINEITEM_PART_PARQ 
WHERE 	EXTRACT(YEAR from l_shipdate::DATE) BETWEEN '1995' AND  '1998' 
ORDER BY l_returnflag, l_linestatus
;


SELECT l_returnflag,l_linestatus 
FROM 	spectrum.LINEITEM_PART_PARQ 
WHERE EXTRACT(YEAR from l_shipdate::DATE) BETWEEN '1995' AND  '1998' 
GROUP BY l_returnflag, l_linestatus 
ORDER BY l_returnflag, l_linestatus
;

ひとつめのクエリでは（DISTINCT 演算子のせいで）プッシュダウンが行われません。そのため Redshift 上でソートおよび重複除去を行うために、大量のレコードが送られます。ふたつめのクエリでは、重たい処理や集約の大半を行う HashAggregate が Redshift Spectrum で行われます。SVL_S3QUERY_SUMMARY にクエリを投げることで、実行計画の違いを確認することができます。

ここでの学びは、可能なときは常に “DISTINCT” を “GROUP BY” で置き換えるべきだということです。

テーブル配置のベストプラクティス

このシンプルなガイドラインでは、最高のパフォーマンスを得るためのテーブルの置き方について述べます。

9. 大きなファクトテーブルを S3 におき、それ以外を Redshift で持つ

Query 2 では、3 つのテーブルに対して結合処理を行っています。自然な疑問として「もし 3 つのテーブルすべてを、パーティション分けした Parquet ファイルとして、S3 においたらどうなるでしょうか？」と思うでしょう。素の Redshift に 3 つのテーブルがすべて置かれているときと比べて、パフォーマンスは良くなるでしょうか、それとも悪くなるでしょうか？

結合処理に対して最適化を行なった（適切な分散キー、ソートキーを設定済みの）Redshift テーブルは、Redshift Spectrum よりも高いパフォーマンスを発揮します。Redshift Spectrum の外部テーブルは、統計情報をサポートしていません。

データベースエンジンはヒューリスティクス、もしくは単純な行数カウントによって結合の順番を決定します。この方法だと、必ずしも最適なパフォーマンスは得られません。AWS では、最も大きいファクトテーブルのみを S3 上に置いて、それ以外の小中規模のディメンジョンテーブルは Redshift に持つことを推奨しています。この場合に、オプティマイザーのヒューリスティクスが最もうまく働きます。

CREATE EXTERNAL TABLE および ALTER TABLE コマンドでは、TABLE PROPERTIES 節の中で (numRows) をテーブル統計情報として設定することができます。この情報によって、オプティマイザーがより良い実行計画を生成するのを手助けすることができます。より詳細な情報については、Redshift のドキュメント内の CREATE EXTERNAL TABLE を参照してください。

少なくとも 3 つのテーブルの結合処理を含むクエリを、それらが正しい順番で結合されるように記述してみてください（Explain 文で確認することができます）これによって、 Redshift Spectrum では複数テーブルの結合を行なってはいけない、といった間違った考えに陥らないようにすることができます。

10 頻繁に結合処理の対象となる大きなテーブルを S3 上に置くときに注意する

素の Redshift は Query 2 のようなクエリについて、大概の同時実行クエリ数において、Redshift Spectrum より約 3 倍のパフォーマンスを発揮します。Query 1 と 2 の違いは、Query 1 では 1 テーブルに対する集約処理しか行なっていないのに対して、Query 2 では 3 つの比較的大きなテーブルの結合処理が含まれている点です。

より明確に言うとすれば、このようなパフォーマンスの違いが生じるのは、結合処理が Redshift の中で行われているためです。結合に必要なデータはまず S3 から読み込まれ、そして Redshift クラスターの各スライスに転送される必要があります。その結果、Redshift のローカルストレージにアクセスするのと比べて、Redshift Spectrum 経由の場合は非常に高いレイテンシーが発生します。

そのため、大きな Redshift テーブルに対して結合を頻繁に行い、かつクエリのワークロードに対して厳しい SLA が設定されている場合には、これらのテーブルを S3 に置くべきではありません。

11 S3外部表のテーブル統計情報 (numRows) を設定する

Redshiftのオプティマイザはより強固な実行計画を生成するために、外部表の統計情報を利用することができます。統計情報がない場合には、S3テーブルのほうが相対的に大きいという仮定に基づいたッヒューリスティクスから、実行計画が生成されます。以下、クエリの実行計画をご覧ください。


explain select * from s3_external_schema.event_desc ed join s3_external_schema.event e
on e.event_cd = ed.event_cd and e.event_type_cd = ed.event_type;

XN Hash Join DS_BCAST_INNER (cost=250000000.00..253625000500000000.00 rows=173286210788786592 width=1580)
    Hash Cond: (("outer".event_cd = "inner".event_cd) AND ("outer".event_type = "inner".event_type_cd))
    -> XN S3 Query Scan ed (cost=0.00..200000000.00 rows=10000000000 width=1052)
        -> S3 Seq Scan s3_external_schema.event_desc ed location:"s3://<your-bucket>/<xyz>/event_desc/" format:PARQUET (cost=0.00..100000000.00 rows=10000000000 width=1052)
    -> XN Hash (cost=200000000.00..200000000.00 rows=10000000000 width=528)
        -> XN S3 Query Scan e (cost=0.00..200000000.00 rows=10000000000 width=528)
            -> S3 Seq Scan s3_external_schema.event e location:"s3://<your-bucket>/<xyz>/event/" format:PARQUET (cost=0.00..100000000.00 rows=10000000000 width=528)

ご覧の通り、結合された順番が最適ではないようです。どのように修正すればよいでしょうか？

外部テーブルに対して ANALYZE を実行することはできませんが、CREATE EXTERNAL TABLE コマンドおよび ALTER TABLE コマンドの TABLE PROPERTIES 句を用いて、テーブル統計 (numRows) を手動で設定できます。

ALTER TABLE s3_external_schema.event_desc SET TABLE PROPERTIES ('numRows'='799');
ALTER TABLE s3_external_schema.event SET TABLE PROPERTIES ('numRows'='122857504');

この情報を使用することにより、Amazon Redshift のオプティマイザは最適な実行計画を生成でき、かつ速くクエリを完了することができます。

XN Hash Join DS_BCAST_INNER (cost=19.98..8669068004.76 rows=170103196 width=1580)
    Hash Cond: (("outer".event_cd = "inner".event_cd) AND ("outer".event_type_cd = "inner".event_type))
    -> XN S3 Query Scan e (cost=0.00..2457150.08 rows=122857504 width=528)
        -> S3 Seq Scan s3_external_schema.event e location:"s3://<your-bucket>/<xyz>/event/" format:PARQUET (cost=0.00..1228575.04 rows=122857504 width=528)
    -> XN Hash (cost=15.98..15.98 rows=799 width=1052)
        -> XN S3 Query Scan ed (cost=0.00..15.98 rows=799 width=1052)
            -> S3 Seq Scan s3_external_schema.event_desc ed location:"s3://<your-bucket>/<xyz>/event_desc/" format:PARQUET (cost=0.00..7.99 rows=799 width=1052)

クエリのコストコントロールに関するベストプラクティス

12 クエリ実行コストに注意をし、クエリのモニタリングルールを利用する

非選択的結合の場合、結合を実施するためには大量データを読み取る必要があります。これが実施されると高いデータ転送コストとネットワークトラフィックが発生し、パフォーマンスが低下、必要以上のコストがかかる可能性があります。

あなたが実行するクエリは重たいスキャン、選択的、重たい結合処理を含んだものでしょうか？　メトリクスを監視し、クエリパターンを理解するために、以下のクエリを実行することができます。

SELECT query,
Round(elapsed :: FLOAT / 1000 / 1000, 3) AS elapsed_sec,
aborted,
is_partitioned,
s3_scanned_rows,
s3query_returned_rows,
s3_scanned_bytes,
s3query_returned_bytes,
files,
max_retries,
Round(max_request_duration :: FLOAT / 1000 / 1000, 3) AS
max_request_dur_sec,
Round(avg_request_duration :: FLOAT / 1000 / 1000, 3) AS
avg_request_dur_sec,
Round(avg_request_parallelism, 2) AS
avg_request_parallelism
FROM svl_s3query_summary
WHERE query = xxxx;

この一環として、次の操作を実施します：

返されたデータのスキャン割合と並列度を確認する
実行するクエリがパーティションプルーニングの恩恵を受けられるか確認する（ベストプラクティス 4 を参照)

どのような状況かを理解できたら、予期しないコストを避けるために、無茶なクエリを停止する WLM のクエリモニタリングルールを設定することができます。

もしユーザーがフィルターを追加することを忘れたり、またはデータが正しくパーティションで分けられていない場合、クエリ内で大量のデータがスキャンされ、高いコストが発生する可能性があります。
クエリパフォーマンスの閾値を設定するには、WLMのクエリのモニタリングルールを利用し、閾値を超えた際にアクションを実施します。例えば、spectrum_scan_size_mb が 20 TB を超える、もしくは spectrum_scan_row_count が10億を超える場合は、クエリを中断するといった形でルールを設定できます。

上記のルール、またはその他のクエリモニタリングルールを使用することで、クエリを終了させたり、マッチする他のキューにクエリをホップさせたり、1 つ以上のルールがトリガされたときにログに記録することができます。詳細については、Redshiftドキュメントの「WLMクエリ監視ルール」を参照してください。

結論

このブログポストでは、Redshift Spectrum のパフォーマンスを改善するための重要なベストプラクティスについて述べてきました。もちろん各ユースケースで異なる点があるため、ここで示したおすすめの方法をどの程度取り入れるかについて、考える必要があります。

原文: 10 Best Practices for Amazon Redshift Spectrum (翻訳: SA志村)

Amazon Web Services ブログ