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
_10CREATE [ 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
TEMPORARYkeyword 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_id | character_name | planet_visited | visit_date | reason_for_visit |
|---|---|---|---|---|
| 1 | Arthur Dent | Betelgeuse | 2024-04-18 | Vacation |
| 2 | Ford Prefect | Earth | 2024-04-20 | Research |
| 3 | Zaphod Beeblebrox | Magrathea | 2024-04-22 | Business |
Snowflake SQL Query
_10CREATE OR REPLACE SECURE VIEW secure_galactic_travel_logs AS_10SELECT character_name, planet_visited_10FROM galactic_travel_logs_10WHERE reason_for_visit <> 'Vacation';
Output
View: secure_galactic_travel_logs
| character_name | planet_visited |
|---|---|
| Ford Prefect | Earth |
| Zaphod Beeblebrox | Magrathea |
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.
