[Redshift] STL_INSERTとSTL_QUERYを使用して過去に実行したクエリの情報を調べる

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

SQLの作成や修正する際に、過去のクエリの実行状況を確認したい事が有ります。Redshiftではクラスターを起動してから終了するまでのクエリをコンソールから見る事ができますが、スナップショットに保存ができなかったり、保存期間が短かったりします。
Redshiftには実行したクエリを自動的に保存してくれるテーブルが有るので活用してみたいと思います。

環境

MacOSX 10.10.5 Yosemite
Redshift 1.0.1125

使用するSTLテーブル

STL_INSERT

Amazon Redshift | STL_INSERT

列名 データ型 説明
userid integer エントリを生成したユーザーのID。
query integer クエリ ID。クエリ列は、他の各種システムテーブルおよびビューを結合するために使用可能。
slice integer クエリが実行されているスライスを識別する番号。
segment integer クエリセグメントを識別する番号。
step integer 実行されたクエリステップ。
starttime timestamp UTCで表されたクエリの実行開始時刻。秒の小数部の精度(6 桁)を使用。
endtime timestamp UTC で表されたクエリの実行終了時刻。秒の小数部の精度(6 桁)を使用。
tasknum integer ステップの実行に割り当てられたクエリタスクプロセスの数。
rows bigint 処理された合計行数。
tbl integer テーブル ID。

STL_QUERY

Amazon Redshift | STL_QUERY

列名 データ型 説明
userid integer エントリを生成したユーザーの ID。
query integer クエリ ID。クエリ列は、他の各種システムテーブルおよびビューを結合するために使用可能。
label character(15) クエリを実行するために使用される名前、またはSET QUERY_GROUPコマンドによって定義されるラベル。クエリがファイルベースでないか、QUERY_GROUPパラメーターが設定されていない場合、このフィールドの値はdefault。
xid bigint トランザクション ID。
pid integer プロセス ID。通常、セッション内のすべてのクエリは同一プロセスで実行されるため、一連のクエリを同一セッションで実行した場合、この値は通常は一定です。Amazon Redshiftは特定の内部イベントに続いてアクティブなセッションを再起動し、新しい PID を割り当てる場合がある。
database character(32) クエリが発行されたときにユーザーが接続されたデータベースの名前。
querytxt character(4000) クエリの実際のクエリテキスト。
starttime timestamp UTCで表されたクエリの実行開始時刻。秒の小数部の精度(6 桁)を使用。
endtime timestamp UTCで表されたクエリの実行終了時刻。秒の小数部の精度(6 桁)を使用。
aborted integer クエリがシステムによって中断されたかユーザーによってキャンセルされた場合、この列は 1 になります。クエリが(クライアントに結果を返すことも含めて)最後まで実行された場合、この列は 0 になります。クライアントが結果を受け取る前に接続を解除した場合、クエリはバックエンドで正常に完了した場合でも、キャンセルされたものとしてマーク(1)されます。
insert_pristine integer 現在のクエリの実行中に書き込みクエリが実行可能であるかどうか。1 = 書き込みクエリは許可されません。0 = 書き込みクエリは許可されます。この列は、デバッグで使用することが意図されています。

使ってみる

目的

ユーザーごとのクエリの開始時間、終了時間、行数、クエリを表示する。

確認

事前に以下のクエリを上から順に実行してから確認しました。
・DROP TABLE
・CREATE TABLE
・INSERT INTO
・COPY
・DELETE
・TRUNCATE

STLテーブルの結合

SELECT
  I.userid
  , I.query
  , convert_timezone('JST',Q.starttime) AS starttime
  , convert_timezone('JST',Q.endtime) AS endtime
  , I.rows
  , Q.querytxt
FROM stl_insert AS I
  LEFT OUTER JOIN stl_query AS Q ON I.query = Q.query
WHERE Q.aborted = 0
  AND Q."database" = 'testdb'
ORDER BY Q.starttime DESC;
;
 userid | query |         starttime          |           endtime          | rows |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             querytxt                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
--------+-------+----------------------------+----------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------
    100 |  1391 | 2016-11-29 22:15:37.349719 | 2016-11-29 22:15:39.832749 |    2 |  COPY blog.check_table FROM 's3://bucket/folder/check_table.tsv' CREDENTIALS '' DELIMITER '\\t' REMOVEQUOTES NULL AS '' IGNOREHEADER 1  
    100 |  1391 | 2016-11-29 22:15:37.349719 | 2016-11-29 22:15:39.832749 |    1 |  COPY blog.check_table FROM 's3://bucket/folder/check_table.tsv' CREDENTIALS '' DELIMITER '\\t' REMOVEQUOTES NULL AS '' IGNOREHEADER 1  
    100 |  1389 | 2016-11-29 22:15:34.685304 | 2016-11-29 22:15:36.129773 |    2 |  INSERT INTO blog.check_table VALUES (1,'aaa'),(2,'bbb'),(3,'ccc')                                                                              
    100 |  1389 | 2016-11-29 22:15:34.685304 | 2016-11-29 22:15:36.129773 |    1 |  INSERT INTO blog.check_table VALUES (1,'aaa'),(2,'bbb'),(3,'ccc')                                                                              
(4 rows)

COPYとINSERTのみが登録されている事が確認できました。
COPYのCREDENTIALは自動的に空欄になる様です。

さいごに

とても便利でありがたいテーブルですので、他のSTLテーブルも探ってみたいと思います。