Comment résoudre l'erreur « HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key » lors de la lecture de fichiers depuis AWS Config dans Athena ?

Brève description

Cette erreur se produit généralement dans l'une des situations suivantes :

• Les ressources AWS Config possèdent plusieurs balises qui portent le même nom.
• Certaines balises sont en majuscules et d'autres en minuscules.

Par exemple, l'enregistrement suivant utilise les clés JSON tc:Name et tc:name :

{
  "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" }
  }
  ]
}

Résolution

Exécutez une instruction CREATE TABLE similaire à l'exemple ci-dessous. Cette instruction crée une table Athena, définit la valeur case.insensitive sur false et mappe les noms de colonne sur les clés JSON qui ne sont pas identiques aux noms de colonne. Avant d'exécuter cette instruction, tenez compte des points suivants :

• Dans le champ LOCATION (EMPLACEMENT), remplacez s3 ://awsexamplebucket/AWSLogs/ par le nom de votre compartiment Amazon Simple Storage Service (Amazon S3).
• Remplacez toutes les propriétés de mappage par vos noms de colonne et clés JSON (par exemple, 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/';

Si vous avez déjà chargé une table avec des partitions, vous pouvez ajouter les nouvelles propriétés SerDe à la table. Utilisez une instruction similaire à ce qui suit :

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')

Lorsque la table est prête, vous pouvez accéder aux balises à l'aide de configurationItem.tags['TAGNAME']. Par exemple, pour accéder à la balise tc:Name, exécutez la requête suivante :

SELECT configurationItem.tags['tc:Name']
FROM your_table
CROSS JOIN unnest(configurationItems) AS t(configurationItem)
WHERE configurationItem.tags['tc:Name'] IS NOT NULL

Informations connexes

Créez les table dans Amazon Athena à partir de mappages et de requêtes JSON imbriquées à l'aide de JSONSerDe

Pourquoi est-ce que je reçois des erreurs en essayant de lire des données JSON dans Amazon Athena ?

Hive-JSON-Serde (Github)