Amazon Redshiftで良く使いそうな便利系SQLをまとめてみた

Amazon Redshiftで良く使いそうな便利系SQL

#SQL

#Amazon Redshift

#AWS

しんや

2014.04.30

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

Redshiftで色々環境構築や調査を進めて行くと、割とちょいちょい良く使うSQL等も出て来ます。そこでこのエントリでは、普段使っている便利系SQL、都度アクセスしてはコピペして使ってるようなSQL、更にはそれらにちょっと一手間加えたSQL等を集約し一覧としてみる事にしました。

必須なもの、また『これも使えるね』というようなものについては適宜追加更新を行っていこうと思ってますので、オススメのSQL文があれば是非教えて頂けると幸いです。

S3からのCOPY処理エラーに関するログを確認する
COPY処理時に出力させるエラー件数量を制御する
指定テーブルのテーブル定義を確認する(type1:psqlコマンドで簡易表示)
指定テーブルのテーブル定義を確認する(type2:distkey,sortkey等も表示)
指定テーブルのテーブル定義を確認する(type3:コメント文も併せて表示)
テーブル一覧を表示する
実行中クエリのプロセスIDを調べる
実行中のクエリをキャンセルする
テーブル分析結果を確認するその1
テーブル分析結果を確認するその2
現在のクラスタ構成に於ける利用ストレージ総容量を確認する
実行中カーソルの結果セットサイズ(ResultSetSize)を確認する
現在登録されているユーザーの一覧を表示する
テーブルに対する権限の確認を行う
スーパーユーザーを作成する
パラメータグループの内容を表示する
クエリステートメントの実行計画を表示する(EXPLAIN)
クエリプランナーで使用するテーブル統計を更新する(ANALYZE)
分析対象テーブルの推奨列エンコード付きレポートを作成する(ANALYZE COMPRESSION)
テーブルに設定されている分散形式(diststyle)を参照する

S3からのCOPY処理エラーに関するログを確認する

STL_LOAD_ERRORS - Amazon Redshift(日本語)
STL_LOAD_ERRORS - Amazon Redshift(英語)

まずはRedshiftに関する作業画取っ掛かりとして取り組む事になるであろう、S3からのデータCOPY時に発生するエラーに関するログテーブル照会クエリです。末尾空白で不要な表示スペースを取ってしまってるのでTRIM関数でその辺除去しました。直近のデータ内容だけ分かれば良いので件数も絞ってます。また、エラー調査の際は併せて以下ページもご参考に。

ロードエラー参照 - Amazon Redshift(日本語)
Load error reference - Amazon Redshift(英語)

SELECT
    TRIM(userid) AS userid,
    TRIM(slice) AS slice,
    TRIM(tbl) AS tbl,
    TRIM(starttime) AS starttime,
    TRIM(session) AS session, 
    TRIM(query) AS query,
    TRIM(filename) AS filename,
    TRIM(line_number) AS line_number,
    TRIM(colname) AS colname,
    TRIM(type) AS type,
    TRIM(col_length) AS col_length,
    TRIM(position) AS position,
    TRIM(raw_line) AS raw_line,
    TRIM(raw_field_value) AS raw_field_value,
    TRIM(err_code) AS err_code,
    TRIM(err_reason) AS err_reason
FROM
  STL_LOAD_ERRORS
ORDER BY starttime DESC
LIMIT 20;

COPY処理時に出力させるエラー件数量を制御する

上記でも言及したCOPY処理時エラーに関連する内容です。デフォルトではCOPY処理を行った際に発生し、STL_LOAD_ERRORSテーブルに記録されるレコードは直近(？)の1件のみです。これが仮に100万件あるデータを投入し、その中に1000件の何らかのエラーが含まれるとしたらどうしましょう。COPYを試してはエラー→ファイルを修正→S3に再アップ→再実行...のサイクルを都合1000回行わないといけない事になってしまいますね。この例えは極端な例かも知れませんが、この辺りの作業は積み重なると結構無視出来ない労力の積み重ねとなる危険性があります。

そこで！COPYコマンドにはMAXERRORオプションが用意されています。これを使う事でこの問題が一気に解決します。お客様から受領したファイルに関するエラーの件数が多い場合も、この設定値を利用して一気にエラー部分を出力し、一部加工するなどして『エラー箇所の一覧』として提出する事でコミュニケーションと手間の効率化も図る事が出来るでしょう。以下に関連エントリのリンク及び関連項目のテキストを記載しておきます。

COPY - Amazon Redshift

MAXERROR [AS] error_count
ロードのエラー数が error_count 以上である場合、ロードは失敗します。
ロードのエラーがそれより少ない場合、処理は続行され、ロードできなかった行数を示す
 INFO メッセージが返されます。データの形式エラーやその他の不整合のために
 一部の行をテーブルにロードできないときにロードを継続できるようにするには、
 このオプションを使用します。最初のエラーが発生したときにロードを失敗させる場合、
 この値を 0 または 1 に設定します。AS キーワードはオプションです。

Amazon Redshift の並列処理のため、報告される実際のエラー数が指定された MAXERROR より
大きくなることがあります。Amazon Redshift クラスターのノードで MAXERROR を超えたことが
検出された場合、各ノードは発生したすべてのエラーを報告します。

指定テーブルのテーブル定義を確認する(type1:psqlコマンドで簡易表示)

psqlのメタコマンド(\d)を使って、シンプルなテーブル定義情報を表示する事が出来ます。\dのみでテーブルの一覧、\d (テーブル名)で指定テーブルの定義を表示します。ただこのコマンドの場合、圧縮コード等の情報は表示されません。

# \d medals;
                         テーブル "public.medals"
          列           |             型              |       修飾語       
-----------------------+-----------------------------+--------------------
 id                    | integer                     | not null
 athelete              | character varying(100)      | not null
 age                   | smallint                    | not null
 gender                | character(1)                | not null
 olympic_year          | smallint                    | 
 closing_celemony_date | date                        | 
 sport                 | character varying(100)      | not null
 gold_medals           | smallint                    | not null default 0
 silver_medals         | integer                     | not null default 0
 bronze_medals         | smallint                    | not null default 0
 total_medals          | bigint                      | not null default 0
 item_decimal          | numeric(12,3)               | 
 item_real             | real                        | 
 item_double           | double precision            | 
 item_timestamp        | timestamp without time zone | 
 item_boolean          | boolean                     | 
インデックス:
    "medals_pkey" PRIMARY KEY, btree (id)

指定テーブルのテーブル定義を確認する(type2:distkey,sortkey等も表示)

圧縮コードや分散キー等、Redshift特有の情報を踏まえた定義を表示するSQLがこちら。Redshift用にちゃんと用意されてました。

PG_TABLE_DEF - Amazon Redshift(日本語)
PG_TABLE_DEF - Amazon Redshift(英語)

こんな感じの定義で作成すると、

DROP TABLE medals;
CREATE TABLE medals (
  id       INT NOT NULL encode delta,
  athelete VARCHAR(100) NOT NULL encode text255,
  age      SMALLINT NOT NULL encode delta,
  gender   CHAR(1) NOT NULL encode bytedict,
  olympic_year SMALLINT encode delta,
  closing_celemony_date DATE encode delta,
  sport    VARCHAR(100) NOT NULL encode text255,
  gold_medals SMALLINT NOT NULL encode delta default 0,
  silver_medals SMALLINT NOT NULL encode delta default 0,
  bronze_medals SMALLINT NOT NULL encode delta default 0,
  total_medals SMALLINT NOT NULL encode delta default 0,
  PRIMARY KEY(id)
)
  distkey(olympic_year)
  sortkey(closing_celemony_date, gold_medals, silver_medals, bronze_medals);

このような内容で情報を取得する事が出来ます。

# SELECT * FROM pg_table_def WHERE schemaname = 'public' AND tablename = 'medals';
 schemaname | tablename |        column         |          type          | encoding | distkey | sortkey | notnull 
------------+-----------+-----------------------+------------------------+----------+---------+---------+---------
 public     | medals    | id                    | integer                | delta    | f       |       0 | t
 public     | medals    | athelete              | character varying(100) | text255  | f       |       0 | t
 public     | medals    | age                   | smallint               | delta    | f       |       0 | t
 public     | medals    | gender                | character(1)           | bytedict | f       |       0 | t
 public     | medals    | olympic_year          | smallint               | delta    | t       |       0 | f
 public     | medals    | closing_celemony_date | date                   | delta    | f       |       1 | f
 public     | medals    | sport                 | character varying(100) | text255  | f       |       0 | t
 public     | medals    | gold_medals           | smallint               | delta    | f       |       2 | t
 public     | medals    | silver_medals         | smallint               | delta    | f       |       3 | t
 public     | medals    | bronze_medals         | smallint               | delta    | f       |       4 | t
 public     | medals    | total_medals          | smallint               | delta    | f       |       0 | t
(11 行)

指定テーブルのテーブル定義を確認する(type3:コメント文も併せて表示)

上記SQL文でも必要最低限の情報は取得可能です。でも...こんな感じでテーブル情報に関するコメントとか設定されてたら表示させたり、デフォルト値とか定義されてたら併せて出したいですよね〜。

COMMENT ON TABLE medals IS '五輪メダル数';
COMMENT ON COLUMN medals.id IS 'ID';
COMMENT ON COLUMN medals.athelete IS '選手名';
COMMENT ON COLUMN medals.age IS '年齢';
COMMENT ON COLUMN medals.gender IS '性別';
COMMENT ON COLUMN medals.olympic_year IS '五輪開催年';
COMMENT ON COLUMN medals.closing_celemony_date IS '閉会日';
COMMENT ON COLUMN medals.sport IS '種目';
COMMENT ON COLUMN medals.gold_medals IS '金メダル数';
COMMENT ON COLUMN medals.silver_medals IS '銀メダル数';
COMMENT ON COLUMN medals.bronze_medals IS '銅メダル数';
COMMENT ON COLUMN medals.total_medals IS '合計メダル数';

つう事で作ってみました。

SELECT
  pg_table_def.schemaname AS schema_name,
  pg_table_def.tablename AS table_name,
  def_comment.table_comment,

  pg_table_def.column,
  def_comment.column_comment,
  pg_table_def.type,
  pg_table_def.encoding,

  CASE pg_table_def.distkey
    WHEN true THEN 'yes'
    WHEN false THEN ''
  END AS is_distkey,

  CASE pg_table_def.sortkey
    WHEN 0 THEN ''
    ELSE 'yes'
  END AS is_sortkey,
  CASE pg_table_def.sortkey
    WHEN 0 THEN NULL
    ELSE pg_table_def.sortkey
  END AS sortkey,

  CASE pg_table_def.notnull
    WHEN true THEN 'NOT NULL'
    WHEN false THEN ''
  END AS is_not_null,
  def_comment.default

FROM
  pg_table_def,
  (SELECT
    pg_stat_user_tables.schemaname AS schema,
    pg_stat_user_tables.relname AS tablename,
    tablecom.description as table_comment,
    pg_attribute.attname AS columnname,
    colcom.description AS column_comment,
    pg_attribute.attnum,
    pg_attrdef.adsrc as default
  FROM
    pg_stat_user_tables,
    pg_type,
    pg_attribute
    LEFT JOIN pg_description tablecom ON pg_attribute.attrelid = tablecom.objoid AND tablecom.objsubid = 0
    LEFT JOIN pg_description colcom   ON pg_attribute.attnum = colcom.objsubid AND pg_attribute.attrelid = colcom.objoid
    LEFT JOIN pg_attrdef              ON pg_attribute.attrelid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum
  WHERE
    pg_attribute.attrelid = pg_stat_user_tables.relid
    AND pg_attribute.atttypid = pg_type.oid
    AND pg_attribute.attnum > 0
    AND pg_stat_user_tables.relname IN (
      SELECT
        pg_class.relname
      FROM
        pg_namespace  JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
      WHERE
        pg_class.relkind = 'r'
        AND pg_class.relname NOT LIKE 'pg_%'
        AND pg_class.relname NOT LIKE 'sql_%'  )
    AND pg_stat_user_tables.schemaname = 'public'      /** スキーマ名 */
    AND pg_stat_user_tables.relname = 'medals'  /** テーブル名 */
  ORDER BY
    pg_stat_user_tables.schemaname,
    pg_stat_user_tables.relname,
    pg_attribute.attnum
    ) def_comment
WHERE
      pg_table_def.tablename = def_comment.tablename
  AND pg_table_def.column    = def_comment.columnname
ORDER BY
  def_comment.schema,
  def_comment.tablename,
  def_comment.attnum;

こういう感じに情報が取得出来ます。

 schema_name | table_name | table_comment |        column         | column_comment |          type          | encoding | is_distkey | is_sortkey | sortkey | is_not_null | default
-------------+------------+---------------+-----------------------+----------------+------------------------+----------+------------+------------+---------+-------------+--------
 public      | medals     | 五輪メダル数  | id                    | ID             | integer                | delta    |            |            |         | NOT NULL    | 
 public      | medals     | 五輪メダル数  | athelete              | 選手名         | character varying(100) | text255  |            |            |         | NOT NULL    | 
 public      | medals     | 五輪メダル数  | age                   | 年齢           | smallint               | delta    |            |            |         | NOT NULL    | 
 public      | medals     | 五輪メダル数  | gender                | 性別           | character(1)           | bytedict |            |            |         | NOT NULL    | 
 public      | medals     | 五輪メダル数  | olympic_year          | 五輪開催年     | smallint               | delta    | yes        |            |         |             | 
 public      | medals     | 五輪メダル数  | closing_celemony_date | 閉会日         | date                   | delta    |            | yes        |       1 |             | 
 public      | medals     | 五輪メダル数  | sport                 | 種目           | character varying(100) | text255  |            |            |         | NOT NULL    | 
 public      | medals     | 五輪メダル数  | gold_medals           | 金メダル数     | smallint               | delta    |            | yes        |       2 | NOT NULL    | 0
 public      | medals     | 五輪メダル数  | silver_medals         | 銀メダル数     | smallint               | delta    |            | yes        |       3 | NOT NULL    | 0
 public      | medals     | 五輪メダル数  | bronze_medals         | 銅メダル数     | smallint               | delta    |            | yes        |       4 | NOT NULL    | 0
 public      | medals     | 五輪メダル数  | total_medals          | 合計メダル数   | smallint               | delta    |            |            |         | NOT NULL    | 0
(11 行)

テーブル一覧を表示する

現在作成されているテーブルの一覧を表示します。単純にテーブル名の一覧とした場合プライマリキーの情報も抽出されるのでWHERE句で省いてます。テーブル毎の詳細内容は以下テーブルより。

PG_TABLE_DEF - Amazon Redshift

SELECT DISTINCT pg_table_def.tablename
FROM pg_table_def
WHERE schemaname = 'public' AND tablename NOT LIKE'%_pkey'
ORDER BY tablename;

実行中クエリのプロセスIDを調べる

実行中のクエリのプロセス ID を調べる - Amazon Redshift

何らかの要因で実行時間が長めに掛かるようなクエリを実行してしまった場合、その実行をキャンセルする為に時折使ったりします。

select pid, user_name, starttime, query
from stv_recents
where status='Running';

実行中のクエリをキャンセルする

ステップ 6: クエリをキャンセルする - Amazon Redshift(日本語)
Step 6: Cancel a query - Amazon Redshift(英語)

上記で調べたプロセスID(pid)に相当するクエリをキャンセルします。

cancel (キャンセルしたいクエリのプロセスID);

テーブル分析結果を確認するその1

テーブル設計の分析 - Amazon Redshift(日本語)
Analyzing table design - Amazon Redshift(英語)

こちらのSQLはテーブルの分散キー(distkey)や項目の圧縮タイプ(encode)を変えた際の状況確認に使います。上記にありますように公式ドキュメントでも展開されているものです。

DROP TABLE temp_staging_tables_1;
DROP TABLE temp_staging_tables_2;
DROP TABLE temp_tables_report;

CREATE TEMP TABLE temp_staging_tables_1
                 (schemaname TEXT,
                  tablename TEXT,
                  tableid BIGINT,
                  size_in_megabytes BIGINT);

INSERT INTO temp_staging_tables_1
SELECT n.nspname, c.relname, c.oid, 
      (SELECT COUNT(*) FROM STV_BLOCKLIST b WHERE b.tbl = c.oid)
FROM pg_namespace n, pg_class c
WHERE n.oid = c.relnamespace 
  AND nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
  AND c.relname <> 'temp_staging_tables_1';

CREATE TEMP TABLE temp_staging_tables_2
                 (tableid BIGINT,
                  min_blocks_per_slice BIGINT,
                  max_blocks_per_slice BIGINT,
                  slice_count BIGINT);

INSERT INTO temp_staging_tables_2
SELECT tableid, MIN(c), MAX(c), COUNT(DISTINCT slice)
FROM (SELECT t.tableid, slice, COUNT(*) AS c
      FROM temp_staging_tables_1 t, STV_BLOCKLIST b
      WHERE t.tableid = b.tbl
      GROUP BY t.tableid, slice)
GROUP BY tableid;

CREATE TEMP TABLE temp_tables_report
                 (schemaname TEXT,
                 tablename TEXT,
                 tableid BIGINT,
                 size_in_mb BIGINT,
                 has_dist_key INT,
                 has_sort_key INT,
                 has_col_encoding INT,
                 pct_skew_across_slices FLOAT,
                 pct_slices_populated FLOAT);

INSERT INTO temp_tables_report
SELECT t1.*,
       CASE WHEN EXISTS (SELECT *
                         FROM pg_attribute a
                         WHERE t1.tableid = a.attrelid
                           AND a.attnum > 0
                           AND NOT a.attisdropped
                           AND a.attisdistkey = 't')
            THEN 1 ELSE 0 END,
       CASE WHEN EXISTS (SELECT *
                         FROM pg_attribute a
                         WHERE t1.tableid = a.attrelid
                           AND a.attnum > 0
                           AND NOT a.attisdropped
                           AND a.attsortkeyord > 0)
           THEN 1 ELSE 0 END,
      CASE WHEN EXISTS (SELECT *
                        FROM pg_attribute a
                        WHERE t1.tableid = a.attrelid
                          AND a.attnum > 0
                          AND NOT a.attisdropped
                          AND a.attencodingtype <> 0)
            THEN 1 ELSE 0 END,
      100 * CAST(t2.max_blocks_per_slice - t2.min_blocks_per_slice AS FLOAT)
            / CASE WHEN (t2.min_blocks_per_slice = 0) 
                   THEN 1 ELSE t2.min_blocks_per_slice END,
      CAST(100 * t2.slice_count AS FLOAT) / (SELECT COUNT(*) FROM STV_SLICES)
FROM temp_staging_tables_1 t1, temp_staging_tables_2 t2
WHERE t1.tableid = t2.tableid;

SELECT * FROM temp_tables_report ORDER BY schemaname, tablename;

テーブル分析結果を確認するその2

こちらはテーブル容量と件数を算出出来るもの。上記『その1』と併せて使いたいところです。

amazon web services - How to measure table space on disk in RedShift / ParAccel - Stack Overflow

『その1』もそうなんですが、こちらの『その2』も容量の単位がメガバイトなんですね。大量データを目の前にしていると、割とすぐにギガバイトの領域に入ってしまうもの。そんな状態でいちいちメガバイト数値をギガバイト換算するのも骨が折れる作業です。(というか目にキます(@_@;)) と言う訳でメガバイト(mbytes)表記の横にギガバイト(gbytes)表記、テラバイト(tbytes)表記も並べてみました。

更には、件数の表示について、3桁カンマ区切り、日本人に優しい4桁カンマ区切りの数値も併記してみました。世界的には3桁区切りが一般的ですが(且つ3桁に慣れとけよ、というのも勿論ありますが)、こっちの方がやはり数字はパッと入ってきます。

select
  trim(pgdb.datname) as Database,
  trim(pgn.nspname) as Schema,
  trim(a.name) as Table,
  b.mbytes,
  (CAST(b.mbytes as double precision) / 1024) as gbytes,
  (CAST(b.mbytes as double precision) / 1048576) as tbytes,
  a.rows,
  to_char(a.rows, '999,999,999,999,999') as rows_ww,
  to_char(a.rows, '9999,9999,9999,9999') as rows_jp
from (
       select db_id, id, name, sum(rows) as rows
       from stv_tbl_perm a
       group by db_id, id, name
     ) as a
  join pg_class as pgc on pgc.oid = a.id
  join pg_namespace as pgn on pgn.oid = pgc.relnamespace
  join pg_database as pgdb on pgdb.oid = a.db_id
  join (
         select tbl, count(*) as mbytes
         from stv_blocklist
         group by tbl
       ) b on a.id = b.tbl
order by mbytes desc, a.db_id, a.name;

注：テーブルの分散スタイルを『ALL』(DISTSTYLE ALL)にしているテーブルは、テーブル1つあたりの件数xノード数＝表示件数となります。

現在のクラスタ構成に於ける利用ストレージ総容量を確認する

上記SQLを応用したものです。っていうか結果を単純にサマっただけですけども。既存クラスタ構成のストレージ容量については、1ノード辺りのストレージ容量xノード数で計算出来るので、簡易的に『今どのくらい容量あるんだろう』ってのを確認するのに使えるかと思います。

select sum(gbytes) as total_storage_gbytes from (
  select
    trim(pgdb.datname) as Database,
    trim(pgn.nspname) as Schema,
    trim(a.name) as Table,
    b.mbytes,
    (CAST(b.mbytes as double precision) / 1024) as gbytes,
    (CAST(b.mbytes as double precision) / 1048576) as tbytes,
    a.rows,
    to_char(a.rows, '999,999,999,999,999') as rows_ww,
    to_char(a.rows, '9999,9999,9999,9999') as rows_jp
  from (
         select db_id, id, name, sum(rows) as rows
         from stv_tbl_perm a
         group by db_id, id, name
       ) as a
    join pg_class as pgc on pgc.oid = a.id
    join pg_namespace as pgn on pgn.oid = pgc.relnamespace
    join pg_database as pgdb on pgdb.oid = a.db_id
    join (
           select tbl, count(*) as mbytes
           from stv_blocklist
           group by tbl
         ) b on a.id = b.tbl
  order by mbytes desc, a.db_id, a.name
);

結果はこんな感じで出ます。ちなみにRedshiftのノードタイプに於ける1ノード当たりのストレージ容量は以下の通り。

料金表 - Amazon Redshift （ペタバイト級のデータウェアハウスサービス） | アマゾンウェブサービス（AWS 日本語）

ノードタイプ	1ノード当たりのストレージ容量
dw1.xlarge	2TB(2000GB)
dw1.8xlarge	16TB(16000GB)
dw2.large	0.16TB(160GB)
dw2.8xlarge	2.56TB(2560GB)

 total_storage_gbytes 
----------------------
         1234.567891
(1 行)

実行中カーソルの結果セットサイズ(ResultSetSize)を確認する

こちらは以下投稿済みエントリのクエリ。詳細はエントリ内容を確認して頂き、ここにはクエリのみ貼っときます。

Amazon Redshiftで扱う"カーソル"(CURSOR)の設定について

SELECT
  STV_ACTIVE_CURSORS.userid,
  STV_ACTIVE_CURSORS.name,
  STV_ACTIVE_CURSORS.pid,
  STV_ACTIVE_CURSORS.starttime as zstarttime,
  STV_ACTIVE_CURSORS.row_count,
  STV_ACTIVE_CURSORS.byte_count,
  (CAST(STV_ACTIVE_CURSORS.byte_count as double precision) / 1024) as kbyte_count,
  (CAST(STV_ACTIVE_CURSORS.byte_count as double precision) / 1048576) as mbyte_count,
  (CAST(STV_ACTIVE_CURSORS.byte_count as double precision) / 1073741824) as gbyte_count,
  STV_ACTIVE_CURSORS.fetched_rows,
  SVL_STATEMENTTEXT.userid,
  SVL_STATEMENTTEXT.xid,
  SVL_STATEMENTTEXT.pid,
  SVL_STATEMENTTEXT.label,
  SVL_STATEMENTTEXT.starttime,
  SVL_STATEMENTTEXT.endtime,
  SVL_STATEMENTTEXT.sequence,
  SVL_STATEMENTTEXT.type,
  SVL_STATEMENTTEXT.text
FROM
  SVL_STATEMENTTEXT
  LEFT OUTER JOIN STV_ACTIVE_CURSORS
    ON (
    SVL_STATEMENTTEXT.userid = STV_ACTIVE_CURSORS.userid
    AND SVL_STATEMENTTEXT.xid  = STV_ACTIVE_CURSORS.xid
    AND SVL_STATEMENTTEXT.pid  = STV_ACTIVE_CURSORS.pid
  )
WHERE
  text like'%SQL_CUR%'
  AND STV_ACTIVE_CURSORS.fetched_rows IS NOT NULL
ORDER BY
  starttime DESC;

現在登録されているユーザーの一覧を表示する

Users - Amazon Redshift

Redshiftクラスタに対して作成したユーザの確認を行う際に使います。BIツールなどで利用するDB接続ユーザを作成する、なんて局面もありますのでこの辺も何気に使うような気が。ちなみにシステムカタログテーブル周りはPostgreSQLに準拠するようです。

システムカタログテーブル - Amazon Redshift
- PG_TABLE_DEF - Amazon Redshift
- カタログテーブルへのクエリの実行 - Amazon Redshift

# select * from pg_user ORDER BY usesysid;
   usename   | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig 
-------------+----------+-------------+----------+-----------+----------+----------+-----------
 rdsdb       |        1 | t           | t        | t         | ******** |          | 
 root        |      100 | t           | t        | f         | ******** |          | 
 ：
 cmuser1     |      111 | f           | t        | f         | ******** |          | 
 cmuser2     |      112 | f           | t        | f         | ******** |          | 
 cmuser3     |      113 | f           | t        | f         | ******** |          |

テーブルに対する権限の確認を行う

作成したユーザーに各種権限付与(GRANT)・剥奪(REVOKE)を行った際に、テーブルに於けるその権限状態を確認する為に用います。

# \dp
または
# \z

上記どちらのコマンドでもRedshift上では表示されました。以下9.2文書からの抜粋です。

rolename=xxxx -- ロールに与えられた権限 =xxxx -- PUBLICに与えられた権限

r -- SELECT（読み取り（read）） w -- UPDATE（書き込み（write）） a -- INSERT（追加（append）） d -- DELETE D -- TRUNCATE x -- REFERENCES t -- TRIGGER X -- EXECUTE U -- USAGE C -- CREATE c -- CONNECT T -- TEMPORARY arwdDxt -- すべての権限 (テーブル用。他のオブジェクトでは異なります。) * -- 直前の権限に関するグラントオプション

/yyyy -- この権限を付与したロール上記の例では、mytableテーブルを作成し、次のコマンドを実行した後にmiriamユーザに表示されます。

GRANT SELECT ON mytable TO PUBLIC; GRANT SELECT, UPDATE, INSERT ON mytable TO admin; GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;

スーパーユーザーを作成する

スーパーユーザー - Amazon Redshift

基本的には新たなユーザーを作成する際、テーブルに於けるユーザーの権限を適切に設定する必要があります。それとは別に、スーパーユーザーでスーパーユーザーを作成する事も可能です。createuserオプションを付ける事でスーパーユーザー権限を持たせる事が出来ます。とても簡単ですが、当然の事ながら全ての処理が行える為使い方を慎重に見極める必要があります。その辺りは十分ご注意下さい。

create user adminuser createuser password '1234Admin'; alter user adminuser createuser;

パラメータグループの内容を表示する

SHOW - Amazon Redshift

パラメータグループに関する内容を確認する際に用います。SHOW パラメータ名でパラメータ個別、SHOW ALLで関連するパラメータグループを全て表示します。

# SHOW ALL;
         name         |    setting    
----------------------+---------------
 datestyle            | ISO, MDY
 extra_float_digits   | 0
 query_group          | default
 search_path          | $user, public
 statement_timeout    | 0
 wlm_query_slot_count | 1
(6 行)

# SHOW datestyle;
 datestyle 
-----------
 ISO, MDY
(1 行)

クエリステートメントの実行計画を表示する(EXPLAIN)

クエリを実行せずに、クエリステートメントの実行計画を表示。こちらはドキュメントのリンクを展開するに留めておきます。

クエリプランナーで使用するテーブル統計を更新する(ANALYZE)

クエリプランナーで使用するテーブル統計を更新します。これもリンクのみ。

分析対象テーブルの推奨列エンコード付きレポートを作成する(ANALYZE COMPRESSION)

圧縮分析を行い、分析されたテーブルについて推奨列エンコードスキーム付きのレポートを作成します。これもリンクのみ。ちなみに実行してみた結果あまり細かく(意図したような)エンコード値が出て来ないような気がしてるんだけど、気のせいかしら？

ANALYZE COMPRESSION - Amazon Redshift

テーブルに設定されている分散形式(diststyle)を参照する

テーブルに対する分散キー指定がKEY指定の場合、pg_table_defテーブルを参照する事でその対象としているキーを確認する事が出来ますが、KEY以外の場合、それがALLなのかEVENなのかの判断がつきません。

以下クエリはその内容を判別、取得する為のものとなります。

Viewing distribution styles - Amazon Redshift

SELECT
  relname,
  reldiststyle,
  CASE reldiststyle
    WHEN 0 THEN 'EVEN'
    WHEN 1 THEN 'KEY'
    WHEN 8 THEN 'ALL'
  END AS diststyle
FROM
  pg_class
WHERE
  relname LIKE 'xxx_xxx_xxxxx'; 
    relname    | reldiststyle | diststyle 
---------------+--------------+-----------
 xxx_xxx_xxxxx |            8 | ALL
(1 行)