Redshift

Redshift System Tables and View Types

There are several types of system tables and views here are there prefixes which categorize purpose: SVV_ prefixedviews contain information about database objects with references to transient STV tables. SYS_ prefixed views are used to monitor query and workload usage for provisioned clusters and serverless workgroups. STL_ prefixed views are generated from logs that have …

Redshift System Tables and View Types Read More »

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 »