Amazon Athena and AWS Glue: Serverless Analytics and ETL – SAP-C02 Study Guide

Purpose of This Lesson

Amazon Athena and AWS Glue are complementary services for building serverless analytics workflows. Athena runs SQL queries against data, primarily in Amazon S3, while AWS Glue discovers, catalogs, transforms, and prepares data for analytics.

The key architectural relationship is:

• Amazon S3 stores the data.
• AWS Glue Crawlers discover schemas and populate the AWS Glue Data Catalog.
• Amazon Athena uses catalog metadata to expose data as queryable tables.
• AWS Glue ETL jobs transform data between sources and targets.

Key Concepts

Amazon Athena

Amazon Athena is a serverless interactive query service. It allows SQL queries against data without provisioning or managing database servers. Athena is commonly used to query files stored in Amazon S3.

Supported data formats from this lesson include:

• CSV
• TSV
• JSON
• Apache Parquet
• Apache ORC

Athena can also query certain non-S3 data sources through connectors. For example, querying CloudWatch Logs through Athena requires a Lambda-based connector that connects Athena to the external source and maps the source data into queryable tables.

Athena relies on table definitions and schemas. These can be created or maintained using the AWS Glue Data Catalog, which stores metadata about databases, tables, schemas, and data locations.

AWS Glue

AWS Glue is a fully managed extract, transform, and load (ETL) service. It is designed to prepare data for analytics and can process data using a managed, distributed Apache Spark environment.

Glue can work with data in several types of systems, including:

• Data lakes such as Amazon S3
• Data warehouses such as Amazon Redshift
• Data stores such as Amazon RDS or data hosted on Amazon EC2

Glue has two especially important functions:

1. Data discovery and cataloging: Glue Crawlers inspect data stores and create or update table definitions in the Glue Data Catalog.
2. ETL processing: Glue jobs use catalog tables as sources and targets while transforming and moving data.

Glue Crawlers

A Glue Crawler examines one or more data stores, infers table structures, and populates or updates the Glue Data Catalog. A crawler can crawl multiple data stores during a single run.

After a crawler completes, its tables can be used by:

• Athena as query sources
• Glue ETL jobs as sources or targets
• Other services that integrate with the Glue Data Catalog

A crawler discovers metadata; it does not replace the underlying data or perform the main transformation workload.

Athena Query Performance Optimization

Athena performance depends heavily on how data is organized in S3. Important optimization techniques include:

• Partition data: Organize data by commonly filtered attributes, such as date, region, or account.
• Use columnar formats: Apache Parquet and ORC allow queries to read only the required columns.
• Compress data: Compression reduces the amount of data that must be read.
• Use appropriate file sizes: Avoid generating excessive numbers of very small files or poorly sized objects.
• Bucket data within partitions when appropriate: Bucketing can organize records by a field within a partition.
• Select only required columns: Avoid SELECT * when only a subset of fields is needed.
• Optimize ORDER BY and GROUP BY: These operations can require substantial processing and should be designed carefully.
• Use approximate functions where exact precision is unnecessary: Approximation can reduce query cost and processing requirements for suitable analytical use cases.

Partitioning, compression, and columnar formats are particularly important exam clues when the requirement is to improve Athena query efficiency.

Exam-Relevant Takeaways

• Choose Amazon Athena when you need serverless SQL queries over data stored in Amazon S3.
• Choose AWS Glue when you need managed ETL, data preparation, schema discovery, or a central metadata catalog.
• Athena and Glue commonly work together: Glue creates or maintains catalog metadata, and Athena queries the cataloged data.
• A Glue Crawler discovers schemas and creates or updates catalog tables; it is not primarily an ETL transformation engine.
• Glue ETL jobs run in a managed, distributed Apache Spark environment.
• Athena can access some external data sources using connectors, often involving AWS Lambda.
• Apache Parquet and ORC are generally more suitable than raw CSV or JSON for analytical workloads because they are columnar formats.
• Athena query performance can be improved by partitioning, compression, columnar storage, suitable file sizing, and selecting only needed columns.
• A Glue Data Catalog contains metadata and schema definitions; it does not serve as the primary storage location for the data itself.

Architecture Decision Guide

Requirement	Appropriate service or feature	Reason
Run ad hoc SQL against files in S3	Amazon Athena	Serverless querying without provisioning database infrastructure
Maintain schemas and table metadata	AWS Glue Data Catalog	Central metadata repository for data sources and tables
Automatically discover schemas in data stores	AWS Glue Crawler	Creates or updates catalog tables from discovered metadata
Transform and prepare data for analytics	AWS Glue ETL	Managed ETL processing using a distributed Spark environment
Query CloudWatch Logs through Athena	Athena data source connector with Lambda	Lambda connects Athena to the external data source
Improve Athena scans for large analytical datasets	S3 partitioning, compression, Parquet, or ORC	Reduces the data read and processed by queries
Move or transform data between S3, Redshift, RDS, or EC2-based stores	AWS Glue ETL	Supports managed data integration across these source and target types

Common Exam Traps

• Confusing Athena with a database: Athena queries files and metadata; it is not a conventional database requiring provisioned tables that store the data.
• Assuming Glue stores the data: The Glue Data Catalog stores metadata and schemas. The actual data may remain in S3, Redshift, RDS, or another data store.
• Using Glue when only SQL querying is required: If data is already structured in S3 and only ad hoc SQL analysis is needed, Athena may be sufficient.
• Using Athena as the ETL engine: Athena is primarily a query service. Use Glue ETL jobs when data must be extracted, transformed, and loaded.
• Treating a crawler as a transformation job: Crawlers discover and catalog schemas; Glue ETL jobs perform transformations.
• Ignoring file format and layout: Querying large, unpartitioned CSV or JSON datasets can be inefficient. Partitioning and columnar formats are important optimization clues.
• Assuming every external source is queried directly: External Athena data sources may require connectors, including Lambda-based integrations.
• Selecting every column by default: Queries should retrieve only the columns required by the workload, particularly when optimizing analytical scans.

Real-World Engineer Notes

• Define a consistent S3 layout before configuring crawlers. Partition paths should reflect the filters most often used by analysts and applications.
• Prefer Parquet or ORC for curated analytical data, especially after an ETL process converts raw CSV or JSON files.
• Separate raw, transformed, and curated datasets so that Glue jobs can produce controlled outputs without altering the original source data.
• Treat the Glue Data Catalog as an interface contract. Changes to schemas, partitions, and table locations can affect Athena queries and downstream ETL jobs.
• Crawlers are useful for discovery, but production pipelines may need explicit schema and partition management when strict control and predictable changes are required.
• Avoid creating a very large number of small S3 objects. File layout affects analytical query performance and operational complexity.
• Use Athena for flexible, intermittent analysis and Glue for repeatable data preparation workflows. The services solve different parts of the analytics pipeline.

Quick Reference Summary

• Athena: Serverless SQL queries, primarily against S3.
• Glue Data Catalog: Metadata and schema repository.
• Glue Crawler: Discovers data structures and creates or updates catalog tables.
• Glue ETL: Managed extract, transform, and load processing using Apache Spark.
• Typical flow: Data in S3 → Glue Crawler → Glue Data Catalog → Athena queries.
• Performance priorities: Partition data, use compression, prefer Parquet or ORC, choose suitable file sizes, and query only required columns.
• External Athena sources: May require connectors and Lambda integration.

Flashcards

1. Q: What is Amazon Athena primarily used for?

A: Running serverless SQL queries against data, especially files stored in Amazon S3.

1. Q: What is the purpose of the AWS Glue Data Catalog?

A: It stores metadata, schemas, database definitions, and table information used by analytics services such as Athena.

1. Q: What does a Glue Crawler do?

A: It examines data stores, discovers schemas, and creates or updates tables in the Glue Data Catalog.

1. Q: Does a Glue Crawler transform the underlying data?

A: No. Crawlers discover and catalog metadata; Glue ETL jobs perform data transformations.

1. Q: What type of processing environment does Glue ETL use?

A: A fully managed, distributed Apache Spark environment.

1. Q: Which data formats are suitable for Athena according to this lesson?

A: CSV, TSV, JSON, Apache Parquet, and Apache ORC.

1. Q: Why are Parquet and ORC useful for Athena workloads?

A: They are columnar formats that can reduce the amount of data read when a query selects only certain columns.

1. Q: How can S3 data layout improve Athena performance?

A: Partition the data, use suitable file sizes, compress it, and use columnar formats.

1. Q: How can Athena query some external data sources such as CloudWatch Logs?

A: By using an Athena connector, potentially with a Lambda function that connects to and maps the external source.

1. Q: When should AWS Glue be selected instead of Athena alone?

A: When the workload requires data discovery, schema management, transformation, or movement between data stores.

Practice Questions

Question 1

A company stores JSON application events in Amazon S3. Analysts need to run occasional SQL queries without managing servers or loading the data into a database. Which solution best meets the requirement?

A. Run AWS Glue ETL jobs for every analyst query
B. Use Amazon Athena to query the S3 data
C. Use Amazon RDS for PostgreSQL and import all event files
D. Run Apache Spark manually on Amazon EC2

Correct answer: B

Explanation: Athena provides serverless SQL queries over data in S3. Glue ETL, RDS, and self-managed Spark introduce unnecessary processing or infrastructure for an ad hoc query requirement.

Question 2

A data lake contains files in multiple S3 locations. The organization wants to automatically discover their schemas and make the resulting tables available to Athena. Which solution should an architect recommend?

A. Configure an AWS Glue Crawler and use the AWS Glue Data Catalog
B. Create an Amazon RDS database for each S3 location
C. Use an Amazon EC2 instance to scan the files and store the data in EBS
D. Configure an Athena workgroup without defining table metadata

Correct answer: A

Explanation: A Glue Crawler discovers schemas and creates or updates tables in the Glue Data Catalog. Athena can then use those catalog tables to query the S3 data.

Question 3

Athena queries over a large S3 dataset are slower than expected. The data is stored as uncompressed CSV files in a single prefix, and analysts frequently filter by date while selecting only a few columns. Which change is most likely to improve query performance?

A. Convert the data to Parquet, partition it by date, and select only required columns
B. Convert the data to uncompressed JSON and remove all partitions
C. Copy all data into an EC2 instance before querying it
D. Add more Glue Crawlers without changing the data layout

Correct answer: A

Explanation: Date partitioning reduces the data scanned for date-filtered queries, while Parquet supports columnar reads. Selecting only required columns further reduces the query workload. Crawlers provide metadata but do not by themselves optimize the underlying files.

Question 4

A company must extract data from Amazon RDS, transform it, and write the results to Amazon S3 for analytics in Athena. Which architecture is most appropriate?

A. Use Amazon Athena as the transformation engine and store the result in RDS
B. Use AWS Glue ETL to read from RDS, transform the data, and write it to S3; catalog the output for Athena
C. Use a Glue Crawler to perform all transformations and write the output to S3
D. Use CloudWatch Logs subscriptions to move the RDS data to Athena

Correct answer: B

Explanation: Glue ETL is designed for managed extraction, transformation, and loading across data stores such as RDS and S3. A crawler can catalog the resulting S3 data, after which Athena can query it. Crawlers discover metadata but do not perform the transformation workload.