Error in Iceberg table creation via Athena

Question

I'm trying to create an Iceberg table on S3 using Athena SQL console, and it is working fine if the table is not partitioned. But I want to create a partitioned table.

1. On using the create table syntax below, getting error "Cannot find source column: orderDate. This query ran against the "database1" database, unless qualified by the query. "

CREATE TABLE IF NOT EXISTS database1.iceberg_table3 (
    OrderID STRING,
    Customer STRING,
    Item STRING,
    Quantity INT,
    Price DOUBLE,
    OrderDate TIMESTAMP,
    Cancelled_Flag INT
)
PARTITIONED BY (MONTH(OrderDate))
LOCATION 's3://my-bucket-new/output/Iceberg/'
TBLPROPERTIES (
    'table_type'='ICEBERG',
    'format'='PARQUET',
    'write_compression'='snappy'
);

2. On using the create table syntax below, getting error "line 10:6: no viable alternative at input 'CREATE TABLE IF NOT EXISTS database1.iceberg_table3 (
 OrderID	STRING,
 Customer STRING,
 Item STRING,
 Quantity INT,	
 Price DOUBLE,
 OrderDate TIMESTAMP,
 Cancelled_Flag INT
)
WITH ('"

CREATE TABLE IF NOT EXISTS database1.iceberg_table3 (
    OrderID	STRING,
    Customer STRING,
    Item STRING,
    Quantity INT,	
    Price DOUBLE,
    OrderDate TIMESTAMP,
    Cancelled_Flag INT
)
WITH (
    table_type = 'ICEBERG',
    format = 'PARQUET',
    partitioning = (MONTH(OrderDate)),
    external_location = 's3://my-bucket-new/output/Iceberg/'
);

Note - The glue catalog and database exists and and correctly configured, since table without partition is getting created successfully.

Florian Turnwald · Accepted Answer

My understanding is you already used the correct `PARTITIONED BY` keyword, so I do not agree to the former answer of the re:Post Agent.  I think the error "*Cannot find source column*" in Athena Iceberg tables is caused by case-sensitivity or syntax ordering.

- Lowercase Columns: Use lowercase for all column names (e.g., `order_date` instead of `OrderDat`e). Athena/Iceberg often fails to map partitioned columns if there is a case mismatch.
- Avoid `WITH` for Partitioning: I think your second attempt failed because Iceberg in Athena does not support the partitioning property inside the `WITH` clause; it must use the `PARTITIONED BY` clause as shown above.

-> https://repost.aws/questions/QU1rLHTPMGRqW7P_9EDGilYA/athena-error-column-not-found-on-certain-columns

"*To create Iceberg tables with partitions, use PARTITIONED BY syntax. Columns used for partitioning must be specified in the columns declarations first. Within the PARTITIONED BY clause, the column type must not be included. You can also define partition transforms in CREATE TABLE syntax. To specify multiple columns for partitioning, separate the columns with the comma (,) character, as in the following example.*"
-> https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-creating-tables.html
- Hidden Partitioning: Do not add a separate column for the month. Iceberg derives the partition directly from the existing `order_date` column. -> https://iceberg.apache.org/docs/latest/partitioning/

re:Post Agent · Answer

The issue with your syntax is that you're using `PARTITION BY` instead of the correct `PARTITIONED BY` keyword for Iceberg tables in Athena.

Here's the corrected syntax for your first attempt:

```
CREATE TABLE IF NOT EXISTS database1.iceberg_table3 (
OrderID STRING,
Customer STRING,
Item STRING,
Quantity INT,
Price DOUBLE,
OrderDate TIMESTAMP,
Cancelled_Flag INT
)
PARTITIONED BY (month(OrderDate))
LOCATION 's3://my-bucket-new/output/Iceberg/'
TBLPROPERTIES (
'table_type'='ICEBERG',
'format'='PARQUET',
'write_compression'='snappy'
);
```

The key changes:
- Use `PARTITIONED BY` instead of `PARTITION BY`
- The partition transform function should be lowercase: `month(OrderDate)` instead of `MONTH(OrderDate)`

This syntax leverages Iceberg's hidden partitioning feature, where the partition value is derived on the fly when you write or query data, rather than being stored as a separate column. The `month()` transform will create monthly partitions from your timestamp column.

Your second syntax attempt using `WITH` clause is not the correct approach for creating Iceberg tables in Athena. Athena uses the `PARTITIONED BY` clause in the table definition, followed by `TBLPROPERTIES` to specify Iceberg-specific properties.  
**Sources**  
[Create Iceberg tables - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-creating-tables.html)  
[Getting started with Iceberg tables in Amazon Athena SQL - AWS Prescriptive Guidance](https://docs.aws.amazon.com/prescriptive-guidance/latest/apache-iceberg-on-aws/getting-started.html)

Error in Iceberg table creation via Athena

Add your answer

Relevant content