TECH **AWS S3 Select, Athena, and Glue: Zero-Fluff, Hands-On Study Guide**

AWS S3 Select, Athena, and Glue: Zero-Fluff, Hands-On Study Guide

(For AWS Solutions Architect – Associate)

1. What This Is & Why It Matters

You’re a cloud engineer at a mid-sized e-commerce company. Your data team dumps terabytes of raw logs, CSV files, and JSON exports into S3 every day. Marketing wants to run ad-hoc queries on customer behavior. Finance needs to reconcile transactions. The BI team is screaming because their SQL queries take hours to run on Redshift.

This is where S3 Select, Athena, and Glue come in.

• S3 Select lets you query data inside S3 objects without downloading the entire file (like searching a book without opening it).
• Athena is a serverless SQL engine that queries data directly in S3 (no database setup, no ETL pipelines).
• Glue is a serverless ETL service that crawls your data, infers schemas, and prepares it for Athena (or Redshift, or EMR).

Why this matters in production:
- Cost: Athena charges $5 per TB scanned (vs. Redshift’s $0.25–$1.50/hour for a cluster).
- Speed: S3 Select can filter a 10GB file in seconds (vs. downloading the whole thing).
- Flexibility: No need to pre-load data into a database—query it where it lives.
- Scalability: Athena auto-scales to petabytes of data (no manual sharding).

Real-world scenario:
You inherit a legacy data lake in S3 with millions of unstructured JSON and CSV files. Your boss says:

"We need to run analytics on this data, but we can’t afford to move it into Redshift. Also, we need results by EOD."

Your options:
1. Write a custom Python script to download, parse, and query (slow, error-prone, expensive).
2. Use Athena + Glue to query the data in place (fast, serverless, cost-effective).

This guide will show you how to do #2—the right way.

2. Core Concepts & Components

? S3 Select

• What it is: A feature that lets you run SQL-like queries on individual S3 objects (CSV, JSON, Parquet) without downloading the entire file.
• Production insight: If you’re filtering large files (e.g., logs, exports), S3 Select can reduce query time by 80% and cost by 90% vs. downloading the whole file.
• Limitation: Only works on one file at a time (no joins, no aggregations across files).

? Athena

• What it is: A serverless SQL query engine that runs on data stored in S3 (powered by Presto/Trino).
• Production insight: Athena is cheap for sporadic queries but expensive for frequent scans (optimize with partitioning and columnar formats like Parquet).
• Pricing: $5 per TB scanned (rounded up to the nearest MB). A 10GB query costs $0.05.
• Performance: Queries run in seconds to minutes, depending on data size and format.

? Glue Data Catalog

• What it is: A central metadata repository that stores table definitions, schemas, and partitions for Athena, Redshift, and EMR.
• Production insight: Without Glue, Athena won’t know your schema—you’ll get errors like HIVE_METASTORE_ERROR.
• Glue Crawlers: Auto-discover schemas by scanning S3 (but be careful with nested JSON—it can infer wrong types).

? Glue ETL (Jobs & Workflows)

• What it is: A serverless ETL service that transforms data (e.g., JSON → Parquet, CSV → partitioned tables).
• Production insight: Glue jobs cost $0.44/hour (vs. EMR at $0.10–$0.50/hour per node). Use for batch transformations, not real-time.
• Spark under the hood: Glue runs Apache Spark, so you can write PySpark or Scala scripts.

? Partitioning

• What it is: Organizing data in S3 by key prefixes (e.g., s3://bucket/logs/year=2023/month=01/day=15/).
• Production insight: Partitioning reduces Athena query costs by 90% (only scans relevant folders).
• Example: Querying WHERE year=2023 AND month=01 only scans s3://bucket/logs/year=2023/month=01/.

? Columnar Formats (Parquet, ORC)

• What it is: File formats optimized for analytics (vs. row-based formats like CSV).
• Production insight: Parquet can reduce Athena costs by 70% (only reads columns you query).
• Tradeoff: Parquet is slower to write but faster to query.

? Workgroups (Athena)

• What it is: A way to isolate queries by team (e.g., marketing, finance) and set cost controls.
• Production insight: Without workgroups, one team can run a $1,000 query and blow your budget.

? Query Result Location

• What it is: Athena stores query results in S3 (default: aws-athena-query-results-<account-id>-<region>).
• Production insight: If you don’t set a custom result location, Athena will fail silently (no error, just no results).

3. Step-by-Step Hands-On: Querying S3 Data with Athena + Glue

Prerequisites

✅ AWS account with admin IAM permissions (or at least AthenaFullAccess, GlueFullAccess, S3FullAccess).
✅ A sample dataset in S3 (we’ll use a public dataset: s3://aws-athena-query-results-us-east-1/samples/parquet/).
✅ AWS CLI installed and configured (aws configure).

Step 1: Set Up an S3 Bucket for Query Results

Athena requires a bucket to store query results. If you don’t set one, queries will fail.

# Create a bucket (replace <your-account-id> and <region>)
aws s3 mb s3://athena-query-results-<your-account-id>-<region> --region <region>

# Example:
aws s3 mb s3://athena-query-results-123456789012-us-east-1 --region us-east-1

Verify:

aws s3 ls s3://athena-query-results-<your-account-id>-<region>/

Step 2: Create a Glue Crawler to Infer Schema

Glue needs to know your data’s schema before Athena can query it.

1. Go to AWS Glue Console → Crawlers → Add crawler.
2. Name: athena-sample-crawler
3. Data source: S3 → s3://aws-athena-query-results-us-east-1/samples/parquet/
4. IAM Role: Create a new role (e.g., AWSGlueServiceRole-AthenaSample).
5. Schedule: Run on demand.
6. Database: Create a new database (e.g., athena_samples).
7. Run the crawler (takes ~1–2 minutes).

Verify:
- Go to Glue Data Catalog → Databases → athena_samples → Tables.
- You should see a table named parquet (or similar).

Step 3: Query Data in Athena

Now that Glue has inferred the schema, you can query the data in Athena.

1. Go to Athena Console → Query Editor.
2. Select the database (athena_samples).
3. Run a test query:
   sql
SELECT * FROM "athena_samples"."parquet" LIMIT 10;
4. Set the query result location (if not already set):
5. Click Settings → Query result location → s3://athena-query-results-<your-account-id>-<region>/

Expected output:
A table with 10 rows from the sample dataset.

Step 4: Optimize with Partitioning & Parquet

Let’s simulate a real-world scenario: You have 100GB of CSV logs in S3, and queries are slow/costly.

Problem:

• Data is in CSV format (row-based, inefficient for analytics).
• No partitioning → Athena scans all files for every query.

Solution:

1. Convert CSV → Parquet (columnar format).
2. Partition by date (e.g., s3://logs/year=2023/month=01/day=15/).

Step-by-Step:

1. Download a sample CSV (e.g., NYC Taxi Trip Data).
2. Upload to S3:
   bash
aws s3 cp yellow_tripdata_2023-01.csv s3://your-bucket/raw/csv/
3. Create a Glue ETL job to convert CSV → Parquet and partition by date:
4. Go to Glue Console → Jobs → Add job.
5. Name: csv-to-parquet
6. IAM Role: AWSGlueServiceRole-AthenaSample
7. Type: Spark
8. Glue version: Glue 4.0

9. Script:
   ```python
   import sys
   from awsglue.transforms import *
   from awsglue.utils import getResolvedOptions
   from pyspark.context import SparkContext
   from awsglue.context import GlueContext
   from awsglue.job import Job

   sc = SparkContext() glueContext = GlueContext(sc) spark = glueContext.spark_session job = Job(glueContext)

   # Read CSV df = spark.read.csv("s3://your-bucket/raw/csv/", header=True, inferSchema=True)

   # Write as Parquet, partitioned by pickup date df.write.partitionBy("tpep_pickup_datetime").parquet("s3://your-bucket/processed/parquet/")

   job.commit() 4. Run the job (takes ~5–10 minutes for 1GB of data).
5. Create a new Glue crawler to infer the schema of the Parquet data.
6. Query in Athena:sql
   -- Only scans the partition for Jan 1, 2023
   SELECT * FROM "processed"."parquet"
   WHERE tpep_pickup_datetime = '2023-01-01'
   LIMIT 10;
   ```

Result:
- Before: Query scans 100GB of CSV → $50 cost.
- After: Query scans 1GB of Parquet → $0.50 cost.

4. ? Production-Ready Best Practices

? Security

• IAM Least Privilege:
• Athena: Only grant athena:StartQueryExecution and athena:GetQueryResults.
• Glue: Restrict crawlers to specific S3 paths (not s3:*).
• S3: Block public access on query result buckets.
• Encryption:
• Enable SSE-S3 or SSE-KMS for query results.
• Use Glue Data Catalog encryption (AWS KMS).
• VPC Endpoints:
• If querying from a VPC, use Athena VPC endpoints to avoid NAT costs.

? Cost Optimization

• Partition your data (e.g., by year/month/day).
• Use columnar formats (Parquet > ORC > CSV).
• Compress data (Snappy for Parquet, Gzip for CSV).
• Set query limits in Athena workgroups (e.g., max 1TB per query).
• Use S3 Select for filtering large files before Athena.

? Reliability & Maintainability

• Naming conventions:
• S3 paths: s3://<company>-<env>-<purpose>/<data-type>/<partition-key>=<value>/
• Example: s3://acme-prod-logs/clickstream/year=2023/month=01/day=15/
• Tagging:
• Tag S3 buckets with Environment=prod, CostCenter=marketing.
• Idempotency:
• Glue jobs should skip existing files (use --enable-continuation).
• Monitoring:
• CloudWatch alarms for Athena query failures (Athena.QueryFailed).

?️ Observability

• Athena Metrics to Monitor:
• Athena.QueryQueueTime (high = too many concurrent queries).
• Athena.QueryExecutionTime (slow queries).
• Athena.DataScanned (cost driver).
• Glue Metrics:
• Glue.JobRunTime (long-running jobs).
• Glue.CrawlerFailed (schema inference issues).
• Logs:
• Athena query history (stored in S3).
• Glue job logs (CloudWatch).

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus (AWS SAA)

Typical Question Patterns

1. "Which service lets you query data in S3 without loading it into a database?"
2. ✅ Athena (serverless SQL on S3).
3. ❌ Redshift (requires loading data).

4. ❌ EMR (requires a cluster).

5. "How can you reduce Athena query costs by 90%?"

6. ✅ Partition data + use Parquet.
7. ❌ Use CSV (row-based, inefficient).

8. ❌ Increase Redshift cluster size (irrelevant).

9. "What’s the cheapest way to filter a 10GB CSV file in S3?"

10. ✅ S3 Select ($0.002/GB scanned).
11. ❌ Athena ($5/TB scanned).

12. ❌ Download and filter locally (slow, expensive).

13. "You need to run ETL on S3 data before querying with Athena. Which service?"

14. ✅ Glue ETL (serverless, integrates with Athena).
15. ❌ EMR (overkill for simple ETL).
16. ❌ Lambda (not designed for large-scale ETL).

Key ⚠️ Trap Distinctions

Common Scenario-Based Question

"You need to analyze 50TB of log data stored in S3. The data is rarely accessed, and queries are ad-hoc. Which solution is the most cost-effective?"

Options:
A) Load into Redshift and run queries.
B) Use Athena with Parquet and partitioning.
C) Spin up an EMR cluster and run Spark jobs.
D) Download the data and analyze locally.

Correct Answer: B
- Why?
- Athena is serverless (no cluster costs).
- Parquet + partitioning minimizes data scanned (low cost).
- Redshift (A) is expensive for infrequent queries.
- EMR (C) is overkill for ad-hoc SQL.
- Downloading (D) is slow and impractical for 50TB.

7. ? Hands-On Challenge (With Solution)

Challenge:

You have a 1GB CSV file in S3 (s3://your-bucket/data/sales.csv). It has columns: order_id, customer_id, amount, order_date.

Task:
1. Query only the top 10 highest-value orders (amount) using S3 Select.
2. Then, use Athena to find the total sales per customer (group by customer_id).

Constraints:
- Do not download the file.
- Use least privilege IAM roles.

Solution:

1. S3 Select (Top 10 Orders)

aws s3api select-object-content \
  --bucket your-bucket \
  --key data/sales.csv \
  --expression "SELECT * FROM s3object s ORDER BY s.amount DESC LIMIT 10" \
  --expression-type "SQL" \
  --input-serialization '{"CSV": {"FileHeaderInfo": "USE", "FieldDelimiter": ","}}' \
  --output-serialization '{"CSV": {}}' \
  "output.csv"

Why it works:
- S3 Select streams only the filtered rows (no full download).
- ORDER BY amount DESC LIMIT 10 returns the top 10 highest-value orders.

2. Athena (Total Sales per Customer)

1. Create a Glue crawler to infer the schema of s3://your-bucket/data/sales.csv.
2. Run in Athena:
   sql
SELECT customer_id, SUM(amount) as total_sales
FROM "your_database"."sales"
GROUP BY customer_id
ORDER BY total_sales DESC; Why it works:
3. Athena queries the data in place (no ETL).
4. SUM(amount) aggregates sales per customer.

8. ? Rapid-Reference Crib