> For the complete documentation index, see [llms.txt](https://gautamnaik1994.gitbook.io/snippets/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://gautamnaik1994.gitbook.io/snippets/aws/glue-and-athena.md).

# Glue and Athena

### How Athena Works with Nested JSON

Athena is a serverless, schema-on-read engine powered by Trino/Presto. It reads raw files directly from Amazon S3 using a Deserializer (SerDe) like `org.openx.data.jsonserde.JsonSerDe`.

* Nested JSON handling: Objects within objects are mapped to the `STRUCT` data type, and lists are mapped to `ARRAY`. You query nested fields using dot notation (`user.address.city`) or flatten arrays using the `UNNEST` SQL operator.
* Missing Keys handling: If a record is missing a field present in the schema, Athena simply evaluates it as `NULL`. It does not throw an error or crash.

### How AWS Glue Crawler Builds the Data Catalog

When a Glue Crawler scans your JSON files on S3, it processes records sequentially using Classifiers (specifically, built-in or custom JSON Classifiers):

1. Infers Nested Types: If it finds a nested object like `{"user": {"id": 101, "role": "admin"}}`, it creates a `STRUCT<id: bigint, role: string>` column in the AWS Glue Data Catalog.
2. Handles Missing Keys (Schema Union): Glue builds a superset (union) of all keys across all records in your dataset. If Record 1 has `{a, b}` and Record 2 has `{b, c}`, Glue creates a table schema containing all three columns (`a`, `b`, `c`). When querying, missing keys evaluate to `NULL`.
3. Resolves Data Type Conflicts: If Record 1 has `"id": 101` (integer) and Record 2 has `"id": "A-101"` (string), Glue resolves conflicting types by merging them into `string` or marking the column with a dynamic schema state.

Key  Takeaways

* Format Requirement: Standard JSON SerDes require ndjson (newline-delimited JSON), where each JSON record sits on a single line. Multi-line or pretty-printed JSON requires the Amazon Ion SerDe.
* Glue DynamicFrames: In Glue ETL jobs, nested JSON is loaded into a `DynamicFrame` without requiring a fixed schema up front. You can use transforms like `.relationalize()` or `.unnest()` to convert nested structs/arrays into flat relational columns.
* ML Pipeline Optimization: For ML workflows, leaving JSON as-is can be slow and expensive. Converting nested JSON into columnar formats like Apache Parquet via Glue ETL drastically improves Athena query performance and lowers costs for ML feature extraction.

#### Quick  Reference Summary

| **Inconsistent Case**                | **Glue Crawler Result**                     | **Athena Behavior**                                                    | **Fix/Best Practice**                                                           |
| ------------------------------------ | ------------------------------------------- | ---------------------------------------------------------------------- | ------------------------------------------------------------------------------- |
| Missing key in nested JSON           | Generates unified `STRUCT`                  | Missing key evaluates to `NULL`                                        | None needed; handled natively.                                                  |
| Missing key in Array of objects      | Generates `ARRAY<STRUCT>`                   | Missing key evaluates to `NULL`                                        | None needed; handled natively.                                                  |
| Type Conflict (`String` vs `STRUCT`) | Logs a `ChoiceType` or defaults to `STRING` | Query fails with SerDe Error (unless `use.null.for.invalid.data=true`) | Use Glue ETL `ResolveChoice` or map as `STRING` and parse using `json_extract`. |

### Spark vs Python Shell

While Glue is best known for running Apache Spark, it is not restricted to Spark only.

You do not have to use heavy multi-node Spark clusters for small tasks. AWS Glue natively supports Python Shell Jobs, which run plain Python scripts without spinning up Spark or Java virtual machines.

#### How Python Shell Jobs Work

Instead of spinning up a multi-node cluster, a Python Shell job provisions a single, standard Linux environment running a native Python interpreter.

* Execution Engine: Standard Python 3.x (no PySpark, no JVM overhead).
* Pre-installed Libraries: It comes pre-loaded with common data engineering and data science libraries, including `pandas`, `numpy`, `scikit-learn`, `boto3`, `scipy`, and `s3fs`.
* Billing & Hardware: You choose between two lightweight compute profiles:
  * 1/16 DPU (0.0625 DPU): Gives you \~1 vCPU and 1 GB RAM.
  * 1 DPU: Gives you \~4 vCPUs and 16 GB RAM.

#### Why Use Python Shell Instead of Spark?

| **Feature**   | **Glue Spark Job**                   | **Glue Python Shell Job**                               |
| ------------- | ------------------------------------ | ------------------------------------------------------- |
| Startup Time  | \~1 to 3 minutes (cluster spin-up)   | \~5 to 10 seconds                                       |
| Minimum DPUs  | 2 DPUs (Worker G.1X/G.2X)            | 0.0625 DPU (1/16th)                                     |
| Billing Floor | 1 minute (2 DPUs min = \~$0.015/min) | 1 minute (0.0625 DPU min = \~$0.00046/min)              |
| Max Data Size | Multi-GB to Terabytes                | Small to medium (fits in instance RAM, e.g., < 10 GB)   |
| Best For      | Distributed Big Data ETL             | Small CSVs, API polling, Pandas cleanups, orchestration |

### AWS Glue

The AWS Glue Data Catalog serves as a centralized metadata repository (a managed Hive Metastore) that decouples underlying data storage (like Amazon S3) from query execution engines.<br>

#### Key Features & Technical Characteristics

* Schema-on-Read Definition: Stores metadata—including database/table names, column data types, field structures (`STRUCT`, `ARRAY`), and exact physical S3 URIs—without modifying the raw files on disk.<br>
* Automated Data Discovery (Crawlers): Glue Crawlers scan files sequentially (CSVs, JSON, Parquet) to infer schemas automatically using classifiers.<br>
* Schema Unioning (Superset Strategy): Handles inconsistent semi-structured data by creating a unified superset schema. Missing keys across records evaluate natively to `NULL`.<br>
* Handling Structural Conflicts: When primitive types clash with nested objects (e.g., `STRING` vs. `STRUCT`), Glue falls back to dynamic types (`ChoiceType`/`STRING`), avoiding total schema generation failure.<br>
* Partition Metadata & Pruning: Maps directory structures (e.g., `/year=2026/month=08/`) to accelerate query performance by allowing engines to skip non-matching S3 file paths without scanning full directories.<br>
* Multi-Engine Interoperability: Functions as a single source of truth across AWS services (Athena, SageMaker, Glue ETL, Redshift Spectrum, EMR).<br>
* Centralized Governance: Integrates with AWS Lake Formation to enforce fine-grained, column-level, and row-level access permissions.<br>

#### What the Glue Data Catalog Is *Not*

* Not a File Reader or Execution Engine: Athena and Spark engines read the raw S3 files directly using the schema pointers inside the Catalog; query engines do not route file payloads through the Catalog itself.<br>
* Not an Inverted/B-Tree Index: It does not index individual row values for point lookups (`WHERE id = 123`). It indexes metadata at the table, column, file, and partition levels.<br>
* Not Aware of Column Renames: It cannot infer if a field was renamed (e.g., `salary` $$ $\rightarrow$ $$ `ctc`). It treats renames statelessly as one deleted column + one added column unless handled via Glue ETL (`rename_field`) or manual schema updates.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://gautamnaik1994.gitbook.io/snippets/aws/glue-and-athena.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.