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

上次更新时间:2019 年 12 月 9 日

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

简短描述

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

{
  "fileVersion": "1.0",
  "configSnapshotId": "35eced35-a13a-45b7-81e4-446e35616e70",
  "configurationItems": [
    {
      "tags": { "tc:Name": "6", "tc:name": "abc6-38" }
    },
    {
      "tags": { "tc:Name": "6", "tc:name": "abc6-38" }
    },
    {
      "tags": { "tc:Name": "6" }
    },
   {
      "tags": { "tc:name": "6" }
    }
  ]
}

解决方案

运行以下类似 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 ( 
  '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://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['TAGNAME'] 访问标签。例如,要访问 tc:Name 标签,请运行以下查询:

select configurationItem.tags['tc:Name']
  from your_table
cross join unnest(configurationItems) as t(configurationItem)
where configurationItem.tags['tc:Name'] is not null