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 traite les données JSON à l’aide de l’un des deux SerDe JSON :

• Apache Hive natif / HCatalog JsonSerDe (org.apache.hive.hcatalog.data.JsonSerDe)
• OpenX SerDe (org.openx.data.jsonserde.JsonSerDe)

Si vous ne savez pas quel SerDe utiliser, essayez les deux versions de SerDe. Si vous utilisez OpenX SerDe, vous pouvez ignorer les enregistrements incorrects pour identifier les lignes à l’origine des erreurs, comme illustré dans l’exemple suivant. Lorsque ignore.malformed.json est défini sur true, les enregistrements incorrects 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 incorrects. Exemple :

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

Utiliser une ligne par enregistrement

Le format des enregistrements JSON suivants est correct :

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

Le format des enregistrements JSON suivants est incorrect :

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

Le format de ces enregistrements est également incorrect :

{ "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 par « .json » suivi de l’extension du format de compression, telle que « .gz ». Par exemple, le format de l’extension « myfile.json.gz » est correct pour un fichier gzip.

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 les noms de vos colonnes diffèrent uniquement selon la casse (par exemple, « Colonne » et « colonne »), Athena génèrera une erreur (« HIVE_CURSOR_ERROR : la ligne n’est pas un objet JSON valide - JSONException : clé en double ») et vos données ne seront pas visibles dans Athena. Le moyen le plus simple d’éviter ce problème est de générer les données avec des colonnes insensibles à la casse.

Si vous utilisez OpenX SerDe, vous pouvez utiliser des noms de clé insensibles à la casse. 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/';

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

Pour savoir si la table Athena contient des lignes JSON ou des noms de fichiers non valides, 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

Résolution des erreurs liées à JSON