How do I query CloudWatch Logs exports in Athena?

Short description

To analyze CloudWatch Logs using Athena, first export your logs to S3 using a date-based partitioning structure. Partitioning is crucial for performance as it allows Athena to scan only the relevant data instead of the entire dataset. You can export logs manually through the console or use a Python script for automation. The logs can then be queried using standard SQL in Athena.

Resolution

Understanding Partitioning

CloudWatch Logs exports generate unpartitioned files by default. Without partitioning:

• Athena must scan all log data for every query
• Queries become expensive and slow as data grows
• You pay for scanning unnecessary data

By organizing logs into partitions by date:

• Queries can skip irrelevant data partitions
• Query performance improves significantly
• Costs are reduced as you only scan needed partitions

Step 1: Export CloudWatch Logs to S3

---

Option 1: Manual Export from Console

Before exporting logs, follow the instructions in Enabling CloudWatch Logs to Export to S3 to set up the required bucket permissions. Without proper permissions, the export task will fail.

1. Open the CloudWatch console
2. In the navigation pane, select Logs > Log groups
3. Select the log group you want to export
4. From the Actions menu, choose Export data to Amazon S3
5. In the export dialog:
   • Set the time range for exactly one day (e.g., from 2025-01-01 00:00:00 to 2025-01-01 23:59:59)
   • Enter your S3 bucket name
   • For the prefix, use: {year}/{month}/{day} (do not include a trailing slash)
     • Example: For January 1, 2025, use: 2025/01/01
6. Choose Export data
7. Monitor the export task status in the Export tasks tab

When exporting manually, ensure your time range covers exactly one day and matches your S3 prefix structure. For example, if your prefix is 2025/01/01, your time range should only include data from January 1, 2025. For exporting multiple days of data, it's recommended to use the automated Python script instead of manual exports to ensure correct partitioning.

Option 2: Automated Export Using Python

Copy the following Python script and save it as export_cloudwatch_logs.py. Before running, modify the script variables at the top according to your needs:

import boto3
import time
from datetime import datetime, timedelta
import re

# ====== MODIFY THESE VARIABLES ======
# The name of your CloudWatch Logs log group
log_group = "/aws/lambda/my-function"

# Optional: Specify a log stream prefix to export only matching streams
# Leave as empty string "" to export all streams
stream_prefix = ""

# The start date for log export (Year, Month, Day)
start_date = datetime(2025, 9, 1)

# The end date for log export (Year, Month, Day)
end_date = datetime(2025, 9, 12)

# Your S3 bucket and prefix in the format s3://bucket-name/prefix
s3_location = "s3://my-log-bucket/cloudwatch-logs"
# ====================================

def wait_for_export_task(client, task_id):
    while True:
        response = client.describe_export_tasks(taskId=task_id)
        status = response['exportTasks'][0]['status']['code']
        if status in ['COMPLETED', 'FAILED', 'CANCELLED']:
            return status
        time.sleep(10)

def extract_bucket_and_prefix(s3_location):
    match = re.match(r's3://([^/]+)/?(.*)/?$', s3_location)
    if not match:
        raise ValueError("Invalid S3 location format")
    bucket = match.group(1)
    prefix = match.group(2)
    return bucket, prefix.rstrip('/')

def export_logs(log_group_name, start_date, end_date, s3_location, stream_prefix=""):
    client = boto3.client('logs')
    
    s3_bucket, s3_prefix_base = extract_bucket_and_prefix(s3_location)
    
    current_date = start_date
    while current_date <= end_date:
        # Create S3 prefix using date-based partitioning
        date_prefix = f"{current_date.year}/{current_date.month:02d}/{current_date.day:02d}"
        s3_prefix = f"{s3_prefix_base}/{date_prefix}".lstrip('/')
        
        # Create export task
        export_params = {
            'logGroupName': log_group_name,
            'fromTime': int(current_date.timestamp() * 1000),
            'to': int((current_date + timedelta(days=1)).timestamp() * 1000),
            'destination': s3_bucket,
            'destinationPrefix': s3_prefix
        }
        
        # Add stream prefix if specified
        if stream_prefix:
            export_params['logStreamNamePrefix'] = stream_prefix
        
        response = client.create_export_task(**export_params)
        
        task_id = response['taskId']
        print(f"Started export task {task_id} for {current_date.date()}")
        if stream_prefix:
            print(f"Exporting streams with prefix: {stream_prefix}")
        
        # Wait for task completion
        status = wait_for_export_task(client, task_id)
        print(f"Export task {task_id} finished with status: {status}")
        
        current_date += timedelta(days=1)

if __name__ == "__main__":
    export_logs(log_group, start_date, end_date, s3_location, stream_prefix)

You can run the script either locally using Python and AWS CLI with configured credentials, or in AWS CloudShell which comes with pre-configured credentials. For CloudShell, simply upload the script using Actions > Upload file, then run python export_cloudwatch_logs.py. For local execution, ensure you have Python and boto3 installed, then run the same command.

Step 2: Create Athena Table

---

Before executing this CREATE TABLE statement:

1. Replace s3://my-bucket/cloudwatch-logs in both the LOCATION and storage.location.template with your S3 bucket path
2. Modify the start date in projection.log_date.range to match your earliest log date
   • Format: YYYY/MM/DD,NOW
   • Example: 2025/01/01,NOW for logs starting from January 1, 2025
   • The NOW keyword automatically handles future dates

CREATE EXTERNAL TABLE cloudwatch_logs (
    log_message string
)
PARTITIONED BY (
   `log_date` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'input.regex' = '(.*)'
)
LOCATION 's3://my-bucket/cloudwatch-logs'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.log_date.format'='yyyy/MM/dd', 
  'projection.log_date.interval'='1', 
  'projection.log_date.interval.unit'='DAYS', 
  'projection.log_date.range'='2025/01/01,NOW', 
  'projection.log_date.type'='date', 
  'storage.location.template'='s3://my-bucket/cloudwatch-logs/${log_date}'
)

Step 3: Query Your Logs

---

Example queries demonstrating partition filtering:

-- Query logs for a specific day
SELECT log_message
FROM cloudwatch_logs
WHERE log_date = '2025/09/12'
AND log_message LIKE '%Error%';

-- Count logs per day in a date range
SELECT log_date, COUNT(*) as log_count
FROM cloudwatch_logs
WHERE log_date BETWEEN '2025/09/01' AND '2025/09/12'
GROUP BY log_date
ORDER BY log_date;

-- Query last 7 days of logs
SELECT log_date, COUNT(*) as log_count
FROM cloudwatch_logs
WHERE log_date >= date_format(current_date - interval '7' day, '%Y/%m/%d')
GROUP BY log_date
ORDER BY log_date;

Notes

• Only one export task can run at a time per account
• Export tasks may take several minutes to complete
• Export tasks time out after 24 hours. If your export tasks are timing out, reduce the time range when you create the export task.
• Use partition filtering in WHERE clauses to optimize query performance

Related information

• Exporting log data to Amazon S3
• Using partition projection with Amazon Athena
• Working with Regex SerDe in Athena