タスク 6: システムテーブルをクエリする
作成したテーブルに加え、データベースにはいくつかのシステムテーブルが含まれています。これらのシステムテーブルには、インストールに関する情報と、システムで実行されている各種のクエリや処理に関する情報が格納されます。これらのシステムテーブルに対してクエリを実行して、データベースに関する情報を収集することができます。
注記
このドキュメントに記載されている、各テーブルについての説明では、テーブルが表示されるのはすべてのユーザーに対してか、スーパーユーザーのみに対してかを示しています。スーパーユーザーのみが表示可能なテーブルに対してクエリを実行するには、スーパーユーザーとしてログインします。
Amazon Redshift では、以下のタイプのシステムテーブルに対するアクセス権を提供しています。
-
これらのシステムテーブルは、システムの履歴を提供するために Amazon Redshift ログファイルから生成されます。ログテーブルには STL プレフィックスが付けられています。
-
これらのテーブルは、現在のシステムデータのスナップショットを格納した仮想システムテーブルです。スナップショットテーブルには STV プレフィックスが付けられています。
-
システムビューには、 の複数の STL および STV システムテーブルから集めたデータのサブセットが含まれます。システムビューには SVV または SVL プレフィックスが付けられています。
-
システムカタログテーブルには、テーブルや列に関する情報などのスキーマメタデータが格納されています。システムカタログテーブルには PG プレフィックスが付けられています。
クエリに関するシステムテーブル情報を取得するには、そのクエリに関連付けられたプロセス ID の指定が必要になる場合があります。詳細については、「実行中のクエリのプロセス ID を調べる」を参照してください。
テーブル名のリストを表示する
スキーマ内のすべてのテーブルのリストを表示するには、PG_TABLE_DEF システムカタログテーブルをクエリします。最初に search_path
に対する設定を確認します。
SHOW search_path;
その結果は以下のようになります。
search_path --------------- $user, public (1 row)
次の例では、SALES
スキーマを検索パスに追加し、SALES
スキーマ内のすべてのテーブルを表示します。
set search_path to '$user', 'public', 'sales'; SHOW search_path; search_path ------------------------ "$user", public, sales (1 row) 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 (2 rows)
次の例では、現在のデータベース上のすべてのスキーマに含まれる、DEMO
という名前のすべてのテーブルのリストを表示します。
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 (4 rows)
詳細については、「PG_TABLE_DEF」を参照してください。
クエリエディタ 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 | ******** | | (3 rows)
定期的な管理およびメンテナンスタスクを実行するために、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 | ******** | | (2 rows)
最近のクエリを表示する
先の例では、adminuser
のユーザー ID (USESYSID) は 100 です。adminuser
が最も最近実行した 5 つのクエリを一覧表示するには、SVL_QLOG ビューをクエリします。
SVL_QLOG ビューには、STL_QUERY テーブルの情報のサブセットがわかりやすく表示されています。このビューを使用して、最近実行されたクエリのクエリ ID (QUERY) やプロセス ID (PID) を知ることができます。また、このビューを使用してクエリが完了するまでにかかった時間を確認できます。クエリを特定しやすくするため、SVL_QLOG には、クエリ文字列 (SUBSTRING) の最初の 60 文字が含まれています。SELECT ステートメントで LIMIT 句を使用すると、結果を 5 行に制限することができます。
SELECT query, pid, elapsed, substring from svl_qlog WHERE userid = 100 ORDER BY starttime desc LIMIT 4;
結果は以下のようになります。
query| pid | elapsed | substring ------+-------+----------+---------------------------------------------------------------- 892 | 21046 | 55868 | SELECT query, pid, elapsed, substring from svl_qlog WHERE us 620 | 17635 | 1296265 | SELECT query, pid, elapsed, substring from svl_qlog WHERE us 610 | 17607 | 82555 | SELECT * from DEMO; 596 | 16762 | 226372 | INSERT INTO DEMO VALUES (100);)