システムテーブルとビューのリファレンス - Amazon Redshift (original) (raw)
システムテーブルとビューのリファレンス
トピック
- システムテーブルとビュー
- システムテーブルとビューのタイプ
- システムテーブルとビューのデータの可視性
- プロビジョニング専用クエリから SYS モニタリングビュークエリへの移行
- SYS モニタリングビューを使用したクエリ識別子の改善
- システムテーブルクエリ、プロセス、セッション ID
- SVV メタデータビュー
- SYS モニタリングビュー
- SYS モニタリングビューに移行するためのシステムビューマッピング
- システムモニタリング (プロビジョニングのみ)
- システムカタログテーブル
システムテーブルとビュー
Amazon Redshift には、システムの動作に関する情報を含む多くのシステムテーブルとビューがあります。これらのシステムテーブルとビューには、その他のデータベーステーブルと同じ方法でクエリを実行できます。このセクションでは、いくつかのサンプルシステムテーブルクエリを示し、以下について説明します。
- 異なるタイプのシステムテーブルとビューが生成される方法
- これらのテーブルから取得できる情報のタイプ
- Amazon Redshift システムテーブルをカタログテーブルに結合する方法
- システムテーブルのログファイルの増大を管理する方法
一部のシステムテーブルは、診断目的のため AWS スタッフのみが使用できます。以下のセクションでは、システム管理者またはその他のデータベースユーザーが有益な情報を取得するためにクエリを実行できるシステムテーブルについて説明します。
注記
システムテーブルは自動または手動クラスターバックアップ（スナップショット）には含まれません。STL システムビューは 7 日間のログ履歴を保持します。ログの保持にお客様によるアクションは不要ですが、ログデータを 7 日間以上保持する場合は、ログを定期的に他のテーブルにコピーするか、Amazon S3 にアンロードする必要があります。
システムテーブルとビューのタイプ
システムテーブルとビューには、以下の複数の種類があります。
- SVV ビューには、一時的な STV テーブルへの参照を含むデータベースオブジェクトに関する情報が含まれます。
- SYS ビューは、プロビジョニングされたクラスターとサーバーレスワークグループのクエリとワークロードの使用状況をモニタリングするために使用します。
- STL ビューは、システムの履歴を提供するためにディスクに保持されたログから生成されます。
- STV テーブルとは、現在のシステムデータのスナップショットを含む仮想システムテーブルです これらのテーブルは一時的なメモリ内データに基づいていて、ディスクベースのログまたは通常のテーブルには保持されません。
- SVCS ビューは、メインクラスターおよび同時実行スケーリングクラスターの両方のクエリに関する詳細を提供します。
- SVL ビューは、メインクラスターのクエリに関する詳細を表示します。
システムテーブルとビューは、通常のテーブルと同じ整合性モデルを使用しません。システムテーブルとビュー (特に STV テーブルと SVV ビュー) にクエリを実行するときは、これについて注意するのことが重要です。たとえば、通常の t1 テーブルと c1 列がある場合、次のクエリが行を返さないことが予測されます。
select * from t1
where c1 > (select max(c1) from t1)ただし、システムテーブルに対する次のクエリは、行を返す場合があります。
select * from stv_exec_state
where currenttime > (select max(currenttime) from stv_exec_state)このクエリが行を返すことがある理由は、currenttime が一時的で、クエリの 2 つの参照が評価時に同じ値を返さない可能性があるためです。
一方、次のクエリは行を返さないと予想されます。
select * from stv_exec_state
where currenttime = (select max(currenttime) from stv_exec_state)システムテーブルとビューのデータの可視性
システムテーブルとビューには、データについて 2 つのクラスの可視性 (ユーザーが表示可能、スーパーユーザーが表示可能) があります。
スーパーユーザー特権を持っているユーザーのみが、スーパーユーザーが表示可能なカテゴリのテーブルのデータを表示できます。通常のユーザーは、ユーザーが表示可能なテーブルのデータを表示できます。スーパーユーザーが表示できるテーブルに通常のユーザーがアクセスできるようにするには、テーブルに対する SELECT 権限を通常のユーザーに付与します。詳細については、「GRANT」を参照してください。
デフォルトでは、ユーザーが表示可能なテーブルの大部分で、別のユーザーによって生成された行は、通常のユーザーには表示されません。通常のユーザーに SYSLOG ACCESS UNRESTRICTED を付与すると、ユーザーが表示できるテーブルのすべての行 (別のユーザーが生成した行を含む) を表示できます。詳細については、「ALTER USER」または「CREATE USER」を参照してください。SVV_TRANSACTIONS のすべての行は、すべてのユーザーが表示可能です。データの可視性の詳細については、AWS re:Post ナレッジベースの記事「Amazon Redshift データベースの通常のユーザーに、自分のクラスターの他のユーザーからのシステムテーブルのデータを見る許可を与えるにはどうすればよいですか?」を参照してください。
メタデータビューの場合、Amazon Redshift は SYSLOG ACCESS UNRESTRICTED が許可されているユーザーには表示を許可しません。
注記
システムテーブルに対する無制限のアクセス権限を付与されたユーザーは、別のユーザーが生成したデータへの可視性が提供されます。たとえば、STL_QUERY と STL_QUERY_TEXT には INSERT、UPDATE、および DELETE ステートメントのフルテキストが含まれており、ユーザーが生成した機密データがこれらに含まれている可能性があります。
スーパーユーザーは、すべてのテーブルのすべての行を表示できます。通常のユーザーに、スーパーユーザーが表示可能なテーブルのアクセス権を付与するには、そのテーブルに対する SELECT 権限の GRANT を、通常のユーザーに対して実行します。
システム生成クエリのフィルタ処理
通常、SVL_QUERY_SUMMARY、SVL_QLOG など、クエリに関連するシステムテーブルとビューには、Amazon Redshift がデータベースのステータスを監視するために使用する、自動的に生成された多数のステートメントが含まれます。これらのシステム生成クエリはスーパーユーザーに表示されますが、ほとんど役立ちません。userid 列を使用するシステムテーブルまたはシステムビューからの選択時にそれらのクエリを除外するには、条件 userid > 1 を WHERE 句に追加します。例:
select * from svl_query_summary where userid > 1プロビジョニング専用クエリから SYS モニタリングビュークエリへの移行
プロビジョニングされたクラスターを Amazon Redshift Serverless に移行する
プロビジョニングされたクラスターを Amazon Redshift Serverless に移行すると、プロビジョニングされたクラスターのデータのみを保存する以下のシステムビューがクエリで使用される場合があります。
- すべての STL ビュー
- すべての STV ビュー
- すべての SVCS ビュー
- すべての SVL ビュー
- 一部の SVV ビュー
- Amazon Redshift Serverless でサポートされていない SVV ビューの完全なリストについては、「Amazon Redshift 管理ガイド」の「Amazon Redshift Serverless でのクエリとワークロードのモニタリング」の下部にあるリストを参照してください。
クエリを引き続き使用するには、SYS モニタリングビューで定義されている列のうち、プロビジョニング専用ビューの列に対応するものを使用するように、クエリを再調整してください。プロビジョニング専用ビューと SYS モニタリングビュー間のマッピング関係を確認するには、「SYS モニタリングビューに移行するためのシステムビューマッピング」を参照してください。
プロビジョニングされたクラスター上でのクエリの更新
Amazon Redshift Serverless に移行しない場合でも、既存のクエリを更新できます。SYS モニタリングビューは、使いやすく、複雑さを軽減するように設計されており、効果的なモニタリングとトラブルシューティングに役立つさまざまなメトリクスを提供します。複数のプロビジョニング専用ビューの情報を統合する SYS_QUERY_HISTORY や SYS_QUERY_DETAIL などの SYS ビューを使用すると、クエリを効率化できます。
SYS モニタリングビューを使用したクエリ識別子の改善
SYS_QUERY_HISTORY や SYS_QUERY_DETAIL などの SYS モニタリングビューには、ユーザーのクエリの識別子を保持する query_id 列が含まれています。同様に、STL_QUERY や SVL_QLOG などのプロビジョニング専用ビューにはクエリ列が含まれ、クエリ識別子も保持されます。ただし、SYS システムビューに記録されるクエリ識別子は、プロビジョニング専用ビューに記録されるクエリ識別子とは異なります。
SYS ビューの query_id 列の値とプロビジョニング専用ビューのクエリ列の値の違いは次のとおりです。
- SYS ビューでは、ユーザーが送信したクエリが query_id 列に元の形式で記録されます。Amazon Redshift オプティマイザは、パフォーマンスを向上させるためにそれらを子クエリに分割する場合がありますが、実行した 1 つのクエリでは SYS_QUERY_HISTORY に 1 つの行しか含まれません。個々の子クエリを確認したい場合は、SYS_QUERY_DETAIL で見つけることができます。
- プロビジョニング専用ビューでは、クエリ列に子クエリレベルでクエリが記録されます。Amazon Redshift オプティマイザが元のクエリを複数の子クエリに書き換えた場合、実行する 1 つのクエリに対して、異なるクエリ識別子の値を持つ複数の行がSTL_QUERY に存在します。
モニタリングクエリと診断クエリをプロビジョニング専用ビューから SYS ビューに移行するときは、この違いを考慮し、適宜クエリを編集します。Amazon Redshift がクエリを処理する方法の詳細については、「クエリプランと実行ワークフロー」を参照してください。
例
Amazon Redshift でクエリを記録する方法が、プロビジョニング専用ビューと SYS モニタリングビューで異なる例については、次のサンプルクエリを参照してください。これは Amazon Redshift で実行する場合と同じように記述されたクエリです。
SELECT
s_name
, COUNT(*) AS numwait
FROM
supplier,
lineitem l1,
orders,
nation
WHERE s_suppkey = l1.l_suppkey
AND o_orderkey = l1.l_orderkey
AND o_orderstatus = 'F'
AND l1.l_receiptdate > l1.l_commitdate
AND EXISTS (SELECT
*
FROM
lineitem l2
WHERE l2.l_orderkey = l1.l_orderkey
AND l2.l_suppkey <> l1.l_suppkey )
AND NOT EXISTS (SELECT
*
FROM
lineitem l3
WHERE l3.l_orderkey = l1.l_orderkey
AND l3.l_suppkey <> l1.l_suppkey
AND l3.l_receiptdate > l3.l_commitdate )
AND s_nationkey = n_nationkey
AND n_name = 'UNITED STATES'
GROUP BY
s_name
ORDER BY
numwait DESC
, s_name LIMIT 100;内部で Amazon Redshift クエリオプティマイザは、ユーザーが送信した上記のクエリを 5 つの子クエリに書き換えます。
最初の子クエリは、サブクエリをマテリアライズするための一時テーブルを作成します。
CREATE TEMP TABLE volt_tt_606590308b512(l_orderkey
, l_suppkey
, s_name ) AS SELECT
l1.l_orderkey
, l1.l_suppkey
, public.supplier.s_name
FROM
public.lineitem AS l1,
public.nation,
public.orders,
public.supplier
WHERE l1.l_commitdate < l1.l_receiptdate
AND l1.l_orderkey = public.orders.o_orderkey
AND l1.l_suppkey = public.supplier.s_suppkey
AND public.nation.n_name = 'UNITED STATES'::CHAR(8)
AND public.nation.n_nationkey = public.supplier.s_nationkey
AND public.orders.o_orderstatus = 'F'::CHAR(1);2 番目の子クエリは、一時テーブルから統計情報を収集します。
padb_fetch_sample: select count(*) from volt_tt_606590308b512;3 番目の子クエリは、上記で作成した一時テーブルを参照して、別のサブクエリをマテリアライズするための別の一時テーブルを作成します。
CREATE TEMP TABLE volt_tt_606590308c2ef(l_orderkey
, l_suppkey) AS (SELECT
volt_tt_606590308b512.l_orderkey
, volt_tt_606590308b512.l_suppkey
FROM
public.lineitem AS l2,
volt_tt_606590308b512
WHERE l2.l_suppkey <> volt_tt_606590308b512.l_suppkey
AND l2.l_orderkey = volt_tt_606590308b512.l_orderkey)
EXCEPT distinct (SELECT volt_tt_606590308b512.l_orderkey, volt_tt_606590308b512.l_suppkey
FROM public.lineitem AS l3, volt_tt_606590308b512
WHERE l3.l_commitdate < l3.l_receiptdate
AND l3.l_suppkey <> volt_tt_606590308b512.l_suppkey
AND l3.l_orderkey = volt_tt_606590308b512.l_orderkey);4 番目の子クエリは、一時テーブルの統計を再度収集します。
padb_fetch_sample: select count(*) from volt_tt_606590308c2ef最後の子クエリは、上記で作成した一時テーブルを使用して出力を生成します。
SELECT
volt_tt_606590308b512.s_name AS s_name
, COUNT(*) AS numwait
FROM
volt_tt_606590308b512,
volt_tt_606590308c2ef
WHERE volt_tt_606590308b512.l_orderkey = volt_tt_606590308c2ef.l_orderkey
AND volt_tt_606590308b512.l_suppkey = volt_tt_606590308c2ef.l_suppkey
GROUP BY
1
ORDER BY
2 DESC
, 1 ASC LIMIT 100;プロビジョニング専用システムビュー STL_QUERY では、Amazon Redshift は次のように 5 つの行を子クエリレベルで記録します。
SELECT userid, xid, pid, query, querytxt::varchar(100);
FROM stl_query
WHERE xid = 48237350
ORDER BY xid, starttime;
userid | xid | pid | query | querytxt
--------+----------+------------+----------+------------------------------------------------------------------------------------------------------
101 | 48237350 | 1073840810 | 12058151 | CREATE TEMP TABLE volt_tt_606590308b512(l_orderkey, l_suppkey, s_name) AS SELECT l1.l_orderkey, l1.l
101 | 48237350 | 1073840810 | 12058152 | padb_fetch_sample: select count(*) from volt_tt_606590308b512
101 | 48237350 | 1073840810 | 12058156 | CREATE TEMP TABLE volt_tt_606590308c2ef(l_orderkey, l_suppkey) AS (SELECT volt_tt_606590308b512.l_or
101 | 48237350 | 1073840810 | 12058168 | padb_fetch_sample: select count(*) from volt_tt_606590308c2ef
101 | 48237350 | 1073840810 | 12058170 | SELECT s_name , COUNT(*) AS numwait FROM supplier, lineitem l1, orders, nation WHERE s_suppkey = l1.
(5 rows)SYS モニタリングビュー SYS_QUERY_HISTORY では、Amazon Redshift は次のようにクエリを記録します。
SELECT user_id, transaction_id, session_id, query_id, query_text::varchar(100)
FROM sys_query_history
WHERE transaction_id = 48237350
ORDER BY start_time;
user_id | transaction_id | session_id | query_id | query_text
---------+----------------+------------+----------+------------------------------------------------------------------------------------------------------
101 | 48237350 | 1073840810 | 12058149 | SELECT s_name , COUNT(*) AS numwait FROM supplier, lineitem l1, orders, nation WHERE s_suppkey = l1.SYS_QUERY_DETAIL では、SYS_QUERY_HISTORY の query_id 値を使用して子クエリレベルの詳細を検索できます。child_query_sequence 列には、子クエリの実行順序が表示されます。SYS_QUERY_DETAIL の列の詳細については、「SYS_QUERY_DETAIL」を参照してください。
select user_id,
query_id,
child_query_sequence,
stream_id,
segment_id,
step_id,
start_time,
end_time,
duration,
blocks_read,
blocks_write,
local_read_io,
remote_read_io,
data_skewness,
time_skewness,
is_active,
spilled_block_local_disk,
spilled_block_remote_disk
from sys_query_detail
where query_id = 12058149
and step_id = -1
order by query_id,
child_query_sequence,
stream_id,
segment_id,
step_id;
user_id | query_id | child_query_sequence | stream_id | segment_id | step_id | start_time | end_time | duration | blocks_read | blocks_write | local_read_io | remote_read_io | data_skewness | time_skewness | is_active | spilled_block_local_disk | spilled_block_remote_disk
---------+----------+----------------------+-----------+------------+---------+----------------------------+----------------------------+----------+-------------+--------------+---------------+----------------+---------------+---------------+-----------+--------------------------+---------------------------
101 | 12058149 | 1 | 0 | 0 | -1 | 2023-09-27 15:40:38.512415 | 2023-09-27 15:40:38.533333 | 20918 | 0 | 0 | 0 | 0 | 0 | 44 | f | 0 | 0
101 | 12058149 | 1 | 1 | 1 | -1 | 2023-09-27 15:40:39.931437 | 2023-09-27 15:40:39.972826 | 41389 | 12 | 0 | 12 | 0 | 0 | 77 | f | 0 | 0
101 | 12058149 | 1 | 2 | 2 | -1 | 2023-09-27 15:40:40.584412 | 2023-09-27 15:40:40.613982 | 29570 | 32 | 0 | 32 | 0 | 0 | 25 | f | 0 | 0
101 | 12058149 | 1 | 2 | 3 | -1 | 2023-09-27 15:40:40.582038 | 2023-09-27 15:40:40.615758 | 33720 | 0 | 0 | 0 | 0 | 0 | 1 | f | 0 | 0
101 | 12058149 | 1 | 3 | 4 | -1 | 2023-09-27 15:40:46.668766 | 2023-09-27 15:40:46.705456 | 36690 | 24 | 0 | 15 | 0 | 0 | 17 | f | 0 | 0
101 | 12058149 | 1 | 4 | 5 | -1 | 2023-09-27 15:40:46.707209 | 2023-09-27 15:40:46.709176 | 1967 | 0 | 0 | 0 | 0 | 0 | 18 | f | 0 | 0
101 | 12058149 | 1 | 4 | 6 | -1 | 2023-09-27 15:40:46.70656 | 2023-09-27 15:40:46.71289 | 6330 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 1 | 5 | 7 | -1 | 2023-09-27 15:40:46.71405 | 2023-09-27 15:40:46.714343 | 293 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 2 | 0 | 0 | -1 | 2023-09-27 15:40:52.083907 | 2023-09-27 15:40:52.087854 | 3947 | 0 | 0 | 0 | 0 | 0 | 35 | f | 0 | 0
101 | 12058149 | 2 | 1 | 1 | -1 | 2023-09-27 15:40:52.089632 | 2023-09-27 15:40:52.091129 | 1497 | 0 | 0 | 0 | 0 | 0 | 11 | f | 0 | 0
101 | 12058149 | 2 | 1 | 2 | -1 | 2023-09-27 15:40:52.089008 | 2023-09-27 15:40:52.091306 | 2298 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 3 | 0 | 0 | -1 | 2023-09-27 15:40:56.882013 | 2023-09-27 15:40:56.897282 | 15269 | 0 | 0 | 0 | 0 | 0 | 29 | f | 0 | 0
101 | 12058149 | 3 | 1 | 1 | -1 | 2023-09-27 15:40:59.718554 | 2023-09-27 15:40:59.722789 | 4235 | 0 | 0 | 0 | 0 | 0 | 13 | f | 0 | 0
101 | 12058149 | 3 | 2 | 2 | -1 | 2023-09-27 15:40:59.800382 | 2023-09-27 15:40:59.807388 | 7006 | 0 | 0 | 0 | 0 | 0 | 58 | f | 0 | 0
101 | 12058149 | 3 | 3 | 3 | -1 | 2023-09-27 15:41:06.488685 | 2023-09-27 15:41:06.493825 | 5140 | 0 | 0 | 0 | 0 | 0 | 56 | f | 0 | 0
101 | 12058149 | 3 | 3 | 4 | -1 | 2023-09-27 15:41:06.486206 | 2023-09-27 15:41:06.497756 | 11550 | 0 | 0 | 0 | 0 | 0 | 2 | f | 0 | 0
101 | 12058149 | 3 | 4 | 5 | -1 | 2023-09-27 15:41:06.499201 | 2023-09-27 15:41:06.500851 | 1650 | 0 | 0 | 0 | 0 | 0 | 15 | f | 0 | 0
101 | 12058149 | 3 | 4 | 6 | -1 | 2023-09-27 15:41:06.498609 | 2023-09-27 15:41:06.500949 | 2340 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 3 | 5 | 7 | -1 | 2023-09-27 15:41:06.502945 | 2023-09-27 15:41:06.503282 | 337 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 4 | 0 | 0 | -1 | 2023-09-27 15:41:06.62899 | 2023-09-27 15:41:06.631452 | 2462 | 0 | 0 | 0 | 0 | 0 | 22 | f | 0 | 0
101 | 12058149 | 4 | 1 | 1 | -1 | 2023-09-27 15:41:06.632313 | 2023-09-27 15:41:06.63391 | 1597 | 0 | 0 | 0 | 0 | 0 | 20 | f | 0 | 0
101 | 12058149 | 4 | 1 | 2 | -1 | 2023-09-27 15:41:06.631726 | 2023-09-27 15:41:06.633813 | 2087 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
101 | 12058149 | 5 | 0 | 0 | -1 | 2023-09-27 15:41:12.571974 | 2023-09-27 15:41:12.584234 | 12260 | 0 | 0 | 0 | 0 | 0 | 39 | f | 0 | 0
101 | 12058149 | 5 | 0 | 1 | -1 | 2023-09-27 15:41:12.569815 | 2023-09-27 15:41:12.585391 | 15576 | 0 | 0 | 0 | 0 | 0 | 4 | f | 0 | 0
101 | 12058149 | 5 | 1 | 2 | -1 | 2023-09-27 15:41:13.758513 | 2023-09-27 15:41:13.76401 | 5497 | 0 | 0 | 0 | 0 | 0 | 39 | f | 0 | 0
101 | 12058149 | 5 | 1 | 3 | -1 | 2023-09-27 15:41:13.749 | 2023-09-27 15:41:13.772987 | 23987 | 0 | 0 | 0 | 0 | 0 | 32 | f | 0 | 0
101 | 12058149 | 5 | 2 | 4 | -1 | 2023-09-27 15:41:13.799526 | 2023-09-27 15:41:13.813506 | 13980 | 0 | 0 | 0 | 0 | 0 | 62 | f | 0 | 0
101 | 12058149 | 5 | 2 | 5 | -1 | 2023-09-27 15:41:13.798823 | 2023-09-27 15:41:13.813651 | 14828 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0
(28 rows)システムテーブルクエリ、プロセス、セッション ID
システムテーブルに表示されるクエリ ID、プロセス ID、セッション ID を分析するときは、以下の点に注意してください。
- クエリ ID の値 (
query_idやqueryなどの列内) は、長期に再利用できます。 - プロセス ID またはセッション ID の値 (
process_id、pid、session_idなどの列内) は、長期に再利用できます。 - トランザクション ID の値 (
transaction_idやxidなどの列内) は一意です。