· 8 years ago · Jan 10, 2018, 06:56 PM
1#Version: 1.0
2#Fields: date time x-edge-location sc-bytes c-ip cs-method cs(Host) cs-uri-stem sc-status cs(Referer) cs(User-Agent) cs-uri-query cs(Cookie) x-edge-result-type x-edge-request-id x-host-header cs-protocol cs-bytes time-taken x-forwarded-for ssl-protocol ssl-cipher x-edge-response-result-type
32016-02-02 07:57:45 LHR5 5001 86.177.253.38 GET d3g47gpj5mj0b.cloudfront.net /foo 404 - Mozilla/5.0%2520(Macintosh;%2520Intel%2520Mac%2520OS%2520X%252010_10_5)%2520AppleWebKit/537.36%2520(KHTML,%2520like%2520Gecko)%2520Chrome/47.0.2526.111%2520Safari/537.36 - - Error -tHYQ3YpojqpR8yFHCUg5YW4OC_yw7X0VWvqwsegPwDqDFkIqhZ_gA== d3g47gpj5mj0b.cloudfront.net https421 0.076 - TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 Error
42016-02-02 07:57:45 LHR5 1158241 86.177.253.38 GET d3g47gpj5mj0b.cloudfront.net /images/posts/cover/404.jpg 200 https://d3g47gpj5mj0b.cloudfront.net/foo Mozilla/5.0%2520(Macintosh;%2520Intel%2520Mac%2520OS%2520X%252010_10_5)%2520AppleWebKit/537.36%2520(KHTML,%2520like%2520Gecko)%2520Chrome/47.0.2526.111%2520Safari/537.36 - - Miss oUdDIjmA1ON1GjWmFEKlrbNzZx60w6EHxzmaUdWEwGMbq8V536O4WA== d3g47gpj5mj0b.cloudfront.net https 419 0.440 - TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 Miss
5
6CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
7 `Date` DATE,
8 Time STRING,
9 Location STRING,
10 Bytes INT,
11 RequestIP STRING,
12 Method STRING,
13 Host STRING,
14 Uri STRING,
15 Status INT,
16 Referrer STRING,
17 os STRING,
18 Browser STRING,
19 BrowserVersion STRING
20 ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
21 WITH SERDEPROPERTIES (
22 "input.regex" = "^(?!#)([^ ]+)\s+([^ ]+)\s+([^ ]+)\s+([^ ]+)\s+([^ ]+)\s+([^ ]+)\s+([^ ]+)\s+([^ ]+)\s+([^ ]+)\s+([^ ]+)\s+[^(]+[(]([^;]+).*%20([^/]+)[/](.*)$"
23 ) LOCATION 's3://test/athena-csv/'
24
25CREATE EXTERNAL TABLE logs (
26 `date` date,
27 `time` string,
28 `location` string,
29 `bytes` int,
30 `request_ip` string,
31 `method` string,
32 `host` string,
33 `uri` string,
34 `status` int,
35 `referer` string,
36 `useragent` string,
37 `uri_query` string,
38 `cookie` string,
39 `edge_type` string,
40 `edget_requiest_id` string,
41 `host_header` string,
42 `cs_protocol` string,
43 `cs_bytes` int,
44 `time_taken` string,
45 `x_forwarded_for` string,
46 `ssl_protocol` string,
47 `ssl_cipher` string,
48 `result_type` string,
49 `protocol` string
50)
51ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
52WITH SERDEPROPERTIES (
53 'input.regex' = '^(?!#.*)(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s*(\S*)'
54) LOCATION 's3://logs'
55
56CREATE EXTERNAL TABLE your_table_name (
57 `Date` DATE,
58 Time STRING,
59 Location STRING,
60 SCBytes BIGINT,
61 RequestIP STRING,
62 Method STRING,
63 Host STRING,
64 Uri STRING,
65 Status INT,
66 Referrer STRING,
67 UserAgent STRING,
68 UriQS STRING,
69 Cookie STRING,
70 ResultType STRING,
71 RequestId STRING,
72 HostHeader STRING,
73 Protocol STRING,
74 CSBytes BIGINT,
75 TimeTaken FLOAT,
76 XForwardFor STRING,
77 SSLProtocol STRING,
78 SSLCipher STRING,
79 ResponseResultType STRING,
80 CSProtocolVersion STRING
81 )
82 ROW FORMAT DELIMITED FIELDS TERMINATED BY 't'
83 LOCATION 's3://path_to_your_data_directory'
84
85CREATE EXTERNAL TABLE IF NOT EXISTS mytablename (
86 `date` date,
87 `time` string,
88 `location` string,
89 `bytes` int,
90 `request_ip` string,
91 `method` string,
92 `host` string,
93 `uri` string,
94 `status` int,
95 `referer` string,
96 `useragent` string,
97 `uri_query` string,
98 `cookie` string,
99 `edge_type` string,
100 `edget_request_id` string,
101 `host_header` string,
102 `cs_protocol` string,
103 `cs_bytes` int,
104 `time_taken` int,
105 `x_forwarded_for` string,
106 `ssl_protocol` string,
107 `ssl_cipher` string,
108 `result_type` string,
109 `protocol_version` string
110)
111ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
112WITH SERDEPROPERTIES (
113 'serialization.format' = '1',
114 'input.regex' = '^(?!#.*)(?!#.*)([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)t+([^t]+)$'
115) LOCATION 's3://mybucket/myprefix/';
116
117CREATE EXTERNAL TABLE IF NOT EXISTS DBNAME.TABLENAME (
118 `date` date,
119 `time` string,
120 `location` string,
121 `bytes` int,
122 `request_ip` string,
123 `method` string,
124 `host` string,
125 `uri` string,
126 `status` int,
127 `referer` string,
128 `useragent` string,
129 `uri_query` string,
130 `cookie` string,
131 `edge_type` string,
132 `edget_requiest_id` string,
133 `host_header` string,
134 `cs_protocol` string,
135 `cs_bytes` int,
136 `time_taken` string,
137 `x_forwarded_for` string,
138 `ssl_protocol` string,
139 `ssl_cipher` string,
140 `result_type` string
141)
142ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
143WITH SERDEPROPERTIES (
144 'serialization.format' = '1',
145 'input.regex' = '^(?!#.*)(?!#.*)([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)s+([^s]+)$'
146) LOCATION 's3://bucket/logs/';