1. CREATE EXTERNAL TABLE IF NOT EXISTS s3_cloudtrail_events_db.s3_server_logging_2(
2.  
3.   bucketowner STRING,
4.  
5.   bucket STRING,
6.  
7.   requestdatetime STRING,
8.  
9.   remoteip STRING,
10.  
11.   requester STRING,
12.  
13.   requestid STRING,
14.  
15.   operation STRING,
16.  
17.   key STRING,
18.  
19.   requesturi_operation STRING,
20.  
21.   requesturi_key STRING,
22.  
23.   requesturi_httpprotoversion STRING,
24.  
25.   httpstatus STRING,
26.  
27.   errorcode STRING,
28.  
29.   bytessent BIGINT,
30.  
31.   objectsize BIGINT,
32.  
33.   totaltime STRING,
34.  
35.   turnaroundtime STRING,
36.  
37.   referrer STRING,
38.  
39.   useragent STRING,
40.  
41.   versionid STRING,
42.  
43.   hostid STRING,
44.  
45.   signature STRING)
46.  
47. ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
48.  
49. WITH SERDEPROPERTIES (
50.  
51. 'serialization.format' = '1',
52.  
53.   'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*)\\s*([^ ]*)\\s*([^ ]*) (- |[^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*).*$'
54.  
55. )LOCATION 's3://nonprod-realize-s3-bucket-logging/'
56.  
57.  
58.  
59. Query -
60.  
61. SELECT DISTINCT "bucket", useragent, signature FROM s3_cloudtrail_events_db.s3_server_logging_2
62. WHERE signature='SigV2'
63.