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,
         client_response_time double,
         elb_response_code string,
         backend_response_code string,
         received_bytes bigint,
         sent_bytes bigint,
         request_verb string,
         url string,
         protocol string,
         user_agent string,
         ssl_cipher string,
         ssl_protocol string 
) PARTITIONED BY(
        year string,
         month string,
         day string
) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
         'serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) ([.0-9]*) ([.0-9]*) ([.0-9]*) (-|[0-9]*) (-|[0-9]*) ([-0-9]*) ([-0-9]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$' ) 
         LOCATION 's3://atom-elb-access-logs/AWSLogs/elasticloadbalancing/us-east-1/'; 


/* apply the partiotion on the table */ 
ALTER TABLE elb_logs_raw_partition ADD PARTITION (year='2016',month='12',day='15') 
location 's3://atom-elb-access-logs/AWSLogs/elasticloadbalancing/us-east-1/2016/12/15/'; 
ALTER TABLE elb_logs_raw_partition ADD PARTITION (year='2016',month='12',day='16') 
location 's3://atom-elb-access-logs/AWSLogs/elasticloadbalancing/us-east-1/2016/12/16/'; 
ALTER TABLE elb_logs_raw_partition ADD PARTITION (year='2016',month='12',day='17') 
location 's3://atom-elb-access-logs/AWSLogs/elasticloadbalancing/us-east-1/2016/12/17/'; 
ALTER TABLE elb_logs_raw_partition ADD PARTITION (year='2016',month='12',day='18') 
location 's3://atom-elb-access-logs/AWSLogs/elasticloadbalancing/us-east-1/2016/12/18/'; 

/* see what partitions were applied */ 
show partitions elb_logs_raw_partition;