Athena query optimize fails for Iceberg table

The issue you're experiencing with the OPTIMIZE command failing on your Iceberg table could be due to several factors.

When you run the OPTIMIZE command on an Iceberg table, Athena needs to scan all the files that match your criteria before rewriting them. With 221 files totaling 36.9 GB, this operation can be resource-intensive, leading to the "Query exhausted resources at this scale factor" error.

Here are some approaches to resolve this issue:

1. Use a WHERE clause with partition columns to process smaller batches of data at a time. The OPTIMIZE statement supports filtering by partitions, which can significantly reduce the amount of data processed in a single operation. For example:

OPTIMIZE my_cat.my_tb REWRITE DATA USING BIN_PACK WHERE partition_column = 'value'

2. Note that the WHERE clause in OPTIMIZE can only include partition columns. If you try to filter on non-partition columns, you'll get an error like "Unexpected FilterNode found in plan; probably connector was not able to handle provided WHERE expression."

3. If your table isn't partitioned, consider partitioning it to make optimization operations more manageable.

4. Although you've already tried adjusting parameters like 'optimize_rewrite_min_data_file_size_bytes', 'write_target_data_file_size_bytes', and 'optimize_rewrite_max_data_file_size_bytes', you might want to try more extreme values to limit the scope of the operation.

5. If these approaches don't work, you might need to consider using a different service like AWS Glue or Amazon EMR with Spark, which might handle larger data volumes more efficiently for Iceberg table optimization.

Remember that the compaction action is charged based on the amount of data scanned during the rewrite process, so optimizing in smaller batches can also help manage costs.
Sources
Athena engine version 3 - Amazon Athena
OPTIMIZE - Amazon Athena

Hello,

Thank you for reaching out regarding the OPTIMIZE BIN_PACK failure on your Iceberg table. I understand you're experiencing the "Query exhausted resources at this scale factor" error when attempting to optimize 221 files totaling 36.9 GB, and that you're using Kafka Connect to sink data to this table.

Understanding the Error

As you may already know, Athena is a server-less technology and queries are executed on shared resources. Athena uses Presto as a query engine. Presto runs different stages of a query in memory. For a small number of queries and for certain operators, Presto brings all the data into a single node's memory and may fail because it cannot spill pages to disk when memory is exhausted.

The error "Query exhausted resources at this scale factor" occurs when your query is very resource intensive (usually memory) thus consuming large amount of resources from the workers. During the execution of your query, the worker nodes crashed due to too much workload.

Likely Root Cause

Based on your mention of using Kafka Connect for data ingestion, this issue is most likely caused by delete file accumulation. When Kafka Connect writes to Iceberg tables, it often creates numerous small delete files as part of its update/upsert operations. Even though your data files total only 36.9 GB, the OPTIMIZE BIN_PACK operation must process both data files AND delete files in memory, which can be significantly larger.

Diagnostic Steps

To confirm the root cause, please run these commands:

1. Check table metadata for delete files:

SELECT * FROM "my_cat$manifests"."my_tb" 
ORDER BY committed_at DESC LIMIT 10;

2. Get detailed snapshot information:

DESCRIBE DETAIL my_cat.my_tb;

Look for high counts of delete files or large delete file sizes in the output.

Recommended Solutions

Option 1: Configure Delete File Threshold (Immediate Fix)

ALTER TABLE my_cat.my_tb SET TBLPROPERTIES (
  'optimize_rewrite_delete_file_threshold' = '5'
);

Then retry your OPTIMIZE command. This forces compaction when there are 5 or more delete files per data file.

Option 2: Run VACUUM First

VACUUM my_cat.my_tb;

This removes old snapshots and associated delete files. After VACUUM completes, retry the OPTIMIZE operation.

Option 3: Use AWS Glue for Heavy Optimization

If Athena continues to fail, consider using AWS Glue with larger memory allocation:

# In AWS Glue job with G.2X worker type (32GB memory)
spark.sql("""
CALL glue_catalog.system.rewrite_data_files(
  table => 'my_cat.my_tb',
  options => map(
    'delete-file-threshold', '5',
    'target-file-size-bytes', '536870912'
  )
)
""")

Option 4: Partition-Level Optimization

If the table is partitioned, try optimizing specific partitions:

OPTIMIZE my_cat.my_tb REWRITE DATA USING BIN_PACK 
WHERE partition_column = 'specific_value';

Long-Term Recommendations

1. Regular Maintenance: Schedule regular VACUUM and OPTIMIZE operations, especially with Kafka Connect ingestion
2. Monitoring: Monitor delete file accumulation using the metadata queries above
3. Configuration: Consider adjusting optimize_rewrite_delete_file_threshold based on your ingestion patterns

Alternative Approach

If this continues to be problematic, you might consider:

• Using append-only operations where possible instead of updates/upserts
• Implementing a batch processing pattern that reduces the frequency of small updates
• Using AWS Glue for regular compaction with more memory resources

Please try the diagnostic steps first to confirm the delete file accumulation, then implement the appropriate solution.