Athena SemanticException Partition - Non-Partition Columns

0

I am trying to use an Athena table for Config data that is supposed to be partitioned. The partition is not getting created because of 'non-partition columns'.

ALTER TABLE aws_config_configuration_snapshot ADD PARTITION (accountid='444453583253', dt='latest', region='us-east-1') location 's3://config-bucket-444453583253-us-east-1/AWSLogs/444453583253/Config/us-east-1/2023/1/24/ConfigSnapshot/'

The error shown in Athena is: [ErrorCategory:USER_ERROR, ErrorCode:SYNTAX_ERROR], Detail:FAILED: SemanticException Partition spec {accountid=444453583253, dt=latest, region=us-east-1} contains non-partition columns This query ran against the "cost" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: a9974a89-2ae7-4416-97de-02a6ee5ee5f2

The external table syntax is as follows:

CREATE EXTERNAL TABLE aws_config_configuration_snapshot ( fileversion STRING, configsnapshotid STRING, configurationitems ARRAY < STRUCT < configurationItemVersion: STRING, configurationItemCaptureTime: STRING, configurationStateId: BIGINT, awsAccountId: STRING, configurationItemStatus: STRING, resourceType: STRING, resourceId: STRING, resourceName: STRING, ARN: STRING, awsRegion: STRING, availabilityZone: STRING, configurationStateMd5Hash: STRING, configuration: STRING, supplementaryConfiguration: MAP < STRING, STRING >, tags: MAP < STRING, STRING >, resourceCreationTime: STRING > > ) PARTITIONED BY (dt STRING, region STRING) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' WITH SERDEPROPERTIES ( 'case.insensitive' = 'false', 'mapping.fileversion' = 'fileVersion', 'mapping.configsnapshotid' = 'configSnapshotId', 'mapping.configurationitems' = 'configurationItems', 'mapping.configurationitemversion' = 'configurationItemVersion', 'mapping.configurationitemcapturetime' = 'configurationItemCaptureTime', 'mapping.configurationstateid' = 'configurationStateId', 'mapping.awsaccountid' = 'awsAccountId', 'mapping.configurationitemstatus' = 'configurationItemStatus', 'mapping.resourcetype' = 'resourceType', 'mapping.resourceid' = 'resourceId', 'mapping.resourcename' = 'resourceName', 'mapping.arn' = 'ARN', 'mapping.awsregion' = 'awsRegion', 'mapping.availabilityzone' = 'availabilityZone', 'mapping.configurationstatemd5hash' = 'configurationStateMd5Hash', 'mapping.supplementaryconfiguration' = 'supplementaryConfiguration', 'mapping.configurationstateid' = 'configurationStateId' ) LOCATION 's3://config-bucket-444453583253-us-east-1/AWSLogs/';

A similar problem is noted here: https://repost.aws/questions/QU43lhf9JOSv6Ew6QT5y4fZg/not-able-to-get-the-data-in-query-result-in-the-athena-for-the-aws-config-from-s-3-bucket

1 Answer
0
Accepted Answer

Your table definition says "PARTITIONED BY (dt STRING, region STRING)"; it's not partitioned by accountid so you can't add a partition containing that column.

EXPERT
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions