Pavel Kerbel kerbelp

## athena_create_table_elb_logs_partitions.sql
/* 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,

## athena_create_table_elb_logs.sql

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,

## redshift_vacuum_full.sql
VACUUM FULL schema.table;

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

## redshift_copy.sql
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;

## redshift_unload_yesterday.sql
UNLOAD ('SELECT "ts", "column_a", "column_b"
				 FROM schema.table
         WHERE ts >= (SELECT DATE_TRUNC(\'day\', (SELECT sysdate - 1)))
             AND ts < (SELECT DATE_TRUNC(\'day\', (SELECT sysdate)))'
       )
TO 's3://bucket/prefix'
GZIP
ESCAPE;

## redshift_stl_load_errors_column_files.sql
SELECT TRUNC(starttime) AS day,
       colname,
       err_reason,
       filename,
       line_number
FROM stl_load_errors
WHERE colname = 'err'
GROUP BY day,
         colname,
         err_reason,

## redshift_stl_load_errors_columns_count.sql
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,

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

## change_mtu.sh
sudo ip link set dev eth0 mtu 1500
	/* 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,
	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;
	UNLOAD ('SELECT "ts", "column_a", "column_b"
	FROM schema.table
	WHERE ts >= (SELECT DATE_TRUNC(\'day\', (SELECT sysdate - 1)))
	AND ts < (SELECT DATE_TRUNC(\'day\', (SELECT sysdate)))'
	)
	TO 's3://bucket/prefix'
	GZIP
	ESCAPE;
	SELECT TRUNC(starttime) AS day,
	colname,
	err_reason,
	filename,
	line_number
	FROM stl_load_errors
	WHERE colname = 'err'
	GROUP BY day,
	colname,
	err_reason,
	SELECT pid,
	user_name,
	starttime,
	duration,
	query
	FROM stv_recents
	WHERE status = 'Running'
	ORDER BY user_name DESC;