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 grant them USAGE privileges to the view schema, and 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).