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