-
-
Save dsaiztc/7a10912c4959c8782c1c94a3de5aac74 to your computer and use it in GitHub Desktop.
Low level Redshift cheat sheet
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Table information like sortkeys, unsorted percentage | |
| -- see http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_TABLE_INFO.html | |
| SELECT * FROM svv_table_info; | |
| -- Table sizes in GB | |
| SELECT t.name, COUNT(tbl) / 1000.0 AS gb | |
| FROM ( | |
| SELECT DISTINCT datname, id, name | |
| FROM stv_tbl_perm | |
| JOIN pg_database ON pg_database.oid = db_id | |
| ) AS t | |
| JOIN stv_blocklist ON tbl = t.id | |
| GROUP BY t.name ORDER BY gb DESC; | |
| -- Table column metadata | |
| SELECT * FROM pg_table_def | |
| WHERE schemaname = 'public' | |
| AND tablename = …; | |
| -- Vacuum progress | |
| -- see http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_VACUUM_PROGRESS.html | |
| SELECT * FROM svv_vacuum_progress; | |
| -- The size in MB of each column of each table (actually the number of blocks, but blocks are 1 MB) | |
| -- see http://stackoverflow.com/questions/33388587/how-can-i-find-out-the-size-of-each-column-in-a-redshift-table | |
| SELECT | |
| TRIM(name) as table_name, | |
| TRIM(pg_attribute.attname) AS column_name, | |
| COUNT(1) AS size | |
| FROM | |
| svv_diskusage JOIN pg_attribute ON | |
| svv_diskusage.col = pg_attribute.attnum-1 AND | |
| svv_diskusage.tbl = pg_attribute.attrelid | |
| GROUP BY 1, 2 | |
| ORDER BY 1, 2; | |
| -- List users and groups | |
| SELECT * FROM pg_user; | |
| SELECT * FROM pg_group; | |
| -- List all databases | |
| SELECT * FROM pg_database; | |
| -- List the 100 last load errors | |
| -- see http://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOAD_ERRORS.html | |
| SELECT * | |
| FROM stl_load_errors | |
| ORDER BY starttime DESC | |
| LIMIT 100; | |
| -- Convert a millisecond resolution number to a TIMESTAMP | |
| SELECT TIMESTAMP 'epoch' + (timestamp/1000 * INTERVAL '1 second') FROM foo; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment