Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra. # Interrogation des données JSON Amazon Athena vous permet d’interroger des données codées en JSON, d’extraire des données de JSON imbriqués, de rechercher des valeurs et de déterminer la longueur et la taille de tableaux JSON. Les exemples suivants de données relatives à des planètes vous aideront à comprendre les principes de base de l’interrogation de données JSON dans Athena : ``` {name:"Mercury",distanceFromSun:0.39,orbitalPeriod:0.24,dayLength:58.65} {name:"Venus",distanceFromSun:0.72,orbitalPeriod:0.62,dayLength:243.02} {name:"Earth",distanceFromSun:1.00,orbitalPeriod:1.00,dayLength:1.00} {name:"Mars",distanceFromSun:1.52,orbitalPeriod:1.88,dayLength:1.03} ``` Vous remarquerez que chaque enregistrement (chaque ligne de la table) se trouve sur une ligne distincte. Pour interroger ces données JSON, vous pouvez utiliser une instruction `CREATE TABLE` de ce type : ``` CREATE EXTERNAL TABLE `planets_json`( `name` string, `distancefromsun` double, `orbitalperiod` double, `daylength` double) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat' LOCATION 's3://amzn-s3-demo-bucket/json/' ``` Pour interroger les données, utilisez une instruction `SELECT` simple semblable à celle présentée dans l’exemple ci-dessous : ``` SELECT * FROM planets_json ``` Les résultats de la requête se présentent comme suit : **** | \$1 | name | distancefromsun | orbitalperiod | daylength | | --- | --- | --- | --- | --- | | 1 | Mercury | 0,39 | 0,24 | 58,65 | | 2 | Venus | 0,72 | 0,62 | 243,02 | | 3 | Earth | 1.0 | 1.0 | 1.0 | | 4 | Mars | 1,52 | 1,88 | 1,03 | Vous remarquez que l’instruction `CREATE TABLE` utilise le [OpenX JSON SerDe](openx-json-serde.md), ce qui nécessite que chaque enregistrement JSON se trouve sur une ligne distincte. Si le JSON est au format d’impression ou si tous les enregistrements se trouvent sur une seule ligne, les données ne seront pas lues correctement. Pour interroger des données JSON dans un joli format d'impression, vous pouvez utiliser le JSON [Amazon Ion Hive SerDe](ion-serde.md) au lieu d'OpenX. SerDe Examinez les données précédentes stockées au format d’impression : ``` { name:"Mercury", distanceFromSun:0.39, orbitalPeriod:0.24, dayLength:58.65 } { name:"Venus", distanceFromSun:0.72, orbitalPeriod:0.62, dayLength:243.02 } { name:"Earth", distanceFromSun:1.00, orbitalPeriod:1.00, dayLength:1.00 } { name:"Mars", distanceFromSun:1.52, orbitalPeriod:1.88, dayLength:1.03 } ``` Pour interroger ces données sans procéder à une remise en forme, vous pouvez utiliser une instruction `CREATE TABLE` semblable à celle présentée dans l’exemple ci-dessous. Notez qu'au lieu de spécifier le code JSON OpenX SerDe, l'instruction spécifie. `STORED AS ION` ``` CREATE EXTERNAL TABLE `planets_ion`( `name` string, `distancefromsun` DECIMAL(10, 2), `orbitalperiod` DECIMAL(10, 2), `daylength` DECIMAL(10, 2)) STORED AS ION LOCATION 's3://amzn-s3-demo-bucket/json-ion/' ``` La requête `SELECT * FROM planets_ion` produit les mêmes résultats que précédemment. Pour plus d'informations sur la création de tables de cette manière à l'aide d'Amazon Ion Hive SerDe, consultez[Création de tables Amazon Ion](ion-serde-using-create-table.md). L’exemple de données JSON précédent ne contient pas de types de données complexes tels que des tableaux ou des structures imbriqués. Pour plus d’informations sur l’interrogation de données JSON imbriquées, consultez [Exemple : désérialisation des données JSON imbriquées](openx-json-serde.md#nested-json-serde-example). **Topics** + [ # Bonnes pratiques pour la lecture des données JSON ](parsing-json-data.md) + [ # Extraction de données JSON à partir de chaînes ](extracting-data-from-JSON.md) + [ # Recherche de valeurs dans les tableaux JSON ](searching-for-values.md) + [ # Obtention de la longueur et de la taille de tableaux JSON ](length-and-size.md) + [ # Résolution des problèmes de requêtes JSON ](json-troubleshooting.md) # Bonnes pratiques pour la lecture des données JSON JavaScript La notation d'objet (JSON) est une méthode courante pour coder des structures de données sous forme de texte. De nombreux outils et applications produisent des données codées en JSON. Dans Amazon Athena, vous pouvez créer des tables à partir de données externes et y inclure des données codées en JSON. Pour ce type de données source, utilisez Athena avec [SerDe bibliothèques JSON](json-serde.md). Utilisez les conseils suivants pour lire les données encodées JSON : + Choisissez le bon SerDe, un JSON SerDe natif ou un OpenX SerDe. `org.apache.hive.hcatalog.data.JsonSerDe` `org.openx.data.jsonserde.JsonSerDe` Pour de plus amples informations, veuillez consulter [SerDe bibliothèques JSON](json-serde.md). + Assurez-vous que chaque enregistrement encodé JSON est représenté sur une ligne distincte, et au non format d'impression (pretty-printed). **Note** Il SerDe s'attend à ce que chaque document JSON se trouve sur une seule ligne de texte sans aucun caractère de fin de ligne séparant les champs de l'enregistrement. Si le texte JSON est dans un joli format d'impression, vous pouvez recevoir un message d'erreur tel que HIVE\$1CURSOR\$1ERROR : Row is not a valid JSON Object ou HIVE\$1CURSOR\$1ERROR : : Unexpected JsonParseException end-of-input : expected close marker for OBJECT lorsque vous essayez d'interroger la table après l'avoir créée. Pour plus d'informations, consultez la section [Fichiers de données JSON](https://github.com/rcongiu/Hive-JSON-Serde#json-data-files) dans la SerDe documentation OpenX sur. GitHub + Générez vos données encodées JSON dans des colonnes non sensibles à la casse. + Fournissez une option pour ignorer les enregistrements incorrects, comme dans cet exemple. ``` CREATE EXTERNAL TABLE json_table ( column_a string, column_b int ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true') LOCATION 's3://amzn-s3-demo-bucket/path/'; ``` + Convertissez les champs en données sources ayant un schéma indéterminé en chaînes encodées JSON dans Athena. Quand Athena crée des tables basées sur des données JSON, le service analyse les données en fonction du schéma existant et prédéfini. Cependant, toutes vos données peuvent ne pas avoir un schéma prédéfini. Pour simplifier la gestion des schémas dans de tels cas, il s'avère souvent utile de convertir les champs des données source ayant un schéma non déterminé en chaînes JSON dans Athena, puis d'utiliser [SerDe bibliothèques JSON](json-serde.md). Prenons, par exemple, une application IoT qui publie des événements avec des champs communs de différents capteurs. L'un de ces champs doit stocker une charge utile personnalisée qui est propre au capteur qui envoie l'événement. Dans ce cas, comme vous ne connaissez pas le schéma, nous vous recommandons de stocker les informations sous la forme d'une chaîne codée en JSON. Pour ce faire, convertissez des données de votre table Athena en JSON, comme dans l'exemple suivant. Vous pouvez également convertir les données codées en JSON en types de données Athena. **Topics** + [ # Conversion de types de données Athena en données JSON ](converting-native-data-types-to-json.md) + [ # Conversion de données JSON en types de données Athena ](converting-json-to-native-data-types.md) # Conversion de types de données Athena en données JSON Pour convertir des données Athena en types de données JSON, utilisez `CAST`. ``` WITH dataset AS ( SELECT CAST('HELLO ATHENA' AS JSON) AS hello_msg, CAST(12345 AS JSON) AS some_int, CAST(MAP(ARRAY['a', 'b'], ARRAY[1,2]) AS JSON) AS some_map ) SELECT * FROM dataset ``` Cette requête renvoie : ``` +-------------------------------------------+ | hello_msg | some_int | some_map | +-------------------------------------------+ | "HELLO ATHENA" | 12345 | {"a":1,"b":2} | +-------------------------------------------+ ``` # Conversion de données JSON en types de données Athena Pour convertir des données JSON en types de données Athena, utilisez `CAST`. **Note** Dans cet exemple, pour indiquer que les chaînes sont codées en JSON, commencez avec le mot-clé `JSON` et utilisez des apostrophes, par exemple, `JSON '12345'` ``` WITH dataset AS ( SELECT CAST(JSON '"HELLO ATHENA"' AS VARCHAR) AS hello_msg, CAST(JSON '12345' AS INTEGER) AS some_int, CAST(JSON '{"a":1,"b":2}' AS MAP(VARCHAR, INTEGER)) AS some_map ) SELECT * FROM dataset ``` Cette requête renvoie : ``` +-------------------------------------+ | hello_msg | some_int | some_map | +-------------------------------------+ | HELLO ATHENA | 12345 | {a:1,b:2} | +-------------------------------------+ ``` # Extraction de données JSON à partir de chaînes Vous pouvez disposer de données source contenant des chaînes codées en JSON que vous ne souhaitez pas forcément désérialiser dans une table dans Athena. Dans ce cas, vous pouvez toujours exécuter des opérations SQL sur ces données à l'aide des fonctions JSON disponibles dans Presto. Envisagez cette chaîne JSON comme exemple d'ensemble de données. ``` {"name": "Susan Smith", "org": "engineering", "projects": [ {"name":"project1", "completed":false}, {"name":"project2", "completed":true} ] } ``` ## Exemples : extraction de propriétés Pour extraire les propriétés `name` et `projects` de la chaîne JSON, utilisez la fonction `json_extract` comme dans l'exemple suivant. La fonction `json_extract` utilise la colonne contenant la chaîne JSON et effectue une recherche dans celle-ci à l'aide d'une expression de type `JSONPath` avec la notation de points `.`. **Note** `JSONPath` effectue un parcours simple de l'arborescence. Cette expression utilise le signe `$` pour indiquer la racine du document JSON, suivi d'un point et d'un élément imbriqué directement sous la racine, par exemple `$.name`. ``` WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS myblob ) SELECT json_extract(myblob, '$.name') AS name, json_extract(myblob, '$.projects') AS projects FROM dataset ``` La valeur renvoyée est une chaîne codée en JSON et non un type de données Athena natif. ``` +-----------------------------------------------------------------------------------------------+ | name | projects | +-----------------------------------------------------------------------------------------------+ | "Susan Smith" | [{"name":"project1","completed":false},{"name":"project2","completed":true}] | +-----------------------------------------------------------------------------------------------+ ``` Pour extraire la valeur scalaire de la chaîne JSON, utilisez la fonction `json_extract_scalar(json, json_path)`. Cette fonction est semblable à `json_extract`, mais renvoie une valeur de chaîne `varchar` au lieu d’une chaîne encodée en JSON. La valeur du *json\$1path* paramètre doit être un scalaire (booléen, numérique ou chaîne). **Note** N'utilisez pas la fonction `json_extract_scalar` sur des tableaux, des mappages ou des structures. ``` WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}' AS myblob ) SELECT json_extract_scalar(myblob, '$.name') AS name, json_extract_scalar(myblob, '$.projects') AS projects FROM dataset ``` Cette requête renvoie : ``` +---------------------------+ | name | projects | +---------------------------+ | Susan Smith | | +---------------------------+ ``` Pour obtenir le premier élément de la propriété `projects` dans l'exemple de tableau, utilisez la fonction `json_array_get` et spécifiez la position d'index. ``` WITH dataset AS ( SELECT '{"name": "Bob Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}' AS myblob ) SELECT json_array_get(json_extract(myblob, '$.projects'), 0) AS item FROM dataset ``` Cette fonction renvoie la valeur à la position d'index spécifiée dans le tableau codé en JSON. ``` +---------------------------------------+ | item | +---------------------------------------+ | {"name":"project1","completed":false} | +---------------------------------------+ ``` Pour renvoyer un type de chaîne Athena, utilisez l'opérateur `[]` à l'intérieur d'une expression `JSONPath`, puis utilisez la fonction `json_extract_scalar`. Pour plus d’informations sur `[]`, consultez [Accès à des éléments de tableau](accessing-array-elements.md). ``` WITH dataset AS ( SELECT '{"name": "Bob Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}' AS myblob ) SELECT json_extract_scalar(myblob, '$.projects[0].name') AS project_name FROM dataset ``` Elle renvoie le résultat suivant : ``` +--------------+ | project_name | +--------------+ | project1 | +--------------+ ``` # Recherche de valeurs dans les tableaux JSON Afin de déterminer si une valeur spécifique existe dans un tableau codé au format JSON, utilisez la fonction `json_array_contains`. La requête suivante répertorie les noms des utilisateurs qui participent à « project2 ». ``` WITH dataset AS ( SELECT * FROM (VALUES (JSON '{"name": "Bob Smith", "org": "legal", "projects": ["project1"]}'), (JSON '{"name": "Susan Smith", "org": "engineering", "projects": ["project1", "project2", "project3"]}'), (JSON '{"name": "Jane Smith", "org": "finance", "projects": ["project1", "project2"]}') ) AS t (users) ) SELECT json_extract_scalar(users, '$.name') AS user FROM dataset WHERE json_array_contains(json_extract(users, '$.projects'), 'project2') ``` Cette requête renvoie une liste d'utilisateurs. ``` +-------------+ | user | +-------------+ | Susan Smith | +-------------+ | Jane Smith | +-------------+ ``` L'exemple de requête suivant répertorie les noms des utilisateurs qui ont terminé des projets, ainsi que le nombre total de projets terminés. La requête effectue les actions suivantes : + Utilise les instructions imbriquées `SELECT` pour plus de clarté. + Extrait le tableau de projets. + Convertit le tableau en un tableau natif de paires clé-valeur utilisant `CAST`. + Extrait chaque élément individuel du tableau à l'aide de l'opérateur `UNNEST`. + Filtre les valeurs obtenues en fonction des projets terminés et en fournit un décompte. **Note** Lorsque vous utilisez `CAST` pour `MAP`, vous pouvez spécifier `VARCHAR` en tant qu'élément clé (chaîne native dans Presto), mais laisser JSON pour la valeur, car les valeurs de `MAP` sont de types différents : chaîne pour la première paire clé-valeur, et booléen pour la seconde. ``` WITH dataset AS ( SELECT * FROM (VALUES (JSON '{"name": "Bob Smith", "org": "legal", "projects": [{"name":"project1", "completed":false}]}'), (JSON '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project2", "completed":true}, {"name":"project3", "completed":true}]}'), (JSON '{"name": "Jane Smith", "org": "finance", "projects": [{"name":"project2", "completed":true}]}') ) AS t (users) ), employees AS ( SELECT users, CAST(json_extract(users, '$.projects') AS ARRAY(MAP(VARCHAR, JSON))) AS projects_array FROM dataset ), names AS ( SELECT json_extract_scalar(users, '$.name') AS name, projects FROM employees, UNNEST (projects_array) AS t(projects) ) SELECT name, count(projects) AS completed_projects FROM names WHERE cast(element_at(projects, 'completed') AS BOOLEAN) = true GROUP BY name ``` Cette requête renvoie le résultat suivant : ``` +----------------------------------+ | name | completed_projects | +----------------------------------+ | Susan Smith | 2 | +----------------------------------+ | Jane Smith | 1 | +----------------------------------+ ``` # Obtention de la longueur et de la taille de tableaux JSON Vous pouvez utiliser les fonctions `json_array_length` et `json_size` pour obtenir la longueur et la taille de tableaux JSON. ## Exemple : `json_array_length` Pour obtenir la longueur d'un tableau codé en JSON, utilisez la fonction `json_array_length`. ``` WITH dataset AS ( SELECT * FROM (VALUES (JSON '{"name": "Bob Smith", "org": "legal", "projects": [{"name":"project1", "completed":false}]}'), (JSON '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project2", "completed":true}, {"name":"project3", "completed":true}]}'), (JSON '{"name": "Jane Smith", "org": "finance", "projects": [{"name":"project2", "completed":true}]}') ) AS t (users) ) SELECT json_extract_scalar(users, '$.name') as name, json_array_length(json_extract(users, '$.projects')) as count FROM dataset ORDER BY count DESC ``` Cette requête renvoie le résultat suivant : ``` +---------------------+ | name | count | +---------------------+ | Susan Smith | 2 | +---------------------+ | Bob Smith | 1 | +---------------------+ | Jane Smith | 1 | +---------------------+ ``` ## Exemple : `json_size` Pour obtenir la taille d'un tableau ou d'un objet codé en JSON, utilisez la fonction `json_size`, puis spécifiez la colonne contenant la chaîne JSON et l'expression `JSONPath` sur le tableau ou l'objet. ``` WITH dataset AS ( SELECT * FROM (VALUES (JSON '{"name": "Bob Smith", "org": "legal", "projects": [{"name":"project1", "completed":false}]}'), (JSON '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project2", "completed":true},{"name":"project3", "completed":true}]}'), (JSON '{"name": "Jane Smith", "org": "finance", "projects": [{"name":"project2", "completed":true}]}') ) AS t (users) ) SELECT json_extract_scalar(users, '$.name') as name, json_size(users, '$.projects') as count FROM dataset ORDER BY count DESC ``` Cette requête renvoie le résultat suivant : ``` +---------------------+ | name | count | +---------------------+ | Susan Smith | 2 | +---------------------+ | Bob Smith | 1 | +---------------------+ | Jane Smith | 1 | +---------------------+ ``` # Résolution des problèmes de requêtes JSON Pour obtenir de l'aide sur la résolution des problèmes liés aux requêtes JSON, conzultez [Erreurs liées à JSON](troubleshooting-athena.md#troubleshooting-athena-json-related-errors) ou les ressources suivantes : + [J'obtiens des erreurs lorsque j'essaie de lire des données JSON dans Amazon Athena.](https://aws.amazon.com/premiumsupport/knowledge-center/error-json-athena/) + [Comment résoudre le problème « HIVE\$1CURSOR\$1ERROR : Row is not a valid JSON object - JSONException : Duplicate key » lors de la lecture de fichiers depuis Athena ? AWS Config](https://aws.amazon.com/premiumsupport/knowledge-center/json-duplicate-key-error-athena-config/) + [La requête SELECT COUNT dans Amazon Athena renvoie un seul enregistrement alors que le fichier JSON d'entrée contient plusieurs enregistrements.](https://aws.amazon.com/premiumsupport/knowledge-center/select-count-query-athena-json-records/) + [Comment puis-je voir le fichier source Simple Storage Service (Amazon S3) pour une ligne dans un tableau Athena?](https://aws.amazon.com/premiumsupport/knowledge-center/find-s3-source-file-athena-table-row/) Consultez également [Considérations et limitations relatives aux requêtes SQL dans Amazon Athena](other-notable-limitations.md).