CREATE VIEW syntax in Snowflake

Views allow you to Grant Access to a Subset of a Table, for example, if you have to expose a few columns as against the entire table. Views also help to write clearer, more modular SQL code

create view mytable_view as select * from mytable;

Creates a new view in the current/specified schema, based on a query of one or more existing tables (or any other valid query expression)

Syntax:

CREATE [ OR REPLACE ] [ SECURE ] VIEW [ IF NOT EXISTS ] <name>
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  AS <select_statement>

To create a secure view:

create or replace secure view myview comment='Test secure view' 
as select * from mytable;

Limitations of a view:

• You cannot update a view. You can SELECT from a view, but you cannot INSERT, UPDATE, DELETE, MERGE, etc. directly through a view
  
• View definitions are not dynamic, i.e. a view is not automatically updated if the underlying sources are modified such that they no longer match the view definition, particularly when columns are dropped

Once the views are created, verify using,

show views;

Mike-Barn

posted on 20 Oct 18