Redshift

Data Warehouse design – Type 2 Slowly Changing Dimensions

The blog outlines one method for handling Slowly Changing Dimensions – probably the most popular method referred to as type 2. Could be applied to any data base product – but – this applies to Redshift and Snowflake and more… A type 2 slowly changing dimension enables you to track the history of updates to …

Data Warehouse design – Type 2 Slowly Changing Dimensions Read More »

Redshift – How to find and kill a process holding locks – and a bonus feature – allow power users to do this too – with a definers right pl/sql proc

To find blocking locks: select a.txn_owner, a.txn_db, 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 …

Redshift – How to find and kill a process holding locks – and a bonus feature – allow power users to do this too – with a definers right pl/sql proc Read More »

Redshift – The basics of granting a shared database or subset of a database

Basically it comes down to this syntactical example: GRANT USAGE ON DATASHARE salesshare TO NAMESPACE ’13b8833d-17c6-4f16-8fe4-1a018f5ed00d’; To determine the namespace of the cluster you want to grant the share to you or someone with access to the cluster should issue this command to identify the namespace that should be grant access to the share: SELECT …

Redshift – The basics of granting a shared database or subset of a database Read More »

Setting up and using Redshift copy and unload from an S3 bucket in another AWS account and region

You may want to use something like this to guide you in implementing cross account s3 access when using Redshift copy or unload: https://aws.amazon.com/premiumsupport/knowledge-center/redshift-s3-cross-account/ It is a somewhat long and drawn out process, which is why I suggest you simply setup a bucket in the same account and region but, if it must be done, …

Setting up and using Redshift copy and unload from an S3 bucket in another AWS account and region Read More »

When autocommit is OFF (or during execution of) select statements obtain and hold AccessShareLock until the session is terminated

Here is an issue we see frequently: User A connects to Redshift with AutoCommit OFF.  User A selects from table T.  User B try’s to drop table T and even though User B owns table T, the drop is blocked by the User A – as User A did not connect with AutoCommit On/True and …

When autocommit is OFF (or during execution of) select statements obtain and hold AccessShareLock until the session is terminated Read More »