Athena error on show create table

The error you're encountering is a known issue when using the SHOW CREATE TABLE command on Iceberg tables in Amazon Athena, particularly for tables created by other engines like Spark.

This error occurs because Iceberg tables created with other engines can have properties that are not exposed or supported in Athena. As a result, Athena cannot reproduce the table structure using the SHOW CREATE TABLE DDL statement.

To work around this issue, you have a few options:

1. Use the DESCRIBE FORMATTED DDL statement instead of SHOW CREATE TABLE. This will show the table properties and structure without attempting to generate a CREATE TABLE statement.

2. If you need the actual CREATE TABLE statement, you may need to use the equivalent command in the engine that originally created the table. In this case, since the table was created by a Spark application, you could use the SHOW CREATE TABLE statement in Spark to get the full DDL.

3. For future table creations, if you need full control over Iceberg table properties and want to ensure compatibility with Athena's SHOW CREATE TABLE, consider creating the Iceberg tables directly with Athena or using AWS Glue.

It's important to note that this limitation is specific to tables with unsupported properties in Athena, and it doesn't affect your ability to query or use the table in Athena - it only impacts the SHOW CREATE TABLE command.
Sources
Troubleshoot Athena Apache Iceberg table errors | AWS re:Post
SHOW CREATE TABLE - Amazon Athena
Working with Apache Iceberg tables by using Amazon Athena SQL - AWS Prescriptive Guidance

This error usually comes up when the Iceberg table was created outside of Athena, like using Spark or Flink. Athena can still query the table just fine, but SHOW CREATE TABLE doesn’t always work because the table metadata includes properties that Athena doesn’t fully support or recognize when trying to reverse-engineer the DDL.

A few things to know: Athena needs specific Iceberg-compatible metadata in the Glue catalog to reconstruct the table.

If the table includes things like advanced partition transforms, schema evolution flags, or Spark-specific properties, Athena can’t render a CREATE TABLE statement and throws this error.

There’s no direct fix for this at the moment. But if you need the DDL:

You can use DESCRIBE <table> to view the schema.

Then manually build a CREATE TABLE statement using that info and the table’s location/format.

It’s a bit of a manual step, but works if you just need to re-register or migrate the table.