Error querying data after moving from one bucket to another and changing the partition structure

Hello Bob,

I understand you're encountering an error after moving your data and changing the partition structure. This issue is common when there's a mismatch between the table definition in Athena and the actual partition structure in your S3 bucket.

The error message "GENERIC_INTERNAL_ERROR: java.lang.IllegalArgumentException: partition value count must match partition column count" indicates that Athena is expecting a different number of partition columns than what's actually present in your data.

To resolve this issue, you'll need to update your table definition in Athena to match the new partition structure. Here are the steps you can take:

1. First, drop the existing table in Athena. You can do this with a DROP TABLE command.

2. Create a new table definition that matches your new partition structure (Year/Month/Day). Make sure the PARTITIONED BY clause in your CREATE TABLE statement only includes these three columns.

3. After creating the new table, you'll need to add the partitions. You can do this using the ALTER TABLE ADD PARTITION command for each partition, or use MSCK REPAIR TABLE if you have Hive-style partitions (which it seems you do).

4. If you have a large number of partitions, you might want to consider using a Glue Crawler to automatically detect and add the partitions to your table.

5. Once the table is recreated and partitions are added, try running your query again.

If you're still experiencing issues after these steps, you may need to double-check that all your data files conform to the new partition structure and that there aren't any leftover files from the old structure causing conflicts.

Remember, whenever you change your data structure or location, it's crucial to update your table definitions in Athena to match. This ensures that Athena can correctly interpret and query your data.

If you continue to face problems, you might want to verify the consistency of your S3 data with the table definition, or consider using AWS Glue to manage your table metadata more dynamically.
Sources
Troubleshoot issues in Athena - Amazon Athena
Troubleshoot Athena queries that have new partitions | AWS re:Post