Inserting aggregated data into summary table using Athena

Question

I have an Athena table that stores S3 server access logs. I want to use a query to summarize the data daily (using Lambda & Event Bridge) and insert it into another table. However, this being my first time doing this, I'm running into some issues and not really sure what's causing the error. I would greatly appreciate any advice/suggestions from the experts. The query is below and the error message I receive is:

GENERIC_INTERNAL_ERROR: No partition found with values [2025, 5, 27]. However, there are valid records in aws_s3_data_downloads.s3_bucket_data_downloads_logs

The table aws_s3_data_downloads.s3_bucket_data_downloads_logs schema uses the same one found at https://repost.aws/knowledge-center/analyze-logs-athena.

```
INSERT INTO s3_download_summary
SELECT
  CAST(date_parse(timestamp, '%Y/%m/%d') AS DATE) AS log_date,
--   date_parse(timestamp, '%Y/%m/%d') AS log_date,
  regexp_extract(key, '^(.*)/[^/]+$', 1) AS prefix,
  regexp_extract(key, '.*/([^/]+)$', 1) AS object_name,
  COUNT(*) AS download_count,
  SUM(bytessent) AS total_bytes_sent,
  SUM(objectsize) AS total_object_size,
  year(date_parse(timestamp, '%Y/%m/%d')) AS year,
  month(date_parse(timestamp, '%Y/%m/%d')) AS month,
  day(date_parse(timestamp, '%Y/%m/%d')) AS day
FROM
  aws_s3_data_downloads.s3_bucket_data_downloads_logs
WHERE (key like 'orthoimagery-program/county-mosaics/%' OR key like 'NCOM-data/%')
  AND operation LIKE 'REST.GET.OBJECT%'
  AND timestamp IS NOT NULL
GROUP BY
--   date_parse(timestamp, '%Y/%m/%d'),
--   regexp_extract(key, '.*/([^/]+)$', 1),
--   regexp_extract(key, '^(.*)/[^/]+$', 1)
  CAST(date_parse(timestamp, '%Y/%m/%d') AS DATE),
  regexp_extract(key, '^(.*)/[^/]+$', 1),
  regexp_extract(key, '.*/([^/]+)$', 1),
  year(date_parse(timestamp, '%Y/%m/%d')),
  month(date_parse(timestamp, '%Y/%m/%d')),
  day(date_parse(timestamp, '%Y/%m/%d'))
```

re:Post Agent · Answer

The error "GENERIC_INTERNAL_ERROR: No partition found with values [2025, 5, 27]" suggests that your INSERT INTO statement is trying to write data to a partition that doesn't exist in your target table (s3_download_summary).

This typically happens when:

1. Your target table is partitioned by year, month, and day (as indicated by your query)
2. Your query is generating data for a partition (2025, 5, 27) that doesn't exist yet in the target table

There are a few ways to resolve this:

1. **Create the partitions first**: Before running your INSERT INTO query, you need to ensure the partitions exist in your target table. You can create them using ALTER TABLE ADD PARTITION statements.

2. **Use CTAS and INSERT INTO approach**: A common pattern in Athena is to:
- First create a table with the right structure using CREATE TABLE AS SELECT (CTAS)
- Then use INSERT INTO to add data to it

3. **Check your data**: Verify if your source data actually contains records for 2025-05-27. This could be a data issue where your timestamp parsing is resulting in future dates.

4. **Modify your table properties**: If you're frequently inserting data into new partitions, you might want to consider setting up your target table to automatically add partitions as needed.

When using INSERT INTO with partitioned tables in Athena, remember that:
- Athena doesn't modify existing files but writes new ones
- The INSERT operation will create new files in the S3 location corresponding to the partition
- The partition must exist before you can insert data into it
- The columns in your SELECT statement must match the order and data types of the target table

For your specific case, I recommend checking if the 2025-05-27 partition exists in your target table and creating it if needed, or investigating why your query is generating data for a future date.

Having said this if you are still facing this issue, I would suggest you to reach out to AWS support via AWS support ticket.

**Sources**  
[INSERT INTO - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/insert-into.html)  
[Use CTAS and INSERT INTO for ETL and data analysis - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/ctas-insert-into-etl.html)

Inserting aggregated data into summary table using Athena

Add your answer

Relevant content