如何解决在 Athena 中读取 AWS Config 中的文件时出现的“HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key”错误？

上次更新时间：2021 年 3 月 25 日

使用 Amazon Athena 查询 AWS Config 文件时，我收到以下错误：“Error: HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key.” 简短描述

此错误通常发生在 AWS Config 资源具有多个采用相同名称的标签，且一部分标签采用大写而另一部分采用小写时。例如，以下记录使用 tc:Name 和 tc:name JSON 键：

{
  "fileVersion": "1.0",
  "configSnapshotId": "35eced35-a13a-45b7-81e4-446e35616e70",
  "configurationItems": 
}

解决方案

运行以下类似 CREATE TABLE 语句。此语句将会创建一个 Athena 表，将 case.insensitive 设为 false，并将列名称映射至与列名称不同的 JSON 键。在运行此语句之前：

在 LOCATION 字段中，将 s3://awsexamplebucket/AWSLogs/ 替换为 Amazon Simple Storage Service (Amazon S3) 存储桶的名称。 将所有映射属性替换为列名称和 JSON 键（例如，mapping.fileversion'='fileVersion'）。

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 (
 <b>'case.insensitive'='false'</b>,
 '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'
 )<br>LOCATION 's3://awsexamplebucket/AWSLogs/';

如果您拥有已加载分区的表，则可以将新的 SerDe 属性添加到如下所示表中：

ALTER TABLE aws_config_configuration_snapshot SET TBLPROPERTIES (
 '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')

当表准备就绪时，您可以使用 configurationItem.tags 访问标签。例如，要访问 tc:Name 标签，请运行以下查询：

SELECT configurationItem.tags
 FROM your_table
CROSS JOIN unnest(configurationItems) AS t(configurationItem)
WHERE configurationItem.tags IS NOT NULL