ステップ 6: システムテーブルをクエリする

データウェアハウスには、作成したテーブルに加え、いくつかのシステムテーブルとビューが含まれています。これらのシステムテーブルには、インストールに関する情報と、システムで実行されている各種のクエリや処理に関する情報が格納されます。これらのシステムテーブルに対してクエリを実行して、データベースに関する情報を収集することができます。詳細については、「Amazon Redshift データベース管理者ガイド」の「システムテーブルとビューのリファレンス」を参照してください。各テーブルやビューについての説明では、テーブルをすべてのユーザーが表示できるか、スーパーユーザーのみが表示できるかを示しています。スーパーユーザーのみが表示可能なテーブルに対してクエリを実行するには、スーパーユーザーとしてログインします。

テーブル名のリストを表示する

スキーマ内のすべてのテーブルのリストを表示するには、PG_TABLE_DEF システムカタログテーブルをクエリします。最初に search_path に対する設定を確認します。

SHOW search_path;

その結果は以下のようになります。

  search_path
---------------
 $user, public

次の例では、SALES スキーマを検索パスに追加し、SALES スキーマ内のすべてのテーブルを表示します。

set search_path to '$user', 'public', 'sales';
                
SHOW search_path;

      search_path       
------------------------
 "$user", public, sales


select * from pg_table_def where schemaname = 'sales';

 schemaname | tablename |  column  |          type          | encoding | distkey | sortkey | notnull 
------------+-----------+----------+------------------------+----------+---------+---------+---------
 sales      | demo      | personid | integer                | az64     | f       |       0 | f
 sales      | demo      | city     | character varying(255) | lzo      | f       |       0 | f

次の例では、現在のデータベース上のすべてのスキーマに含まれる、DEMO という名前のすべてのテーブルのリストを表示します。

set search_path to '$user', 'public', 'sales';
select * from pg_table_def where tablename = 'demo';

 schemaname | tablename |  column  |          type          | encoding | distkey | sortkey | notnull 
------------+-----------+----------+------------------------+----------+---------+---------+---------
 public     | demo      | personid | integer                | az64     | f       |       0 | f
 public     | demo      | city     | character varying(255) | lzo      | f       |       0 | f
 sales      | demo      | personid | integer                | az64     | f       |       0 | f
 sales      | demo      | city     | character varying(255) | lzo      | f       |       0 | f

詳細については、「PG_TABLE_DEF」を参照してください。

Amazon Redshift クエリエディタ v2 を使用して接続先のデータベースを最初に選択することで、指定したスキーマ内のすべてのテーブルを表示することもできます。

ユーザーを表示する

ユーザー ID (USESYSID) およびユーザー権限とともに、すべてのユーザーのリストを表示するには、PG_USER カタログをクエリします。

SELECT * FROM pg_user;

  usename   | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
------------+----------+-------------+----------+-----------+----------+----------+-----------
 rdsdb      |        1 | true        | true     | true      | ******** | infinity |
 awsuser    |      100 | true        | true     | false     | ******** |          |
 guest      |      104 | true        | false    | false     | ******** |          |

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

SELECT * FROM pg_user WHERE usesysid > 1;

  usename   | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
------------+----------+-------------+----------+-----------+----------+----------+-----------
 awsuser    |      100 | true        | true     | false     | ******** |          |
 guest      |      104 | true        | false    | false     | ******** |          |

最近のクエリを表示する

先の例では、adminuser のユーザー ID (user_id) は 100 です。adminuser が実行した最近の 4 つのクエリを一覧表示するには、SYS_QUERY_HISTORY ビューをクエリできます。

このビューを使用して、最近実行したクエリのクエリ ID (query_id) やプロセス ID (session_id) を確認できます。また、このビューを使用してクエリが完了するまでにかかった時間を確認できます。SYS_QUERY_HISTORY には、特定のクエリを見つけやすくするために、クエリ文字列 (query_text) の最初の 4,000 文字が含まれています。SELECT ステートメントで LIMIT 句を使用すると、結果を制限できます。

SELECT query_id, session_id, elapsed_time, query_text 
FROM sys_query_history
WHERE user_id = 100
ORDER BY start_time desc
LIMIT 4;

結果は以下のようになります。

 query_id |  session_id  |  elapsed_time |   query_text
----------+--------------+---------------+----------------------------------------------------------------
 892      |    21046     |       55868   | SELECT query, pid, elapsed, substring from ...
 620      |    17635     |     1296265   | SELECT query, pid, elapsed, substring from ...
 610      |    17607     |       82555   | SELECT * from DEMO; 
 596      |    16762     |      226372   | INSERT INTO DEMO VALUES (100);

実行中のクエリのセッション ID を確認する

クエリに関するシステムテーブル情報を取得するには、クエリに関連するセッション ID (プロセス ID) の指定が必要になる場合があります。または、実行中のクエリのセッション ID の確認が必要になる場合もあります。例えば、プロビジョニングされたクラスターで実行時間が長すぎるクエリをキャンセルする場合は、セッション ID が必要になります。STV_RECENTS システムテーブルにクエリを実行すると、実行中のクエリのプロセス ID および対応するクエリ文字列のリストを取得できます。クエリから複数のセッションが返された場合は、クエリテキストを参照して、どのセッション ID が必要かを判断できます。

実行中のクエリのセッション ID を確認するには、次の SELECT ステートメントを実行します。

SELECT session_id, user_id, start_time, query_text
FROM sys_query_history
WHERE status='running';