Athena error when inserting data to S3 table bucket

Question

I get error: [ErrorCode: INTERNAL_ERROR_QUERY_ENGINE] when I execute insert query to S3 table bucket. The error happens when I use Athena workgroup with query results KMS encryption. For workgroup without encryption insert query runs successfully.

Below are queries I run:

--Use the following statement to create a table in your S3 Table bucket.
CREATE TABLE `test_namespace`.daily_sales (
sale_date date, 
product_category string, 
sales_amount double)
PARTITIONED BY (month(sale_date))
TBLPROPERTIES ('table_type' = 'iceberg')

/*
Next steps 1) Use the following SQL statement to insert data to your table.
INSERT INTO daily_sales
VALUES
(DATE '2024-01-15', 'Laptop', 900.00),
(DATE '2024-01-15', 'Monitor', 250.00),
(DATE '2024-01-16', 'Laptop', 1350.00),
(DATE '2024-02-01', 'Monitor', 300.00),
(DATE '2024-02-01', 'Keyboard', 60.00),
(DATE '2024-02-02', 'Mouse', 25.00),
(DATE '2024-02-02', 'Laptop', 1050.00),
(DATE '2024-02-03', 'Laptop', 1200.00),
(DATE '2024-02-03', 'Monitor', 375.00);

2) Use the following SQL statement to run a sample analytics query.
SELECT 
product_category,
COUNT(*) as units_sold,
SUM(sales_amount) as total_revenue,
AVG(sales_amount) as average_price
FROM daily_sales
WHERE sale_date BETWEEN DATE '2024-02-01' and DATE '2024-02-29'
GROUP BY product_category
ORDER BY total_revenue DESC;
*/

jarx · Accepted Answer

It is known limitation https://docs.aws.amazon.com/athena/latest/ug/gdc-register-s3-table-bucket-cat.html

re:Post Agent · Answer

The error you're encountering with Athena when inserting data into your S3 table bucket appears to be related to the encryption configuration in your workgroup.

When running an `INSERT` query on a table with underlying data that is encrypted in Amazon S3, there can be compatibility issues with the query results encryption. The error code `INTERNAL_ERROR_QUERY_ENGINE` indicates an internal Athena error occurring on the query engine side, which in this case is likely related to how Athena is handling the encryption for your Iceberg table.

For Iceberg tables specifically, there are special considerations when working with encrypted datasets. Unlike regular tables where you might specify `'has_encrypted_data'='true'` as a table property, Iceberg tables handle encryption differently.

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

1. Verify that your IAM role has the necessary permissions to both access the encrypted data and use the KMS key specified in your workgroup configuration.

2. Check that you have the appropriate permissions for both Athena operations and KMS key usage, including `kms:Decrypt` for working with encrypted datasets.

3. Try simplifying your query to see if the error persists with a smaller data set or fewer operations.

4. If possible, check the Athena service status in your region to ensure there are no ongoing issues.

5. As a workaround, you could try using a workgroup without encryption for the insert operation, and then switch back to the encrypted workgroup for subsequent queries.

If the problem persists after trying these steps, you may need to contact AWS support for further assistance, as this appears to be an internal error within Athena's query engine when handling encrypted Iceberg tables.  
**Sources**  
[INSERT INTO - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/insert-into.html)  
[ErrorCode: INTERNAL_ERROR_QUERY_ENGINE | AWS re:Post](https://repost.aws/questions/QUigPXLxaERTGs6l9oXq3ZRw/errorcode-internal-error-query-engine)  
[Create iceberg tables based on encrypted datasets in Amazon S3 | AWS re:Post](https://repost.aws/questions/QUsu3gXg1iQ9CdBKPmE8Nwzw/create-iceberg-tables-based-on-encrypted-datasets-in-amazon-s3)

Athena error when inserting data to S3 table bucket

Add your answer

Relevant content