Redshift - nimrody/knowledgebase GitHub Wiki

Epoch to timestamp: (timestamp 'epoch' + rx_time_utc/1000 * INTERVAL '1 Second')

timestamp to epoch extract('epoch' from timestamp '1970-01-01 01:00:00')

timestamp to date: trunc(timestamp '1970-01-01 01:00:00')

Distinct users per day

SELECT (TIMESTAMP 'epoch' + FLOOR(rx_time_utc/ 86400000)86400INTERVAL '1 Second ') AS DATE, CAST(COUNT(*) AS NUMERIC) / 1e6 AS clicks, CAST(COUNT(DISTINCT user_id) AS NUMERIC) / 1e6 AS distinct_users FROM user_activity WHERE package_name = 'com.rge.sport5' GROUP BY 1 ORDER BY 1

Load from S3

copy user_device_status from 's3://xxx' credentials 'aws_access_key_id=AAAAAA;aws_secret_access_key=bbbbbbbbbbbbb' format as avro 'auto';

Run this query to figure out pids of AccessShareLock

select current_time, c.relname, l.database, l.transaction, l.pid, a.usename, l.mode, l.granted from pg_locks l join pg_catalog.pg_class c ON c.oid = l.relation join pg_catalog.pg_stat_activity a ON a.procpid = l.pid where l.pid <> pg_backend_pid();

Kill the processes using select pg_terminate_backend(<pid>)

Or use this query

select * from pg_locks where relation = (select oid from pg_class where relname = 'the_table') select pg_cancel_backend(pid)

Currently active transactions

select * from svv_transactions where pid != pg_backend_pid()

Information on stuck queries

Create redshift_import user

create user redshift_import with password '333333' ;
grant all privileges on all tables in schema public to redshift_import with grant option; alter table xxx owner to 'redshift_import'

Guide to Redshift

Redshift administration

Heap analytics redshift pitfalls