#!/bin/bash
#
# Many third-party data sources do lazy character column definitions, making them varchar(65535), which is stoopid.
# This little script will read in the DDL for a table, parse out the columns defined like this and then hork out a
# SQL script that discovers the actual max length of the actual data for more efficient DDL. Suggested use is to
# run this script and output it to a xxxx.sql file, then \i that file in Redshift while in the correct schema.
#
# Assumes tablename will be passed on the command line and a file, tablename.sql will exist in this dir.
TABLENAME=${1}
if [ "${TABLENAME}" == "" ]
then
echo -e "\nUSAGE: $0 redshiftTableName\n\nExpects redshiftTableName.sql to exist in this directory.\n\n"
exit 1
fi
COLNAMES=`grep 65535 ${TABLENAME}.sql | cut -f2 -d","|cut -f1 -d" "`
for COL in ${COLNAMES}
do
echo "SELECT MAX(LEN(${COL})) AS max_${COL} FROM ${TABLENAME};"
done
Month: August 2022
Various Queries For Showing User Permissions To Objects
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, t.tablename, 'update') AS user_has_update_permission,
has_table_privilege (u.usename, t.tablename, 'delete') AS user_has_delete_permission,
has_table_privilege (
u.usename,
t.tablename,
'references'
) AS user_has_references_permission
FROM
pg_user u
CROSS JOIN pg_tables t
WHERE u.usename = 'tylerbullen' -- AND t.tablename = 'myTableName'
AND t.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, t.tablename, 'update') AS user_has_update_permission,
has_table_privilege (u.usename, t.tablename, 'delete') AS user_has_delete_permission,
has_table_privilege (
u.usename,
t.tablename,
'references'
) AS user_has_references_permission
FROM
pg_user u
CROSS JOIN pg_tables t
WHERE -- u.usename = 'tylerbullen'
-- AND t.tablename = 'myTableName'
user_has_insert_permission = 't'
AND t.schemaname = 'vacasa';
Generate Overall Rowcount Tallies In Redshift
#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 -t -h warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "select table_name from information_schema.tables where table_schema='${SCHEMA}' and table_type='BASE TABLE' ORDER BY table_name;"`
for TAB in $TABLES
do
echo ">>> Working on ${DBASE}.${SCHEMA}.${TAB}..."
TABLINE=`PGPASSWORD=xxxxxxxxxx psql -t -h warehouse.vacasa.services -p 5439 -Uvacasaroot -n -q -d${DBASE} -c "insert into admin.overall_rowcount select '${SCHEMA}','$TAB',count(*) from ${SCHEMA}.${TAB}"`
done
done
echo "*** All done with database ${DBASE}."
# eng_metrics database
DBASE="eng_metrics"
SCHEMALIST="jira"
# 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 -t -h warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "select table_name from information_schema.tables where table_schema='${SCHEMA}' and table_type='BASE TABLE' ORDER BY table_name;"`
for TAB in $TABLES
do
echo ">>> Working on ${DBASE}.${SCHEMA}.${TAB}..."
TABLINE=`PGPASSWORD=xxxxxxxxxx psql -t -h warehouse.vacasa.services -p 5439 -Uvacasaroot -n -q -d${DBASE} -c "insert into admin.overall_rowcount select '${SCHEMA}','$TAB',count(*) from ${SCHEMA}.${TAB}"`
done
done
echo "*** All done with database ${DBASE}."
# warehouse database
DBASE="warehouse"
SCHEMALIST="adwords_master adwords_owner aircall analysis_ready bing_ads common data_staging etl_testing facebook_ads finance google_adwords google_analytics high_risk housekeeping hubspot looker mailing marketo owner_portal_prod phones property_nexus prospect public rates reporting reporting_prod s3_files secure secure_scrubbed segment segment_marketo split_io_production splitio vacasa vacasa_gps_service vacasa_prod vacasa_units_svc_prod"
# 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 -t -h warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "select table_name from information_schema.tables where table_schema='${SCHEMA}' and table_type='BASE TABLE' ORDER BY table_name;"`
for TAB in $TABLES
do
echo ">>> Working on ${DBASE}.${SCHEMA}.${TAB}..."
TABLINE=`PGPASSWORD=xxxxxxxxxx psql -t -h warehouse.vacasa.services -p 5439 -Uvacasaroot -n -q -d${DBASE} -c "insert into admin.overall_rowcount select '${SCHEMA}','$TAB',count(*) from ${SCHEMA}.${TAB}"`
done
done
echo "*** All done with database ${DBASE}."
ENDTIME=`date`
echo "All done with every damned thing."
echo "Startime was: $STARTIME"
echo "Endtime was : $ENDTIME"
Inelegant Duplicate Row Remover For Redshift
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_item_old;
Redshift User And Grant Cheatsheet
-- 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') AS ref
FROM
(
SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
WHERE schemaname NOT IN ('pg_internal')
UNION
SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
WHERE schemaname NOT IN ('pg_internal')
) AS objs
,(SELECT * FROM pg_user) AS usrs
ORDER BY fullobj
)
WHERE (sel = TRUE OR ins = TRUE OR upd = TRUE OR del = TRUE OR ref = TRUE)
AND schemaname='reporting'
AND usename = 'emilybrown';
-- check perms for a table
\dp units
-- show all privs for role
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 = 'job_analyst'
AND s.schemaname = 'marketo'
;
-- grants for a specific table
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,t.tablename,'update') AS user_has_update_permission,
has_table_privilege(u.usename,t.tablename,'delete') AS user_has_delete_permission,
has_table_privilege(u.usename,t.tablename,'references') AS user_has_references_permission
FROM
pg_user u
CROSS JOIN
pg_tables t
WHERE
u.usename = 'marcurbaitel'
AND t.tablename = 'trust_rev_payments'
;
-- Users and their groups
SELECT usename, groname
FROM pg_user, pg_group
WHERE pg_user.usesysid = ANY(pg_group.grolist)
AND pg_group.groname in (SELECT DISTINCT pg_group.groname from pg_group) order by usename;
-- Users in a particular group
select usename from pg_user , pg_group where
pg_user.usesysid = ANY(pg_group.grolist) and
pg_group.groname='grp_maxbi'
ORDER BY BY usename;
-- Grant and future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA hubspot GRANT SELECT ON TABLES TO GROUP grp_fivetran_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA hubspot TO GROUP grp_fivetran_analyst;
GRANT USAGE ON SCHEMA hubspot TO GROUP grp_fivetran_analyst;
-- Same for single user
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO looker;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO looker;
GRANT USAGE ON SCHEMA reporting TO looker;
/*Check active permissions for a certain user*/
SELECT distinct a1.schemaname, a1.groupname,a2.usename, a1.sel, a1.upd, a1.ins, a1.del, a1.drp, a1."ref"
FROM "admin".v_get_tbl_priv_by_group a1
inner join "admin".v_get_users_in_group a2
on a2.groname = a1.groupname
and a2.usename = '<username>';
Database Blog 