The issue you're encountering when trying to query your DuckDB-Iceberg created table with Athena appears to be related to metadata compatibility between the two systems.

When creating Iceberg tables in Athena, there are specific requirements and table properties that need to be set correctly. The error "Cannot invoke "java.lang.Long.longValue()" because "value" is null" suggests that Athena is expecting a Long value in the metadata that's either missing or not properly formatted in the Iceberg table created by DuckDB.

Here are some steps to troubleshoot and potentially resolve this issue:

1. First, ensure you're creating the Iceberg table in Athena with the proper format. In Athena, you would typically create an Iceberg table using:

CREATE TABLE database_name.table_name (
column_name data_type
)
LOCATION 's3://your-bucket/path/'
TBLPROPERTIES ('table_type' = 'ICEBERG')

2. Check if there are any version compatibility issues between the Iceberg format version used by DuckDB and the one supported by Athena. Athena might be expecting specific metadata fields or formats that your DuckDB implementation isn't providing.

3. Try creating a simple Iceberg table directly in Athena first, then examine its metadata structure in S3. Compare this with the metadata structure of your DuckDB-created table to identify any differences.

4. If you're implementing your own Iceberg functionality, ensure you're following the Iceberg specification exactly, particularly for metadata files like manifest files and manifest lists.

5. Consider using AWS Glue crawler to crawl your S3 location containing the Iceberg table. This might help in properly registering the table in the AWS Glue Data Catalog with the correct metadata.

6. If possible, try to examine the Athena query logs or CloudWatch logs for more detailed error information that might point to the specific metadata field causing the issue.

Without being able to directly debug the Athena code, your best approach is to compare the metadata structures between a working Athena-created Iceberg table and your DuckDB-created one, focusing particularly on numeric fields that might be causing the null Long value error.
Sources
Unable to view Iceberg table data in Athena despite successful Glue crawl | AWS re:Post
Create Iceberg tables - Amazon Athena
Getting started with Iceberg tables in Amazon Athena SQL - AWS Prescriptive Guidance

Can you please try with the following steps:

• Create the table with Glue/Spark/Athena that ensures the metadata matches Athena’s expectations.
• Point Athena at that Glue catalog table, then you can query it without errors.
• Use DuckDB only for reading/writing data, not for initial table creation. DuckDB is great for local experimentation, but its Iceberg support isn’t production-ready for cross-tool interoperability yet.

That’s why you saw DuckDB query fine but Athena choke they’re interpreting the metadata differently.

Debug and Fix Step 1: Inspect your Iceberg metadata in S3 In your S3 bucket, open the metadata/ folder of your Iceberg table. Look for v1.metadata.json or similar. Check fields like last-sequence-number, last-updated-ms, and snapshots. If any are missing or null, Athena will fail.

Step 2: Register the table in Glue Athena needs to query it through a catalog. Run:

aws glue create-database --database-input Name=mydb
aws glue create-table --database-name mydb --table-input file://table-definition.json

Where table-definition.json points to the Iceberg table’s location (s3://bucket/path/to/table1/).

Then query via:

SELECT * FROM mydb.table1;

Step 3: Use Athena’s MSCK REPAIR TABLE If partitions are present but missing, repair them: MSCK REPAIR TABLE mydb.table1;

Step 4: Align DuckDB + Athena Iceberg versions Try writing the Iceberg table in Parquet v2 format with full metadata:

PRAGMA enable_object_storage_writes;
CREATE TABLE s3_catalog.default.table1
AS SELECT * FROM range(10);

Or use Spark/Iceberg CLI to create the table first, then read with DuckDB.

Hi,

Thank you for you response. I think my question was not clear enough. I want to be able to create the Iceberg table with DuckDB and read with Athena, I don't want to change that workflow. My question then it.

What fields or metadata fields could I be missing? The fields last-sequence-number, last-updated-ms, and snapshots all exist and are valid, so I know it's not one of those.