Athena query by partition column returns an error on hive partitioned table (vpc flow logs)

The error you're encountering with your Athena query on the partitioned VPC flow logs table is related to how you're trying to reference the partition columns in your query.

When querying partitioned tables in Athena, you need to reference the partition columns in the WHERE clause rather than trying to use an equals sign in the column name itself. The error message "Column 'partition_0 = awslogs' cannot be resolved" suggests you might be using incorrect syntax when filtering by partition.

For example, instead of writing something like:

SELECT * FROM vpc_flow_logs_db.your_table WHERE partition_0 = awslogs

You should use:

SELECT * FROM vpc_flow_logs_db.your_table WHERE partition_0 = 'awslogs'

Note that string values need to be enclosed in quotes.

Another possibility is that there might be a schema mismatch between the table definition and the actual partitions. When Athena runs a query, it validates the schema of the table and the schema of any partitions necessary for the query. If there's a mismatch, Athena may not be able to process the query.

Additionally, ensure that your IAM role has all the necessary permissions to access both the table metadata in AWS Glue Data Catalog and the underlying data in S3. Even though you mentioned providing all permissions for Glue and Athena, sometimes specific S3 bucket policies or access point configurations might be restricting access.

If the issue persists, you might want to try refreshing the table metadata in Athena or re-creating the table definition to ensure it properly aligns with your data structure.
Sources
Troubleshoot issues in Athena - Amazon Athena
Athena query failed when trying to access S3 from access point | AWS re:Post