Schéma d’information de Snowflake

Le Schéma d’information de Snowflake (alias « Dictionnaire de données ») se compose d’un ensemble de vues définies par le système et de fonctions de tables qui fournissent des métadonnées détaillées sur les objets créés dans votre compte. Le Schéma d’information de Snowflake est basé sur le Schéma d’information SQL-92 ANSI, avec l’ajout de vues et de fonctions spécifiques à Snowflake.

Information Schema est implémenté comme un schéma nommé INFORMATION_SCHEMA que Snowflake crée automatiquement dans chaque base de données d’un compte.

Note

ANSI utilise le terme « catalogue » pour désigner des bases de données. Pour assurer la compatibilité avec la norme, les rubriques du Schéma d’information de Snowflake utilisent le terme « catalogue » plutôt que « base de données », le cas échéant. À toutes fins utiles, les termes sont conceptuellement équivalents et interchangeables.

Dans ce chapitre :

Qu’est-ce que INFORMATION_SCHEMA ?

Chaque base de données créée dans votre compte inclut automatiquement un schéma intégré en lecture seule nommé INFORMATION_SCHEMA. Le schéma contient les objets suivants :

  • Vues pour tous les objets contenus dans la base de données, ainsi que les vues pour les objets au niveau du compte (c’est-à-dire les objets autres que les bases de données tels que les rôles, les entrepôts virtuels et les bases de données).

  • Fonctions de table pour les données historiques et les données d’utilisation de votre compte.

Vues du schéma d’information et fonctions de table

Liste des vues

Les vues dans INFORMATION_SCHEMA affichent des métadonnées sur les objets définis dans la base de données, ainsi que des métadonnées sur les objets au niveau du compte, qui ne sont pas des bases de données et qui sont communs à l’ensemble d’entre elles. Chaque instance de INFORMATION_SCHEMA comprend :

  • Des vues ANSI standards de la base de données et des objets au niveau du compte qui sont pertinents pour Snowflake.

  • Des vues spécifiques à Snowflake pour les objets non standards pris en charge par Snowflake (zones de préparation, formats de fichiers, etc.).

Sauf indication contraire, les vues du schéma d’information de Snowflake sont conformes ANSI :

Vue

Type

Spécifique à Snowflake

Remarques

APPLICABLE_ROLES

Compte

CLASS_INSTANCE_FUNCTIONS

Base de données

CLASS_INSTANCE_PROCEDURES

Base de données

CLASS_INSTANCES

Base de données

CLASSES

Base de données

COLUMNS

Base de données

CURRENT_PACKAGES_POLICY

Base de données

DATABASES

Compte

ELEMENT_TYPES

Base de données

ENABLED_ROLES

Compte

EVENT_TABLES

Base de données

EXTERNAL_TABLES

Base de données

FIELDS

Base de données

FILE FORMATS

Base de données

FUNCTIONS

Base de données

HYBRID_TABLES

Base de données

INDEXES

Base de données

INDEX_COLUMNS

Base de données

INFORMATION_SCHEMA_CATALOG_NAME

Compte

LOAD_HISTORY

Compte

Données conservées pendant 14 jours.

OBJECT_PRIVILEGES

Compte

PACKAGES

Base de données

PIPES

Base de données

PROCEDURES

Base de données

REFERENTIAL_CONSTRAINTS

Base de données

REPLICATION_DATABASES

Compte

REPLICATION_GROUPS

Compte

SCHEMATA

Base de données

SEQUENCES

Base de données

SERVICES

Base de données

STAGES

Base de données

TABLE_CONSTRAINTS

Base de données

TABLE_PRIVILEGES

Base de données

TABLE_STORAGE_METRICS

Base de données

TABLES

Base de données

Affiche des tables et des vues.

USAGE_PRIVILEGES

Base de données

Affiche des privilèges sur des séquences uniquement, pour afficher des privilèges sur d’autres types d’objets, utilisez la vue OBJECT_PRIVILEGES.

VIEWS

Base de données

Liste des fonctions de table

Les fonctions de la table dans INFORMATION_SCHEMA peuvent être utilisées pour retourner des informations sur l’utilisation au niveau du compte et l’historique au niveau du stockage, des entrepôts virtuels, des connexions d’utilisateurs et des requêtes :

Fonction de la table

Données Conservation

Remarques

AUTOMATIC_CLUSTERING_HISTORY

14 jours

Les résultats dépendent du privilège MONITOR USAGE. [1]

AUTO_REFRESH_REGISTRATION_HISTORY

14 jours

Les résultats dépendent du privilège MONITOR USAGE. [1]

COMPLETE_TASK_GRAPHS

60 minutes

Renvoie les résultats uniquement pour le rôle ACCOUNTADMIN , le propriétaire de la tâche (c’est-à-dire le rôle doté du privilège OWNERSHIP sur la tâche) ou un rôle disposant du privilège global MONITOR EXECUTION.

COPY_HISTORY

14 jours

Les résultats dépendent des privilèges attribués au rôle actuel de l’utilisateur.

CURRENT_TASK_GRAPHS

N/A

Renvoie les résultats uniquement pour le rôle ACCOUNTADMIN , le propriétaire de la tâche (c’est-à-dire le rôle doté du privilège OWNERSHIP sur la tâche) ou un rôle disposant du privilège global MONITOR EXECUTION.

DATA_TRANSFER_HISTORY

14 jours

Les résultats dépendent du privilège MONITOR USAGE. [1]

DATABASE_REFRESH_HISTORY

14 jours

Les résultats dépendent des privilèges attribués au rôle actuel de l’utilisateur.

DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB

14 jours

Les résultats dépendent des privilèges attribués au rôle actuel de l’utilisateur.

DATABASE_REPLICATION_USAGE_HISTORY

14 jours

Les résultats ne sont retournés que pour le rôle ACCOUNTADMIN.

DATABASE_STORAGE_USAGE_HISTORY

6 mois

Les résultats dépendent du privilège MONITOR USAGE. [1]

DYNAMIC_TABLE_GRAPH_HISTORY

N/A

Les résultats dépendent du privilège MONITOR USAGE. [1]

DYNAMIC_TABLE_REFRESH_HISTORY

1 jour

Les résultats dépendent du privilège MONITOR USAGE. [1]

EXTERNAL_FUNCTIONS_HISTORY

14 jours

Les résultats dépendent du privilège MONITOR USAGE. [1]

EXTERNAL_TABLE_FILES

N/A

Les résultats dépendent des privilèges attribués au rôle actuel de l’utilisateur.

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

30 jours

Les résultats dépendent des privilèges attribués au rôle actuel de l’utilisateur.

LOGIN_HISTORY , LOGIN_HISTORY_BY_USER

7 jours

Les résultats dépendent des privilèges attribués au rôle actuel de l’utilisateur.

MATERIALIZED_VIEW_REFRESH_HISTORY

14 jours

Les résultats dépendent du privilège MONITOR USAGE. [1]

NOTIFICATION_HISTORY

14 jours

Renvoie les résultats uniquement pour le rôle ACCOUNTADMIN, le propriétaire de l’intégration (c’est-à-dire le rôle doté du privilège OWNERSHIP sur l’intégration) ou un rôle disposant du privilège USAGE sur l’intégration.

PIPE_USAGE_HISTORY

14 jours

Les résultats dépendent du privilège MONITOR USAGE. [1]

POLICY_REFERENCES

N/A

Les résultats ne sont retournés que pour le rôle ACCOUNTADMIN.

QUERY_ACCELERATION_HISTORY

14 jours

Les résultats dépendent du privilège MONITOR USAGE. [1]

QUERY_HISTORY , QUERY_HISTORY_BY_*

7 jours

Les résultats dépendent des privilèges attribués au rôle actuel de l’utilisateur.

REPLICATION_GROUP_REFRESH_HISTORY

14 jours

Les résultats ne sont renvoyés que pour un rôle ayant un quelconque privilège sur le groupe de réplication ou de basculement.

REPLICATION_GROUP_REFRESH_PROGRESS, REPLICATION_GROUP_REFRESH_PROGRESS_BY_JOB

14 jours

Les résultats ne sont renvoyés que pour un rôle ayant un quelconque privilège sur le groupe de réplication ou de basculement.

REPLICATION_GROUP_USAGE_HISTORY

14 jours

Les résultats dépendent du privilège MONITOR USAGE. [1]

REPLICATION_USAGE_HISTORY

14 jours

Les résultats ne sont retournés que pour le rôle ACCOUNTADMIN.

REST_EVENT_HISTORY

7 jours

Les résultats ne sont retournés que pour le rôle ACCOUNTADMIN.

SEARCH_OPTIMIZATION_HISTORY

14 jours

Les résultats dépendent du privilège MONITOR USAGE. [1]

SERVERLESS_TASK_HISTORY

14 jours

Les résultats dépendent du privilège MONITOR USAGE. [1]

STAGE_DIRECTORY_FILE_REGISTRATION_HISTORY

14 jours

Les résultats dépendent des privilèges attribués au rôle actuel de l’utilisateur.

STAGE_STORAGE_USAGE_HISTORY

6 mois

Les résultats dépendent du privilège MONITOR USAGE. [1]

TAG_REFERENCES

N/A

Les résultats ne sont renvoyés que pour le rôle qui a accès à l’objet spécifié.

TAG_REFERENCES_ALL_COLUMNS

N/A

Les résultats ne sont renvoyés que pour le rôle qui a accès à l’objet spécifié.

TASK_DEPENDENTS

N/A

Les résultats sont renvoyés uniquement pour le propriétaire du rôle ou de la tâche ACCOUNTADMIN (rôle avec le privilège OWNERSHIP sur la tâche).

TASK_HISTORY

7 jours

Renvoie les résultats uniquement pour le rôle ACCOUNTADMIN , le propriétaire de la tâche (c’est-à-dire le rôle doté du privilège OWNERSHIP sur la tâche) ou un rôle disposant du privilège global MONITOR EXECUTION.

VALIDATE_PIPE_LOAD

14 jours

Les résultats dépendent des privilèges attribués au rôle actuel de l’utilisateur.

WAREHOUSE_LOAD_HISTORY

14 jours

Les résultats dépendent du privilège MONITOR USAGE. [1]

WAREHOUSE_METERING_HISTORY

6 mois

Les résultats dépendent du privilège MONITOR USAGE. [1]

[1] Retourne des résultats si le rôle s’est vu attribuer le privilège global MONITOR USAGE ; sinon, renvoie les résultats uniquement pour le rôle ACCOUNTADMIN.

Notes générales sur l’utilisation

  • Chaque schéma INFORMATION_SCHEMA est en lecture seule (c’est-à-dire que le schéma, et toutes les vues et fonctions de la table dans le schéma, ne peuvent être modifiés ou détruits).

  • Les requêtes sur les vues INFORMATION_SCHEMA ne garantissent pas la cohérence par rapport aux DDL concurrentes. Par exemple, si un ensemble de tables est créé pendant qu’une requête INFORMATION_SCHEMA de longue durée est exécutée, le résultat de la requête peut inclure certaines, aucune ou toutes les tables créées.

  • La sortie d’une fonction de vue ou de table dépend des privilèges accordés au rôle actuel de l’utilisateur. Lors de l’interrogation d’une fonction INFORMATION_SCHEMA de vue ou de table, seuls les objets pour lesquels le rôle courant a obtenu des droits d’accès sont retournés.

  • Pour éviter les problèmes de performance, l’erreur suivante est retournée si les filtres spécifiés dans une requête INFORMATION_SCHEMA ne sont pas suffisamment sélectifs :

    Information schema query returned too much data. Please repeat query with more selective predicates.

  • Les vues spécifiques à Snowflake sont susceptibles d’être modifiées. Évitez de sélectionner toutes les colonnes dans ces vues. À la place, sélectionnez les colonnes que vous voulez. Par exemple, si vous voulez la colonne name utilisez SELECT name, plutôt que SELECT *.

Astuce

Les vues Information Schema sont optimisées pour les requêtes qui récupèrent un petit sous-ensemble d’objets du dictionnaire. Dans la mesure du possible, optimisez les performances de vos requêtes en filtrant les noms de schémas et d’objets.

Pour plus d’informations et de détails sur l’utilisation, voir le billet de notre blog sur le schéma d’information de Snowflake.

Considérations relatives au remplacement de commandes SHOW par des vues de schéma d’information

Les vues INFORMATION_SCHEMA fournissent une interface SQL aux mêmes informations que celles fournies par les commandes SHOW <objets>. Vous pouvez utiliser les vues pour remplacer ces commandes ; cependant, il existe quelques différences importantes à prendre en compte avant d’opérer le changement :

Considérations

Commandes SHOW

Vues du schéma d’information

Entrepôts virtuels

Exécution pas obligatoire.

L’entrepôt virtuel doit être en cours d’exécution et actuellement utilisé pour interroger les vues.

Correspondance des motifs/filtrage

Insensible à la casse (lors du filtrage avec LIKE).

Sémantique SQL standard (sensible à la casse). Snowflake convertit automatiquement les identificateurs sans guillemets et insensibles à la casse en majuscules en interne, de sorte que les noms d’objets sans guillemets doivent être interrogés en majuscules dans les vues du schéma d’information.

Résultats de la requête

La plupart des commandes SHOW limitent les résultats au schéma courant par défaut.

Les vues affichent tous les objets de la base de données actuelle/spécifiée. Pour interroger un schéma particulier, vous devez utiliser un prédicat de filtre (par exemple, ... WHERE table_schema = CURRENT_SCHEMA()...). Notez que les requêtes du schéma d’information qui manquent de filtres suffisamment sélectifs retournent une erreur et ne s’exécutent pas (voir Notes générales sur l’utilisation dans ce chapitre).

Qualification des noms des vues de schéma d’information et des fonctions de table dans les requêtes

Lors de l’interrogation d’une fonction de vue ou de table INFORMATION_SCHEMA, vous devez utiliser le nom qualifié de la fonction de vue/table ou le schéma INFORMATION_SCHEMA doit être utilisé pour la session.

Par exemple :

  • Pour effectuer une requête en utilisant les noms complets de la fonction de vue et de table, sous la forme de database.information_schema.name :

    SELECT table_name, comment FROM testdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(testdb.INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
    
    Copy
  • Pour effectuer une requête en utilisant les noms qualifiés de la fonction de vue et de table, sous la forme de information_schema.name :

    USE DATABASE testdb;
    
    SELECT table_name, comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
    
    Copy
  • Pour effectuer une requête avec le schéma INFORMATION_SCHEMA utilisé pour la session :

    USE SCHEMA testdb.INFORMATION_SCHEMA;
    
    SELECT table_name, comment FROM TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(LOGIN_HISTORY( ... ));
    
    Copy

    Note

    Si vous utilisez une base de données qui a été créée à partir d’un partage et que vous avez sélectionné INFORMATION_SCHEMA comme schéma actuel pour la session, l’instruction SELECT peut échouer avec l’erreur suivante :

    INFORMATION_SCHEMA does not exist or is not authorized

    Si cela se produit, sélectionnez un schéma différent pour le schéma actuel de la session.

Pour des exemples plus détaillés, reportez-vous à la documentation de référence de chaque fonction de vue/table.