kerbelp

COPY schema.table ("ts","column_a","column_b")
FROM 's3://bucket/prefix' 
REGION 'us-east-1' /* S3 bucket is in us-east-1 region */
GZIP 
ESCAPE;

kerbelp

SELECT pid,
       user_name,
       starttime,
       duration,
       query
FROM stv_recents
WHERE status = 'Running'
ORDER BY user_name DESC;

kerbelp

SELECT TRUNC(starttime) AS day,
       colname,
       err_reason,
       COUNT(*) AS amount
FROM stl_load_errors
WHERE starttime >sysdate -1
GROUP BY day,
         colname,
         err_reason
ORDER BY day,

kerbelp

CREATE EXTERNAL TABLE IF NOT EXISTS elb_logs_raw (
         request_timestamp string,
         elb_name string,
         request_ip string,
         request_port int,
         backend_ip string,
         backend_port int,
         request_processing_time double,
         backend_processing_time double,

kerbelp

WITH total_count_calc AS
(
  SELECT COUNT(*) AS total_count
  FROM elb_logs_raw_partition
  WHERE YEAR = '2016'
  AND   MONTH = '12'
  AND   DAY = '15'
),
status_count_calc AS
(

kerbelp

SELECT SUM(capacity) FROM stv_partitions;

kerbelp

CREATE OR REPLACE VIEW admin.v_tables_to_vacuum 
AS
SELECT (('VACUUM FULL ' || derived_table1.schemaname) || '.') || derived_table1.tablename AS query
FROM (SELECT BTRIM(pgdb.datname) AS dbase_name,
             BTRIM(pgn.nspname) AS schemaname,
             BTRIM(a.name) AS tablename,
             a.id AS tbl_oid,
             b.mbytes AS megabytes,
             a. "rows" AS rowcount,
             a.unsorted_rows AS unsorted_rowcount,

kerbelp

CREATE OR REPLACE VIEW admin.v_vacuum_jobs_summary
AS 
 SELECT DISTINCT us.usename, perm.name, vac.eventtime, trunc(vac.eventtime) AS date, vac.status, vac."rows", vac.sortedrows
   FROM stl_vacuum vac
   JOIN stv_tbl_perm perm ON perm.id = vac.table_id
   JOIN pg_user us ON us.usesysid = vac.userid
  WHERE trunc(vac.eventtime) >= trunc('now' - 1)
  ORDER BY vac.eventtime DESC;

kerbelp

CREATE OR REPLACE VIEW admin.v_users_groups 
AS
SELECT pg_group.groname as group_name,
       pg_user.usename as user_name,
       pg_user.usesuper as is_superuser
FROM pg_group,
     pg_user
WHERE pg_user.usesysid = ANY (pg_group.grolist);

kerbelp

CREATE OR REPLACE VIEW admin.v_encoded_columns_percentage 
AS
SELECT def.tablename,
       def.mbytes,
       SUM(CASE WHEN def.attencodingtype = 0 THEN 0 ELSE 1 END)::double precision / COUNT(DISTINCT def.attname)::double precision AS encoded_percentage
FROM (SELECT n.nspname AS schemaname,
             c.oid,
             c.relname AS tablename,
             format_encoding(a.attencodingtype) AS "encoding",
             a.attencodingtype,