CREATE VIEW¶

Syntaxe¶

CREATE [ OR REPLACE ] [ SECURE ] [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE } ] [ RECURSIVE ] VIEW [ IF NOT EXISTS ] <name>
  [ ( <column_list> ) ]
  [ <col1> [ WITH ] MASKING POLICY <policy_name> [ USING ( <col1> , <cond_col1> , ... ) ]
           [ WITH ] PROJECTION POLICY <policy_name>
           [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ , <col2> [ ... ] ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] AGGREGATION POLICY <policy_name> ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  AS <select_statement>

Paramètres requis¶

name

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.

select_statement

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)

{ [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE }

Spécifie que la vue ne persiste que pendant la durée de la session dans laquelle vous l’avez créée. Une vue temporaire et tout son contenu sont supprimés à la fin de la session.

Les synonymes et abréviations de TEMPORARY (comme GLOBAL TEMPORARY) sont fournis pour assurer la compatibilité avec d’autres bases de données (par exemple, pour éviter les erreurs lors de la migration des instructions CREATE VIEW). Les vues créées avec l’un de ces mots-clés apparaissent et se comportent de la même manière qu’une vue créée avec le mot-clé TEMPORARY.

Par défaut : aucune valeur. Si une vue n’est pas déclarée comme TEMPORARY, la vue est permanente.

Si vous voulez éviter des conflits inattendus, évitez de nommer les vues temporaires d’après des vues qui existent déjà dans le schéma.

Si vous avez créé une vue temporaire portant le même nom qu’une autre vue du schéma, toutes les requêtes et opérations utilisées sur la vue n’affectent que la vue temporaire dans la session, jusqu’à ce que vous supprimiez la vue temporaire. Si vous supprimez la vue, vous supprimez la vue temporaire et non la vue qui existe déjà dans le schéma.

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)

column_list

Si vous voulez modifier le nom d’une colonne ou ajouter un commentaire à une colonne dans la nouvelle vue, incluez une liste de colonnes qui spécifie les noms des colonnes et (si nécessaire) les commentaires sur les colonnes. (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 (pre_tax_profit, taxes, after_tax_profit) AS
    SELECT revenue - cost, (revenue - cost) * tax_rate, (revenue - cost) * (1.0 - tax_rate)
    FROM table1;

Copy

Vous pouvez spécifier un commentaire facultatif pour chaque colonne. Par exemple :

CREATE VIEW v1 (pre_tax_profit COMMENT 'revenue minus cost',
                taxes COMMENT 'assumes taxes are a fixed percentage of profit',
                after_tax_profit)
    AS
    SELECT revenue - cost, (revenue - cost) * tax_rate, (revenue - cost) * (1.0 - tax_rate)
    FROM table1;

Copy

Les commentaires sont particulièrement utiles lorsque les noms des colonnes sont cryptiques.

Pour afficher les commentaires, utilisez DESCRIBE VIEW.

MASKING POLICY = policy_name

Spécifie la politique de masquage à définir sur une colonne.

USING ( col_name , cond_col_1 ... )

Spécifie les arguments à passer dans l’expression SQL de la politique de masquage conditionnelle.

La première colonne de la liste spécifie la colonne pour les conditions de la politique de masquage ou de tokenisation des données et doit correspondre à la colonne à laquelle la politique de masquage est définie.

Les colonnes supplémentaires spécifient les colonnes à évaluer pour déterminer s’il faut masquer ou tokeniser les données de chaque ligne du résultat de la requête lorsqu’une requête est effectuée sur la première colonne.

Si la clause USING est omise, Snowflake traite la politique de masquage conditionnelle comme une politique de masquage normale.

PROJECTION POLICY policy_name

Spécifie la politique de projection à définir sur une colonne.

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 = 'string_literal'

Spécifie un commentaire pour la vue.

Par défaut : aucune valeur

ROW ACCESS POLICY policy_name ON ( col_name [ , col_name ... ] )

Spécifie la politique d’accès aux lignes à définir sur une vue.

AGGREGATION POLICY policy_name

Spécifie la politique d’agrégation à définir sur une vue.

TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )

Spécifie le nom de la balise et la valeur de la chaîne de la balise.

La valeur de la balise est toujours une chaîne de caractères et le nombre maximum de caractères pour la valeur de la balise est 256.

Pour plus d’informations sur la spécification des balises dans une instruction, voir Quotas de balises pour les objets et les colonnes.

Exigences en matière de contrôle d’accès¶

Un rôle utilisé pour exécuter cette commande SQL doit avoir les privilèges suivants définis au minimum ainsi :

Notez que l’exploitation d’un objet dans un schéma requiert également le privilège USAGE sur la base de données et le schéma parents.

Pour obtenir des instructions sur la création d’un rôle personnalisé avec un ensemble spécifique de privilèges, voir Création de rôles personnalisés.

Pour des informations générales sur les rôles et les privilèges accordés pour effectuer des actions SQL sur des objets sécurisables, voir Aperçu du contrôle d’accès.

Notes sur l’utilisation¶

• Une définition de vue peut inclure une clause ORDER BY (par exemple create view v1 as select * from t1 ORDER BY column1). Cependant, Snowflake recommande d’exclure la clause ORDER BY de la plupart des définitions de vues. Si la vue est utilisée dans des contextes qui ne bénéficient pas du tri, alors la clause ORDER BY ajoute des coûts inutiles. Par exemple, lorsque la vue est utilisée dans une jointure, et que la colonne de jointure n’est pas la même que la colonne ORDER BY , le coût supplémentaire pour trier les résultats de la vue est généralement gaspillé. Si vous devez trier les résultats de la requête, il est généralement plus efficace de spécifier ORDER BY dans la requête qui utilise la vue, plutôt que dans la vue elle-même.

• La définition d’une vue est limitée à 95KB.

• Les niveaux d’imbrication sont limités à un maximum de 20. Toute tentative de création d’une vue imbriquée plus de 20 fois échouera.

• 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 des modifications sont apportées aux colonnes de la table, par exemple :

    • Une colonne est supprimée.

    • Une colonne est ajoutée.

    • L’ordre des colonnes est modifié.

  Dans ces cas, l’interrogation de la vue renvoie une erreur liée à la colonne.

• 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é.

• Utiliser OR REPLACE équivaut à utiliser DROP VIEW sur la vue existante, puis à créer une nouvelle vue avec le même nom.

  Les instructions CREATE OR REPLACE <objet> sont atomiques. En d’autres termes, lorsqu’un objet est remplacé, l’ancien objet est supprimé et le nouvel objet est créé dans une seule transaction.

  Cela signifie que toutes les requêtes simultanées à l’opération CREATE OR REPLACE VIEW utilisent soit l’ancienne soit la nouvelle version de la vue.

  La recréation ou le remplacement d’une vue entraîne la destruction de ses données de modification et donc l’obsolescence de tout flux sur la vue. Un flux périmé est illisible.

• 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.

• Concernant les métadonnées :

  Attention

  Les clients doivent s’assurer qu’aucune donnée personnelle (autre que pour un objet utilisateur), donnée sensible, donnée à exportation contrôlée ou autre donnée réglementée n’est saisie comme métadonnée lors de l’utilisation du service Snowflake. Pour plus d’informations, voir Champs de métadonnées dans Snowflake.

• Lors de la création d’une vue avec une politique de masquage sur une ou plusieurs colonnes de la vue, ou une politique d’accès aux lignes ajoutée à la vue, utilisez la fonction POLICY_CONTEXT pour simuler une requête sur la ou les colonnes protégées par une politique de masquage et la vue protégée par une politique d’accès aux lignes.

• Ne créez pas de vues avec des flux en tant qu’objets sources à moins que le même rôle ne possède à la fois la vue et les flux sources (c’est-à-dire que le même rôle, ou un rôle inférieur dans une hiérarchie de rôles, possède le privilège OWNERSHIP sur la vue et les flux sources). Au lieu de cela, créez des vues dont les objets à suivre sont les objets sources. Ensuite, créez des flux sur ces vues. Pour plus d’informations, voir Streams on Views.

Notes de portage¶

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

  CREATE OR REPLACE FORCE VIEW ...
  

  Copy

  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 la création d’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 |
+---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+

Copy

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 table_name = 'MYVIEW';

Copy

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);

Copy

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);

Copy

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
);

Copy

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          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+

Copy

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
);

Copy

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          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+

Copy