USE demo; CREATE OR REPLACE VIEW high_sales AS SELECT * FROM delta.`wasbs://courseware@dbacademy.blob.core.windows.net/data-analysis-with-databricks/v01/sales` WHERE total_price > 10000; SELECT * FROM high_sales;
CREATE VIEW (Databricks SQL)
CREATE [ OR REPLACE ] [ TEMPORARY ] VIEW [ IF NOT EXISTS ] view_name [ column_list ] [ COMMENT view_comment ] [ TBLPROPERTIES clause ] AS query column_list ( { column_alias [ COMMENT column_comment ] } [, ...] )
Parameters
- OR REPLACE – If a view of the same name already exists, it is replaced. To replace an existing view you must be its owner.
- TEMPORARY – TEMPORARY views are visible only to the session that created them and are dropped when the session ends.
- IF NOT EXISTS – Creates the view only if it does not exist. If a view by this name already exists the
CREATE VIEW
statement is ignored.You may specify at most one ofIF NOT EXISTS
orOR REPLACE
. - view_name – The name of the newly created view. A temporary view’s name must not be qualified. A the fully qualified view name must be unique.
- column_list – Optionally labels the columns in the query result of the view. If you provide a column list the number of column aliases must match the number of expressions in the query. In case no column list is specified aliases are derived from the body of the view.
- column_alias – The column aliases must be unique.
- column_comment – An optional STRING literal describing the column alias.
- view_comment – An optional STRING literal providing a view-level comments.
- TBLPROPERTIES – Optionally sets one or more user defined properties.
- AS query – A query that constructs the view from base tables or other views.
Examples
-- Create or replace view for `experienced_employee` with comments. > CREATE OR REPLACE VIEW experienced_employee (id COMMENT 'Unique identification number', Name) COMMENT 'View for experienced employees' AS SELECT id, name FROM all_employee WHERE working_years > 5; -- Create a temporary view `subscribed_movies`. > CREATE TEMPORARY VIEW subscribed_movies AS SELECT mo.member_id, mb.full_name, mo.movie_title FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;