Amazon Redshiftのシステムテーブルを見る時によく使うSQL ９選

はじめに

Redshiftには多くのシステムビューがあり、それらを使ってRedshiftの状態を確認することができます。同じSQLをよく実行することがあるので、自分用のメモとして書き溜めていたものをブログにまとめたいと思います。

ログ記録のための STL テーブル - Amazon Redshift システムビューの一覧 - Amazon Redshift システムカタログテーブル - Amazon Redshift

SQL一覧

1. 統計情報の鮮度とソートされていない行の割合を確認したい
2. どの程度テーブルに削除マーク付きのレコードが含まれているかを確認したい
3. VACUUMコマンドの進捗を確認したい
4. SQLの開始時間と終了時間を確認したい
5. SQLの開始時間と終了時間を確認したい。(全部入り)
6. 実行中のクエリの進捗を確認したい
7. どの程度ストレージサイズを使っているかを確認したい
8. 分散キー、ソートキー、圧縮エンコードが設定されていることを確認したい
9. テーブルに依存しているビューを探したい

統計情報の鮮度とソートされていない行の割合を確認したい

統計情報の鮮度とソートされていない行の割合はSVV_TABLE_INFOというシステムビューを見れば確認できます。UNSORTEDの数値が高いテーブルはVACUUMして、STATS_OFFの数値が高いテーブルはANALYZEしましょう。因みにSVV_やSVT_で始まっているものはシステムビューになります。

SVV_TABLE_INFO - Amazon Redshift

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

どの程度テーブルに削除マーク付きのレコードが含まれているかを確認したい

UPDATE、DELETEを実行すると削除マーク付きのレコードが増えていきパフォーマンスが低下する恐れがあります。以下はテーブルにどの程度、削除マーク付きのレコードが含まれているかを確認するSQLです。削除マーク付きのレコードが溜まっている場合はVACUUMコマンドを実行して削除マーク付きのレコードを解放しましょう。

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コマンドの進捗を確認したい

あとどれくらいでVACUUMコマンドが完了するかを確認するSQLです。ただしVACUUMコマンドはいくつかのフェーズに分かれており、このSQLで確認できるのはそのフェーズがどのくらいか終わるかだけなので全体でどの程度かかるかはわかりません。STL_VACUUMテーブルを見れば前回実行時の処理時間を確認できますのでそれを見て残り時間を予想するしかありません。

SVV_VACUUM_PROGRESS - Amazon Redshift STL_VACUUM - Amazon Redshift

SELECT * FROM SVV_VACUUM_PROGRESS;

SELECT
    STL_VACUUM.*,
    SVV_TABLE_INFO."SCHEMA",
    SVV_TABLE_INFO."TABLE"
FROM STL_VACUUM INNER JOIN  SVV_TABLE_INFO
    ON STL_VACUUM.TABLE_ID=SVV_TABLE_INFO.TABLE_ID
WHERE "SCHEMA"='[スキーマ名]' AND "TABLE"='[テーブル名]';

SQLの開始時間と終了時間を確認したい

実行したSQLがどの位の処理時間だったかを確認したことはあると思います。そのような場合に実行時間を表示するSQLになります。querytxtにテーブル名などのキーワードを入れてあいまい検索してください。処理時間の計算にはDATEDIFF関数を使って処理時間を計算しています。

SELECT
    *,
    DATEDIFF(SEC,STARTTIME,ENDTIME) AS DATEDIFF
FROM
    STL_QUERY
WHERE
    QUERYTXT LIKE 'SELECT * FROM%'
ORDER BY
    STARTTIME DESC;

SQLの開始時間と終了時間を確認したい。(全部入り)

上のSQLはDDLなどを含んでいません。全部入りで見たい場合はSVL_STATEMENTTEXTテーブルを参照します。 以下の例はキーワードを含むSQLを処理時間の降順で表示するSQLです。STL_QUERYを参照するよりも時間がかかります。種類がTYPEカラムで分けられていますが、QUERY、DDL、UTILITYのどれかです。UTILITYに含まれるのはTRUNCATE、ANALYZE、BEGIN、COMMIT、ROLLBACKなどになります。因みにVACUUMはQUERYに含まれます。

SVL_STATEMENTTEXT - Amazon Redshift

SELECT
    *,
    DATEDIFF(SEC,STARTTIME,ENDTIME) AS DATEDIFF
FROM
    SVL_STATEMENTTEXT
WHERE
    TYPE = 'DDL' AND TEXT LIKE LOWER('CREATE %')
ORDER BY
    DATEDIFF(SEC,STARTTIME,ENDTIME) DESC;

実行中のクエリの進捗を確認したい

実行中のSQLがどの程度すすんでいるのかを確認するSQLになります。処理される件数に対し、そのステップ内であればROWSカラムの値がどの程度進んでいるかでいるかで進捗が分かります。ただし、初めて実行するSQLの場合、セグメントとステップがいくつあるかは分かりませんので全体でどれくらいかかるかは確認できません。

SVV_QUERY_STATE - Amazon Redshif

SELECT
    *
FROM
    SVV_QUERY_STATE
WHERE
    QUERY = [クエリのID]
ORDER BY
    SEG, STEP;

1回実行したことのあるSQLであれば、セグメント、ステップの処理件数や時間をSQL実行後に確認したい場合はSVL_QUERY_REPORTテーブルを見れば確認できます。

SVL_QUERY_REPORT - Amazon Redshift

SELECT
  USERID, QUERY, SEGMENT, STEP, MIN(START_TIME), MAX(END_TIME), SUM(ROWS) , LABEL
FROM
    SVL_QUERY_REPORT
WHERE
    QUERY = [クエリのID]
GROUP BY 
    USERID, QUERY, SEGMENT, STEP , LABEL
ORDER BY
    SEGMENT, STEP;

どの程度ストレージサイズを使っているかを確認したい

どの位ストレージを使用しているかテーブル単位で降順に表示するSQLです。単位はMBになります。

STV_BLOCKLIST - Amazon Redshift

SELECT 
  SVV_TABLE_INFO.SCHEMA,
  STV_TBL_PERM.NAME, 
  MB
FROM
(
  SELECT
	TBL,
	COUNT(*) as MB
  FROM
    STV_BLOCKLIST
  WHERE
    TBL IN (SELECT ID FROM STV_TBL_PERM)
  GROUP BY TBL
) BLOCK_LIST
    INNER JOIN STV_TBL_PERM ON BLOCK_LIST.TBL=STV_TBL_PERM.ID
    INNER JOIN SVV_TABLE_INFO ON STV_TBL_PERM.ID = SVV_TABLE_INFO.TABLE_ID
GROUP bY
  TBL,
  STV_TBL_PERM.NAME,
  BLOCK_LIST.MB,
  SVV_TABLE_INFO.SCHEMA
ORDER BY
  MB DESC;

分散キー、ソートキー、圧縮エンコードが設定されていることを確認したい

分散キー、ソートキー、圧縮エンコードを確認したい場合は以下のSQLを実行します。PG_TABLE_DEFで参照できるのはSEARCH_PATHに設定されているスキーマのテーブルのみになります。SEARCH_PATHに調査したいスキーマが含まれている場合は1行目は実行しなくてもいいです。

PG_TABLE_DEF - Amazon Redshift

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

テーブルに依存しているビューを探したい

テーブルをDROPする際に依存しているビューがあると失敗します。cascadeオプションをつけると削除できますがどのビューが削除されるかを確認する必要があります。 以下のSQLのWHERE句にテーブル名を指定して実行すると依存しているビューを確認できます。

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";

最後に

私がよく使っているSQLは以上になります。まだ使ったことがないシステムテーブルもありますので、これからも調査を続け便利なSQLがあったらどんどん追加していきたいと思います。あと先日、Redshiftの注意点をまとめた記事を公開しましたので、よろしければこちらもご覧ください。

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