よく使う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 をキーに結合すれば変換可能