Handy RedShift Queries

redshift.md

show tables

SELECT DISTINCT tablename
FROM pg_table_def
WHERE schemaname = 'public'
ORDER BY tablename;

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'schema_name'
ORDER BY table_name;

desc table

SELECT d.column, d.type
FROM pg_table_def d
WHERE tablename = 'table_name'
AND schemaname = 'public';

SELECT column_name, data_type, is_nullable, column_default, ordinal_position
FROM information_schema.columns
WHERE table_schema = 'schema_name' AND table_name = 'table_name';

Inspect Long Run(ning) Queries

select
  datediff(seconds, starttime, endtime) as time_taken_in_secs,
  querytxt,
  *
from stl_query
-- NOTE: Querying only those that were executed in the last hour
where starttime >= dateadd(hours, -1, getdate()) and endtime < getdate()
order by time_taken_in_secs desc;