How do I resolve the "HIVE_INVALID_PARTITION_VALUE" error message when I query an Amazon VPC table in Amazon Athena?

Short description

You get the following error message when the partition values are incorrect in the ALTER TABLE ADD PARTITION SQL query:

"HIVE_INVALID_PARTITION_VALUE: Invalid partition value 'YYYY-MM-dd' for DATE partition key: date=YYYY-MM-dd"

Resolution

To resolve this issue, make sure that the partition values are correct in the ALTER TABLE ADD PARTITION query.

The following example Amazon VPC Flow Logs query correctly creates a single partition for a specific date:

ALTER TABLE vpc_flow_logs
ADD PARTITION (date='2023-12-06')
LOCATION 's3://DOC-EXAMPLE-BUCKET/prefix/AWSLogs/123456789/vpcflowlogs/us-east-1/2023/12/06';

Note: Replace the date, location, account ID, and AWS Region with your variables.

The following example VPC Flow Logs query fails because the date partition values aren't specified:

ALTER TABLE vpc_flow_logs
ADD PARTITION (date='YYYY-MM-dd')
LOCATION 's3://DOC-EXAMPLE-BUCKET/prefix/AWSLogs/123456789/vpcflowlogs/us-east-1/2023/12/06';

Related information

Creating and querying tables for custom VPC flow logs

How do I use Amazon Athena to analyze VPC flow logs?