よく使うRedshift運用系SQL

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


# [1] クエリ状況系
## ◆Runningクエリ確認

```sql
select pid, trim(user_name), starttime, substring(query,1,20) 
from stv_recents
where status='Running';
```
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/cancel_query.html

##◆クエリ実行履歴

`STL_QUERY`

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STL_QUERY.html

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

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

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


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

`STL_WLM_QUERY`

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

```sql
select * from stl_wlm_query
```

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

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

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

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

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

```sql
set wlm_query_slot_count to 3; 
```

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_wlm_query_slot_count.html

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

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

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_SVL_QUERY_SUMMARY.html

```sql
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;

```

https://aws.typepad.com/sajp/2015/12/top-10-performance-tuning-techniques-for-amazon-redshift.html


★Concurrency系の注意点

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

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

→ `STL_WLM_QUERY` の slot_count 列で確認できる

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

`STV_WLM_SERVICE_CLASS_STATE`

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STV_WLM_SERVICE_CLASS_STATE.html

##◆自分のクエリのステータス
キュー待ちなのか、実行中なのか

`STV_WLM_QUERY_STATE`

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STV_WLM_QUERY_STATE.html

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

`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

```sql
CANCEL [PID(プロセスID)]
```

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

# [2] テーブル系
##◆外部スキーマ一覧

```sql
select * from PG_EXTERNAL_SCHEMA
```

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

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

例）

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

`serach paht`の確認と設定

例）

```sql
#確認
show search_path;

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

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

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

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

例）

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

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

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

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_ANALYZE_COMPRESSION.html

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

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

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

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

※sortkeyの場合は `alter  [COMPOUND] sortkey`

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_ALTER_TABLE.html

# [3] Redshift Spectrum系

##◆Spectrumクエリログを確認

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

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_SVL_S3QUERY_SUMMARY.html

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

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

https://docs.aws.amazon.com/redshift/latest/dg/r_STL_WLM_RULE_ACTION.html

# [4] Glue Data Catalog系

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

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

```sql
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などがある点。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c-spectrum-external-tables.html

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_ALTER_TABLE_external-table.html

例

```sql

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

# [5] ユーザ系

##◆ユーザ追加

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

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

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

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

##◆rdsdbユーザって何？

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

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

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_querying_redshift_system_tables.html

##◆ユーザ一覧確認

`\du`

または

`select * from pg_user;`


```sql
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` をキーに結合すれば変換可能