Repository navigation
CREATE TABLE renders ROW(...) for struct columns and single-precision FLOAT for sa.Float #855
Description
Problem
Table.create() compiles an AthenaStruct column as Trino's ROW(x INTEGER). CREATE EXTERNAL TABLE is parsed as Hive DDL, which expects STRUCT<x:INT>, so Athena rejects the whole statement (line 1:8: mismatched input 'EXTERNAL'). A struct nested inside an array already renders as STRUCT<...>.
A generic sa.Float column (for example float64 columns from DataFrame.to_sql) is rendered as FLOAT, which in Hive DDL is a 32-bit type, so values lose precision (-2.25e-10 does not round-trip). DOUBLE would match SQLAlchemy's generic float.
Reproduction
import sqlalchemy as sa
from sqlalchemy.schema import CreateTable
from pyathena.sqlalchemy.types import AthenaStruct
engine = sa.create_engine('awsathena+rest://a:b@athena.us-east-1.amazonaws.com/db?s3_staging_dir=s3://x/y/')
t = sa.Table('t', sa.MetaData(), sa.Column('st', AthenaStruct(('x', sa.Integer))), sa.Column('f', sa.Float),
schema='db', awsathena_location='s3://x/t/')
print(CreateTable(t).compile(engine))
# st ROW(x INTEGER),
# f FLOAT,Environment
PyAthena 3.35.4 and master (c01c56f), Python 3.11, SQLAlchemy 2.0.52, Athena engine version 3.
Proposed fix (optional)
In DDL column specifications only (the DDL compiler passes the Column as type_expression; CAST passes a TypeClause), render structs as STRUCT<name:type, ...> and generic floats without a precision <= 24 as DOUBLE, keeping sa.REAL as FLOAT and CAST output unchanged.
Activity
laughingman7743 commentedon Sep 26, 2026
These are two separate topics, and only one of them is a defect.
STRUCT columns: confirmed.
visit_structrendersSTRUCT<...>only on the ARRAY DDL path andROW(...)everywhere else, including the value side ofMAP<...>. Athena'sCREATE TABLEDDL requiresSTRUCT<...>, so this is a bug we want fixed. Please scope the issue to this: DDL column specifications for top-level structs and struct values insideMAP, withCASToutput unchanged. Note thattest_create_table_with_struct_types/test_create_table_with_map_typesintests/pyathena/sqlalchemy/test_base.pyonly compile the DDL and currently assertROW(...), and the STRUCT and MAP sections ofdocs/sqlalchemy.mdshowROW(...)as the generated SQL. Those tests and docs need to be updated with the fix, and the change will be release-noted as a behavior change.sa.Float->FLOAT: this is documented and intentional. See the "Floating-point types" table indocs/sqlalchemy.md:Float/FLOAT/REALrenderFLOAT,DoublerendersDOUBLE, andFloat(precision)does not change the Athena type. That was decided in #761 (PR #826). Usesa.Doublefor 64-bit columns. Please drop this part from the issue.Per CONTRIBUTING.md, the STRUCT fix needs a live
CREATE TABLE(and a read back) in your own AWS environment with before/after results recorded in the PR's TEST section. External-fork PRs are not run in the project's AWS CI.laughingman7743 commentedon Sep 26, 2026
Target
masterfor the fix; the maintainers will port it to the3.xmaintenance branch afterwards, so please do not open a PR against3.x.laughingman7743 commentedon Sep 26, 2026
If you plan to send a PR for this, please say so here. If the issue stays open for a long time without a PR, the maintainers may pick it up and implement the fix themselves.
fix: render Hive STRUCT syntax in table column DDL
laughingman7743 commentedon Sep 29, 2026
Thank you for the report, @aminghadersohi. The STRUCT column DDL fix (#870 by @mvanhorn) is now released in PyAthena v3.37.0 on the 3.x line, and it will also ship in the next major release.
CREATE TABLEnow renders STRUCT columns, including STRUCT values inside MAP and ARRAY, asSTRUCT<name:type>.