Redshift PSQL Commands

Source: http://docs.aws.amazon.com/redshift/latest/dg/c_SQL_commands.html

List users

\du

 

List namespaces (or schema)

\dn 

 

List tables

\dt 

 

Describe table TABLENAME

\d TABLENAME 

 

Change your current schema to the SCHEMA

set search_path=’SCHEMA’;  

 

Show last 10 load errors in Redshift

select * from stl_load_errors order by starttime desc limit 10;

 

Show all queries from June 16th, 2009

select starttime, type, rtrim(text) from svl_statpreenttext

where starttime like '2009-06-16%' and type='DDL' order by starttime asc;

Source: http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_STATpreENTTEXT.html

 

Show all running queries

select pid, user_name, starttime, query

from stv_recents
where status='Running';
 

 

View list of recent queries

select query, pid, elapsed, substring from svl_qlog

where userid = 100
order by starttime desc
limit 5;
 

 

View list of all current sessions on Redshift

select *

from stv_sessions;
 

 

Show extended column information for table ‘sometable’ (ie. displays distkey/sortkey)

select * from pg_table_def where tablename=’sometable’; 

 

Show free space of the Redshift cluster

select sum(capacity)/1024 as capacity_gbytes,

sum(used)/1024 as used_gbytes,
(sum(capacity) - sum(used))/1024 as free_gbytes
from stv_partitions where part_begin=0;
  

 

Show # of rows and diskspace order by tablename

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
order by mbytes desc, a.db_id, a.name;
 

 

List 100 queries that have very slow query execution times in the past 24 hours

select row_number() over(order by total_exec_time desc) as rownum, ql.query, ql.substring as querytxt,

  wq.total_exec_time / 1000000 as exec_time_sec, queue_start_time
from stl_wlm_query wq
left join svl_qlog ql on ql.query = wq.query
where queue_start_time >= sysdate - interval '24 hours'
order by total_exec_time desc limit 100;
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk