Athena SQL reads S3 data through table definitions. Partition filters, columnar formats, and file sizes determine how much work a query performs; permissions and workgroups control access and query behavior.
Partitions and columnar formats
Partitioning and columnar storage reduce the data Athena needs to read. A partitionA grouping of related data selected through partition key values. groups objects by a key such as date. A columnar file groups values by column so a query can read the fields it needs.
How it works
- Choose partition keysA virtual column whose value identifies a data partition. that match common filters. Define partition columns as STRING so Athena can push their filtering into the Glue Data CatalogA store of metadata that describes databases, tables, and columns..
- Convert logs to Parquet or ORC with CREATE TABLE AS SELECTA SQL operation that creates a table and writes data selected by a query. (CTAS), which creates a table from query output, or with a Glue extract, transform, and loadProcessing that reads source data, changes its structure or format, and writes the result. (ETL) job.
- Filter partition columns in SQLA language for selecting and aggregating data through tables.. Parquet and ORC also use block statistics for predicate pushdownUsing filter conditions and block statistics to avoid reading blocks that cannot match., which skips blocks that cannot satisfy a filter.
- Compact small files, meaning combine them into larger files, to reduce listing and request overhead. Inspect planned partition values with EXPLAIN.
| Technique | What it avoids reading or doing |
|---|---|
| Partition filtering | Unmatched partition locations |
| Parquet or ORC | Unneeded columns and blocks excluded by statistics |
| Column compressionEncoding data in fewer bytes to reduce storage and read work. | Extra storage and data reading |
| File compactionCombining small data files into larger files to reduce per-file overhead. | Repeated work for many small files |
Exam cues
- If the question says queries scan years of logs for a date range, think date partition filters, because only matching partition locations need reading.
- If the question says Parquet queries remain slow with many tiny files, think compaction, because columnar storage still has per-file overhead.
- If the question says convert JSON logs to Parquet with Athena, think CTAS, because it creates a table from query output in the chosen format.
Remember
- Parquet and ORC support column compression and predicate pushdown.
- Choose partition keys for common queries; excessive keys can create too many small files.
- Athena cannot read more than one million partitions in one scan.
- Most compressed text files cannot be split across readers. Avoid consolidating into too few files that prevent parallel reading.
- Tables describe data in S3 for read-time queries; Athena never changes that data.
- WorkgroupsAn Athena boundary for query configuration, access, and usage controls. separate teams, track query metrics and control costs.
Trap: Partitioning does not automatically restrict a query to the dates you intended. Include conditions on the partition columns and inspect EXPLAIN when the scan is unexpectedly broad.
CTAS reads source records through the source table.
CTAS writes the selected records in the chosen columnar format.
From the AWS docs (16)
- Partitioning groups related data by keys such as date; filtering partition columns restricts which locations Athena scans.
- Choose partition keys for common query filters; excessive keys can create fragmented datasets with too many small files.
- Athena recommends STRING partition keys so partition filtering can be pushed into the Glue Data Catalog.
- Parquet and ORC organize data by column so Athena loads only the columns required by the query.
- Columnar storage supports compression by column to reduce storage and query I/O.
- Parquet and ORC use data-block statistics to skip blocks that cannot match a filter, a behavior called predicate pushdown.
- Athena CREATE TABLE AS SELECT can convert source data into Parquet or ORC.
- CREATE TABLE AS SELECT creates a table from query output.
- Glue ETL jobs can transform CSV or JSON into Parquet or ORC.
- Many small files increase Athena listing and per-file processing overhead, including for columnar data.
- Buffering writes or combining files with Glue ETL can produce larger files and reduce small-file overhead.
- Most compressed text files cannot be split across readers, so avoid reducing a dataset to too few such files.
- EXPLAIN shows which partition values Athena plans to scan.
- Athena cannot read more than one million partitions in a single scan.
- An Athena table only describes the schema and S3 location of data for read-time querying; Athena does not modify the data in Amazon S3.
- Athena workgroups separate workloads, control team access, enforce settings, track query metrics and control costs.
Flashcards
Nothing to review here right now. Pick other topics or another filter.
·
All 7 cards as a list
How can a team make Athena SQL queries over a large S3 log archive scan less data and run faster?
Use Parquet or ORC, partition for common filters such as date, and filter partition columns. Compact small files so listing and per-file overhead do not erase the gains.
An Athena table uses Parquet, but thousands of tiny files make queries slow. What should the team change?
Compact the files or buffer writes to create larger files. Columnar format does not remove the overhead of listing and requesting many files.
An Athena query needs only a small selection of columns from S3 logs. How do Parquet and ORC help?
They let Athena load required columns and skip blocks that cannot satisfy filter predicates, while using column compression.
An Athena table has partitions by date, but queries omit the date partition filter. Will partitioning alone restrict the scan to the desired dates?
No. Filter the partition columns to select matching partition locations. Use EXPLAIN to inspect planned partition values.
A team wants Athena to convert JSON logs to Parquet without moving them through a separate processing service. Which SQL operation fits?
CREATE TABLE AS SELECT, also called CTAS, specifying Parquet output. Glue ETL is another supported conversion path.
Does creating an Amazon Athena table load or change the underlying data in Amazon S3?
No. The table only describes the schema and location for read-time querying.
Two analytics teams share Amazon Athena and finance needs separate cost tracking and limits per team. What should be configured?
A workgroup per team, which separates workloads, tracks query metrics and controls costs.