Overview of Views¶

What is a View?¶

A view allows the result of a query to be accessed as if it were a table. The query is specified in the CREATE VIEW statement.

Views serve a variety of purposes, including combining, segregating, and protecting data. For example, you can create separate views that meet the needs of different types of employees, such as doctors and accountants at a hospital:

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

Copy

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;

Copy

A view can be used almost anywhere that a table can be used (joins, subqueries, etc.). For example, using the views created above:

• Show all of the types of medical problems for each patient:

  SELECT DISTINCT diagnosis FROM doctor_view;
  +--------------------+
  | DIAGNOSIS          |
  |--------------------|
  | Industrial Disease |
  | python bite        |
  +--------------------+
  

  Copy

• Show the cost of each treatment (without showing personally identifying information about specific patients):

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

  Copy

A CREATE VIEW command can use a fully-qualified, partly-qualified, or unqualified table name. For example:

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;

Copy

If the schema is not specified, then Snowflake assumes that the table is in the same schema as the view. (If the table were assumed to be in the active schema, then the view could refer to different tables at different times.)

Types of Views¶

Snowflake supports two types of views:

• Non-materialized views (usually simply referred to as “views”)

• Materialized views.

Secure Views¶

Both non-materialized and materialized views can be defined as secure. Secure views have advantages over standard views, including improved data privacy and data sharing; however, they also have some performance impacts to take into consideration.

For more details, see Working with Secure Views.

Recursive Views (Non-materialized Views Only)¶

A non-materialized view can be recursive (i.e. the view can refer to itself).

Use of recursion in views is similar to the use of recursion in recursive CTEs. In fact, a view can be defined with a recursive CTE. For example:

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

Instead of using a recursive CTE, you can create a recursive view with the keyword RECURSIVE, for example:

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

For more details, including examples, see CREATE VIEW.

Advantages of Views¶

Limitations on Views¶

• For limitations and usage notes related to creating views, see CREATE VIEW.

• The definition for a view cannot be updated (i.e. you cannot use ALTER VIEW or ALTER MATERIALIZED VIEW to change the definition of a view). To change a view definition, you must recreate the view with the new definition.

• Views are read-only (i.e. you cannot execute DML commands directly on a view). However, you can use a view in a subquery within a DML statement that updates the underlying base table. For example:

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

  Copy

• Changes to a table are not automatically propagated to views created on that table. For example, if you drop a column in a table, the views on that table might become invalid.