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

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;

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

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

# \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)

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 行)

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 行)

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

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

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

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;

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;

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
);

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

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;

# 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         | ******** |          | 

# \dp
または
# \z

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;

# 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 行)

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 行)