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.
S3 log queries and permissions
Athena runs SQLA language for selecting and aggregating data through tables., the language used to select and aggregate table data, directly over logs stored in S3. It is serverlessA service model in which users do not provision or manage the query servers.: you do not manage the query servers. A table supplies the schemaThe definition of fields and their data types in a table., meaning field names and types, while the log objects stay in S3.
How it works
- Store supported logs in S3. Athena documentation covers CloudTrail, Application Load Balancer, CloudFront, and VPC flow logs.
- Define a table in the Glue Data CatalogA store of metadata that describes databases, tables, and columns., the metadata store for table definitions. Create it with SQL or use a Glue crawlerA process that examines stored data to infer a table schema., which infers the schema from data. For CloudTrail, point the external tableA table definition that describes data stored outside the query engine. LOCATION at the log files.
- Authorize Athena API calls, the source data, and catalog resources. Identity and Access Management (IAM) policies govern these permissions. If keys from KMS, AWS’s encryption-key service, protect the data, authorize the keys too.
- Select result storage through a workgroupAn Athena boundary for query configuration, access, and usage controls., the query configuration and access boundary. Use a customer-owned S3 result location or Athena managed results.
- Run SQL and apply scan controls where needed. SQL on-demand pricing charges for data scanned; capacity-based pricing is also available.
| Access boundary | What to authorize |
|---|---|
| Athena | Query actions and workgroup access |
| Source S3 objects | Read access to the log data |
| Glue Data Catalog | Access to the database and table metadata |
| Customer-owned results bucket | Write and read access to result files |
| KMS-encrypted data and S3 results | Decrypt for data; GenerateDataKey and Decrypt for results |
Workgroup Override client-side settings enforces its result configuration. A per-query scan limit cancels an oversized query, which still incurs charges. A workgroup-wide scan alert uses CloudWatch and SNS without automatically canceling queries.
Managed query results became available in June 2025. This option needs no customer-owned results bucket and deletes results after 24 hours. Retrieve them through GetQueryResults or GetQueryResultsStream permissions on the workgroup, according to the access method.
Federated queries use connectorsA component that lets Athena read a supported external data source. to read supported sources outside S3 in place. Since April 21, 2026, certain newly created Glue Data Catalog connectors use no Lambda function in your account. Athena data catalog connectors, including custom connectors, still require one. For existing CloudTrail Lake customers, event data store federation uses Glue metadata and Lake Formation access control. CloudTrail Lake closed to new customers on May 31, 2026.
Exam cues
- If the question says occasional SQL over S3 service logs without servers, think Athena, because it queries those objects through table definitions.
- If the question says access denied despite Athena permissions, think source data, catalog, results, and KMS permissions, because query permission alone does not grant those other accesses.
- If the question says query supported databases outside S3 in place, think Federated QueryA query that accesses supported external data sources in place through connectors., because its connectors access external sources.
- If the question says cancel a query that scans too much, think per-query limit, because aggregate workgroup alerts only notify.
Remember
- KMS-encrypted source data requires kms:Decrypt.
- KMS-encrypted S3 results require kms:GenerateDataKey and kms:Decrypt.
- Managed results expire after 24 hours.
- Workgroup result settings override clients only when Override client-side settings is enabled.
Trap: Denying GetQueryResults does not block direct access to customer-owned S3 result files. A user with S3 GetObject can still read those files.
Athena reads log objects using the catalog table definition.
Athena writes output to the configured customer-owned S3 result location.
Da documentação da AWS (30)
- Athena SQL queries data directly in S3 without requiring users to manage query servers.
- Athena supports querying CloudTrail, Application Load Balancer, CloudFront, and VPC flow log datasets.
- Athena uses the Glue Data Catalog for S3 table metadata such as column and table names.
- Athena table definitions can be created with CREATE TABLE or inferred from S3 data by a Glue crawler.
- A CloudTrail external table defines log fields and an S3 LOCATION pointing at the log files.
- Running queries requires permissions for the Athena API actions.
- Running queries over S3 requires access to the underlying S3 data locations.
- Running queries with Glue catalog resources requires access to the relevant metadata and resources.
- With a customer-owned results bucket, Athena stores result files in S3 and users need permissions to write and retrieve them.
- Enabling Override client-side settings makes Athena workgroup result settings take precedence over client settings.
- Athena managed query results removes the need to provide a customer-owned S3 results bucket.
- Athena introduced managed query results in June 2025.
- Athena managed query results remain available for 24 hours.
- Athena automatically deletes managed query results after their retention period.
- Managed result access requires GetQueryResults or GetQueryResultsStream permissions on the workgroup, according to the retrieval method.
- Reading a KMS-encrypted S3 dataset requires kms:Decrypt permission in addition to Athena and S3 access.
- For KMS-encrypted S3 query results, Athena users need kms:GenerateDataKey and kms:Decrypt.
- Athena reads encrypted table data using its data encryption settings, independently of the result encryption settings.
- An Athena per-query data scan limit cancels a query that exceeds it.
- Athena canceled queries are charged according to its pricing.
- Athena workgroup-wide data scan alerts use CloudWatch alarms and SNS; reaching the threshold does not automatically cancel queries.
- A user with S3 GetObject access to customer-owned result files can read them directly even if Athena GetQueryResults is denied.
- Athena Federated Query uses connectors to query relational, non-relational, object, and custom sources outside S3 in place.
- Starting April 21, 2026, certain newly created Glue Data Catalog federated connectors are automatically registered and do not use a Lambda function in the customer account.
- Athena data catalog federated connectors, including custom Query Federation SDK connectors, require a Lambda function in the customer account.
- CloudTrail Lake event data store federation exposes metadata through Glue and manages access through Lake Formation so Athena can query the events.
- CloudTrail Lake closed to new customers starting May 31, 2026; existing customers can continue using it.
- Athena SQL on-demand pricing is based on data scanned; Athena also offers capacity-based pricing.
- A CloudTrail external table declares log field names and data types.
- Athena can query S3 server access logs through an external table defined over the log bucket, for example to rank objects by number of requests.
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.
Da documentação da AWS (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.
Partition discovery and SerDes
Athena tables need partitionA grouping of related data selected through partition key values. locations and a serializer and deserializer, or SerDe, suited to the data format. The path layout determines how partitions can be discovered, while partition projectionCalculating partition locations from table-property rules rather than individual catalog entries. calculates locations from rules.
How it works
- Check the partition path layout. Hive-style paths use
key=valuecomponents and can be discovered withMSCK REPAIR TABLE. For non-Hive paths, such as typical CloudTrail date paths, add partitions explicitly withALTER TABLE ADD PARTITION. - Alternatively, configure partition projection. Athena computes locations in memory from rules in table properties instead of looking them up in the Glue Data CatalogA store of metadata that describes databases, tables, and columns..
- Choose a SerDe matching the table data, such as a JSON SerDe for JSON records.
Exam cues
- If the question says
MSCK REPAIR TABLEfinds no partitions in standard CloudTrail date paths, thinkALTER TABLE ADD PARTITIONor partition projection, because those paths are not Hive-style. - If the question says a projected table has many empty partition combinations, think sparse partition overhead, because Athena lists calculated locations even when they contain no data.
- If the question says an Athena table must parse JSON, think JSON SerDe, because the SerDe should match the data format.
Remember
- Hive-style paths use
key=valuecomponents. - Partition projection uses table-property rules instead of Glue partition lookups.
- Sparse tables can perform worse with projection when calculated locations contain no data.
Trap:
MSCK REPAIR TABLEdiscovers Hive-style paths; it does not discover typical non-Hive CloudTrail date paths. Use explicit partition additions or partition projection for those paths.
Da documentação da AWS (5)
- Hive-style partition paths use key=value components; MSCK REPAIR TABLE discovers this layout.
- Non-Hive partition paths, including typical CloudTrail paths, require explicit partition additions such as ALTER TABLE ADD PARTITION.
- Partition projection stores rules in table properties and computes partition locations in memory rather than looking them up in Glue.
- Partition projection can perform worse for sparse tables because Athena lists calculated locations even when they contain no data.
- An Athena table can specify a SerDe that matches its data format, such as a JSON SerDe for JSON data.
Flashcards
Nada para revisar aqui agora. Escolha outros tópicos ou outro filtro.
·
Todos os 20 flashcards em lista
A team needs SQL analysis of CloudTrail trail logs stored in S3. How can it use Athena?
Define an external table with the CloudTrail fields and the S3 log location, then run SQL queries. Existing CloudTrail Lake customers can also federate an event data store through Glue and Lake Formation.
A team needs ad hoc SQL queries over ALB, CloudTrail, or VPC flow logs in S3 without managing servers. Which service fits?
Athena SQL, with log table definitions in the Glue Data Catalog. SQL on-demand queries are billed by data scanned.
An Athena user can start queries but cannot read S3 logs or write S3 query results. Which permissions should the team review?
Review source S3 access, result-bucket write and read permissions, Glue catalog access, and relevant KMS permissions. Athena API permission alone does not grant data access.
A security team needs SQL analysis of CloudTrail logs stored in Amazon S3 without managing query servers. Which service fits?
Use Amazon Athena to query the logs directly in S3.
How can a team prepare CloudTrail logs in Amazon S3 for analysis with Amazon Athena?
Create an external table with the log fields and an S3 LOCATION pointing to the log files.
A team wants to rank S3 objects by request count using server access logs. How can Amazon Athena help?
Create an external table over the log bucket and query the logs with Athena.
A team denies Athena GetQueryResults but users can still retrieve files from its S3 results bucket. Why?
Their S3 GetObject access still allows direct file retrieval. Restrict the S3 result location as well as Athena API access.
An Athena user can call the query API but cannot analyze encrypted logs in Amazon S3. What access should the team check?
Check access to the S3 data and relevant Glue catalog resources, plus kms:Decrypt for the encrypted dataset.
An Athena query reads data encrypted with one KMS key and writes results encrypted with another. Which keys must be authorized?
Authorize decrypt access to the data key and the required GenerateDataKey and Decrypt access for the result key. Data and output encryption settings are separate.
A team wants the cheapest way to find which S3 objects get the most requests, using the bucket's server access logs. What fits?
Athena with an external table over the access log bucket, then a SQL query that counts requests per object key.
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.
An Athena administrator runs MSCK REPAIR TABLE against standard CloudTrail date paths and finds no partitions. What should the administrator use?
Use ALTER TABLE ADD PARTITION for non-Hive paths, or configure partition projection. MSCK discovers Hive-style key=value paths.
What is Athena partition projection, and when can it make query planning worse?
It calculates partition locations from table-property rules instead of catalog lookups. On sparse datasets it can list many locations that contain no data.
A team creates an Athena table over JSON data in S3. What should the table use to parse the data?
Specify a JSON SerDe, a serializer and deserializer suited to the data format.