これからAmazon Redshiftを始める技術者が注意すべき２２つのポイント

#AWS

#Amazon Redshift

T君

2016.09.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

２年ほど前にRedshiftを使う際に注意すべき点をまとめた記事を書いたのですが、その後Redshift案件をいくつか経験し、項目を追加したくなったのでもう一度まとめました。Redshiftを業務で使う方はぜひ読んでほしいです。1から11までは以下のページになります。

これからAmazon Redshiftを始める技術者が注意すべき１１つのポイント

注意事項一覧

ALTER TABLE RENAMEでテーブル名を変更するとViewの参照先が変わるので注意！
ALTER TABLE APPENDで移動したデータは未ソートになるので注意！
UDFのパフォーマンスに注意！
TO_TIMESTAMP関数がないので注意！
VARCHAR(65535)は推奨されていないので注意！
統計情報の鮮度とソートされていない行の割合に注意！
削除マーク付きのレコードが増えるとパフォーマンスが落ちるので注意！
コンパウンドソートキーの第1カラムは圧縮しない方がよい
VACUUMでソートするよりもディープコピーでソートした方が早い！
S3の結果整合性に関して注意！
その他もろもろ注意！

ALTER TABLE RENAMEでテーブル名を変更するとViewの参照先が変わるので注意！

RedshiftではALTER TABLEでテーブル名を変更すると自動的にそのテーブルが使われているViewの定義も変更されます。 Redshiftでは分散キーやソートキー、圧縮エンコーディングをALTER TABLEで変更することはできないため、新しいテーブルを作成後データを移してリネームし古いテーブルをDROPする、といった使い方をよくするかと思いますのでこの仕様には気を付けたほうがいいと思います。アトミック洗い替えする場合はビューは使わないようにしましょう。もし古いテーブル名に依存したビューが残ってしまい、どのビューか分からなくなった場合は以下のSQLを実行してください。

SELECT
  DISTINCT PG_CLASS_2.RELNAME
FROM
  PG_CLASS PG_CLASS_1
    INNER JOIN PG_DEPEND PG_DEPEND_1 ON PG_CLASS_1.RELFILENODE = PG_DEPEND_1.REFOBJID
    INNER JOIN PG_DEPEND PG_DEPEND_2 ON PG_DEPEND_1.OBJID = PG_DEPEND_2.OBJID
    INNER JOIN PG_CLASS PG_CLASS_2 ON PG_DEPEND_2.REFOBJID = PG_CLASS_2.RELFILENODE
WHERE
  PG_CLASS_1.RELNAME = '[テーブル名]' AND
  PG_DEPEND_2.DEPTYPE = 'i'::"char" AND
  PG_CLASS_2.RELKIND = 'v'::"char";

ALTER TABLE APPENDで移動したデータは未ソートになるので注意！

ALTER TABLE APPENDを使ってファイル移動するとINSERTするよりも早く便利なのですが注意点があります。SVV_TABLE_INFOテーブルのunsortedが0になっているテーブルのソート済みデータを移動した場合でも、移動後は未ソートリージョンに移動してしまうため未ソートとされてしまいます。例えば、空のテーブルにALTER TABLE APPENDするとunsortedは100になります。パフォーマンスに問題が出る場合はVACUUMが必要になるので注意しましょう。以下のSQLで確認できるので試してみて下さい。

-- 準備する
CREATE TABLE TEST_TABLE_1 ( COLUMN_1 INTEGER ) SORTKEY(COLUMN_1);
CREATE TABLE TEST_TABLE_2 ( COLUMN_1 INTEGER ) SORTKEY(COLUMN_1);
INSERT INTO TEST_TABLE_2 VALUES (5);

-- INSERT SELECTの場合
INSERT INTO TEST_TABLE_1 SELECT * FROM TEST_TABLE_2;
SELECT "TABLE", UNSORTED FROM SVV_TABLE_INFO WHERE "TABLE"='test_table_1';

-- TEST_TABLE_1を元に戻す
TRUNCATE TABLE TEST_TABLE_1;

-- ALTER TABLE APPENDの場合
ALTER TABLE TEST_TABLE_1 APPEND FROM TEST_TABLE_2;
SELECT "TABLE", UNSORTED FROM SVV_TABLE_INFO WHERE "TABLE"='test_table_1';

SVV_TABLE_INFO - Amazon Redshift ALTER TABLE APPEND - Amazon Redshift

UDFのパフォーマンスに注意！

Redshiftではユーザ定義関数(UDF)を使って関数を作成することができます。とても便利な機能ですがパフォーマンスが低下する可能性があります。ロードした大量のデータを集計しデータマートを作成する場合などにはあまりお勧めできません。使う場合は必ず性能要件を満たせるかテストしたほうがいいでしょう。以下のTO_TIMESTAMPの例のようにまずは可能な限りUDFを使わずに実装する方法を検討しましょう。どうしても使わないといけない場合はCREATE FUNCTIONのオプション(VOLATILE | STABLE | IMMUTABLE)で同じ処理の場合は省略することもできるのでこちらも試してみる価値はあると思います。

CREATE FUNCTION - Amazon Redshift

TO_TIMESTAMP関数がないので注意

以前はサポートされていなかったのですが現在はサポートされています。 TO_TIMESTAMP 関数 - Amazon Redshift

RedshiftはPostgreSQLがベースですが、PostgreSQLで使えるすべての関数が使えるわけではありません。文字列からTimestamp型に変換するTO_TIMESTAMP もRedshiftでは未サポートとなっています。UDFで文字列からTimestamp型に変換する関数を定義することもできますが、パフォーマンス的に多くの件数を処理するのには向きませんので以下のようにキャストで対応したほうがいいでしょう。因みにTO_DATE関数はサポートされているのでこちらを使いましょう。

SELECT
    (SUBSTRING(SAMPLE_DATE,1,8) || ' ' || SUBSTRING(SAMPLE_DATE,9,6))::TIMESTAMP
FROM
    (SELECT '20160913123456'::text SAMPLE_DATE);

TO_DATE - Amazon Redshift

VARCHAR(65535)は推奨されていないので注意！

VARCHARは最大で65535バイトまで指定することができます。ストレージ内では実際の使用量のみ消費されるので必要以上に長くしても問題ないと思うかもしれませんが、以下のページにあるように中間クエリ結果は圧縮されないのでパフォーマンスへ影響を与えることがあります。適切なサイズで定義しましょう。

最小列サイズの使用 - Amazon Redshift

統計情報の鮮度とソートされていない行の割合に注意！

統計情報が古くなったりソートされていない行の割合が高くなるとパフォーマンスが低下します。この２つの値はSVV_TABLE_INFOというシステムテーブルを見れば確認できます。UNSORTEDの数値が高いテーブルはVACUUMして、STATS_OFFの数値が高いテーブルはANALYZEしましょう。

SELECT
    "SCHEMA",
    "TABLE",
    UNSORTED,
    STATS_OFF
FROM
    SVV_TABLE_INFO
ORDER BY
   UNSORTED, STATS_OFF DESC;

SVV_TABLE_INFO - Amazon Redshift

削除マーク付きのレコードが増えるとパフォーマンスが落ちるので注意！

UPDATEやDELETEを実行し削除マーク付きのレコードが増えていくとスキャン時に読み込むブロックが多くなりパフォーマンスが低下していきます。削除マーク付きのレコードは貯めずにVACUUMコマンドでクリアしたほうがいいでしょう。以下のSQLでどの程度削除マークがついているレコードが含まれる件数と割合を算出することができます。

SELECT
    ROWS1, ROWS2, 1 - ROWS1 / ROWS2 AS DELETE_MARK_RATE
FROM
    (SELECT COUNT(*) AS ROWS1 FROM [スキーマ名].[テーブル名]),
    (SELECT TBL_ROWS AS ROWS2 FROM SVV_TABLE_INFO WHERE "SCHEMA"='[スキーマ名]' AND "TABLE"='[テーブル名]');

VACUUMは負荷が高いのでなるべくINSERT SELECT、TRUNCATE、DROP TABLE、ALTER TABLE RENAME、ALTER TABLE APPEND などを使って処理したほうがいいです。多くの削除マーク付きレコードをスキャンした場合はSTL_ALERT_EVENT_LOGテーブルにScanned a large number of deleted rowsというイベントが発生するのでこちらをチェックしてもいいかと思います。

SELECT
    STL_ALERT_EVENT_LOG.*,
    STL_QUERY.QUERYTXT
FROM
    STL_ALERT_EVENT_LOG
        INNER JOIN STL_QUERY ON STL_ALERT_EVENT_LOG.QUERY = STL_QUERY.QUERY
where
    EVENT like ('Scanned a large number of deleted rows%');

クエリアラートの確認 - Amazon Redshift

コンパウンドソートキーは圧縮しない方がよい

基本的には未圧縮よりも圧縮エンコードが設定されている方がパフォーマンスがいいですが、コンパウンドソートキーの第1カラムは圧縮しない方がよい、と言われています。以下のSQLでチェックしましょう。search_pathにスキーマが指定されていないテーブル情報は表示されないので以下のように設定しましょう。

SET SEARCH_PATH TO '$USER', PUBLIC, [スキーマ名];
SELECT
    * 
FROM
    PG_TABLE_DEF 
WHERE
    SCHEMANAME='[スキーマ名]' AND TABLENAME LIKE LOWER('[テーブル名]');

VACUUMでソートするよりもディープコピーでソートした方が早い！

VACUUMコマンドでは削除マーク付きの解放とレコードのソートを行いますが、実はinsert selectでソートしたデータを作成する方が早いです。しかも、VACUUMが並列実行できないのに対しinsert selectの場合は並列で処理することも可能です。ただし、insert selectの場合はVACUUMと違って実行中の更新ができないので、その点は注意しましょう。因みにテーブルが空の時にCOPYコマンドやINSERT SELECTでデータを挿入した場合は自動的にソートされます。

ディープコピーを実行する - Amazon Redshift

S3の結果整合性に関して注意！

RedshiftではなくS3の注意点なのですが知らないとハマるので入れておきます。COPYコマンドでS3にあるファイルをロードする場合の話です。同じフォルダの中にロード対象のファイルを入れてCOPYでロードした後にフォルダ内の全ファイルを削除し、同じフォルダに別のファイルを入れてフォルダ指定でCOPYコマンドを実行するとロード対象のファイルがないというエラーが起きる場合があります。これは結果整合性というS3の特性が原因です。COPYコマンド実行時に内部では取得するファイルの一覧を取得するのですが、そこに削除したファイルが含まれており、ロードしようとしても実際は削除されて存在しないためエラーになります。この現象を回避する場合は常に新しいフォルダを作成しその中にファイルを入れてからCOPYコマンドを実行します。その場合は問題は起きません。

結果整合性に関しては以下の記事をご覧ください。【アップデート】S3の一貫性に変更があります

その他もろもろ注意！

その他細かいですが、知っておいた方がよさそうなことを箇条書きにしておきます。

ALTER TABLE RENAMEでテーブル名は変更できるがスキーマは変更できない。
COMPOUND SORTKEYは最初の8バイトしか見ていないので“http://w”のような文字列が入ったカラムを指定しても効果が薄い。
Timestamp型など日時の値を＋、－で計算することはサポートされていないため、DATEDIFF、DATEADDなどの関数を使って計算する。
テーブル名を指定せずにVACUUMコマンドを実行するとシステムテーブルもVACUUMされる。
VACUUMコマンドを実行する際に処理するカラムが多いと、Vacuum Column Limit Exceededエラーが発生する。wlm_query_slot_countを変更して対応する。
RedshiftでORDER BYした場合、NULL値のデータは最大値とみなされる。変更したい場合はORDER BYのオプション(NULLS FIRST、NULLS LAST)を指定する。因みにNetezzaは逆でNULLを最小とみなします。

最後に

前に書いたときはそれほどRedshift案件を経験していなかったのですが、その後２年間Redshiftの案件に携わり続けてある程度はRedshiftに詳しくなってきました。２年前の記事を今見ると微妙だなと思うところが何点かあったので前の記事も修正しています。これからも何か新しい発見があったらどんどん追加していきたいと思います！