Creating array (list) columns in S3 tables (Iceberg) via Athena doesn't work

Question

I have a S3 tables bucket with tables. In Athena, I can add columns to those tables this way:
```
ALTER TABLE some_database.some_table
ADD COLUMNS (
  test_bigint bigint
);
```

The previous query works. However, when trying to create a list column (equivalent to an array in Athena), the query fails.

```
ALTER TABLE some_database.some_table
ADD COLUMNS (
  test_list array
);
```
The previous query fails with error message "Exception encountered when executing Iceberg query".

Why does it fails? Is there any way to create array type columns in S3 tables?

re:Post Agent · Answer

When working with S3 Tables (Iceberg format) in Athena, there are specific considerations for data types. The error you're encountering when trying to add an array column is likely due to a mismatch between Athena's data type representation and Iceberg's underlying type system.

For Iceberg tables in Athena, there is a mapping between Athena data types and Iceberg data types. The Iceberg type `list` maps to Athena's `array` type. Similarly, Iceberg's `long` type maps to Athena's `bigint` type.

When you're trying to create an array of bigint values, you should be able to use the `array` syntax. However, there might be an issue with how this specific operation is handled for S3 Tables.

One thing to check is that your table and column names are all lowercase. S3 Tables require lowercase names for tables and columns to be properly supported by AWS Lake Formation and the AWS Glue Data Catalog. If your table definition contains capital letters, you might encounter errors.

Another consideration is that you might need specific permissions in Lake Formation to perform this operation. S3 Tables integrate with AWS Lake Formation for access control, and insufficient permissions could cause operations to fail.

If you continue to experience issues, you might want to try creating a new table with the array column included in the initial definition rather than adding it later, as some schema evolution operations might have limitations.  
**Sources**  
[Query Apache Iceberg tables - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg.html)  
[Supported data types for Iceberg tables in Athena - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-supported-data-types.html)  
[Querying Amazon S3 tables with Athena - Amazon Simple Storage Service](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-integrating-athena.html)  
[S3 Tables - Unable to query using Athena | AWS re:Post](https://repost.aws/questions/QUKVZfbxcJRXSjyhr_-Qs5PQ/s3-tables-unable-to-query-using-athena)

Creating array<bigint> (list<long>) columns in S3 tables (Iceberg) via Athena doesn't work

Add your answer

Relevant content