Architecting Activity Log Analytics on S3 + Athena — Without Glue

A Step-by-Step Guide with Code to Store, Partition, and Query Logs Efficiently — Plus Our Migration Story from MongoDB to S3 + Athena

Why This Matters

When building any modern cloud-native application — whether it’s a SaaS platform, mobile backend, or analytics dashboard — activity logging is critical. But logging isn’t just about writing files. You need:

• Structured, queryable logs
• Cost-efficient storage
• Scalable analytics

That’s where Amazon S3 + Athena shines. Even better — you can do it without AWS Glue, making it lightweight, fast to prototype, and free of crawler overhead.

Architecture Overview

Here’s what we’ll build:

• Store activity logs in S3 as Parquet files for performance
• Partition data by main_event_id, year, month, and day
• Query logs efficiently using Amazon Athena
• Skip AWS Glue — define schema manually
• Everything is easily automatable with Python + Boto3

What Are Activity Logs?

Here’s a sample log entry:

{
  "fk_company_id": 101,
  "fk_project_id": 201,
  "done_by_id": 1,
  "done_by_email": "alice@example.com",
  "done_by_name": "Alice",
  "main_event_id": 15,
  "action_name": "Update",
  "action_date": "2025-07-04T10:15:30Z",
  "sub_event_id": 51,
  "log_details": "{\"msg\": \"Capital adjusted successfully.\"}"
}

Why Use Parquet Instead of JSON?

When storing activity logs, you might be tempted to just dump raw JSON files into S3. It’s simple. It works. But it doesn’t scale well.

Real Impact Example

Let’s say you store 1 million log records:

In JSON, this might be 1.5 GB of text

In Parquet, it could be as small as 150 MB

With Athena pricing at $5 per TB scanned, using Parquet can save 90%+ in cost per query!

Bonus: Column Pruning in Action

If your JSON has 20 fields but your Athena query selects only 2 fields:

• Parquet will read only those 2 columns
• JSON will scan the entire 1.5 GB file regardless

That’s not just slower — it’s expensive.

Here’s a detailed comparison of why Parquet is preferred over JSON for S3 logs, explained in bullet points:

• Columnar Storage for Faster Queries:
  Parquet stores data in a columnar format, meaning only the columns required by a query are read from storage. JSON is row-based, so every field in every record must be scanned, which slows down queries and increases costs.
• Significant Cost Savings with Athena:
  Since Athena pricing is based on the amount of data scanned, Parquet’s ability to read only required columns and compressed data drastically reduces the number of bytes processed, resulting in much lower query costs compared to JSON.
• Built-in Compression and Encoding:
  Parquet inherently supports highly efficient compression (like Snappy, Gzip) and data encoding, reducing storage size by up to 10x compared to raw JSON. JSON is plain text and less compact.
• Schema Evolution Support:
  Parquet supports rich schema definitions and data types (e.g., INT, FLOAT, TIMESTAMP), making it easier to query and integrate with analytics tools. JSON is schemaless, requiring constant parsing and type inference during queries, which is slower.
• Better Performance with Partitioned Data:
  When combined with S3 partitioning, Parquet’s columnar layout allows Athena to skip irrelevant partitions and columns, making queries faster. JSON files still need to be fully parsed, even within partitions.
• Compatibility with Data Analytics Tools:
  Parquet is widely used in big data ecosystems (Spark, Presto, Hive, EMR), ensuring smooth integration for ETL and analytics pipelines. JSON is less optimized for these environments.
• Reduced Network I/O and Memory Footprint:
  Smaller, compressed Parquet files mean less data transfer from S3 to Athena and less memory usage during queries, improving performance and reducing resource consumption.

Choose Parquet for Logs If You:

• Want faster query performance
• Need lower storage and query costs
• Prefer schema enforcement
• Are building any kind of data lake or analytics platform

Step 1: Prepare Logs in Parquet Format

Parquet is columnar, compressed, and far more efficient than JSON.

Convert Logs Using Python

import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq

# Sample logs
logs = [
    {
        "fk_company_id": 101,
        "fk_project_id": 201,
        "done_by_id": 1,
        "done_by_email": "alice@example.com",
        "done_by_name": "Alice",
        "main_event_id": 15,
        "action_name": "Update",
        "action_date": "2025-07-04T10:15:30Z",
        "sub_event_id": 51,
        "log_details": '{"msg": "Capital adjusted successfully."}'
    }
]
# DataFrame + schema enforcement
df = pd.DataFrame(logs)
df["action_date"] = pd.to_datetime(df["action_date"])
df = df.astype({
    "fk_company_id": "int32",
    "fk_project_id": "int32",
    "done_by_id": "int32",
    "sub_event_id": "int32",
    "main_event_id": "int32"
})
# Save to Parquet
table = pa.Table.from_pandas(df)
pq.write_table(table, "log.parquet", compression="snappy")

Step 2: Upload to S3 with Partitioned Folders

Create this folder path:

s3://your-bucket-name/audit_logs/
 └── main_event_id=15/
     └── year=2025/
         └── month=07/
             └── day=04/
                 └── log.parquet

Upload with AWS CLI:

aws s3 cp log.parquet s3://your-bucket-name/audit_logs/main_event_id=15/year=2025/month=07/day=04/

Step 3: Create Athena Table (No Glue!)

Run this DDL in Athena:

CREATE EXTERNAL TABLE IF NOT EXISTS audit_logs (
  fk_company_id INT,
  fk_project_id INT,
  done_by_id INT,
  done_by_email STRING,
  done_by_name STRING,
  action_name STRING,
  action_date TIMESTAMP,
  sub_event_id INT,
  log_details STRING
)
PARTITIONED BY (
  main_event_id INT,
  year STRING,
  month STRING,
  day STRING
)
STORED AS PARQUET
LOCATION 's3://your-bucket-name/audit_logs/'
TBLPROPERTIES ('parquet.compress'='SNAPPY');

Step 4: Add Partition

When you upload a new day of logs or a new event ID, add that as a partition:

ALTER TABLE audit_logs ADD IF NOT EXISTS 
PARTITION (main_event_id=15, year='2025', month='07', day='04')
LOCATION 's3://your-bucket-name/audit_logs/main_event_id=15/year=2025/month=07/day=04/';

Step 5: Query Logs with SQL

Now you can run blazing-fast SQL queries on raw log data:

SELECT * FROM audit_logs
WHERE main_event_id = 15
  AND year = '2025'
  AND month = '07'
  AND day = '04';

Bonus: Automate Partitioning with Python

import boto3
athena = boto3.client('athena')
def add_partition(event_id, year, month, day):
    location = f"s3://your-bucket-name/audit_logs/main_event_id={event_id}/year={year}/month={month}/day={day}/"
    sql = f"""
    ALTER TABLE audit_logs ADD IF NOT EXISTS 
    PARTITION (main_event_id={event_id}, year='{year}', month='{month}', day='{day}')
    LOCATION '{location}';
    """
    athena.start_query_execution(
        QueryString=sql,
        QueryExecutionContext={"Database": "default"},
        ResultConfiguration={"OutputLocation": "s3://your-query-results/"}
    )

Migration from MongoDB to Amazon S3 + Athena

Why We Migrated:
We transitioned from MongoDB to an S3 + Athena architecture to significantly reduce infrastructure costs and gain more flexibility for analytics. The pay-per-query model and serverless architecture of Athena allow us to handle large datasets without managing complex database infrastructure.

Cost Impact

• By migrating from MongoDB to S3 + Athena, we have observed a cost reduction of approximately 60–70% on monthly database and storage expenses.
• MongoDB Costs: Always-on instances and storage for large datasets incurred fixed monthly charges, regardless of query frequency.
• S3 + Athena Costs: With low-cost S3 storage and a pay-as-you-go model for queries, costs scale only when data is actively queried.
• We also eliminated expenses for indexing, replication servers, and backup clusters, which further contributed to cost savings.

Quick Comparison Table

Key Differences

1. Cost Efficiency

MongoDB:

• Requires dedicated instances (self-hosted or Atlas) with ongoing compute and storage costs.
• Costs scale with dataset size, indexes, and performance requirements.
• Continuous costs even for infrequent queries due to always-on infrastructure.

S3 + Athena:

• Pay-per-query model — you only pay when running queries.
• S3 provides low-cost, durable object storage (11 nines durability) with no provisioning overhead.
• No server costs; infrastructure is fully managed by AWS, reducing operational expenses.

2. Query Engine and Data Access

MongoDB:

• Document-based database, optimized for fast operational queries (CRUD).
• Requires indexing strategies to ensure performance for large datasets.

Athena (with S3):

• Serverless, SQL-based query engine that works directly on data stored in S3.
• Ideal for ad-hoc analytics, reporting, and data exploration without data movement.
• Uses Presto/Trino under the hood, with support for various file formats (Parquet, ORC, JSON, CSV).

3. Scalability

MongoDB

• Horizontal scaling is possible (sharding), but involves significant management complexity and costs.

S3 + Athena:

• Automatically scales to handle large-scale analytics across petabytes of data without manual provisioning.

4. Maintenance & Operations

MongoDB:

• Requires backups, replication, performance tuning, and instance scaling.
• Higher operational overhead for large-scale deployments.

S3 + Athena

• Fully managed, no server maintenance, automatic high availability, and no infrastructure tuning.

5. Use Cases

• MongoDB: Ideal for real-time transactional workloads.
• S3 + Athena: Best suited for batch analytics, BI reporting, and historical data queries.

The Takeaway

By moving to S3 + Athena, we’ve shifted from managing infrastructure to focusing on insights and analytics. This approach gives us scalability on demand, minimal maintenance, and major cost savings, making it the right fit for our current data strategy.

Final Thoughts

This approach gives you:

Scalable log analytics

Cost savings with Parquet & partitioning

Flexibility (no Glue dependency)

Fast queries via Athena

You can easily plug this into real-time pipelines using Lambda or Airflow. For production systems, consider lifecycle policies to auto-expire S3 data.

Contributors

This article was written by Mousumi Bakshi (Senior Solutions Architect)

Let’s Build the Future Together!
For your Generative AI solutions and project development needs, get in touch with MSS — we’re here to help bring your ideas to life.

👉 Follow us on LinkedIn and Medium for weekly tech insights and practical tips to supercharge your development journey!