Charger XML avec SQL Review Part One

Charger XML avec SQL Review Part One


Avec ça blog en deux parties, nous examinons comment charger des données XML dans Snowflake et interroger avec le SQL standard ANSI. Cela vous permettra d’avoir des capacités de base de données relationnelles complètes pour XML, bien qu’il s’agisse d’un magasin de documents, d’un format de données non relationnel.

Et si vous vous demandez : « XML, est-il encore utilisé ? Rassurez-vous, la réponse est un « Oui » retentissant ! Même si JSON a dépassé XML en tant que format de fichier d’échange de données léger et facile à comprendre préféré, XML reste très bien ancré dans l’informatique d’entreprise. Par exemple, XML a de nombreux cas d’utilisation allant des applications bureautiques aux applications d’entreprise basées sur SOA en passant par une variété d’API et de normes de protocole d’échange de données au niveau de l’industrie. En ce qui concerne les normes de l’industrie en particulier, il y a HL7 pour le secteur de la santé, il y a FpML pour les services financiers et OTA pour les voyages. Tout indique que XML continuera de prospérer et d’évoluer.

Compte tenu de la prévalence du XML, vous souhaiterez un entrepôt de données qui facilite l’interrogation du XML pour poser des questions sur les données et produire des informations.

D’autres approches nécessitent que vous transformiez d’abord XML avant de le charger dans une base de données SQL. Cela peut ajouter de la complexité ou des retards puisque vous devez utiliser un outil distinct pour transformer les données XML. Sinon, vous devrez personnaliser vous-même les scripts de transformation. Avec Snowflake, vous pouvez ingérer du XML directement dans la solution. Aucune pré-transformation requise. Grâce au type de données breveté VARIANT de Snowflake, les données XML peuvent être stockées dans leur propre table de base de données en colonnes ou insérées dans une table existante dans Snowflake.

Les étapes suivantes décrivent comment.

Collecte d’exemples de données XML

Tout d’abord, rassemblons les données XML. Pour notre échantillon, nous utilisons des données d’adjudication du Trésor américain accessibles au public. Les données peuvent être trouvées ici. Ce site Web fournit un enregistrement de toutes les annonces d’adjudication du Trésor pour les bons du Trésor, les billets, les obligations, etc., au cours de la dernière décennie (de 2008 à 2017) sous forme de données XML. Au total, le site contient environ 25 000 fichiers XML. Nous en avons utilisé cinq comme exemples pour cet article.

La figure 1 fournit une lecture de l’un des fichiers de données d’enchères du Trésor basés sur XML :

Picture1
Figure 1. Données sur les enchères du Trésor américain au format XML

Charger des données XML dans Snowflake

Ensuite, nous allons charger les données dans Snowflake. Snowflake fournit une interface utilisateur intuitive, qui facilite le chargement et la préparation des données pour exécuter des requêtes à des fins d’analyse. Les étapes se résument comme suit :

  1. Créez une nouvelle table avec une colonne XML à l’aide du type de données VARIANT breveté de Snowflake.
  2. Créez un nouveau format de fichier orienté colonne pour XML (« columnarisation » du XML), à utiliser avec la nouvelle table.
  3. Chargez les exemples de données XML dans la colonne XML de la nouvelle table.

La séquence réelle des étapes est la suivante.

Créer un nouveau tableau

  • Créez une table avec une colonne XML avec le type de données VARIANT.
  • Dans l’interface utilisateur de Snowflake, sélectionnez la base de données dans laquelle les données XML seront chargées (par exemple, « TUTORIAL_DB » pour notre exemple ici). [To learn how to create a new database, click here].
  • Dans la barre d’outils de la base de données, cliquez sur Tables -> Créer
  • Entrez le nom de la table sous la forme ‘treasury_auction_xml’. Le nom de schéma par défaut est ‘Public’. Spécifiez le schéma (colonne et type de données comme indiqué) :

Screen Shot 2019 04 09 at 5.37.55 PM

Si vous préférez travailler avec des instructions SQL pour un script ou un classeur, vous pouvez cliquer sur le lien « Afficher SQL » dans le Créer un tableau boîte de dialogue pour afficher les instructions SQL équivalentes qui peuvent être coupées et collées dans votre script. Snowflake propose cette option pour la plupart des zones de sélection. Faire ainsi pour cet exemple révélera :

Picture3

Créer un nouveau format de fichier pour XML

Dans le cadre du processus de chargement des données, classifiez le XML via un nouveau format de fichier :

  • Sélectionnez la base de données ‘TUTORIAL_DB’ et cliquez sur la table ‘treasury_auction_xml’.
  • Cliquer sur Tableau de charge. Une série de fenêtres contextuelles s’affichera pour guider l’utilisateur tout au long du processus de chargement des données. Sélectionnez l’entrepôt que vous désignez pour cet exercice. Ensuite, spécifiez l’emplacement des fichiers XML à charger. La boîte de dialogue de notre exemple est illustrée ci-dessous :

Picture4

  • Ensuite, dans l’étape de formatage du fichier, cliquez sur + et une fenêtre contextuelle s’affiche. Spécifiez les différentes options comme indiqué ci-dessous et cliquez sur Finir:

Picture Create File

Terminer le processus de chargement

Sélectionner Options de chargement. Il s’agit de la dernière étape pour terminer le chargement des données XML dans la nouvelle table avec une colonne XML. Quelques options de gestion des erreurs différentes sont présentées comme indiqué. Sélectionnez le choix qui convient à vos besoins.

  • Pour cet exemple, nous choisissons d’ignorer un fichier XML si une erreur est détectée lors de l’analyse. Cliquer sur Charger pour finir.

Picture5

  • Le résultat du chargement est affiché dans l’exemple ci-dessous, montrant qu’un certain nombre de fichiers XML ont été chargés avec succès dans la nouvelle table ‘treasury_auction_xml’.

Picture6

Interroger les données XML

Avec les données XML chargées, vous pouvez facilement interroger les données de manière entièrement relationnelle, en exprimant des requêtes avec un SQL ANSI robuste. Par exemple, pour un aperçu rapide de tous les documents XML chargés dans la table, exécutez cette requête :

SELECT src_xml FROM treasure_auction_xml;

Cette requête renverra tous les documents XML dans la colonne XML. La figure suivante montre une partie de la sortie :

Picture7

Ceci est juste un exemple rapide de la façon dont vous pouvez facilement interroger XML avec Snowflake avec SQL standard. De plus, vous disposez d’un assortiment de fonctions XML pour interroger les éléments XML dans les fichiers XML. Cela inclut une puissante fonction de valeur de table FLATTEN, qui est une fonction de désimbrication qui vous permettra d’accéder aux structures hiérarchiques inhérentes au sein d’un fichier XML. Dans la partie 2 de cet article, nous examinons 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.

Liens supplémentaires

Abonnez-vous au blog des flocons de neige

Laisser un commentaire

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