Amazon Redshift Daily Command

Amazon Redshift Daily:

SELECT CURRENT_AWS_ACCOUNT;

SELECT CURRENT_DATABASE();

SELECT CURRENT_NAMESPACE();

SELECT CURRENT_SCHEMA();

SELECT CURRENT_USER_ID();

SELECT CURRENT_USER;

-- Metadata

select distinct(nspname) from pg_catalog.pg_namespace where nspname not like 'pg_temp%';

-- Describe Table

select * from information_schema.columns where table_name='columns';

select column_name, data_type, character_maximum_length from information_schema.columns where table_name='columns';

-- Create Read-only Group

Create group ro_group;

-- Create User

Crate user ro_user with password <password>;

-- Add user to Read-only Group

alter group ro_group ADD user ro_user;

-- Grant Usage permission to Read-Only Group to specific schema

grant usage on schema "ro_schema" to group ro_group;

-- Grant select permisson to read-only group to specific schema

grant select on all tables in schema "ro_schema" to group "ro_group";

-- Alter default privileges to maintain the permission on new tables

alter default privileges in schema "ro_schema" grant select on tables to group ro_group;

-- Revoke crate privileges from group

revoke create on schema "ro_schema" from group ro_group;

Example:

create group testgroup;

create user test1 password 'test123' in group testgroup;

grant usage on schema "rs_public" to group testgroup;

grant select on all tables in schema rs_public to group testgroup;

grant select on all tables in schema grant select on all tables in schema public to group data_viewers;

select * from pg_user where usename like 't%';

select groname from pg_catalog.pg_group pg where groname like 'test%';

select groname from pg_catalog.pg_group pg where groname like 'l%';

call rs_public.idm_ndew_revoke_from_login_role ('testgroup', 'test1');

grant all on procedure public. idm_new_revoke_from_login_role_bkup (in varchar, inout varchar) to test1;

create group testgroup;

create user test1 password 'test123' in group testgroup;

grant usage on schema "rs_public_ to group testgroup;

grant select on all tables in schema rs_public to group testgroup; 

Rename Table:

alter table db_name.schema_name.table_name rename to new_table_name;

Note: Remember not to include db_name.schema_name while giving new_table_name.

Troubleshoot ETL Extract and ETL Load:

To troubleshoot ETL Extract, and ETL Load query the system tables stl_load_errors, and stl_loaderror_detail;

select * from stl_load_errors;

select * from stl_loaderror_detail;