Snowflake
Create Views

Snowflake CREATE VIEW: How to Create Views

The CREATE VIEW command in SQL is used to create a new view, or virtual table, in the current or specified schema based on a query involving one or more existing tables or other valid query expressions.

What it does

CREATE VIEW allows you to store the SQL statement for later use as a virtual table, which can be referenced in future SQL queries. A view can simplify complex queries, enhance security by limiting data exposure, and present a different representation of the data.

Syntax


_10
CREATE [ OR REPLACE ] [ SECURE ] [ TEMPORARY ] VIEW [ IF NOT EXISTS ] <view_name>
_10
[ (column_list) ]
_10
AS <select_statement>;

  • OR REPLACE: Optional keyword that allows you to replace an existing view with the new one if it already exists.
  • SECURE: Optional keyword for creating secure views that protect sensitive data by hiding the view definition and preventing indirect data exposure through optimizations. Ideal for enhancing data privacy but may impact query performance.
  • TEMPORARY: Optional keyword indicating the view is temporary and only exists during the session.
  • IF NOT EXISTS: Optional clause to prevent an error if the view already exists.
  • view_name: The name of the new view.
  • column_list: Optional list of names for the columns in the view.
  • select_statement: The SQL SELECT statement that defines the view.

Key points

  • View Lifespan: By default, views are permanent unless the TEMPORARY keyword is used. Temporary views disappear at the end of the session.
  • Data Security: Secure views restrict data visibility to authorized users only.
  • Custom Column Names: You can rename columns in the view for clarity or security purposes.

Examples

Create a secure view

Suppose we have a table called galactic_travel_logs that contains detailed logs of characters’ travels, including sensitive information like travel reasons and exact dates, which we want to secure and partially exclude from the view.

Input

Table: galactic_travel_logs

character_idcharacter_nameplanet_visitedvisit_datereason_for_visit
1Arthur DentBetelgeuse2024-04-18Vacation
2Ford PrefectEarth2024-04-20Research
3Zaphod BeeblebroxMagrathea2024-04-22Business

Snowflake SQL Query


_10
CREATE OR REPLACE SECURE VIEW secure_galactic_travel_logs AS
_10
SELECT character_name, planet_visited
_10
FROM galactic_travel_logs
_10
WHERE reason_for_visit <> 'Vacation';

Output

View: secure_galactic_travel_logs

character_nameplanet_visited
Ford PrefectEarth
Zaphod BeeblebroxMagrathea

Explanation

  • Secure View: The view is marked as SECURE, ensuring that the underlying data and view definition are not accessible to unauthorized users.
  • Data Filtering: Sensitive information about the reasons and dates of visits are excluded to protect the privacy of the characters involved.
  • Simplicity: The view provides a simplified dataset focusing on characters and their destinations, suitable for non-sensitive usage like reporting or non-privileged user access.
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free