Question

1. Write the SQL statement to find the uri with the largest number of entries for the host with the largest number of log entries. Paste it here.
2. Execute the statement on the cluster and paste the results here.
3. 
   1Summary: This sample shows you how to analyze CloudFront logs stored in S3 using Hive 3Create table using sample data in S3. Note: you can replace this S3 path with your own 4 CREATE EXTERNAL TABLE IF NOT EXISTS cloudfrontlogs 5 Dateobject Date, Time STRING, Location STRING, Bytes INT, RequestIP STRING, Method STRING, Host STRING, Uri STRING, Status INT Referrer STRING, oS String, Browser String, BrowserVersion String 12 13 14 15 16 17 18 19 ROW FORMAT SERDE org.apache.hadoop.hive.serde2.RegexSerDe' 20 WITH SERDEPROPERTIES 22LOCATION $[INPUT /cloudfront/data'; 24 Total requests per operating system for a given time frame 25 INSERT OVERWRITE DIRECTORY$tOUTPUT/queries/" SELECTFROM cloudfront_logs LIMIT 1;
   

Answer 1

To find largest number of log entries of uri 1st we need to get of count. We get count by doing count of ip address/dateobject. For every hit on dateobject will created. Upon getting count we need to find largest of count. Below is with which we can achieve results.

Select uri,max(CountUri) from (

Se|ect uri, count(dateobject) as CountUri from Cloudfront_logs group by uri) AS subTable;