Aperçu des vues

Cette rubrique traite de concepts pour comprendre et utiliser les vues.

Dans ce chapitre :

Qu’est-ce qu’une vue ?

Une vue permet d’accéder au résultat d’une requête comme s’il s’agissait d’une table. La requête est spécifiée dans l’instruction CREATE VIEW.

Les vues servent à diverses fins, notamment la combinaison, la séparation et la protection des données. Par exemple, vous pouvez créer des vues séparées qui répondent aux besoins de différents types d’employés, comme les médecins et les comptables d’un hôpital :

CREATE TABLE hospital_table (patient_id INTEGER,
                             patient_name VARCHAR, 
                             billing_address VARCHAR,
                             diagnosis VARCHAR, 
                             treatment VARCHAR,
                             cost NUMBER(10,2));
INSERT INTO hospital_table 
        (patient_ID, patient_name, billing_address, diagnosis, treatment, cost) 
    VALUES
        (1, 'Mark Knopfler', '1982 Telegraph Road', 'Industrial Disease', 
            'a week of peace and quiet', 2000.00),
        (2, 'Guido van Rossum', '37 Florida St.', 'python bite', 'anti-venom', 
            70000.00)
        ;
CREATE VIEW doctor_view AS
    SELECT patient_ID, patient_name, diagnosis, treatment FROM hospital_table;

CREATE VIEW accountant_view AS
    SELECT patient_ID, patient_name, billing_address, cost FROM hospital_table;

Une vue peut être utilisée presque partout où une table peut être utilisée (dans une jointure ou une sous-requête, etc.). Par exemple, en utilisant les vues créées ci-dessus :

  • Affichez tous les types de problèmes médicaux de chaque patient :

    SELECT DISTINCT diagnosis FROM doctor_view;
    +--------------------+
    | DIAGNOSIS          |
    |--------------------|
    | Industrial Disease |
    | python bite        |
    +--------------------+
    
  • Indiquez le coût de chaque traitement (sans fournir d’informations permettant d’identifier personnellement des patients spécifiques) :

    SELECT treatment, cost 
        FROM doctor_view AS dv, accountant_view AS av
        WHERE av.patient_ID = dv.patient_ID;
    +---------------------------+----------+
    | TREATMENT                 |     COST |
    |---------------------------+----------|
    | a week of peace and quiet |  2000.00 |
    | anti-venom                | 70000.00 |
    +---------------------------+----------+
    

Une commande CREATE VIEW peut utiliser un nom de table complet, partiellement qualifié ou non qualifié. Par exemple :

CREATE VIEW v1 AS SELECT ... FROM my_database.my_schema.my_table;

CREATE VIEW v1 AS SELECT ... FROM my_schema.my_table;

CREATE VIEW v1 AS SELECT ... FROM my_table;

Si le schéma n’est pas spécifié, Snowflake suppose que la table est dans le même schéma que la vue. (Si la table était supposée être dans le schéma actif, la vue pourrait faire référence à différentes tables à différents moments.)

Types de vues

Snowflake prend en charge deux types de vues :

  • Vues non matérialisées (généralement simplement appelées « vues »).

  • Vues matérialisées.

Vues non matérialisées

Le terme « vue » désigne de manière générique tous les types de vues ; cependant, le terme est utilisé ici pour désigner spécifiquement des vues non matérialisées.

Fondamentalement, une vue est une définition nommée d’une requête. Les résultats d’une vue non matérialisée sont créés en exécutant la requête au moment où la vue est référencée dans une requête. Les résultats ne sont pas stockés pour une utilisation future. Les performances sont plus lentes qu’avec les vues matérialisées. Les vues non matérialisées sont le type de vue le plus courant.

Toute expression de requête qui renvoie un résultat valide peut être utilisée pour créer une vue matérialisée, par exemple :

  • Sélectionner une partie (ou de la totalité) des colonnes d’une table.

  • Sélectionner une plage spécifique de données dans les colonnes de table.

  • Joindre des données de deux tables ou plus.

Vues matérialisées

Bien qu’une vue matérialisée soit nommée comme s’il s’agissait d’un type de vue, à bien des égards, elle se comporte davantage comme une table. Les résultats d’une vue matérialisée sont stockés, presque comme s’il s’agissait d’une table. Cela permet un accès plus rapide, mais requiert de l’espace de stockage et une maintenance active, ces deux éléments impliquant chacun des coûts supplémentaires.

De plus, les vues matérialisées comportent des restrictions que les vues non matérialisées ne possèdent pas.

Pour plus de détails, voir Travailler avec des vues matérialisées.

Vues sécurisées

Les vues non matérialisées et matérialisées peuvent être définies comme sécurisées. Les vues sécurisées présentent des avantages par rapport aux vues standard, notamment l’amélioration de la confidentialité et du partage des données ; cependant, elles ont également certains impacts sur les performances à prendre en considération.

Pour plus de détails, voir Utilisation de vues sécurisées.

Vues récursives (vues non matérialisées uniquement)

Une vue non matérialisée peut être récursive (c’est-à-dire que la vue peut se référer à elle-même).

L’utilisation de la récursivité dans les vues est similaire à l’utilisation de la récursivité dans les CTEs récursifs. En fait, une vue peut être définie avec un CTE récursif. Par exemple :

CREATE VIEW employee_hierarchy (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
   WITH RECURSIVE employee_hierarchy_cte (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
      -- Start at the top of the hierarchy ...
      SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
        FROM employees
        WHERE title = 'President'
      UNION ALL
      -- ... and work our way down one level at a time.
      SELECT employees.title, 
             employees.employee_ID, 
             employees.manager_ID, 
             employee_hierarchy_cte.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
             employee_hierarchy_cte.title AS "MGR TITLE"
        FROM employees INNER JOIN employee_hierarchy_cte
       WHERE employee_hierarchy_cte.employee_ID = employees.manager_ID
   )
   SELECT * 
      FROM employee_hierarchy_cte
);

Au lieu d’utiliser un CTE récursif, vous pouvez créer une vue récursive avec le mot-clé RECURSIVE, par exemple :

CREATE RECURSIVE VIEW employee_hierarchy_02 (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
      -- Start at the top of the hierarchy ...
      SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
        FROM employees
        WHERE title = 'President'
      UNION ALL
      -- ... and work our way down one level at a time.
      SELECT employees.title, 
             employees.employee_ID, 
             employees.manager_ID, 
             employee_hierarchy_02.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
             employee_hierarchy_02.title AS "MGR TITLE"
        FROM employees INNER JOIN employee_hierarchy_02
        WHERE employee_hierarchy_02.employee_ID = employees.manager_ID
);

Pour plus de détails, y compris des exemples, voir CREATE VIEW.

Avantages des vues

Les vues permettent d’écrire des codes plus modulaires.

Les vues vous aident à écrire un code SQL plus clair et plus modulaire. Supposons, par exemple, que la base de données de votre hôpital contienne une table contenant des informations sur tous les employés. Vous pouvez créer des vues pour faciliter l’extraction d’informations concernant uniquement le personnel médical ou le personnel de maintenance. Vous pouvez même créer des hiérarchies de vues.

Par exemple, vous pouvez créer une vue pour les médecins et une vue pour les infirmières. Ensuite, la vue medical_staff peut être créée en vous référant à la vue pour les médecins et à la vue pour les infirmières :

CREATE TABLE employees (id INTEGER, title VARCHAR);
INSERT INTO employees (id, title) VALUES
    (1, 'doctor'),
    (2, 'nurse'),
    (3, 'janitor')
    ;

CREATE VIEW doctors as SELECT * FROM employees WHERE title = 'doctor';
CREATE VIEW nurses as SELECT * FROM employees WHERE title = 'nurse';
CREATE VIEW medical_staff AS
    SELECT * FROM doctors
    UNION
    SELECT * FROM nurses
    ;
SELECT * 
    FROM medical_staff
    ORDER BY id;
+----+--------+
| ID | TITLE  |
|----+--------|
|  1 | doctor |
|  2 | nurse  |
+----+--------+

Dans de nombreux cas, plutôt que d’écrire une grande requête difficile à comprendre, vous pouvez décomposer la requête en plus petits morceaux et créer une vue pour chacun de ces morceaux. Cela rend non seulement le code plus facile à comprendre, mais dans de nombreux cas, il rend aussi le code plus facile à déboguer parce que vous pouvez déboguer une vue à la fois, plutôt que la requête entière.

Une vue peut être référencée par de nombreuses requêtes différentes, ce qui permet d’augmenter la réutilisation du code.

Les vues permettent d’accorder l’accès à un sous-ensemble d’une table

Les vues vous permettent d’autoriser l’accès à seulement une partie des données d’une ou plusieurs tables. Supposons, par exemple, que vous ayez une table de dossiers médicaux de patients. Le personnel médical a accès à toutes les informations médicales (par exemple, le diagnostic) mais pas aux renseignements financiers (par exemple, le numéro de carte de crédit du patient). Le personnel comptable a accès aux informations relatives à la facturation, comme le coût de chacune des ordonnances remises au patient, mais pas aux données médicales privées, comme le diagnostic d’un problème psychiatrique. Vous pouvez créer deux vues distinctes : une pour le personnel médical et une autre pour le personnel comptable, de sorte que chacun de ces rôles ne voie que les informations nécessaires à l’exécution de son travail. Les vues permettent cela, car vous pouvez accorder des privilèges sur une vue particulière à un rôle particulier, sans que ce rôle ait de privilèges sur la ou les tables sous-jacentes à cette vue.

Dans l’exemple médical :

  • Le personnel médical n’aurait pas de privilèges sur les tables de données, mais aurait des privilèges sur la vue affichant le diagnostic et le traitement.

  • Le personnel comptable n’aurait pas de privilèges sur les tables de données, mais aurait des privilèges sur la vue affichant les informations de facturation.

Pour plus de sécurité, Snowflake prend en charge la définition d’une vue comme sécurisée. Pour plus d’informations sur les vues sécurisées, voir Utilisation de vues sécurisées.

Les vues permettent de renforcer les performances

Dans certaines situations, les vues peuvent améliorer les performances.

Vues non matérialisées

Lorsqu’une vue non matérialisée est référencée dans une requête, l’optimiseur de requête Snowflake utilise des optimisations internes pour traiter la vue et la requête ensemble, ce qui permet d’évaluer la vue plus efficacement que si elle était évaluée séparément.

Vues matérialisées

Les vues matérialisées sont conçues pour améliorer les performances. Les vues matérialisées contiennent une copie d’un sous-ensemble des données d’une table. En fonction de la quantité de données dans la table et dans la vue matérialisée, l’analyse de la vue matérialisée peut être beaucoup plus rapide que l’analyse de la table. Les vues matérialisées prennent également en charge le clustering. Vous pouvez créer plusieurs vues matérialisées sur les mêmes données, chaque vue matérialisée étant groupée dans une colonne différente, de sorte que différentes requêtes puissent être exécutées sur la vue avec le meilleur clustering pour cette requête.

Limites des vues

  • La définition d’une vue ne peut pas être mise à jour (vous ne pouvez pas utiliser ALTER VIEW ou ALTER MATERIALIZED VIEW pour modifier la définition d’une vue, par ex.). Au lieu de cela, vous devez recréer la vue avec la nouvelle définition.

  • Les modifications apportées à une table ne sont pas automatiquement propagées aux vues créées sur cette table. Par exemple, si vous supprimez une colonne dans une table, les vues de cette table risquent de ne plus être valides.

  • Les vues sont en lecture seule. Vous ne pouvez pas exécuter de commandes DML directement sur une vue. Cependant, vous pouvez utiliser une vue dans une sous-requête dans une instruction DML qui met à jour la table de base sous-jacente. Par exemple :

    DELETE FROM hospital_table 
        WHERE cost > (SELECT AVG(cost) FROM accountant_view);