Using views

Views can be used in the FROM clause of any SQL query or subquery.

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;

Important

If the view references an external table, you must also grant USAGE privileges to the external table's schema. So, if schema1.view1 references external table schema2.extTable1, you must also grant user2 USAGE privileges to schema2:

=> GRANT USAGE on schema schema2 to user2;

For more information see GRANT (view).