Amazon Redshift テーブル設計詳細ガイド：Part 1 序文、事前準備、優先順位付け
AWS Big Data Blogに掲載された全5回シリーズの「Amazon Redshift Engineering’s Advanced Table Design Playbook」の翻訳をお届けします。日本語版も原文と同じでパート１から５に分かれており、このエントリはパート１です。パート２以降は順次公開されていきます。
Part 1: Preamble, Prerequisites, and Prioritization（序文、事前準備、優先順位付け）（本稿）
Part 2: Distribution Styles and Distribution Keys（分散スタイルと分散キー）
Part 3: Compound and Interleaved Sort Keys(Compound と Interleaved ソートキー)
Part 4: Compression Encodings (圧縮エンコーディング)
Part 5: Table Data Durability（テーブルデータの永続性）
著者Zach Christopherson はAmazon Redshiftチームのシニアデータベースエンジニア
Amazon Redshift はフル・マネージドで、ペタバイト級までスケールでき、大規模な並列処理を行うことができるデータウェアハウスであり、シンプルなオペレーションと高いパフォーマンスを提供します。AWSのお客様は、性能をスケールさせることが難しい既存のデータベース環境を高速化するためであったり、Webログを解析するビッグデータ分析の用途など、多様な目的で利用されています。Amazon Redshiftは業界標準のJDBC/ODBC接続を提供しているため、既存のビジネスインテリジェンス（BI）ツールから接続し、既存の分析用クエリを再利用することが可能です。
Amazon Redshiftを使用することで、業界の一般的な考え方であるデータモデルを実装することができます。第三正規形（3NF）であっても、スタースキーマであっても、スノーフレークであっても、非正規化されたフラットテーブルであっても、もしくはそれらのコンビネーションであっても、Amazon Redshift独自のテーブル設計の特性を活用することで、複雑なアナリティクス用のワークロードをペタバイト級のデータセットに対して、高いパフォーマンスで実行することが可能です。
私はこれまでの経験から、桁違いに大きいデータに対してクエリのパフォーマンスを向上させる一番の方法は、Amazon Redshiftのテーブル設計を利用したいワークロードにより良くフィットさせることだと学びました。この５回のブログシリーズではディストリビューションスタイルの選択、ソートキー、圧縮エンコード等をどう適用するか、またデータの耐久性やリカバリの目的に合わせてテーブルをどう設定するかをお伝えします。
事前準備
もしあなたが既存のAmazon Redshift上でのワークロードについて作業されているなら、Amazon Redshift内のシステムテーブルから最も理想的な設定を決めるための情報を得ることができます。全体のデータの状況を把握するため、これらのシステムテーブルをクエリするには、クラスターへのSuper user権限が必要になります。以下のクエリで、usesper列の結果見ることで、あなたが使用しているユーザが権限を持っているかを確認できます:
root@redshift/dev=# SELECT usename, usesysid, usesuper FROM pg_user WHERE usename=current_user;
usename | usesysid | usesuper
---------+----------+----------
root | 100 | t
(1 row)
Amazon Redshiftで表や列の設定を変える場合、ほとんどのケースで表の再作成が必要になります。 表を再作成する時間を短くするため、事前に必要な変更内容を全て洗い出しておき、表の再作成の回数を一度で済ませるようにしましょう。変更内容を決定したら、私達が作成したamazon-redshift-utilsで作成されるビュー定義 (v_generate_tbl_ddl) をつかって既存表のDDLを生成することが可能になり、これを使って変更後のDDLを作成することが可能です。
私は既存のSVV_TALE_INFOをもとにスキーマやワークロードのレビューを効果的に出力するよう拡張した v_extended_table_infoと呼ぶビューを作成しました。本シリーズを通してこのビューをクエリした結果を参照しますので、あなたが最適化を試みるAmazon Redshiftクラスター上にこのビューを作成しておくことを推奨します。
また、簡潔に説明をするために、各表はオブジェクトID（OID）を使って参照します。OIDは以下のように色々な方法で取得可能です:
root@redshift/dev=# SELECT 'bi.param_tbl_chriz_header'::regclass::oid;
oid
--------
108342
(1 row)
root@redshift/dev=# SELECT oid, relname FROM pg_class
WHERE relname='param_tbl_chriz_header';
oid | relname
--------+------------------------
108342 | param_tbl_chriz_header
(1 row)
root@redshift/dev=# SELECT table_id, "table" FROM svv_table_info
WHERE "table"='param_tbl_chriz_header';
table_id | table
----------+------------------------
108342 | param_tbl_chriz_header
(1 row)
root@redshift/dev=# SELECT DISTINCT id FROM stv_tbl_perm
WHERE name='param_tbl_chriz_header';
id
--------
108342
(1 row)
優先順位付け
このシリーズでは、テーブル単位で実施できる多くの手法を見ていきます。Amazon Redshiftクラスターの中で多種多様なワークロードが実行され、数千の表が存在する、というのはさほど珍しいことではありません。時間は有限ですので、対象のワークロードに対して最も重要な表を優先順位付けし、クラスター全体でパフォーマンスを向上させるよう進める必要があります。
もしあなたがAmazon Redshiftを直接利用しているユーザであるか、もしくは利用するユーザと直接コミュニケーションが取れる立場にある場合、どこから最適化を始めるかはすでに明白になっているかもしれません。おそらくユーザはクラスターでどの処理が遅いのかを把握しており、どの表から最適化すべきかを明白にしてくれるでしょう。
最適化をプランする環境について本質的なナレッジが不足している場合、そのシナリオの進め方は明確ではないかもしれません。例えば、以下のどれかのようなシチュエーションです:
- あなたは外部から来たコンサルタントで、新しいクライアントの未知のワークロードを最適化する仕事を請け負った。
- あなたはある団体におけるAmazon Redshiftの担当エキスパートであり、しばしば自分が設計・実装をしていないAmazon Redshiftのリソースに関するアドバイスを求められる
- 既存のAmazon Redshiftクラスタのオーナー権限は移譲されているが、そのワークロードや課題についてよく分かっていない。
どういったシナリオであれ、時間を最も効率的に利用するアプローチを使いながら最適化を進めることは常に重要です。
私は、最適化をする対象の表の重要性を見積もるには「スキャン頻度」と「テーブルサイズ」が最も関連性が高い指標であることを発見しました。以下のSQLは、最近のワークロード履歴の特性に基づき、それぞれシナリオにおいて、最適化する対象となるテーブルのリストを得るために役立つものです。それぞれの結果はスキャン頻度順に並べられ、最も多くスキャンされた表が先頭に表示されます。
シナリオ: 「遅いという事に対して具体的な情報はありません。しかし、全てのテーブルをレビューして、クラスターのパフォーマンスを最大にしたいと考えています」
-- Returns table information for all scanned tables
SELECT * FROM admin.v_extended_table_info
WHERE table_id IN (
SELECT DISTINCT tbl FROM stl_scan WHERE type=2
)
ORDER BY SPLIT_PART("scans:rr:filt:sel:del",':',1)::int DESC,
size DESC;
シナリオ: 「クエリーID 4941313が遅い」
-- Returns table information for all tables scanned by query 4941313
SELECT * FROM admin.v_extended_table_info
WHERE table_id IN (
SELECT DISTINCT tbl FROM stl_scan WHERE type=2 AND query = 4941313
)
ORDER BY SPLIT_PART("scans:rr:filt:sel:del",':',1)::int DESC,
size DESC;
シナリオ: 「トランザクションID(XID) 23200のクエリが遅い」
-- Returns table information for all tables scanned within xid 23200
SELECT * FROM admin.v_extended_table_info
WHERE table_id IN (
SELECT DISTINCT tbl FROM stl_scan
WHERE type=2
AND query IN (SELECT query FROM stl_query WHERE xid=23200)
)
ORDER BY SPLIT_PART("scans:rr:filt:sel:del",':',1)::int DESC,
size DESC;
シナリオ: 「02:00～04:00 UTCの間で実行された私達のETLワークロードがSLA（サービスレベルアグリーメント）違反を起こしています」（※訳注：規定された時間以内に処理が終らなかった）
-- Returns table information for all tables scanned by “etl_user”
-- during 02:00 and 04:00 on 2016-09-09
SELECT * FROM admin.v_extended_table_info
WHERE table_id IN (
SELECT DISTINCT tbl FROM stl_scan
WHERE type=2
AND query IN (
SELECT q.query FROM stl_query q
JOIN pg_user u ON u.usesysid=q.userid
WHERE u.usename='etl_user'
AND starttime BETWEEN '2016-09-09 2:00' AND '2016-09-09 04:00')
)
ORDER BY SPLIT_PART("scans:rr:filt:sel:del",':',1)::int DESC,
size DESC;
シナリオ: “「'sales'スキーマ上のテーブルを使ったレポーティングのワークロードが遅い」
-- Returns table information for all tables scanned by queries
-- from "reporting_user" which scanned tables in the "sales" schema
SELECT * FROM admin.v_extended_table_info
WHERE table_id IN (
SELECT DISTINCT tbl FROM stl_scan
WHERE type=2 AND query IN (
SELECT DISTINCT s.query FROM stl_scan s
JOIN pg_user u ON u.usesysid = s.userid
WHERE s.type=2 AND u.usename='reporting_user' AND s.tbl IN (
SELECT c.oid FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE nspname='sales'
)
)
)
ORDER BY SPLIT_PART("scans:rr:filt:sel:del",':',1)::int DESC,
size DESC;
シナリオ: 「ダッシュボード用のクエリに最適化が必要です」
-- Returns table information for all tables scanned by queries
-- from “dashboard_user”
SELECT * FROM admin.v_extended_table_info
WHERE table_id IN (
SELECT DISTINCT s.tbl FROM stl_scan s
JOIN pg_user u ON u.usesysid = s.userid
WHERE s.type=2 AND u.usename='dashboard_user'
)
ORDER BY SPLIT_PART("scans:rr:filt:sel:del",':',1)::int DESC,
size DESC;
これで最適化のために、テーブルの優先順位を付けることができましたので、ここがスタート点になります。
シリーズの次のブログ記事では、ディストリビューションスタイルとディストリビューションキーについて説明します。
Amazon Redshift Engineering’s Advanced Table Design Playbook
Part 1: Preamble, Prerequisites, and Prioritization (本稿)
Part 2: Distribution Styles and Distribution Keys
Part 3: Compound and Interleaved Sort Keys
Part 4: Compression Encodings
Part 5: Table Data Durability
著者について
Zach Christopherson はパロアルトを拠点とするAWSのシニアデータベースエンジニアです
彼は、全ての業界において、Amazon Redshiftのユーザーが最適なパフォーマンスを得るためのワークロードをチューニングすることを支援しています。 Amazon Redshiftサービスチームの一員として、彼は新しいサービスや既存のサービスの開発にも影響を与えており、貢献しています。休暇の際は妻のメアリーと一緒に新しいレストランにトライし、生まれたばかりの娘ソフィアの世話を楽しんでいます。
日本語翻訳：下佐粉昭 (simosako@)
コメント