Comment charger et interroger facilement des données XML avec Snowflake

Comment charger et interroger facilement des données XML avec Snowflake


VEUILLEZ NOTER : Ce message a été initialement publié en juillet 2018. Il a été mis à jour pour refléter les produits, fonctionnalités et fonctionnalités actuellement disponibles.

Ce billet de blog est la deuxième partie de notre série en deux parties sur la façon d’interroger facilement XML avec Snowflake SQL. Reprenant là où nous nous sommes arrêtés avec Partie 1, une fois les données XML chargées, vous pouvez interroger les données de manière entièrement relationnelle, en exprimant des requêtes avec un SQL ANSI robuste. Vous pouvez ensuite facilement émettre des requêtes SQL pour obtenir un aperçu des données sans transformer ou prétraiter le XML.

Cet article décrit quelques exemples de requêtes pour analyser facilement les données à l’aide des différentes fonctions XML et de la fonction d’interrogation de données semi-structurées appelée LATERAL FLATTEN. De puissantes capacités d’AJUSTEMENT LATERAL vous permettent d’accéder aux structures hiérarchiques inhérentes au sein des données XML.

Utilisation des fonctions d’élément et d’attribut XML

Snowflake fournit un certain nombre de fonctions SQL pour accéder aux constructions XML telles que les éléments et les attributs des documents XML. Les requêtes suivantes montrent quelques exemples d’utilisation de ces fonctions. Pour un aperçu rapide de tous les documents XML chargés dans notre table, exécutez la requête suivante :

SÉLECTIONNER src_xml À PARTIR de treasury_auction_xml ;

  • Cette requête renvoie le document XML complet dans la colonne XML.
Fonctions d'éléments et d'attributs XML

Pour accéder à l’attribut d’un élément, utilisez l’opérateur « @ » dans la requête suivante :

SÉLECTIONNEZ src_xml : « @ » DE treasury_auction_xml ;

  • Cette requête renvoie le nom de l’élément racine du document XML dans la colonne XML.

« td : données d’enchères »

La requête suivante exploite l’opérateur « $ » pour interroger le contenu de l’élément racine du document XML :

SÉLECTIONNEZ src_xml :  » $  » DE treasury_auction_xml ;

  • Cette requête renvoie le contenu de l’élément racine du document XML dans la colonne XML.
eaaYM2ycHmpVabtnZk IOZG1Sus3W5n6yNAWyKjCn lihAxBfob YAaVjae6lkMGfcuzeBDGUN28yaESpyoSVhOYBR368zvRbCjBYi7SXyc43CstqBPjR0VN lUxuBZP9Eihandl2U3OkEf tQ
Fonction XMLGET flexible
Utilisation de la fonction flexible XMLGET

La requête suivante récupère le premier élément contenu dans l’élément racine du document XML :

SELECT XMLGET(src_xml, ‘AuctionAnnouncement’, 0) FROM treasury_auction_xml ;

  • Cette requête renvoie le contenu de l’élément racine du document XML dans la colonne XML.
fpadxUQXGNqTAjQMvnCyP6D8pcABJPXkJoEbNyae60OIYCbv9d4DHEcn84jiNTwc0hq gm2NrhGHl sEdvKheICfYMLIhSuyfJ2Em XxX sNCZFhUyz9hXwXq2QEnLRB8CYLgqtbXLJuJm4flQ

La requête suivante récupère tout le contenu du premier élément « AuctionAnnouncement » :

SELECT XMLGET(src_xml, ‘AuctionAnnouncement’, 0): »$ » FROM treasury_auction_xml;

  • Cette requête renvoie tout le contenu du premier élément sous l’élément racine.
Aplatissement latéral
Utilisation des puissantes capacités LATERAL FLATTEN

La puissante fonction de valeur de table LATERAL FLATTEN est l’un des mécanismes les plus fondamentaux offerts par Snowflake pour interroger et explorer des données semi-structurées. Si vous connaissez le concept de la fonction SQL UNNEST dans la gestion de bases de données relationnelles, vous pouvez penser à FLATTEN dans le même sens. Essentiellement, il renvoie une « table virtuelle » de {zéro, un ou plusieurs} valeurs qui sont ensuite jointes à la ligne actuelle pour produire {zéro, un ou plusieurs} lignes contenant tous les attributs de la ligne d’origine plus la valeur de la tableau virtuel.

Dans les requêtes suivantes, nous offrons quelques exemples sur la façon d’exploiter cette fonctionnalité puissante.

Considérons une requête que nous pouvons utiliser pour retourner une ligne pour chaque élément inclus dans l’annonce d’un titre du Trésor. Des exemples d’éléments comprennent la durée du titre, le montant de l’offre, la date d’émission, le montant attribué, le taux d’actualisation ou le taux d’intérêt.

SÉLECTIONNER

auction_announcement.index comme auction_contents_index,

auction_announcement.value en tant que auction_contents_value

DE treasury_auction_xml,

LATERAL FLATTEN(to_array(treasury_auction_xml.src_xml:”$” )) xml_doc,

LATERAL FLATTEN(to_array(xml_doc.VALUE: »$ » )) auction_announcement ;

  • Dans cette requête, LATERAL FLATTEN prend une entrée XML et éclate la liste des sous-éléments dans sa propre ligne (dénormalisée).
    • Exemple de sortie (interface Web Snowflake) :
Élément d'annonce d'enchères

Cet exemple combine l’utilisation de XMLGET avec la fonction LATERAL :

SELECT XMLGET(value, ‘SecurityType’ ): »$ » comme « Type de sécurité »,

XMLGET( value, ‘MaturityDate’ ): »$ » comme « Date d’échéance »,

XMLGET( value, ‘OfferingAmount’ ): »$ » as « Offering Amount »,

XMLGET( value, ‘MatureSecurityAmount’ ): »$ » as « Mature Security Amount »

DE treasury_auction_xml,

LATERAL FLATTEN(to_array(treasury_auction_xml.src_xml: »$ » )) auction_announcement ;

  • Dans cette requête, LATERAL FLATTEN parcourt les éléments enfants du document XML et XMLGET mappe l’élément enfant « SecurityType », ou « MaturityDate », ou « OfferingAmount », ou « MatureSecurityAmount » à sa propre colonne.
    • Exemple de sortie (interface Web Snowflake) :
XMLGet avec la fonction LATERAL
Sommaire

Avec les étapes et les exemples de requêtes que nous avons partagés dans cette série en 2 parties, nous avons démontré à quel point il est simple d’ingérer des données XML. Snowflake ingère nativement des données semi-structurées et vous permet d’interroger immédiatement les données avec un SQL ANSI robuste, sans avoir besoin de transformer ou de prétraiter les données au préalable. Cette approche simplifie également considérablement le processus de travail avec des données semi-structurées en éliminant les étapes de préparation des données et simplifie votre architecture de données en éliminant le besoin d’une deuxième plate-forme de traitement de données.

Consolidez vos diverses données sur Snowflake et recevez un entrepôt de données SQL piloté par logiciel facile à utiliser en tant que service, pour démarrer, pour vos analyses.

Commencer Votre essai gratuit de 30 jours au cours duquel vous pouvez recevoir 400 $ de crédits pour essayer les fonctionnalités de Snowflake.

Laisser un commentaire

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