0001 ---
0002 layout: global
0003 title: CREATE VIEW
0004 displayTitle: CREATE VIEW
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 Views are based on the result-set of an `SQL` query. `CREATE VIEW` constructs
0025 a virtual table that has no physical data therefore other operations like
0026 `ALTER VIEW` and `DROP VIEW` only change metadata.
0027
0028 ### Syntax
0029
0030 ```sql
0031 CREATE [ OR REPLACE ] [ [ GLOBAL ] TEMPORARY ] VIEW [ IF NOT EXISTS ] view_identifier
0032 create_view_clauses AS query
0033 ```
0034
0035 ### Parameters
0036
0037 * **OR REPLACE**
0038
0039 If a view of same name already exists, it will be replaced.
0040
0041 * **[ GLOBAL ] TEMPORARY**
0042
0043 TEMPORARY views are session-scoped and will be dropped when session ends
0044 because it skips persisting the definition in the underlying metastore, if any.
0045 GLOBAL TEMPORARY views are tied to a system preserved temporary database `global_temp`.
0046
0047 * **IF NOT EXISTS**
0048
0049 Creates a view if it does not exist.
0050
0051 * **view_identifier**
0052
0053 Specifies a view name, which may be optionally qualified with a database name.
0054
0055 **Syntax:** `[ database_name. ] view_name`
0056
0057 * **create_view_clauses**
0058
0059 These clauses are optional and order insensitive. It can be of following formats.
0060
0061 * `[ ( column_name [ COMMENT column_comment ], ... ) ]` to specify column-level comments.
0062 * `[ COMMENT view_comment ]` to specify view-level comments.
0063 * `[ TBLPROPERTIES ( property_name = property_value [ , ... ] ) ]` to add metadata key-value pairs.
0064
0065 * **query**
0066 A [SELECT](sql-ref-syntax-qry-select.html) statement that constructs the view from base tables or other views.
0067
0068 ### Examples
0069
0070 ```sql
0071 -- Create or replace view for `experienced_employee` with comments.
0072 CREATE OR REPLACE VIEW experienced_employee
0073 (ID COMMENT 'Unique identification number', Name)
0074 COMMENT 'View for experienced employees'
0075 AS SELECT id, name FROM all_employee
0076 WHERE working_years > 5;
0077
0078 -- Create a global temporary view `subscribed_movies` if it does not exist.
0079 CREATE GLOBAL TEMPORARY VIEW IF NOT EXISTS subscribed_movies
0080 AS SELECT mo.member_id, mb.full_name, mo.movie_title
0081 FROM movies AS mo INNER JOIN members AS mb
0082 ON mo.member_id = mb.id;
0083 ```
0084
0085 ### Related Statements
0086
0087 * [ALTER VIEW](sql-ref-syntax-ddl-alter-view.html)
0088 * [DROP VIEW](sql-ref-syntax-ddl-drop-view.html)
0089 * [SHOW VIEWS](sql-ref-syntax-aux-show-views.html)
