Pourquoi est-ce que je reçois des messages d’erreurs lorsque j’essaie de lire des données JSON dans Amazon Athena ?

Résolution

Vérifiez les problèmes courants suivants :

Utiliser un SerDe JSON adapté

Athena utilise l’un des SerDes JSON suivants pour traiter le JSON :

• Le SerDe Hive JSON natif
• Le SerDe OpenX JSON
• Le SerDe Amazon Ion Hive

Si vous devez exécuter la requête INSERT INTO sur la table créée, utilisez le SerDe Hive JSON.

Si vous utilisez le SerDe OpenX JSON, vous pouvez ignorer les enregistrements mal formés pour identifier les fichiers à l’origine des erreurs. Dans l'exemple suivant, lorsque ignore.malformed.json est défini sur true, les enregistrements mal formés sont renvoyés sous la forme NULL :

CREATE EXTERNAL TABLE json (    id int,
    name string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/';

Interrogez la nouvelle table pour identifier les fichiers contenant des enregistrements mal formés, comme suit :

SELECT "$PATH", * FROM your_table where your_column is NULL

Remarque : si vous exécutez des requêtes Athena pour des fichiers JSON joliment imprimés, vous pouvez utiliser le SerDe Amazon Ion Hive. Le SerDe Amazon Ion Hive ne s’attend pas à ce que chaque ligne de données se trouve sur une seule ligne. Utilisez cette fonctionnalité pour interroger des jeux de données JSON dans un format à impression élégante ou pour séparer les champs dans une ligne à l'aide de caractères de saut de ligne.

Utiliser une ligne par enregistrement

Les enregistrements JSON suivants sont correctement formatés et peuvent être lus par les trois SerDe JSON :

{ "id" : 50, "name":"John" }
{ "id" : 51, "name":"Jane" }
{ "id" : 53, "name":"Jill" }

Les enregistrements JSON suivants ne peuvent être lus que par le SerDe Amazon Ion Hive :

{  "id" : 50,
  "name":"John"
}
{
  "id" : 51,
  "name":"Jane"
}
{
  "id" : 53,
  "name":"Jill"
}

Les données JSON suivantes contiennent plusieurs enregistrements sur une seule ligne et ne sont pas correctement formatées :

{ "id" : 50, "name":"John" } { "id" : 51, "name":"Jane" } { "id" : 53, "name":"Jill" }

Utiliser un type de données adapté dans chaque colonne

La deuxième ligne de l’exemple suivant contient un type de données incorrect pour la valeur « age ». La valeur de la colonne devrait être « 11 » au lieu de « eleven ». Cela entraîne le message d’erreur suivant : HIVE_BAD_DATA : erreur lors de l’analyse de la valeur du champ « eleven » dans le champ 1 : pour la chaîne de saisie : « eleven ».

{"name":"Patrick","age":35,"address":"North Street"}
{"name":"Carlos","age":"eleven","address":"Flowers Street"}
{"name":"Fabiana","age":22,"address":"Main Street"}

Utiliser l’extension appropriée pour les fichiers JSON compressés

Lorsque vous utilisez un fichier JSON compressé, celui-ci doit se terminer au format « .gz ». Par exemple, « monfichier.json.gz » ou « myfile.gz » sont des extensions correctement formatées.

Utiliser des colonnes insensibles à la casse ou définir la propriété case.insensitive sur « false »

Par défaut, Athena ne distingue pas les majuscules et minuscules. Si le nom de vos colonnes diffère en majuscules, par exemple « Colonne » et « colonne », Athena génère l’erreur suivante :

« HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key »

De plus, vos données ne sont pas visibles dans Athena. Pour éviter cette erreur, générez vos données avec des colonnes ne faisant pas la distinction entre majuscules et minuscules.

Si vous utilisez OpenX SerDe, utilisez des noms de clé ne faisant pas la distinction entre majuscules et minuscules. Pour ce faire, définissez la propriété SerDe case.insensitive sur false et ajoutez un mappage pour la clé majuscule. Par exemple, vous pouvez utiliser des colonnes majuscules et minuscules comme celles-ci :

{"Username": "bob1234", "username": "bob" }

Utilisez les propriétés SerDe suivantes :

CREATE external TABLE casesensitive_json (user_name String,username String)
ROW FORMAT serde 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'mapping.user_name' = 'Username','case.insensitive'='false')
LOCATION 's3://awsexamplebucket/';

Si le fichier JSON contient des noms de colonnes faisant la distinction entre majuscules et minuscules, mettez-le à jour pour supprimer les colonnes dupliquées :

{"username": "bob1234", "username": "bob" }

Vérifier que toutes les lignes de la table SerDe JSON sont au format JSON

Pour savoir si des lignes JSON ou des noms de fichiers ne sont pas valides dans la table Athena, procédez comme suit :

1. Créez une table avec un délimiteur qui n’est pas présent dans les fichiers d’entrée. Exécutez une commande similaire au modèle suivant :

   CREATE EXTERNAL TABLE IF NOT EXISTS json_validator (jsonrow string) ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '%'
   location 's3://awsexamplebucket/';

2. Exécutez une requête similaire au modèle suivant pour renvoyer le nom du fichier, les détails des lignes et le chemin Amazon S3 pour les lignes JSON non valides :

   WITH testdataset AS (SELECT "$path" s3path,jsonrow,try(json_parse(jsonrow)) isjson FROM json_validator)
   SELECT * FROM testdataset WHERE ISJSON IS NULL;

Informations connexes

Bonnes pratiques pour la lecture des données JSON

Erreurs liées à JSON