Category: Amazon Redshift

Dump redshift grants

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 ('pg_catalog', 'pg_toast', 'information_schema')
    AND C.relacl[1] IS NOT NULL
  ),
  object_permissions(schema_name,object_name,permission_string)
  AS (
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',1) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',2) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',3) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',4) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',5) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',6) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',7) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',8) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',9) FROM object_list
    UNION ALL
    SELECT schema_name,object_name, SPLIT_PART(permission_info,',',10) FROM object_list
  ),
  permission_parts(schema_name, object_name,security_principal, permission_pattern)
  AS (
      SELECT
          schema_name,
          object_name,
          LEFT(permission_string ,CHARINDEX('=',permission_string)-1),
          SPLIT_PART(SPLIT_PART(permission_string,'=',2),'/',1)
      FROM object_permissions
      WHERE permission_string >''
  )
SELECT
    schema_name,
    object_name,
    'GRANT ' ||
    SUBSTRING(
        case when charindex('r',permission_pattern) > 0 then ',SELECT ' else '' end
      ||case when charindex('w',permission_pattern) > 0 then ',UPDATE ' else '' end
      ||case when charindex('a',permission_pattern) > 0 then ',INSERT ' else '' end
      ||case when charindex('d',permission_pattern) > 0 then ',DELETE ' else '' end
      ||case when charindex('R',permission_pattern) > 0 then ',RULE ' else '' end
      ||case when charindex('x',permission_pattern) > 0 then ',REFERENCES ' else '' end
      ||case when charindex('t',permission_pattern) > 0 then ',TRIGGER ' else '' end
      ||case when charindex('X',permission_pattern) > 0 then ',EXECUTE ' else '' end
      ||case when charindex('U',permission_pattern) > 0 then ',USAGE ' else '' end
      ||case when charindex('C',permission_pattern) > 0 then ',CREATE ' else '' end
      ||case when charindex('T',permission_pattern) > 0 then ',TEMPORARY ' else '' end
    ,2,10000
    )
    || ' ON ' || schema_name||'.'||object_name
     || ' TO ' || security_principal
     || ';' as grantsql
FROM permission_parts

;

Show Most Used Tables In Redshift

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
LIMIT 40;

View privileged objects in Redshift

SELECT
   namespace AS schemaname,
   item AS object,
   pu.groname AS groupname,
   DECODE(
      charindex (
         'r',
         split_part (
            split_part (
               array_to_string (relacl, '|'),
               pu.groname,
               2
            ),
            '/',
            1
         )
      ),
      0,
      0,
      1
   ) AS
   SELECT
      ,
      DECODE(
         charindex (
            'w',
            split_part (
               split_part (
                  array_to_string (relacl, '|'),
                  pu.groname,
                  2
               ),
               '/',
               1
            )
         ),
         0,
         0,
         1
      ) AS
      UPDATE
         ,
         DECODE(
            charindex (
               'a',
               split_part (
                  split_part (
                     array_to_string (relacl, '|'),
                     pu.groname,
                     2
                  ),
                  '/',
                  1
               )
            ),
            0,
            0,
            1
         ) AS
         INSERT,
         DECODE(
            charindex (
               'd',
               split_part (
                  split_part (
                     array_to_string (relacl, '|'),
                     pu.groname,
                     2
                  ),
                  '/',
                  1
               )
            ),
            0,
            0,
            1
         ) AS
         DELETE
         FROM
            (SELECT
               use.usename AS SUBJECT,
               nsp.nspname AS namespace,
               c.relname AS item,
               c.relkind AS TYPE,
               use2.usename AS OWNER,
               c.relacl
            FROM
               pg_user USE
               CROSS JOIN pg_class c
               LEFT JOIN pg_namespace nsp
                  ON (c.relnamespace = nsp.oid)
               LEFT JOIN pg_user use2
                  ON (c.relowner = use2.usesysid)
            WHERE c.relowner = use.usesysid
               AND item = 'fa_analystbyregion'
               AND nsp.nspname NOT IN (
                  'pg_catalog',
                  'pg_toast',
                  'information_schema'
               ))
            JOIN pg_group pu
               ON array_to_string (relacl, '|') LIKE '%' || pu.groname || '%';

Find Locking/Blocking Redshift Queries

https://aws.amazon.com/premiumsupport/knowledge-center/prevent-locks-blocking-queries-redshift/

#!/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.
#
#                
# TODO: Utilize a sourced bash file with usernames and passwords, as with other scripts                   
# in the library.                    
#                    
#################################################################################

RSUSER="vacasaroot"
RSPASS="xxxxxxxxxx"

read -r -d '' "stats_sql" << 'EOF'

SELECT
  a.txn_owner,
  a.xid,
  a.pid,
  a.txn_start,
  a.lock_mode,
  a.relation AS table_id,
  nvl (TRIM(c."name"), d.relname) AS tablename,
  a.granted,
  b.pid AS blocking_pid,
  DATEDIFF(s, a.txn_start, getdate ()) / 86400 || ' days ' || DATEDIFF(s, a.txn_start, getdate ()) % 86400 / 3600 || ' hrs ' || DATEDIFF(s, a.txn_start, getdate ()) % 3600 / 60 || ' mins ' || DATEDIFF(s, a.txn_start, getdate ()) % 60 || ' secs' AS txn_duration
FROM
  svv_transactions a
  LEFT JOIN
    (SELECT
      pid,
      relation,
      granted
    FROM
      pg_locks
    GROUP BY 1,
      2,
      3) b
    ON a.relation = b.relation
    AND a.granted = 'f'
    AND b.granted = 't'
  LEFT JOIN
    (SELECT
      *
    FROM
      stv_tbl_perm
    WHERE slice = 0) c
    ON a.relation = c.id
  LEFT JOIN pg_class d
    ON a.relation = d.oid
WHERE a.relation IS NOT NULL;

EOF

echo "$stats_sql" > /tmp/stats.sql


BLOCKPIDS=`cat /tmp/stats.sql | PGPASSWORD=${RSPASS} psql -h  warehouse.vacasa.services -p 5439 -U${RSUSER} -dwarehouse | cut -d'|' -f9 | grep "\S" | egrep -v "blocking|rows|\-\-\-"| sed 's/ //g' | sort -u`

#construct IN clause

for PID in $BLOCKPIDS
do
PIDCLAUSE="$PIDCLAUSE,$PID"
done
PIDCLAUSE=" AND pid in (${PIDCLAUSE:1}) "


stats_sql="select pid, duration/1000000 as seconds, trim(user_name) as user,substring(query,1,200) as querytxt from stv_recents where status = 'Running' and seconds >=1 ${PIDCLAUSE} order by seconds desc;"

echo $stats_sql > /tmp/stats.sql


echo "

Current active Redshift queries that are blocking the execution of other queries (may or may not be critical)
=============================================================================================================
"


cat /tmp/stats.sql | PGPASSWORD=${RSPASS} psql -h  warehouse.vacasa.services -p 5439 -U${RSUSER} -dwarehouse

Setting up DBeaver for use with Redshift

Step by step 

Setting up DBeaver for use with Redshift is not the most intuitive thing you’ll ever do. A common misconception is that since Redshift is (sorta) built on Postgres, then a Postgres driver is the correct choice. Alas, nope.

Here is a quick how-to for setting up DBeaver correctly as possible for Redshift.

Here’s the standard DBeaver opening screen

Right-click on your Redshift connection and choose “Edit Connection (F4)”

That will present you with the Connection Settings dialog:

Where it says “Driver name” it’s gotta be AWS / Redshift. If it doesn’t, then click the “Edit Driver Settings” button. You’ll get this:

Choose the AWS category and the ID as shown–if you do not have the driver installed, or if you do, but want to upgrade it, click the website link and DBeaver will get the most recent stable version for your OS and install it. Then you can continue with setting the host, port, database, user, etc. in the previous screen.

And as Steve used to say, “oh, and one more thing.”

Back on the first Edit Connection screen, there’s a menu choice called “Initialization.” Go back after your driver is configured and click THAT.

SET. THAT. KEEP ALIVE.

I’d recommend something relatively LOW, say, 55 secs. Try that for a while and if it helps, gradually increase the value until it’s around 5-10 minutes; enough to keep your connection alive, but not so low as to be annoying.