Catégories :

Table, vue et séquence DDL

CREATE VIEW

Crée une nouvelle vue dans le schéma actuel/spécifié, basée sur une requête d’une ou plusieurs tables existantes (ou toute autre expression de requête valide).

Syntaxe

CREATE [ OR REPLACE ] [ SECURE ] [ RECURSIVE ] VIEW [ IF NOT EXISTS ] <name>
  [ ( <column_list> ) ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  AS <select_statement>

Paramètres requis

nom

Spécifie l’identificateur de la vue ; il doit être unique pour le schéma dans lequel la vue est créée.

De plus, l’identificateur doit commencer par un caractère alphabétique et ne peut pas contenir d’espaces ou de caractères spéciaux à moins que toute la chaîne d’identificateur soit délimitée par des guillemets doubles (p. ex. "My object"). Les identificateurs entre guillemets doubles sont également sensibles à la casse.

Pour plus de détails, voir Exigences relatives à l’identificateur.

instruction_select

Spécifie la requête utilisée pour créer la vue. Peut porter sur une ou plusieurs tables sources ou toute autre instruction SELECT valide. Cette requête sert de texte/définition pour la vue et est affichée dans la sortie SHOW VIEWS et la vue Information Schema VIEWS.

Paramètres facultatifs

SECURE

Indique que la vue est sécurisée. Pour plus d’informations sur les vues sécurisées, voir Utilisation de vues sécurisées.

Par défaut : aucune valeur (la vue n’est pas sécurisée)

RECURSIVE

Spécifie que la vue peut se référer à elle-même à l’aide d’une syntaxe récursive sans nécessairement utiliser un CTE (expression de table commune). Pour plus d’informations sur les vues récursives en général, et le mot clé RECURSIVE en particulier, voir Vues récursives (vues non matérialisées uniquement) et les exemples de vues récursives ci-dessous.

Par défaut : aucune valeur (la vue n’est pas récursive ou est récursive uniquement en utilisant un CTE)

liste_colonne :

Si vous ne souhaitez pas que les noms de colonne de la vue soient identiques à ceux de la table sous-jacente, vous pouvez inclure une liste de colonnes dans laquelle vous spécifiez les noms de colonne. (Vous n’avez pas besoin de spécifier les types de données des colonnes.)

Si l’une des colonnes de la vue est basée sur des expressions (pas seulement des noms de colonnes simples), vous devez fournir un nom de colonne pour chaque colonne de la vue. Par exemple, les noms de colonnes sont obligatoires dans le cas suivant :

CREATE VIEW v1 (x, x_times_2) AS SELECT x, x * 2 FROM table1;
COPY GRANTS

Conserve les autorisations d’accès de la vue d’origine lorsqu’une nouvelle vue est créée à l’aide de la clause OR REPLACE.

Le paramètre copie tous les privilèges, sauf OWNERSHIP, de la vue existante vers la nouvelle vue. La nouvelle vue n’autorise pas les attributions futures définies pour le type d’objet dans le schéma. Par défaut, le rôle qui exécute l’instruction CREATE VIEW possède la nouvelle table.

Si le paramètre n’est pas inclus dans l’instruction CREATE VIEW, la nouvelle vue n’hérite pas des privilèges d’accès explicites accordés sur la vue d’origine, mais des attributions futures définies pour le type d’objet dans le schéma.

Notez que l’opération de copie des autorisations s’effectue de manière atomique avec l’instruction CREATE VIEW (c’est-à-dire dans la même transaction).

Par défaut : aucune valeur (les autorisations ne sont pas copiées)

COMMENT = 'litéral_chaine'

Spécifie un commentaire pour la vue.

Par défaut : aucune valeur

Notes sur l’utilisation

  • Les définitions de vue ne sont pas dynamiques. Une vue n’est pas automatiquement mise à jour si les sources sous-jacentes sont modifiées de sorte qu’elles ne correspondent plus à la définition de la vue, en particulier lorsque des colonnes sont détruites. Par exemple :

    • Une vue est créée en faisant référence à une colonne spécifique dans une table source et la colonne est ensuite détruite de la table.

    • Une vue est créée à l’aide de SELECT * à partir d’une table et toute colonne est ensuite détruite de la table.

    Dans l’un ou l’autre de ces scénarios, l’interrogation de la vue renvoie une erreur d’inadéquation des colonnes.

  • Si une table source d’une vue est détruite, l’interrogation de la vue renvoie une erreur object does not exist.

  • Un schéma ne peut pas contenir une table et une vue portant le même nom. Une instruction CREATE VIEW produit une erreur si une table portant le même nom existe déjà dans le schéma.

  • Lorsqu’une vue est créée, les références non qualifiées aux tables et autres objets de base de données sont résolues dans le schéma de la vue, pas dans le schéma actuel de la session. De même, les objets partiellement qualifiés (c’est-à-dire schema.object) sont résolus dans la base de données de la vue, et non dans la base de données actuelle de la session.

    Le paramètre de session SEARCH_PATH (s’il est présent) est ignoré.

  • Utilisation de COPY GRANTS :

    • Partage des données :

      • Si la vue sécurisée existante a été partagée avec un autre compte, la vue de remplacement est également partagée.

      • Si la vue sécurisée existante a été partagée avec votre compte en tant que consommateur de données et que l’accès a été accordé à d’autres rôles dans le compte (en utilisant GRANT IMPORTED PRIVILEGES sur la base de données mère), l’accès est également accordé à la vue de remplacement.

    • La sortie SHOW GRANTS pour la vue de remplacement liste le concessionnaire des privilèges copiés comme le rôle qui a exécuté l’instruction CREATE VIEW, avec l’horodatage courant lorsque l’instruction a été exécutée.

  • Lorsque vous créez une vue, puis que vous accordez des privilèges à cette vue sur un rôle, le rôle peut utiliser la vue même s’il ne dispose pas de privilèges sur les tables sous-jacentes auxquelles la vue accède. Cela signifie que vous pouvez utiliser une vue pour donner un accès de rôle à un sous-ensemble d’une table uniquement. Par exemple, vous pouvez créer une vue qui accède aux informations de facturation médicales, mais pas aux informations de diagnostic médical dans la même table. Vous pouvez alors accorder des privilèges sur cette vue au rôle de « comptable » afin que les comptables puissent consulter les informations de facturation sans voir le diagnostic du patient.

  • De par sa conception, la commande SHOW VIEWS ne fournit pas d’informations sur les vues sécurisées. Pour afficher des informations sur une vue sécurisée, vous devez utiliser la vue VIEWS dans Information Schema, et vous devez utiliser le rôle qui possède la vue.

  • Une vue récursive doit fournir une liste de noms de colonnes.

  • Lors de la définition de vues récursives, évitez la récursivité infinie. La clause WHERE dans la définition de la vue récursive devrait permettre à la récursivité de s’arrêter au bout du compte, généralement en manquant de données après avoir traité le dernier niveau d’une hiérarchie de données.

Notes de portage

  • Certains fournisseurs prennent en charge le mot clé FORCE :

    CREATE OR REPLACE FORCE VIEW ...
    

    Snowflake accepte le mot clé FORCE, mais ne le prend pas en charge. En d’autres termes, vous n’obtenez pas d’erreur de syntaxe si vous utilisez ce mot clé, mais l’utilisation de FORCE ne force pas le serveur à créer une vue si les objets de base de données sous-jacents (table(s) ou vue(s)) n’existent pas déjà. Tenter de créer une vue d’une table ou d’une vue inexistante génère un message d’erreur même si le mot clé FORCE est utilisé.

  • Lors de la recherche des tables dans une vue, certains fournisseurs recherchent des noms de table non qualifiés dans le schéma actif ; Snowflake recherche les noms de table non qualifiés dans le même schéma que la vue. Lors du portage vers Snowflake, envisagez de mettre à jour les vues pour utiliser des noms de table complets.

Exemples

Créer une vue dans le schéma actif, avec un commentaire, qui sélectionne toutes les lignes d’une table :

CREATE VIEW myview COMMENT='Test view' AS SELECT col1, col2 FROM mytable;

SHOW VIEWS;

+---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+
| created_on                      | name              | reserved | database_name | schema_name | owner    | comment   | text                                                                     |
|---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------|
| Thu, 19 Jan 2017 15:00:37 -0800 | MYVIEW            |          | MYTEST1       | PUBLIC      | SYSADMIN | Test view | CREATE VIEW myview COMMENT='Test view' AS SELECT col1, col2 FROM mytable |
+---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+

L’exemple suivant est le même que l’exemple précédent, sauf que la vue est sécurisée :

CREATE OR REPLACE SECURE VIEW myview COMMENT='Test secure view' AS SELECT col1, col2 FROM mytable;

SELECT is_secure FROM information_schema.views WHERE view_name = 'MYVIEW';

Voici deux façons de créer des vues récursives :

Tout d’abord, créez et chargez la table :

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);

Créez une vue à l’aide d’un CTE récursif, puis interrogez la vue.

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
);
SELECT * 
    FROM employee_hierarchy 
    ORDER BY employee_ID;
+----------------------------+-------------+------------+-----------------------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_EMP_ID (SHOULD BE SAME) | MGR TITLE                  |
|----------------------------+-------------+------------+-----------------------------+----------------------------|
| President                  |           1 |       NULL | NULL                        | President                  |
| Vice President Engineering |          10 |          1 | 1                           | President                  |
| Vice President HR          |          20 |          1 | 1                           | President                  |
| Programmer                 |         100 |         10 | 10                          | Vice President Engineering |
| QA Engineer                |         101 |         10 | 10                          | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | 20                          | Vice President HR          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+

Créez une vue à l’aide du mot clé RECURSIVE, puis interrogez la vue.

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
);
SELECT * 
    FROM employee_hierarchy_02 
    ORDER BY employee_ID;
+----------------------------+-------------+------------+-----------------------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_EMP_ID (SHOULD BE SAME) | MGR TITLE                  |
|----------------------------+-------------+------------+-----------------------------+----------------------------|
| President                  |           1 |       NULL | NULL                        | President                  |
| Vice President Engineering |          10 |          1 | 1                           | President                  |
| Vice President HR          |          20 |          1 | 1                           | President                  |
| Programmer                 |         100 |         10 | 10                          | Vice President Engineering |
| QA Engineer                |         101 |         10 | 10                          | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | 20                          | Vice President HR          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+