Complete Guide to Amazon Athena: Serverless SQL Analytics for S3#

1
-- Creating table for different formats
2
CREATE EXTERNAL TABLE parquet_table (
3
  id bigint,
4
  name string,
5
  created_date string
6
)
7
STORED AS PARQUET
8
LOCATION 's3://my-bucket/parquet-data/';
9

10
CREATE EXTERNAL TABLE json_table (
11
  id bigint,
12
  name string,
13
  metadata struct<tags:array<string>>
14
)
15
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
16
LOCATION 's3://my-bucket/json-data/';

1
CREATE DATABASE my_analytics_db
2
COMMENT 'Database for analytics workloads'
3
LOCATION 's3://my-athena-results/databases/my_analytics_db/';

1
CREATE EXTERNAL TABLE my_analytics_db.web_logs (
2
  timestamp string,
3
  ip_address string,
4
  method string,
5
  uri string,
6
  status_code int,
7
  response_size bigint,
8
  user_agent string
9
)
10
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
11
WITH SERDEPROPERTIES (
12
  'serialization.format' = '\t',
13
  'field.delim' = '\t'
14
)
15
LOCATION 's3://my-log-bucket/web-logs/';

1
-- Basic query
2
SELECT method, COUNT(*) as request_count
3
FROM my_analytics_db.web_logs
4
WHERE status_code = 200
5
GROUP BY method
6
ORDER BY request_count DESC;
7

8
-- Time-based analysis
9
SELECT DATE(timestamp) as log_date,
10
       COUNT(*) as total_requests,
11
       COUNT(CASE WHEN status_code >= 400 THEN 1 END) as error_requests
12
FROM my_analytics_db.web_logs
13
WHERE timestamp >= '2024-01-01'
14
GROUP BY DATE(timestamp)
15
ORDER BY log_date;

1
CREATE EXTERNAL TABLE partitioned_logs (
2
  timestamp string,
3
  ip_address string,
4
  method string,
5
  uri string,
6
  status_code int
7
)
8
PARTITIONED BY (
9
  year int,
10
  month int,
11
  day int
12
)
13
STORED AS PARQUET
14
LOCATION 's3://my-bucket/partitioned-logs/';
15

16
-- Add partitions
17
ALTER TABLE partitioned_logs ADD PARTITION (year=2024, month=1, day=1)
18
LOCATION 's3://my-bucket/partitioned-logs/year=2024/month=01/day=01/';

1
-- Working with arrays
2
SELECT user_id,
3
       cardinality(tags) as tag_count,
4
       array_join(tags, ', ') as tag_list
5
FROM user_data
6
WHERE contains(tags, 'premium');
7

8
-- Working with structs
9
SELECT user_id,
10
       profile.name,
11
       profile.email,
12
       profile.settings.theme
13
FROM user_profiles;

1
SELECT user_id,
2
       purchase_date,
3
       amount,
4
       SUM(amount) OVER (
5
         PARTITION BY user_id
6
         ORDER BY purchase_date
7
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
8
       ) as running_total
9
FROM purchases
10
ORDER BY user_id, purchase_date;

1
-- Convert CSV to Parquet
2
CREATE TABLE optimized_table
3
WITH (
4
  format = 'PARQUET',
5
  external_location = 's3://my-bucket/optimized-data/'
6
)
7
AS SELECT * FROM csv_table;

1
-- Partition by date for time-series data
2
CREATE EXTERNAL TABLE time_series_data (
3
  metric_name string,
4
  value double,
5
  timestamp string
6
)
7
PARTITIONED BY (
8
  dt string  -- format: YYYY-MM-DD
9
)
10
LOCATION 's3://my-bucket/time-series/';

1
-- Use LIMIT for exploratory queries
2
SELECT * FROM large_table LIMIT 10;
3

4
-- Use approximate functions for large datasets
5
SELECT approx_distinct(user_id) as unique_users
6
FROM web_logs;
7

8
-- Filter early and often
9
SELECT user_id, COUNT(*)
10
FROM events
11
WHERE event_date >= '2024-01-01'  -- Filter first
12
  AND event_type = 'purchase'
13
GROUP BY user_id;

1
-- Analyze web server logs
2
SELECT
3
  DATE(timestamp) as date,
4
  status_code,
5
  COUNT(*) as request_count,
6
  AVG(response_size) as avg_response_size
7
FROM web_logs
8
WHERE timestamp >= '2024-01-01'
9
GROUP BY DATE(timestamp), status_code
10
ORDER BY date, status_code;

1
-- Find API calls by user
2
SELECT
3
  useridentity.type,
4
  useridentity.principalid,
5
  eventname,
6
  COUNT(*) as call_count
7
FROM cloudtrail_logs
8
WHERE eventtime >= '2024-01-01'
9
GROUP BY useridentity.type, useridentity.principalid, eventname
10
HAVING COUNT(*) > 100
11
ORDER BY call_count DESC;

1
-- Sales analysis
2
SELECT
3
  DATE_TRUNC('month', order_date) as month,
4
  product_category,
5
  SUM(amount) as total_revenue,
6
  COUNT(DISTINCT customer_id) as unique_customers
7
FROM orders
8
WHERE order_date >= DATE('2024-01-01')
9
GROUP BY DATE_TRUNC('month', order_date), product_category
10
ORDER BY month, total_revenue DESC;

1
{
2
  "Version": "2012-10-17",
3
  "Statement": [
4
    {
5
      "Effect": "Allow",
6
      "Action": [
7
        "athena:GetQueryExecution",
8
        "athena:GetQueryResults",
9
        "athena:StartQueryExecution"
10
      ],
11
      "Resource": "*"
12
    },
13
    {
14
      "Effect": "Allow",
15
      "Action": [
16
        "s3:GetObject",
17
        "s3:ListBucket"
18
      ],
19
      "Resource": [
20
        "arn:aws:s3:::my-data-bucket",
21
        "arn:aws:s3:::my-data-bucket/*"
22
      ]
23
    }
24
  ]
25
}

1
-- Create view with restricted columns
2
CREATE VIEW public_user_data AS
3
SELECT user_id,
4
       name,
5
       email,
6
       created_date
7
FROM users
8
-- Exclude sensitive columns like SSN, phone
9
WHERE status = 'active';

1
# Glue crawler to discover schema
2
import boto3
3

4
glue = boto3.client('glue')
5
glue.create_crawler(
6
    Name='my-data-crawler',
7
    Role='AWSGlueServiceRole',
8
    DatabaseName='my_database',
9
    Targets={
10
        'S3Targets': [
11
            {
12
                'Path': 's3://my-bucket/data/',
13
                'Exclusions': ['**/_SUCCESS', '**/.DS_Store']
14
            }
15
        ]
16
    },
17
    Schedule='cron(0 12 * * ? *)'  # Daily at noon
18
)

1
-- Use MSCK REPAIR to fix partition metadata
2
MSCK REPAIR TABLE my_partitioned_table;
3

4
-- Or add partitions manually
5
ALTER TABLE my_table ADD PARTITION (year=2024, month=1, day=1)
6
LOCATION 's3://my-bucket/year=2024/month=01/day=01/';