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

DELETE FROM schema.table
WHERE ts < (SELECT sysdate - 90);

kerbelp

VACUUM FULL schema.table;

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

/* create elb raw logs table with partition */
CREATE EXTERNAL TABLE IF NOT EXISTS elb_logs_raw_partition (
         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

CREATE OR REPLACE VIEW admin.v_space_by_schema
AS
WITH CAPACITY AS
(
  SELECT SUM(capacity) FROM stv_partitions
),
USAGE AS
(
  SELECT TRIM(pgdb.datname) AS DATABASE,
         TRIM(pgn.nspname) AS SCHEMA,

kerbelp

SELECT SUM(capacity) FROM stv_partitions;

kerbelp

SELECT TRIM(pgdb.datname) AS DATABASE,
         TRIM(pgn.nspname) AS SCHEMA,
         TRIM(a.name) AS TABLE,
         b.mbytes,
         a.rows
  FROM (SELECT db_id,
               id,
               name,
               SUM(ROWS) AS ROWS
        FROM stv_tbl_perm a

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,