Undesired behaviour with AthenaArray/AthenaStruct

[jmspereira]

Hi @laughingman7743,

I have the following model defined:

from sqlalchemy import Column, Float, Integer
from sqlalchemy.orm import declarative_base
from pyathena.sqlalchemy.types import AthenaArray, AthenaStruct, AthenaTimestamp

Base = declarative_base()


class DummyTable(Base):
    __tablename__ = "dummy_table_athena"

    id = Column(Integer, primary_key=True)

    positions = Column(
        "positions",
        AthenaArray(
            AthenaStruct(
                ("header", AthenaStruct(("stamp", AthenaTimestamp))), ("x", Float), ("y", Float)
            )
        ),
    )

And if I access the database directly, I can see that the data has indeed the defined format (i.e., header={stamp=xyz, seq=ghi}, x=abc, y=def}). However, after executing a simple select:

query = (
    athena_session.query(DummyTable.positions)
    .where(DummyTable.id == <requested_id>)
)

The value of the positions is something like:

[{'seq': 0, 'x': 4.736, 'y': 0.583}, {'seq': 0, 'x': 4.501, 'y': 1.023}, ...]

I.e., does not even reference the header{stamp} and has the seq at the same level as the other attributes.
Do you have any ideas of what may be causing this?

Best regards,

[laughingman7743]

Hi @jmspereira,

Thank you for reporting this issue! I've identified and fixed the bug. The problem was in the _parse_named_struct function in pyathena/converter.py.

Problem Analysis

When your data contains nested structs like {header={stamp=xyz, seq=123}, x=4.736, y=0.583}, the parser had two critical issues:

1. Simple Comma Splitting

The function was using basic string splitting:

pairs = [pair.strip() for pair in inner.split(",")]

This incorrectly split your data:

Input: "header={stamp=xyz, seq=123}, x=4.736, y=0.583"
Wrong split:
  - "header={stamp=xyz"      ← Nested struct broken here
  - " seq=123}"              ← Rest of nested struct
  - " x=4.736"
  - " y=0.583"

2. Brace Filtering

The function then skipped any values containing {}:

if any(char in value for char in '{}="'):
    continue  # Skip this pair!

This caused:

• header={stamp=xyz → contains { → skipped
• seq=123} → contains } → skipped
• x=4.736 → no braces → kept ✓
• y=0.583 → no braces → kept ✓

Result: Only x and y were kept, header and seq were completely lost!

Solution

I've updated _parse_named_struct to:

1. Use brace-depth-aware splitting - The same smart splitting logic that _parse_array_native uses
2. Recursively parse nested structs - When a value looks like {...}, call _to_struct recursively

Verification

from pyathena.converter import _to_struct

# Before fix
>>> _to_struct("{header={stamp=2024-01-01, seq=123}, x=4.736, y=0.583}")
{'x': 4.736, 'y': 0.583}  # header lost!

# After fix
>>> _to_struct("{header={stamp=2024-01-01, seq=123}, x=4.736, y=0.583}")
{'header': {'stamp': '2024-01-01', 'seq': 123}, 'x': 4.736, 'y': 0.583}  # ✓

PR Details

I've created PR #628 with:

• ✅ Fix for the parsing logic
• ✅ 10 comprehensive test cases for nested structs
• ✅ 2 SQLAlchemy integration tests with real Athena queries
• ✅ All existing tests pass

The fix handles:

• Single/double/triple nesting levels
• Multiple nested fields
• Arrays containing nested structs
• Null values in nested structs

Note on Your Schema

I noticed your model definition doesn't include the seq field in the header struct:

("header", AthenaStruct(("stamp", AthenaTimestamp))),  # seq is missing

But your actual data has header.seq. This won't cause errors with the fix, but you might want to update your model to match the actual data schema.

Let me know if you have any questions!