YDSbVRh7

1/* create elb raw logs table with partition */
2CREATE EXTERNAL TABLE IF NOT EXISTS elb_logs_raw_partition (
3         request_timestamp string,
4         elb_name string,
5         request_ip string,
6         request_port int,
7         backend_ip string,
8         backend_port int,
9         request_processing_time double,
10         backend_processing_time double,
11         client_response_time double,
12         elb_response_code string,
13         backend_response_code string,
14         received_bytes bigint,
15         sent_bytes bigint,
16         request_verb string,
17         url string,
18         protocol string,
19         user_agent string,
20         ssl_cipher string,
21         ssl_protocol string 
22) PARTITIONED BY(
23        year string,
24         month string,
25         day string
26) 
27ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
28WITH SERDEPROPERTIES (
29         '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.-]*)$' ) 
30         LOCATION 's3://atom-elb-access-logs/AWSLogs/elasticloadbalancing/us-east-1/'; 
31
32
33/* apply the partiotion on the table */ 
34ALTER TABLE elb_logs_raw_partition ADD PARTITION (year='2016',month='12',day='15') 
35location 's3://atom-elb-access-logs/AWSLogs/elasticloadbalancing/us-east-1/2016/12/15/'; 
36ALTER TABLE elb_logs_raw_partition ADD PARTITION (year='2016',month='12',day='16') 
37location 's3://atom-elb-access-logs/AWSLogs/elasticloadbalancing/us-east-1/2016/12/16/'; 
38ALTER TABLE elb_logs_raw_partition ADD PARTITION (year='2016',month='12',day='17') 
39location 's3://atom-elb-access-logs/AWSLogs/elasticloadbalancing/us-east-1/2016/12/17/'; 
40ALTER TABLE elb_logs_raw_partition ADD PARTITION (year='2016',month='12',day='18') 
41location 's3://atom-elb-access-logs/AWSLogs/elasticloadbalancing/us-east-1/2016/12/18/'; 
42
43/* see what partitions were applied */ 
44show partitions elb_logs_raw_partition;