Utilisation du schéma d’informations de flocon de neige

Utilisation du schéma d’informations de flocon de neige


Comme toute bonne base de données, Snowflake possède un dictionnaire de données que nous exposons aux utilisateurs. Nous l’appelons le schéma d’information. En fait, il s’agit d’un ensemble de vues par rapport à notre couche de métadonnées qui vous permet d’examiner facilement certaines des informations sur les bases de données, les schémas et les tables que vous avez créés dans Snowflake.

Quels objets puis-je voir ?

Il existe 18 vues dans le schéma d’informations que vous pouvez interroger directement. Vous pouvez voir la liste complète dans la documentation ici.

Il est important de noter que, pour chaque base de données dans Snowflake, il existe un schéma d’informations distinct afin que les requêtes ne renvoient que des données sur votre base de données actuelle. De plus, lors de l’écriture du SQL, les noms de vue dans le schéma d’informations doivent être pleinement qualifiés, en particulier avec ‘information_schema’ comme vous le verrez dans les exemples.

Pour plus de commodité, j’ai inversé le schéma du schéma d’information dans un diagramme de modèle de données et ajouté dans les PK et FK appropriés. Vous pouvez utiliser ce diagramme comme guide lors de l’écriture de SQL par rapport au schéma. (Télécharger un PDF avec des descriptions ici)

Modèle de schéma d'informations
Modèle de schéma d’informations

Comment puis-je utiliser le schéma d’information ?

Pour ceux qui n’ont peut-être pas encore écrit de requêtes sur un dictionnaire de données, laissez-moi vous donner quelques exemples.

1. Lister les tables et les vues dans un schéma

Un point de départ très simple consiste à répertorier les tables et les vues dans l’un de vos schémas de base de données :

SELECT table_name, table_type 

FROM kent_db.information_schema.tables 

WHERE table_schema="PUBLIC"

ORDER BY 1;

Notez que cet exemple SQL (et tous les exemples de cet article) spécifie un schéma particulier (c’est-à-dire PUBLIC). Si vous avez plusieurs schémas dans votre base de données, il serait sage d’inclure une spécification de schéma dans le prédicat chaque fois que possible (sauf si vous voulez vraiment tout voir dans la base de données). Gardez également à l’esprit que les valeurs dans les vues de schéma d’informations sont généralement des chaînes et sont sensibles à la casse. Assurez-vous donc de les placer entre guillemets simples lors du référencement dans la clause de prédicat.

Les résultats devraient ressembler à ceci :

Select Tables

2. Construire un dictionnaire de données

Supposons que vous souhaitiez générer une liste de type dictionnaire de données sur vos tables pour un rapport. Si vous avez enregistré des COMMENTAIRES sur votre table, vous pouvez exécuter cette requête pour créer cette liste :

SELECT 'Comment on table.'||table_name||' is '''||nvl(tables.comment||'''', 'No Comment Recorded''')

FROM kent_db.information_schema.tables tables

WHERE table_schema="PUBLIC";

En plus de vous montrer des commentaires sur chaque table du schéma PUBLIC, ce SQL indique également où vous n’avez pas ajouté de commentaires à vos tables.

NB : La plupart des outils de BI peuvent procéder à l’ingénierie inverse des commentaires d’un dictionnaire de données. Ces informations peuvent donc être utilisées pour créer les métadonnées dans votre outil de BI afin de permettre aux utilisateurs de connaître la signification des différentes tables du système.

Une autre possibilité est que vous vouliez une liste de toutes les contraintes que vous avez définies dans un schéma particulier. Vous pouvez exécuter un SQL comme celui-ci :

SELECT table_name, constraint_type, constraint_name

FROM sales.information_schema.table_constraints

WHERE constraint_schema="TPC_DV_OPT"

ORDER BY table_name;

Les résultats devraient ressembler à ceci :

Constraints

3. Générer dynamiquement SQL

Dynamic SQL est une méthode d’utilisation des données du schéma d’informations pour générer des instructions SQL. Par exemple, supposons que vous deviez nettoyer une base de données et supprimer la plupart des tables afin de pouvoir tester la régression du script CREATE. Il existe de nombreuses façons de procéder. Si vous souhaitez utiliser un script SQL pour le faire, vous pouvez écrire le script à la main, ce qui est bien si vous n’avez que quelques tables, mais il serait préférable de générer le script.

En utilisant le schéma d’information dans Snowflake, vous pouvez faire quelque chose comme ceci :

SELECT 'drop table '||table_name||' cascade;'

FROM kent_db.information_schema.tables tables

WHERE table_schema="PUBLIC"

ORDER BY 1;

La sortie doit être un ensemble de commandes SQL que vous pouvez ensuite exécuter. Et au fur et à mesure que le schéma évolue et que d’autres tables sont ajoutées, ce script récupèrera les nouvelles tables la prochaine fois que vous l’exécuterez, vous n’aurez donc même pas à vous rappeler de le modifier (d’où la partie « dynamique » du « SQL dynamique ») .

DROP SQL

Avec l’interface utilisateur Snowflake, vous pouvez exporter ces résultats, les enregistrer dans un script, puis les exécuter.

Avec l’accès à ce type de métadonnées, les possibilités sont infinies.

Comme toujours, gardez un œil sur ce site de blog, nos flux Twitter Snowflake (@SnowflakeDB), (@kentgraziano), et (@cloudsommelier) pour des mises à jour sur toutes les actions et activités ici à Snowflake Computing.

Laisser un commentaire

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