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

Dernière mise à jour : 23/05/2022

Lorsque j'essaie de lire des données JSON dans Amazon Athena, je reçois des erreurs de données NULL ou incorrectes.

Solution

Vérifiez les problèmes courants suivants :

Utiliser le SerDe JSON correct

Athena traite les données JSON en utilisant l'un des deux SerDes JSON :

Si vous ne savez pas quel est le SerDe que vous avez utilisé, essayez les deux versions de SerDe. Si vous utilisez le SerDe d'OpenX, vous pouvez ignorer les enregistrements incorrects pour identifier les lignes à l'origine des erreurs, comme illustré dans l'exemple suivant. Lorsque ignore.malformed.json a la valeur true, les enregistrements mal formatés renvoient 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 avec des enregistrements incorrectement formés. Par exemple :

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

Utiliser une ligne pour chaque enregistrement

Les enregistrements JSON suivants sont formatés correctement :

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

Les enregistrements JSON suivants ne sont pas formatés correctement :

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

Ces enregistrements sont également formatés incorrectement :

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

Utiliser le bon type de données dans chaque colonne

La deuxième ligne de l'exemple suivant contient un type de données incorrect pour « age » (âge). La valeur de la colonne doit être « 11 » au lieu de « eleven ». Il en résulte le message d'erreur suivant : HIVE_BAD_DATA: Error parsing field value 'eleven' for field 1: For input string: "eleven" (HIVE_BAD_DATA : erreur lors de l'analyse de la valeur de champ « onze » du champ 1 : pour la chaîne d'entrée : « onze »).

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

Utiliser la bonne extension pour les fichiers JSON compressés

Lorsque vous utilisez un fichier JSON compressé, le fichier doit se terminer par « .json », suivi de l'extension du format de compression, par exemple « .gz ». Par exemple, ceci est une extension correctement formatée pour un fichier gzip : « myfile.json.gz ».

Utiliser des colonnes qui ne sont pas sensibles à la casse ou affecter la valeur false à la propriété case.insensitive

Par défaut, Athena n'est pas sensible à la casse. Si vous avez des noms de colonne qui diffèrent uniquement par la casse (par exemple, « Colonne » et « colonne »), Athena génère une erreur (« HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException : Duplicate key ») (HIVE_CURSOR_ERROR : la ligne n'est pas un objet JSON valide - JSONException : dupliquer la clé) et vos données ne sont pas visibles dans Athena. La manière la plus simple d'éviter ce problème consiste à générer les données avec des colonnes qui ne sont pas sensibles à la casse.

Avec le SerDe d'OpenX, vous pouvez utiliser des noms de clé sensibles à la casse. Pour ce faire, affectez à la propriété SerDe case.insensitive la valeur false et ajoutez le mappage pour la clé en majuscules. Par exemple, pour utiliser des colonnes en majuscules et en minuscules comme ci-dessous :

{"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/';

Assurez-vous que toutes les lignes de la table SerDE JSON sont au format JSON

Pour déterminer s'il y a des lignes JSON ou des noms de fichiers non 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 à la suivante :

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 à la suivante pour renvoyer le nom de fichier, les détails de la ligne et le chemin d'accès 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;

Bonnes pratiques pour la lecture des données JSON

Résolution des problèmes liés à JSON

Cet article vous a-t-il été utile ?

Envoyer un commentaire

Avez-vous besoin d'aide pour une question technique ou de facturation ?

Contactez AWS Support