Collection of Redshift Utility Queries

redshift_copy.sql

-- Identify how many S3 objects each COPY command loads
SELECT
    query, COUNT(*) num_files,
    ROUND(MAX(wq.total_exec_time/1000000.0),2) execution_secs,
    ROUND(SUM(transfer_size)/(1024.0*1024.0),2) total_mb,
    SUBSTRING(querytxt,1,60) copy_sql
FROM stl_s3client s
JOIN stl_query q USING (query)
JOIN stl_wlm_query wq USING (query)
WHERE s.userid>1 AND http_method = 'GET'
    AND POSITION('COPY ANALYZE' IN querytxt) = 0
    AND aborted = 0 AND final_state='Completed'
GROUP BY query, querytxt
HAVING (SUM(transfer_size)/(1024*1024))/COUNT(*) >= 2
ORDER BY CASE
WHEN COUNT(*) < (SELECT max(node)+1 FROM stv_slices) THEN 1
WHEN COUNT(*) < (SELECT COUNT(*) FROM stv_slices WHERE node=0) THEN 2
ELSE 2+((COUNT(*) % (SELECT COUNT(*) FROM stv_slices))/(SELECT COUNT(*)::DECIMAL FROM stv_slices))
END, (SUM(transfer_size)/(1024.0*1024.0))/COUNT(*) DESC;

redshift_permissions.sql

-- QUERY TABLE OWNER
SELECT
  *
FROM (
  SELECT
    n.nspname AS schema_name,
    pg_get_userbyid(c.relowner) AS table_owner,
    c.relname AS table_name,
    CASE WHEN c.relkind = 'v' THEN 'view' ELSE 'table' END AS table_type,
    d.description AS table_description
  FROM
    pg_class AS c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
    LEFT JOIN pg_description AS d ON (
      d.objoid = c.oid
      AND d.objsubid = 0
    )
  WHERE
    c.relkind IN ('r', 'v')
-- ORDER BY n.nspname, c.relname
)
WHERE
  -- target schema name
  schema_name = 'public'
ORDER BY
  schema_name, table_owner
;

-- QUERY TABLE PERMISSIONS
SELECT
  *
FROM (
  SELECT
    schemaname,
    objectname,
    usename AS username,
    HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select')
    AND has_schema_privilege(usrs.usename, schemaname, 'usage') AS sel,
    HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert')
    AND has_schema_privilege(usrs.usename, schemaname, 'usage') AS ins,
    HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update')
    AND has_schema_privilege(usrs.usename, schemaname, 'usage') AS upd,
    HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete')
    AND has_schema_privilege(usrs.usename, schemaname, 'usage') AS del,
    HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references')
    AND has_schema_privilege(usrs.usename, schemaname, 'usage') AS ref
  FROM (
    SELECT
      schemaname, 't' AS obj_type,
      tablename AS objectname,
      schemaname + '.' + tablename AS fullobj
    FROM pg_tables
    WHERE schemaname NOT IN ('pg_internal')
    UNION
    SELECT
      schemaname, 'v' AS obj_type,
      viewname AS objectname,
      schemaname + '.' + viewname AS fullobj
    FROM pg_views
    WHERE schemaname NOT IN ('pg_internal')
  ) AS objs, (
    SELECT * FROM pg_user
  ) AS usrs
  ORDER BY
    fullobj
)
WHERE
  (sel = true OR ins = true OR upd = true OR del = true OR ref = true)
  -- target schema name
  AND schemaname = 'public'
  -- target user name
  AND username = 'analyst'
;

-- QUERY SCHEMA PERMISSIONS
SELECT
  *
FROM (
  SELECT
    schemaname,
    usename AS username,
    HAS_SCHEMA_PRIVILEGE(usrs.usename, schemaname, 'usage') AS usg,
    HAS_SCHEMA_PRIVILEGE(usrs.usename, schemaname, 'create') AS crt
  FROM (
    SELECT DISTINCT(schemaname)
    FROM pg_tables
    WHERE schemaname NOT IN ('pg_internal')
    UNION
    SELECT DISTINCT(schemaname)
    FROM pg_views
    WHERE schemaname NOT IN ('pg_internal')
  ) AS objs, (
    SELECT * FROM pg_user
  ) AS usrs
  ORDER BY
    schemaname
)
WHERE
  (usg = true OR crt = true)
  -- target schema name
  AND schemaname = 'public'
  -- target user name
  AND username = 'analyst'
;

redshift_table_size.sql

-- QUERY TABLE SIZE
SELECT 
  schema        as table_schema,
  "table"       as table_name,
  size / 1024.0 as used_gb,
  tbl_rows      as table_rows
FROM svv_table_info
ORDER BY size DESC;