Redshift analysis of ELB logs

elb_logs.sql

CREATE TABLE elb_logs (
     RequestTime DateTime encode lzo,
     ELBName varchar(30) encode lzo,
     RequestIP_Port varchar(50) encode lzo,
     BackendIP_Port varchar(50) encode lzo,
     RequestProcessingTime FLOAT encode bytedict,
     BackendProcessingTime FLOAT encode bytedict,
     ClientResponseTime FLOAT encode bytedict,
     ELBResponseCode INTEGER encode lzo,
     BackendResponseCode INTEGER encode lzo,
     ReceivedBytes BIGINT encode lzo,
     SentBytes BIGINT encode lzo,
     Request varchar(32767) encode lzo,
     UserAgent varchar(4095) encode lzo,
     SSLCipher varchar(100) encode lzo,
     SSLProtocol varchar(100) encode lzo
)
sortkey(RequestTime);

load.R

x <- read.csv('Code/elb-logs/minutely.csv', colClasses=c("POSIXct", "factor", "numeric", "numeric", "numeric", "numeric", "factor", "factor", "numeric", "numeric", "factor"))

minute_summary.sql

create table elb_minutely
sortkey(minute)
as
select date_trunc('minute', requesttime) as minute,
       elbname,
       avg(requestprocessingtime) as req_time_a,
       max(requestprocessingtime) as req_time_m,
       avg(backendprocessingtime) as be_time_a,
       max(backendprocessingtime) as be_time_m,
       elbresponsecode as elb_status,
       backendresponsecode as be_status,
       sum(receivedbytes) as ingress,
       sum(sentbytes) as egress,
       sslprotocol
  from elb_logs
 group by minute, elbname, elb_status, be_status, sslprotocol;