Amazon Redshift便利ツール『amazon-redshift-utils』の便利SQLスクリプト紹介(AdminScripts編)
Amazon Redshiftでは日々の作業を行う上で様々な情報を必要とし、その過程で『こういう情報が欲しい』という局面が多々あります。当ブログでも適宜『便利系SQL』として必要な情報を取得する為のSQLをご紹介して来ましたが、以下のGitHub上で展開されている各種スクリプトがとても有用そうでしたので2回に分けて中身を見てみたいと思います。当エントリは『管理系スクリプト(AdminScripts)』に関する内容となります。
目次
- commit_stats.sql
- current_session_info.sql
- filter_used.sql
- missing_table_stats.sql
- perf_alert.sql
- table_info.sql
- top_queries.sql
commit_stats.sql
COMMIT文を介して、消費量に関する情報を表示します。過去2日間のコミットキュー統計を返します。
current_session_info.sql
現在実行中のクエリとのセッションについての情報を表示します。
filter_used.sql
テーブルスキャン時に適用されるフィルタの情報を返します(過去7日間分)。ソートキーを選択する上での有用な情報となります。
select trim(s.perm_Table_name) as table , substring(trim(info),1,180) as filter, sum(datediff(seconds,starttime,endtime)) as secs, count(*) as num, max(i.query) as query from stl_explain p join stl_plan_info i on ( i.userid=p.userid and i.query=p.query and i.nodeid=p.nodeid ) join stl_scan s on (s.userid=i.userid and s.query=i.query and s.segment=i.segment and s.step=i.step) where s.starttime > dateadd(day, -7, current_Date) and s.perm_table_name not like 'Internal Worktable%' and p.info <> '' and s.perm_table_name like '%' -- chose table(s) to look for group by 1,2 order by 1, 4 desc , 3 desc;
missing_table_stats.sql
"missing statistics"というフラグが立っているEXPLAINプランを表示します。
- EXPLAIN - Amazon Redshift
- Table Statistics Missing or Out of Date / Improving Query Performance - Amazon Redshift
perf_alert.sql
直近1週間の中で出現したパフォーマンス上の"アラート"について表示します。このクエリについてはより有用そうなので項目に関する情報を付記してみます。
| 項目 | 説明 |
|---|---|
| table | 対象テーブル名 |
| minutes | アクションに掛かった分数。(※全てのアラートで出る訳ではない模様) |
| rows | scan/dist/bcastの対象行数 |
| event | アラートイベント名 |
| solution | アラート及びパフォーマンスの問題を避けるために提案される解決策 |
| sample_query | アラートが出現した直近のquery_id |
| count | アラートの出現数 |
select
trim(s.perm_table_name) as table ,
(sum(abs(datediff(seconds, coalesce(b.starttime,d.starttime,s.starttime),coalesce(b.endtime,d.endtime,s.endtime))))/60)::numeric(24,0) as minutes,
sum(coalesce(b.rows,d.rows,s.rows)) as rows,
trim(split_part(l.event,':',1)) as event,
substring(trim(l.solution),1,60) as solution,
max(l.query) as sample_query,
count(*)
from stl_alert_event_log as l
left join stl_scan as s on s.query = l.query and s.slice = l.slice and s.segment = l.segment
left join stl_dist as d on d.query = l.query and d.slice = l.slice and d.segment = l.segment
left join stl_bcast as b on b.query = l.query and b.slice = l.slice and b.segment = l.segment
where l.userid >1
and l.event_time >= dateadd(day, -3, current_Date)
group by 1,4,5 order by 2 desc,6 desc;
queuing_queries.sql
WLMクエリスロットを待機しているクエリを示します。
table_info.sql
テーブルのストレージ情報(サイズやスキュー等)を返します。SVV_TABLE_INFOで得られる情報と近いものがある感じです。
top_queries.sql
過去7日間で『最も時間の掛かったSQL文TOP50』を返します。
上記GitHub上のSQLを実行してみましたが一部エラーが出る模様。SQL文で検索を掛けてみたところAWS公式でも同種のSQLが紹介されていましたのでこちらのリンクを展開しておきます。こっちのページでも"使える"SQLがまとめられていますね。
select
trim(database) as db,
count(query) as n_qry,
max(substring (qrytext,1,80)) as qrytext,
min(run_minutes) as "min" ,
max(run_minutes) as "max",
avg(run_minutes) as "avg",
sum(run_minutes) as total,
max(query) as max_query_id,
max(starttime)::date as last_run,
sum(alerts) as alerts,
aborted
from
(select
userid,
label,
stl_query.query,
trim(database) as database,
trim(querytxt) as qrytext,
md5(trim(querytxt)) as qry_md5,
starttime,
endtime,
(datediff(seconds, starttime,endtime)::numeric(12,2))/60 as run_minutes,
alrt.num_events as alerts,
aborted
from stl_query
left outer join (select query, 1 as num_events from stl_alert_event_log group by query ) as alrt
on alrt.query = stl_query.query
where
userid <> 1 and starttime >= dateadd(day, -7, current_date))
group by
database, label, qry_md5, aborted
order by
total desc limit 50;
まとめ
以上、管理系スクリプトに関する『amazon-redshift-utils』のご紹介でした。この辺りのスクリプトを使えば更にRedshift業務が捗りますね！同時投稿の以下エントリでは『管理ビュー(AdminViews)』についても書いていますので併せてご覧頂けますと幸いです。こちらからは以上です。
![[レポート]AWSのデータ取り込み戦略 - Solving different data ingestion use cases with AWS (ANT330) #AWSreInvent](https://images.ctfassets.net/ct0aopd36mqt/3IQLlbdUkRvu7Q2LupRW2o/edff8982184ea7cc2d5efa2ddd2915f5/reinvent-2024-sessionreport-jp.jpg)
![[Redshift] テーブルへのアクセス権付与権限について調べてみた](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
