【Redshift】SQLでテーブルごとのアクセス状況を可視化する

2025.10.14

 はじめにデータ事業本部の荒木です。
Redshiftの運用のなかで、「このテーブルってどれくらいの頻度で使われているんだろう？」「特定のテーブルにアクセスしているユーザーは誰だろう？」と疑問に思うことはありませんか？

今回は、そうした疑問を解消するために、Redshiftのシステムビューからテーブルごとのアクセス状況（SELECT/INSERT/DELETE） を可視化するSQLを作成したのでご紹介します。

このSQLを使えば、使われていないテーブルの特定や、特定のユーザー・テーブルへのアクセス傾向の分析に役立てることができます。
 作成したSQL今回作成したSQL全体がこちらです。

					
			WITH base AS (
    SELECT 
        s.userid, 
        s.query, 
        s.tbl AS table_id, 
        'SELECT' AS op_type,
        starttime
    FROM stl_scan s
    UNION
    SELECT i.userid,
        i.query,
        i.tbl AS table_id,
        'INSERT' AS op_type,
        starttime
    FROM stl_insert i
    UNION
    SELECT d.userid,
        d.query,
        d.tbl AS table_id,
        'DELETE' AS op_type,
        starttime
    FROM stl_delete d
)
SELECT
    u.usename AS user_name,
    ti."schema" AS schemaname,
    ti."table"  AS tablename,
    b.starttime::DATE  AS date,
    COUNT(DISTINCT CASE WHEN b.op_type = 'SELECT' THEN b.query END) AS select_query_count,
    COUNT(DISTINCT CASE WHEN b.op_type = 'INSERT' THEN b.query END) AS insert_query_count,
    COUNT(DISTINCT CASE WHEN b.op_type = 'DELETE' THEN b.query END) AS delete_query_count
FROM base b
JOIN pg_user u ON u.usesysid   = b.userid
JOIN svv_table_info ti ON ti.table_id   = b.table_id
WHERE ti."schema" NOT IN ('pg_internal','pg_catalog','information_schema')
AND u.usename <> 'rdsdb'
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4
;

		
		
このSQLは、CTE（共通テーブル式） とメインクエリの2つの部分に分かれています。
 CTE（base）部分の解説最初のCTE（base）では、以下のDML操作履歴が格納されたテーブルから直近7日間のテーブル操作履歴を取得し、UNIONで結合しています。
stl_scan: テーブルへのSELECT操作の履歴
stl_insert: テーブルへのINSERT操作の履歴
stl_delete: テーブルへのDELETE操作の履歴
各ビューから userid、query、table_id を取得し、op_type という列でそれぞれの操作タイプ（SELECT、INSERT、DELETE）を識別しています。これにより、全てのDML操作を一元的に集計するための土台を作成しています。
 メインクエリ部分の解説次に、このCTE（base）を使って、最終的な集計を行います。
CTE（base）にユーザー情報（pg_user）およびテーブル情報（svv_table_info）と結合することで、IDをユーザー名、スキーマ名、テーブル名に変換しています。

また、COUNT(DISTINCT b.query) を使用することで、同じクエリが複数回実行された場合でも、一意のクエリIDとして実行回数（クエリ数）をカウントし、op_type に応じてselect_query_count、insert_query_count、delete_query_count を分離して集計しています。
 SQLの実行結果と活用方法このSQLを実行すると、以下のような結果が得られます。


user_name
schemaname
tablename
date
select_query_count
insert_query_count
delete_query_count


user_a
public
sales_data
2025-10-10
0
5


user_a
raw
click_log
2025-10-09       30
0
0


user_b
public
user_info
2025-10-08       50
10
0


user_b
marketing
campaign_result
2025-10-07        0
0
0


システムテーブル内のログ履歴の保管期間は7日未満となるため、過去の実行結果なども保管したい場合には、これらの結果等を定期的に専用のテーブルにデータをコピーしたりしておく必要があります。
 この結果から何がわかるか？テーブルの使用頻度: select_query_count が0のテーブル（例: campaign_result）は、直近1週間で参照されていないことがわかります。アーカイブや削除を検討する際の判断材料になります。
ユーザーのアクセス傾向: user_a は sales_data テーブルを頻繁に参照しているなど、特定のユーザーのアクセス傾向を分析できます。
更新処理の状況: insert_query_count や delete_query_count を見れば、どのテーブルにどれくらいの更新処理が走っているか把握でき、データロード処理の健全性チェックにも利用可能です。
 まとめ今回は、Redshiftのシステムビューを横断的に活用して、テーブルごとのアクセス状況を詳細に可視化するSQLをご紹介しました。

お役に立てば幸いです。
 参照ログ記録のための STL ビュー