QA46JTiM

1# Create the partitioned table
2CREATE EXTERNAL TABLE IF NOT EXISTS {SCHEMA}.{TABLE_NAME} (
3  type string,
4  time string,
5  elb string,
6  client_ip string,
7  client_port int,
8  target_ip string,
9  target_port int,
10  request_processing_time double,
11  target_processing_time double,
12  response_processing_time double,
13  elb_status_code int,
14  target_status_code string,
15  received_bytes int,
16  sent_bytes int,
17  request_verb string,
18  request_url string,
19  request_proto string,
20  user_agent string,
21  ssl_cipher string,
22  ssl_protocol string,
23  target_group_arn string,
24  trace_id string,
25  hostname string,
26  ssl_certificate string
27)
28PARTITIONED BY(year int, month int, day int) 
29ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
30WITH SERDEPROPERTIES (
31  'serialization.format' = '1',
32  'input.regex' = '([^ ]+) ([^ ]+) ([^ ]+) ([0-9.]+):([0-9]+) ([0-9.]+):([0-9]+) ([0-9.]+) ([0-9.]+) ([0-9.]+) ([0-9-]+) ([0-9-]+) ([0-9-]+) ([0-9-]+) \"([^ ]*) ([^ ]*) ([^ ]*)\" \"([^\"]*)\" ([^ ]+) ([^ ]+) ([^ ]+) \"([^ ]+)\" \"([^ ]+)\" \"([^ ]+)\"'
33) LOCATION 's3://{BUCKET_NAME}/AWSLogs/{ACCOUNT_ID}/elasticloadbalancing/{REGION}/';
34
35# Create a partition
36ALTER TABLE sampledb.appelb add partition (year=2017, month=12, day=14)
37LOCATION "s3://{BUCKET_NAME}/AWSLogs/{ACCOUNT_ID}/elasticloadbalancing/{REGION}/2017/12/14/";