Category : Redshift snippets


Step 1: There were two nasty queries active that wouldn’t cancel/terminate: pid | seconds | user | querytxt ——-+———+—————+—————————————————————————————————— 9315 | 22223 | rsrootuser | /*MTLN-1.37.4 (build 264)*/ + | | | DROP TABLE IF EXISTS “analysis_ready”.”airdna_airbnb_properties” CASCADE 23925 | 14125 | rsrootuser | /*MTLN-1.37.4 (build 264)*/ + | | | select + | | ..

Read more


SELECT u.usename, s.schemaname, has_schema_privilege (u.usename, s.schemaname, ‘create’) AS user_has_select_permission, has_schema_privilege (u.usename, s.schemaname, ‘usage’) AS user_has_usage_permission FROM pg_user u CROSS JOIN (SELECT DISTINCT schemaname FROM pg_tables) s WHERE u.usename = ‘tylerbullen’ AND s.schemaname = ‘vacasa’; SELECT u.usename, t.schemaname || ‘.’ || t.tablename, has_table_privilege (u.usename, t.tablename, ‘select’) AS user_has_select_permission, has_table_privilege (u.usename, t.tablename, ‘insert’) AS user_has_insert_permission, has_table_privilege (u.usename, ..

Read more


SELECT * FROM stl_scan ss JOIN pg_user pu ON ss.userid = pu.usesysid JOIN svl_query_metrics_summary sqms ON ss.query = sqms.query JOIN temp_mone_tables tmt ON tmt.table_id = ss.tbl AND tmt.table = ss.perm_table_name; SELECT perm_table_name, SUM(ROWS), SUM(bytes) SUM(fetches) FROM stl_scan WHERE starttime >= ‘2018-09-01 00:00:00’ GROUP BY perm_table_name ORDER BY SUM(bytes) DESC..

Read more


Save the SQL part as dump_rs_grants.sql, then run this bash bit: cd /usr/local/cron/dump_rs_grants PGPASSWORD=xxxxxxxxx psql -h redshiftFQDN -p 5439 -Uxxxxxx -dyyyyyy < dump_rs_grants.sql > current_rs_grants.txt dump_rs_grants.sql: WITH object_list(schema_name,object_name,permission_info) AS ( SELECT N.nspname, C.relname, array_to_string(relacl,’,’) FROM pg_class AS C INNER JOIN pg_namespace AS N ON C.relnamespace = N.oid WHERE C.relkind in (‘v’,’r’) AND N.nspname NOT IN ..

Read more


#SCHEMA=”${2}” STARTIME=`date` echo “Starting rowcount update at $STARTIME” # fivetran database DBASE=”fivetran” SCHEMALIST=”greenhouse hubspot jira mandrill marketo” # Truncate rollup table echo “Deleting from rollup table for ${DBASE} schema…” PGPASSWORD=xxxxxxxxxx psql -t -h warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c “TRUNCATE TABLE admin.overall_rowcount;” # Iterate through schemata for SCHEMA in ${SCHEMALIST} do TABLES=`PGPASSWORD=xxxxxxxxxx psql ..

Read more


#!/bin/bash ################################################################################# # findlockblocks.sh # # Dead-stupid script that leverages existing RS queries and does a mashup that reports # the current running queries that are blocking others, sorted by time running. # # Nice, simple way to see if there’s actually a problem or if RS is just swamped. # # v1.01 2019-06-14 rlbyrd ..

Read more


#!/bin/bash ################################################################################# # do_analyze_by_batch.sh # # Using this query as the source… # # SELECT database, schema || ‘.’ || “table” AS “table”, stats_off # FROM svv_table_info # WHERE stats_off > 5 # ORDER BY 3 DESC, 2; # # …which displays all tables and their stats_off percentage in descending order. # # # Then ..

Read more


— Table level permissions SELECT * FROM ( SELECT schemaname ,objectname ,usename ,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’) ..

Read more


select relacl , ‘grant ‘ || substring( case when charindex(‘r’,split_part(split_part(array_to_string(relacl, ‘|’),pu.groname,2 ) ,’/’,1)) > 0 then ‘,select ‘ else ” end ||case when charindex(‘w’,split_part(split_part(array_to_string(relacl, ‘|’),pu.groname,2 ) ,’/’,1)) > 0 then ‘,update ‘ else ” end ||case when charindex(‘a’,split_part(split_part(array_to_string(relacl, ‘|’),pu.groname,2 ) ,’/’,1)) > 0 then ‘,insert ‘ else ” end ||case when charindex(‘d’,split_part(split_part(array_to_string(relacl, ‘|’),pu.groname,2 ) ,’/’,1)) ..

Read more


It’s columnar, donchaknow. drop table if exists example.reservation_finance_item_dedupe; create table example.reservation_finance_item_dedupe as (select distinct * from example.reservation_finance_item); select count(*) from example.reservation_finance_item; select count(*) from example.reservation_finance_item_dedupe; drop table if exists example.reservation_finance_item_old; set search_path=example; alter table reservation_finance_item rename to reservation_finance_item_old; alter table reservation_finance_item_dedupe rename to reservation_finance_item; select count(*) from example.reservation_finance_item; select count(*) from example.reservation_finance..

Read more


Using the AWS console. Login to the AWS Management Console. Navigate to Redshift dashboard at https://console.aws.amazon.com/redshift/. In the left navigation panel, under Redshift Dashboard, click Clusters. Click Launch Cluster button from the dashboard top menu to start the cluster setup process. On the Cluster Details configuration page, enter a unique name for your new cluster ..

Read more