athena-queries-0982391

CREATE DATABASE mydatabase

aws s3 ls s3://athena-examples-us-east-1/cloudfront/plaintext/ --human-readable

aws s3 cp s3://athena-examples-us-east-1/cloudfront/plaintext/log1 ./log1

2014-07-05	20:00:00	LHR3	4260	10.0.0.15	GET	eabcd12345678.cloudfront.net	/test-image-1.jpeg	200	-	Mozilla/5.0%20(MacOS;%20U;%20Windows%20NT%205.1;%20en-US;%20rv:1.9.0.9)%20Gecko/2009040821%20IE/3.0.9
2014-07-05	20:00:00	MIA3	10	10.0.0.15	GET	eabcd12345678.cloudfront.net	/test-image-1.jpeg	304	-	Mozilla/5.0%20(Linux;%20U;%20Windows%20NT%205.1;%20en-US;%20rv:1.9.0.9)%20Gecko/2009040821%20Chrome/3.0.9

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
  `Date` DATE,
  Time STRING,
  Location STRING,
  Bytes INT,
  RequestIP STRING,
  Method STRING,
  Host STRING,
  Uri STRING,
  Status INT,
  Referrer STRING,
  ClientInfo STRING
  ) 
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  LOCATION 's3://athena-examples-us-east-1/cloudfront/plaintext/';

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
  `Date` DATE,
  Time STRING,
  Location STRING,
  Bytes INT,
  RequestIP STRING,
  Method STRING,
  Host STRING,
  Uri STRING,
  Status INT,
  Referrer STRING,
  ClientInfo STRING
  ) 
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  LOCATION 's3://athena-examples-us-east-1/cloudfront/plaintext/';

SELECT 
  CASE 
    WHEN ClientInfo LIKE '%IE/%' THEN 'Internet Explorer'
    WHEN ClientInfo LIKE '%Chrome/%' THEN 'Chrome'
    WHEN ClientInfo LIKE '%Firefox/%' THEN 'Firefox'
    WHEN ClientInfo LIKE '%Safari/%' THEN 'Safari'
    WHEN ClientInfo LIKE '%Opera/%' THEN 'Opera'
    ELSE 'Other'
  END AS Browser,
  COUNT(*) AS RequestCount,
  SUM(Bytes) AS TotalBytes
FROM cloudfront_logs 
WHERE date BETWEEN date '2014-07-05' AND date '2014-08-05' 
GROUP BY 
  CASE 
    WHEN ClientInfo LIKE '%IE/%' THEN 'Internet Explorer'
    WHEN ClientInfo LIKE '%Chrome/%' THEN 'Chrome'
    WHEN ClientInfo LIKE '%Firefox/%' THEN 'Firefox'
    WHEN ClientInfo LIKE '%Safari/%' THEN 'Safari'
    WHEN ClientInfo LIKE '%Opera/%' THEN 'Opera'
    ELSE 'Other'
  END
ORDER BY RequestCount DESC