How to set the identifier-field-ids to the Iceberg tables created through AWS Glue SDK.

Setting the identifier-field-ids for Iceberg tables created through AWS Glue SDK can be challenging, as it's not directly supported through the standard Glue API or Athena ALTER TABLE commands. However, there are a few approaches you can consider to achieve this:

1. Using Apache Spark: If you have access to Apache Spark (e.g., through Amazon EMR or AWS Glue ETL jobs), you can use Spark SQL to alter the table and add the identifier-field-ids. This method is more aligned with Iceberg's native operations.

2. AWS Glue ETL Job: You can create an AWS Glue ETL job to update the table schema and add the identifier-field-ids. In your Glue ETL script, you would: a. Read the existing table data b. Define the new schema with identifier-field-ids c. Write the data back to the same table with the updated schema

When writing the data, make sure to set enableUpdateCatalog to true and updateBehavior to UPDATE_IN_DATABASE in your job options. This will update the schema in the Glue Data Catalog.

3. Manual Update: As a last resort, you can manually edit the table schema through the AWS Glue Console. Navigate to the table in the Glue Data Catalog, edit the schema, and add the identifier-field-ids.

It's important to note that adding identifier-field-ids to an existing Iceberg table may require rewriting the data to organize it according to the new structure. This operation can be resource-intensive for large datasets.

For Firehose to perform update/delete operations on Iceberg tables, you must either specify unique keys for the destination table when setting up your Firehose stream, or set identifier-field-ids in Iceberg when you run create table or alter table operations. If both are not configured, the delivery of data with update and delete operations will fail.

If you're still encountering issues after trying these methods, you may need to verify that the 'id' field in your data exactly matches the name and data type of the 'id' column in your Iceberg table. Even small differences in capitalization or data type can cause errors. Also, ensure that the 'id' field is at the top level of your JSON structure, as Firehose only processes the first level of nodes in a multi-level nested JSON.

Remember, configuring unique keys per table during Firehose stream creation is optional. If you don't configure unique keys per table during stream creation, Firehose checks for identifier-field-ids for required tables and will use them as unique keys.

Sources
How to add partition fields to Iceberg table | AWS re:Post
Route incoming records to different Iceberg tables - Amazon Data Firehose
Set up the Firehose stream - Amazon Data Firehose
firehose delivery stream destination error | AWS re:Post