Useful AWS Redshift Queries

RedshiftQueries.sql

/* show running queries */
select pid, user_name, starttime, query
from stv_recents
where lower(status) = 'running';

/* show recent completed queries */
select pid, user_name, starttime, query
from stv_recents
where lower(status) = 'done'
order by starttime desc;

/* table rows by schema */
select
    trim(pgdb.datname) as Database,
    trim(pgn.nspname) as Schema,
    trim(a.name) as Table,
    b.mbytes,
    a.rows
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
where trim(pgn.nspname) = 'sat'
order by  mbytes desc, a.db_id, a.name; 

/* view dependencies for a table and/or schema */
SELECT *
FROM vault_xero.dvs.vwdependencies 
where schemaname = 'tempstage'

/* view table columns and datatypes */
select distinct attrelid, rtrim(name), attname, typname
from pg_attribute a, pg_type t, stv_tbl_perm p
where t.oid=a.atttypid and a.attrelid=p.id
and a.attrelid between 100100 and 110000
and typname not in('oid','xid','tid','cid')
order by a.attrelid asc, typname, attname;

/* view vacuum progress/summary */
select * from svv_vacuum_progress;
select * from svv_vacuum_summary;

/* view sort key(s) for table */
select * from svv_table_info;

/* Use the SET command to set the value of wlm_query_slot_count for the duration of the current session. */
set wlm_query_slot_count to 3;