Views

• 1: Creating views
• 2: Using views
• 3: View execution
• 4: Managing views

1 - Creating views

You can create two types of views:

• CREATE VIEW creates a view that persists across all sessions until it is explicitly dropped with DROP VIEW

• CREATE LOCAL TEMPORARY VIEW creates a view that is accessible only during the current Vertica session, and only to its creator. The view is automatically dropped when the current session ends.

After you create a view, you cannot change its definition. You can replace it with another view of the same name; or you can delete and redefine it.

Permissions

To create a view, a non-superuser must have the following privileges:

For information about enabling users to access views, see View Access Permissions.

2 - Using views

Views can be used in the FROM clause of any SQL query or subquery. At execution, Vertica internally substitutes the name of the view used in the query with the actual query used in the view definition.

CREATE VIEW example

The following CREATE VIEW statement creates the view myview, which sums all individual incomes of customers listed in the store.store_sales_fact table, and groups results by state:

=> CREATE VIEW myview AS
   SELECT SUM(annual_income), customer_state FROM public.customer_dimension
     WHERE customer_key IN (SELECT customer_key FROM store.store_sales_fact)
     GROUP BY customer_state
     ORDER BY customer_state ASC;

You can use this view to find all combined salaries greater than $2 billion:

=> SELECT * FROM myview where sum > 2000000000 ORDER BY sum DESC;
     SUM     | customer_state
-------------+----------------
 29253817091 | CA
 14215397659 | TX
  5225333668 | MI
  4907216137 | CO
  4581840709 | IL
  3769455689 | CT
  3330524215 | FL
  3310667307 | IN
  2832710696 | TN
  2806150503 | PA
  2793284639 | MA
  2723441590 | AZ
  2642551509 | UT
  2128169759 | NV
(14 rows)

Enabling view access

You can query any view that you create. To enable other non-superusers to access a view, you must:

• Have SELECT...WITH GRANT OPTION privileges on the view's base table

• Grant users USAGE privileges on the view schema

• Grant users SELECT privileges to the view itself

The following example grants user2 access to view schema1.view1:

=> GRANT USAGE ON schema schema1 TO user2;
=> GRANT SELECT ON schema1.view1 TO user2;

=> GRANT USAGE on schema schema2 to user2;

For more information see GRANT (view).

3 - View execution

When Vertica processes a query that contains a view, it treats the view as a subquery. Vertica executes the query by expanding it to include the query in the view definition. For example, Vertica expands the query on the view myview shown in Using Views, to include the query that the view encapsulates, as follows:

=> SELECT * FROM
   (SELECT SUM(annual_income), customer_state FROM public.customer_dimension
    WHERE customer_key IN
      (SELECT customer_key FROM store.store_sales_fact)
    GROUP BY customer_state
    ORDER BY customer_state ASC)
    AS ship where sum > 2000000000;

View optimization

If you query a view and your query only includes columns from a subset of the tables that are joined in that view, Vertica executes that query by expanding it to include only those tables. This optimization requires one of the following conditions to be true:

• Join columns are foreign and primary keys.

• The join is a left or right outer join on columns with unique values.

View sort order

When processing a query on a view, Vertica considers the ORDER BY clause only in the outermost query. If the view definition includes an ORDER BY clause, Vertica ignores it. Thus, in order to sort the results returned by a view, you must specify the ORDER BY clause in the outermost query:

=> SELECT * FROM view-name ORDER BY view-column;

For example, the following view definition contains an ORDER BY clause inside a FROM subquery:

=> CREATE VIEW myview AS SELECT SUM(annual_income), customer_state FROM public.customer_dimension
   WHERE customer_key IN
     (SELECT customer_key FROM store.store_sales_fact)
   GROUP BY customer_state
   ORDER BY customer_state ASC;

When you query the view, Vertica does not sort the data:

=> SELECT * FROM myview WHERE SUM > 2000000000;
     SUM     | customer_state
-------------+----------------
  5225333668 | MI
  2832710696 | TN
 14215397659 | TX
  4907216137 | CO
  2793284639 | MA
  3769455689 | CT
  3310667307 | IN
  2723441590 | AZ
  2642551509 | UT
  3330524215 | FL
  2128169759 | NV
 29253817091 | CA
  4581840709 | IL
  2806150503 | PA
(14 rows)

To return sorted results, the outer query must include an ORDER BY clause:

=> SELECT * FROM myview WHERE SUM > 2000000000 ORDER BY customer_state ASC;
     SUM     | customer_state
-------------+----------------
  2723441590 | AZ
 29253817091 | CA
  4907216137 | CO
  3769455689 | CT
  3330524215 | FL
  4581840709 | IL
  3310667307 | IN
  2793284639 | MA
  5225333668 | MI
  2128169759 | NV
  2806150503 | PA
  2832710696 | TN
 14215397659 | TX
  2642551509 | UT
(14 rows)

Run-time errors

If Vertica does not have to evaluate an expression that would generate a run-time error in order to answer a query, the run-time error might not occur.

For example, the following query returns an error, because TO_DATE cannot convert the string F to the specified date format:

=> SELECT TO_DATE('F','dd mm yyyy') FROM customer_dimension;
   ERROR: Invalid input for DD: "F"

Now create a view using the same query:

=> CREATE VIEW temp AS SELECT TO_DATE('F','dd mm yyyy')
   FROM customer_dimension;
   CREATE VIEW

In many cases, this view generates the same error message. For example:

=> SELECT * FROM temp;
   ERROR: Invalid input for DD: "F"

However, if you query that view with the COUNT function, Vertica returns with the desired results:

=> SELECT COUNT(*) FROM temp;
 COUNT
-------
   100
(1 row)

This behavior works as intended. You can create views that contain subqueries, where not every row is intended to pass the predicate.

4 - Managing views

Obtaining view information

You can query system tables VIEWS and VIEW_COLUMNS to obtain information about existing views—for example, a view's definition and the attributes of columns that comprise that view. You can also query system table VIEW_TABLES to examine view-related dependencies—for example, to determine how many views reference a table before you drop it.

Renaming a view

Use ALTER VIEW to rename a view.

Dropping a view

Use DROP VIEW to drop a view. Only the specified view is dropped. Vertica does not support CASCADE functionality for views, and does not check for dependencies. Dropping a view causes any view that references it to fail.

Disabling and re-enabling views

If you drop a table that is referenced by a view, Vertica does not drop the view. However, attempts to use that view or access information about it from system table VIEW_COLUMNS return an error that the referenced table does not exist. If you restore that table, Vertica also re-enables usage of the view.