Schéma sur Lire JSON Partie II

Schéma sur Lire JSON Partie II


Dans le Premier article de cette série, j’ai discuté du type de données Snowflake VARIANT, montré un exemple simple de chargement d’une colonne VARIANT dans une table avec un document JSON, puis à quel point il est facile d’interroger des données directement à partir de ce type de données. Dans cet article, je vais vous montrer comment accéder à un tableau de données dans le document JSON et comment nous gérons les tableaux imbriqués. Enfin, je vais vous donner un exemple d’agrégation à l’aide de données dans la structure JSON et à quel point il est simple de filtrer les résultats de votre requête en faisant référence à des valeurs dans un tableau.

Gestion des tableaux de données

L’une des fonctionnalités de JSON est la possibilité de spécifier et d’intégrer un tableau de données dans le document. Dans mon exemple, un tel tableau est children :

    "children": [
         { "name": "Jayden", "gender": "Male", "age": "10" },
         { "name": "Emma", "gender": "Female", "age": "8" },
         { "name": "Madelyn", "gender": "Female", "age": "6" }
     ]

Vous remarquerez qu’il y a effectivement 3 lignes dans le tableau et que chaque ligne comporte 3 sous-colonnes – nom, sexe et âge. Chacune de ces rangées constitue le évaluer de cette entrée de tableau qui comprend toutes les étiquettes et données de sous-colonne (souvenez-vous de cela pour plus tard). Alors, comment savoir combien de lignes il y a si vous n’avez pas accès aux données brutes ?

Comme ça:

select array_size(v:children) from json_demo;

La fonction taille_tableau le calcule pour nous. Pour extraire les données de chaque « ligne » du tableau, nous utilisons la notation par points d’avant, mais maintenant avec la spécification ajoutée pour le numéro de ligne du tableau (dans le [] supports):

select v:children[0].name from json_demo
union all
select v:children[1].name from json_demo
union all
select v:children[2].name from json_demo;

Prénoms d'enfants

Donc c’est intéressant mais alors, je ne veux vraiment pas écrire union all SQL pour parcourir tout le tableau (auquel cas j’ai besoin de savoir combien de valeurs il y a dans le tableau, n’est-ce pas ?).

Nous résolvons ce problème avec une autre nouvelle fonction SQL étendue appelée APLATIR. FLATTEN prend un tableau et renvoie une ligne pour chaque élément du tableau. Avec cela, vous pouvez sélectionner toutes les données du tableau comme si elles se trouvaient dans des lignes de tableau (donc pas besoin de savoir combien d’entrées il y a).

Au lieu de faire l’ensemble des UNION ALL, nous ajoutons le FLATTEN dans le DE clause et donnez-lui un alias de table :

select f.value:name
from json_demo, table(flatten(v:children)) f;

Cette syntaxe nous permet de créer un table virtuelle en ligne dans le DE clause.

Dans le SELECT, nous pouvons alors le référencer comme une table. Remarquez la notation f.value:nom.

F = l’alias de la table virtuelle du tableau children

évaluer = le contenu de l’élément retourné par la fonction FLATTEN

Nom = l’étiquette de la sous-colonne spécifique que nous voulons extraire de la évaluer

Les résultats, dans ce cas, sont les mêmes que le SELECT avec les UNION mais l’en-tête de colonne de sortie reflète la syntaxe différente (puisque je n’ai pas encore ajouté d’alias de colonne).

Aplatir les enfants 1

Maintenant, si un autre élément est ajouté au tableau (c’est-à-dire un 4e enfant), le SQL sera ne pas il faut changer. FLATTEN nous permet de déterminer la structure et le contenu du tableau à la volée! Cela rend le SQL résilient aux modifications du document JSON.

Avec cela en main, nous pouvons bien sûr obtenir toutes les sous-colonnes du tableau et les formater comme une table relationnelle :

select
   f.value:name::string as child_name,
   f.value:gender::string as child_gender,
   f.value:age::string as child_age
from json_demo, table(flatten(v:children)) f;

Aplatir les enfants du format

En mettant tout cela ensemble, je peux écrire une requête pour obtenir le nom du parent et tous les enfants comme ceci :

select
   v:fullName::string as parent_name,
   f.value:name::string as child_name,
   f.value:gender::string  as child_gender,
   f.value:age::string as child_age
from json_demo, table(flatten(v:children)) f;

Ce qui donne cette sortie :

Parent et enfants

Si je veux juste un décompte rapide des enfants par parent, je n’ai pas besoin de FLATTEN mais je me réfère à array_size :

select
   v:fullName::string as Parent_Name,
   array_size(v:children) as Number_of_Children
from json_demo;

Compter les enfants

Gestion de plusieurs baies

Vous vous souviendrez peut-être qu’il existe plusieurs tableaux dans notre exemple de chaîne JSON. Je peux tirer de plusieurs tableaux à la fois sans problème:

select
   v:fullName::string as Parent_Name,
   array_size(v:citiesLived) as Cities_lived_in,
   array_size(v:children) as Number_of_Children
from json_demo;

Parents, Enfants.  Ville

Qu’en est-il d’un tableau dans un tableau ?

Snowflake peut également gérer cela. À partir de nos données d’échantillon, nous pouvons voir annéesVécu est un tableau imbriqué dans le tableau décrit par villesVécu:

"citiesLived": [
 { "cityName": "London",
   "yearsLived": [ "1989", "1993", "1998", "2002" ]
 },
 { "cityName": "San Francisco",
   "yearsLived": [ "1990", "1993", "1998", "2008" ]
 },
 { "cityName": "Portland",
   "yearsLived<": [ "1993", "1998", "2003", "2005" ]
 },
 { "cityName": "Austin",
   "yearsLived": [ "1973", "1998", "2001", "2005" ]
 }
 ]

Pour extraire ces données, nous ajoutons un deuxième Clause FLATTEN qui transforme la annéesVécu tableau dans le FLATTENed villesVécu déployer.

select
  tf.value:cityName::string as city_name,
  yl.value::string as year_lived
from json_demo,
     table(flatten(v:citiesLived)) tf,
     table(flatten(tf.value:yearsLived)) yl;

Dans ce cas les 2nd FLATTEN (alias « yl”) transforme (vraiment pivote) le annéesVécu déployer pour chaque évaluer revenu du 1St Aplatir de la villesVécu tableau (« tf »).

La sortie des résultats montre Année vécue par ville :

AnnéeEn direct par ville

Comme mon exemple précédent, je peux ensuite augmenter ce résultat en ajoutant également le nom (pour que je sache qui a vécu où):

select
  v:fullName::string as parent_name,
  tf.value:cityName::string as city_name,
  yl.value::string as year_lived
from json_demo,
     table(flatten(v:citiesLived)) tf,
     table(flatten(tf.value:yearsLived)) yl;

Nom et année de vie

Agrégations ?

Oui, nous pouvons même agréger des données dans des données semi-structurées. (Nous ne serions pas vraiment un service d’entrepôt de données si nous ne le pouvions pas, n’est-ce pas ?)

Donc, tout comme ANSI SQL, nous pouvons faire un compter et unpar groupe

select
   tf.value:cityName::string as city_name,
   count(*) as years_lived
from json_demo,
     table(flatten(v:citiesLived)) tf,
     table(flatten(tf.value:yearsLived)) yl
group by 1;

:

Et les résultats :

Compter les années

Filtration? Bien sûr! Ajoutez simplement un

select
  tf.value:cityName::string as city_name,
  count(*) as years_lived
from json_demo,
     table(flatten(v:citiesLived)) tf,
     table(flatten(tf.value:yearsLived)) yl
where city_name="Portland"
group by 1;

clause.

Où Ville Pour simplifier les choses, remarquez que j’ai utilisé l’alias de colonne Nom de Ville dans le prédicat, mais vous pouvez également utiliser la spécification complète de la sous-colonne tf.value:cityName

aussi bien.

Schema-on-Read est une réalité Je pourrais continuer, mais maintenant je pense que vous pouvez voir que nous avons rendu très facile le chargement et l’extraction d’informations à partir de données semi-structurées à l’aide de Snowflake. Nous avons ajouté un tout nouveau type de données,UNE VARIANTE qui vit dans un relationnel structure de table dans une base de données relationnelle sans pour autant

la nécessité d’analyser la structure à l’avance, de concevoir des tables de base de données appropriées, puis de broyer les données dans ce schéma prédéfini. Ensuite, je vous ai montré quelques extensions faciles à apprendre du SQL standard ANSI pour accéder à ces données de manière très flexible et résiliente. Avec ces fonctionnalités, Snowflake vous donne la possibilité réelle de rapidement et facilement

chargez des données semi-structurées dans un entrepôt de données relationnelles et rendez-les disponibles pour une analyse immédiate. Pour voir cette fonctionnalité en action, consultez ce courtvidéo

.

https://www.youtube.com/watch?v=H0sbMDqdYQ8Assurez-vous de garder un œil sur ce blog ou suivez-nous sur Twitter ( @flocondeneige et@kentgraziano

) pour toutes les nouvelles et les événements ici à Snowflake. PS Si vous n’avez pas encore de compte Snowflake, vous pouvez vous inscrire pour un compte en libre-service ici

et démarrez avec 400 $ de crédits gratuits !

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.