Amazon Athena

Hands-On Demo

V2

Create S3 Bucket for Athena Data

athena-queries-0982391

Go to Amazon Athena

Assign S3 Bucket to Store Query Results

Choose S3 Bucket

Saved Settings

Create Database

CREATE DATABASE mydatabase

Inspect S3 Sample Log Data

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

Log Data Format

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 Table

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 Table

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