Redshift のクエリの性能情報を取得する bash スクリプト

説明

Redshift のクエリの性能情報を取得する bash スクリプト

• 前提: bash と psql が使えること
• 使い方: Configuration の情報を設定する

実行方法

• bash スクリプトに実行権限を付与する

$ chmod u+x ./redshift_query_diag.sh <クエリID>

• 情報採取する

$ ./redshift_query_diag.sh 57557880 # クエリIDを指定する

• 実行結果を確認する

$ ls -1 redshift_query_diag_57557880_20260212-015340/
REDSHIT_VERSION.csv
SVV_TABLE_INFO.csv
SYS_QUERY_DETAIL.csv
SYS_QUERY_EXPLAIN.csv
SYS_QUERY_HISTORY.csv
redshift_query_diag_57557880_20260212-015340.zip # このファイルに圧縮されている

スクリプト

• redshift_query_diag.sh

#!/usr/bin/env bash

# Configuration
CLUSTER_ENDPOINT="<Cluster Endpoint>"  # Redshift cluster endpoint for psql -h
DB_NAME="<Database Name>"          # Database name for psql -d
USER_NAME="<Username>"        # Username for psql -U
SYS_QUERY_ID="<Query ID>"     # Target query_id

export LC_ALL=C
export TZ=Asia/Tokyo
BASE_NAME=$(basename $0)
SCRIPT_BASE_NAME=$(basename $0 .sh)
CURRENT_DATE=`date '+%Y%m%d-%H%M%S'`
BASE_DIR=$(cd $(dirname $0);pwd)

if [ -z "$1" ]; then
  echo "Please specify query_id of SYS_QUERY_HISTORY"
  echo "  Usage: ${BASE_NAME} <QUERY_ID>"
  echo "  Ref: https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/SYS_QUERY_HISTORY.html"
  exit 1
fi

# Create and move to work directory
mkdir -p "redshift_query_diag_${SYS_QUERY_ID}_${CURRENT_DATE}" && cd "redshift_query_diag_${SYS_QUERY_ID}_${CURRENT_DATE}"

# Execute all queries
psql -h "${CLUSTER_ENDPOINT}" -p 5439 -d "${DB_NAME}" -U "${USER_NAME}" << EOF
\pset format unaligned
\pset format CSV
\t off

\o REDSHIT_VERSION.csv
select version();

\o SYS_QUERY_HISTORY.csv
SELECT a.* FROM SYS_QUERY_HISTORY a WHERE query_id = ${SYS_QUERY_ID};

\o SYS_QUERY_DETAIL.csv
SELECT a.*, rtrim(b.plan_node) AS plan_node, rtrim(b.plan_info) AS plan_info
FROM SYS_QUERY_DETAIL a
LEFT OUTER JOIN SYS_QUERY_EXPLAIN b
    ON a.plan_node_id = b.plan_node_id
    AND a.query_id = b.query_id
    AND a.child_query_sequence = b.child_query_sequence
WHERE a.query_id = ${SYS_QUERY_ID}
ORDER BY a.child_query_sequence,a.stream_id,a.segment_id,a.step_id;

\o SYS_QUERY_EXPLAIN.csv
SELECT a.*
FROM SYS_QUERY_EXPLAIN a
WHERE a.query_id = ${SYS_QUERY_ID}
ORDER BY a.child_query_sequence, a.plan_node_id;

\o SVV_TABLE_INFO.csv
SELECT a.* FROM SVV_TABLE_INFO a
WHERE table_id IN (SELECT table_id FROM SYS_QUERY_DETAIL WHERE query_id = ${SYS_QUERY_ID});
EOF

# Create zip archive
zip -r "redshift_query_diag_${SYS_QUERY_ID}_${CURRENT_DATE}.zip" ./*