よく使うRedshift運用系SQLや運用Tips。
GGなのですぐ忘れる + いちいちググるのに疲れたのでメモ。
随時更新。

[1] クエリ状況系

◆Runningクエリ確認

select pid, trim(user_name), starttime, substring(query,1,20) 
from stv_recents
where status='Running';

◆クエリ実行履歴

STL_QUERY

クエリの全体を確認したい場合

STL_QUERY の querytxt カラムは、SQLが長いと途中で切れてしまう。
全体をチェックしたい場合は、STL_QUERYTEXT で query(クエリID)を指定して抽出。

select text from STL_QUERYTEXT where query = 'クエリID' order by sequence

◆WLMキューの詰まり具合確認

STL_WLM_QUERY

クエリ単位で、キュー待ち開始～終了時間と、実際のクエリ実行開始～終了時間がわかる。

select * from stl_wlm_query

◆特定のクエリへのリソース割り当てを増やす（パフォーマンス改善）

セッション単位で、クエリへのslot（同時実行数）の割り当てを増やすことで、
メモリリソースの割り当てが増え、パフォーマンスが改善する場合がある。

クラスタに規定する同時実行数（concurrency）が例えば推奨値15の場合、
slot で 3 を割り当てると、該当のセッションのクエリは 3/15 のリソースを利用し、
残りで 12/15 を分け合う。

パフォーマンス改善の理屈は、クエリ処理で一時的にメモリを使う際に
割り当てられたメモリでは足りない場合にDISK書き込みが発生する場合がある。
メモリを十分詰んでおけば、そのオーバーヘッドがなくなり早くなる。
※つまり、メモリ不足のクエリほど効果が期待できる

下記は 3 slot を割り当てた例

set wlm_query_slot_count to 3;

実際に割り当てられたslot数の確認は、 STL_WLM_QUERY の slot_count でチェックできる。

なお、「メモリ不足のクエリかどうか」は、SVL_QUERY_SUMMARY の is_diskbased = trueの条件で
下記で確認できる。

SELECT
q.query, trim(q.cat_text)
FROM (SELECT query, replace( listagg(text,' ') WITHIN GROUP (ORDER BY sequence), '\\n', ' ') AS cat_text FROM stl_querytext WHERE userid>1 GROUP BY query) q
JOIN
(SELECT distinct query FROM svl_query_summary WHERE is_diskbased='t' AND (LABEL LIKE 'hash%' OR LABEL LIKE 'sort%' OR LABEL LIKE 'aggr%') AND userid > 1) qs ON qs.query = q.query;

★Concurrency系の注意点

・全体の並列実行数を増やしすぎると、1クエリあたりのメモリ割り当てが少なくなる
・クエリ当たりのslot数を増やしすぎると、その確保までキュー待ちが発生しやすくなる

実際のクエリへのsloq割り当て状況を確認

→ STL_WLM_QUERY の slot_count 列で確認できる

◆キュー単位の待ち数と実行中クエリ数確認

STV_WLM_SERVICE_CLASS_STATE

◆自分のクエリのステータス

キュー待ちなのか、実行中なのか

STV_WLM_QUERY_STATE

◆クエリのスワップ状況を確認

SVL_QUERY_SUMMARY の is_diskbased カラムが t の場合、メモリ内で処理できずにDISKスペースを利用しているhttps://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_SVL_QUERY_SUMMARY.html

キーはqueryidで指定する必要があるため、必要に応じて STL_QUERY で対称クエリを特定する

◆クエリKILL

CANCEL [PID(プロセスID)]

※PIDは↑の「Runningクエリ確認」でチェック

[2] テーブル系

◆外部スキーマ一覧

select * from PG_EXTERNAL_SCHEMA

◆テーブルのDist KeyとSort Keyの設定有無を確認

・pg_table_def をチェック
・結果が出ない場合は、serach pathを設定

例）

select * from pg_table_def where tablename = [テーブル名];

serach pahtの確認と設定

例）

#確認
show search_path;

#設定
set search_path to [スキーマ名];

set は同一セッション中のみ有効

◆テーブルのカラムのエンコードタイプ（圧縮形式）を確認する

・上と同じくpg_table_def をチェック

例）

select * from pg_table_def where tablename = [テーブル名];

◆テーブルのカラムのエンコードタイプ（圧縮形式）の最適なものは？

ANALYZE COMPRESSIONで、最適なカラム圧縮形式を確認できる

ALTER TABLEではエンコードタイプの変更はできないので、CREATE TABLEして
INSERT INTO する必要がある。

◆テーブルのdistkey、sortkey設定を変更する

昔はテーブルを作り直す必要があったが、今はalter tableで可能

alter table [テーブル名] alter distkey [カラム名];

※sortkeyの場合は alter [COMPOUND] sortkey

[3] Redshift Spectrum系

◆Spectrumクエリログを確認

Spectrumクエリのスキャン量（行、サイズ）、abort有無など。
SVL_S3QUERY_SUMMARY テーブルを確認

◆WLM の実行結果/ログを確認

WLMの条件に合致したクエリのログやabort有無など。
STL_WLM_RULE_ACTION テーブルを確認

[4] Glue Data Catalog系

◆外部スキーマ作成と紐づけ

Redshiftで外部スキーマを作成して、Glue Data Catalogのdatabaseと紐づける
※ROLEやRedshift～Glue間の接続設定については省略

create  external  schema  if not exists [外部スキーマ名]
from data  catalog  database '[外部スキーマ名]'
iam_role 'arn:aws:iam::xxxxxxxxx:role/xxxx'
create external database if not exists;

◆外部テーブル作成と紐づけ

紐づけ済の外部スキーマ配下に作成するだけ。
通常のCreate Tableとの違いとして、ファイル格納先のlocationや
メタ情報を設定する table propertiesなどがある点。

例


create external table [スキーマ].[テーブル](
[column] [type] [encode],
：
)
row format delimited
fields terminated by '\t'
stored as textfile
location 's3://[バケット]'
table properties ('numRows'='100000');

[5] ユーザ系

◆ユーザ追加

CREATE USER [ユーザID] PASSWORD ‘******’ VALID UNTIL ‘yyyy-mm-dd’ ;

※パスワードと有効期限付き

◆ユーザをグループに追加

ALTER  GROUP [グループ名] ADD  USER  [ユーザID] ;

◆rdsdbユーザって何？

Redshiftが内部のメンテナンスタスク等を実行するために使用するusername。

定期的な管理およびメンテナンスタスクを実行するために、Amazon Redshift の内部でユーザー名 rdsdb が使用されます。SELECT ステートメントに where usesysid > 1 を追加することで、クエリをフィルタリングしてユーザー定義のユーザー名のみを表示することができます。

◆ユーザ一覧確認

\du

または

select * from pg_user;

select * from pg_user;

  usename   | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
------------+----------+-------------+----------+-----------+----------+----------+-----------
 rdsdb      |        1 | t           | t        | t         | ******** |          |
 masteruser |      100 | t           | t        | f         | ******** |          |
 dwuser     |      101 | f           | f        | f         | ******** |          |
 simpleuser |      102 | f           | f        | f         | ******** |          |
 poweruser  |      103 | f           | t        | f         | ******** |          |
 dbuser     |      104 | t           | f        | f         | ******** |          |
(6 rows)

◆グループ一覧確認

SELECT * FROM PG_GROUP

◆スキーマ一覧確認

\dn

または

SELECT * FROM PG_NAMESPACE

◆システムテーブルのuseridとDBユーザ名(username)変換

pg_user テーブルの usesysid をキーに結合すれば変換可能

よく使うRedshift運用系SQL