CREATE VIEW (U-SQL)
CREATE VIEW statement creates the view with the specified identifier based on the provided query expression. The view’s schema is inferred from the query’s result type.
Create_View_Statement := 'CREATE' 'VIEW' ['IF' 'NOT' 'EXISTS'] Identifier 'AS' Query_Expression.
Specifies the name of the view to be defined. If the
Identifieris a three-part identifier, the view will be created in the specified database and schema. If it is a two-part identifier, then the view will be created in the specified schema of the current database context. If the identifier is a simple identifier, then the view will be created in the current database and schema context.
If an object of the given name already exists in the specified database and schema context or the user has no permissions to create a view, an error is raised.
IF NOT EXISTS
If the optional
IF NOT EXISTSis specified, then the statement creates the view if it does not already exist, or succeeds without changes if the view already exists and the user has permission to at least enumerate all existing views.
The query expression is not allowed to refer to variables or user-defined objects like user-defined functions, user-defined operators or user-defined types. Currently, the creation of the view will succeed, but subsequent invocations of the view will raise errors if they refer to variables or user-defined objects. Please use U-SQL Table-valued Functions instead, if you need to use user-defined functions, operators or types.
- The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
- The examples below use the sample data provided with your Data Lake Analytics account. See Prepare source data for additional information.
View based on a table
The following example creates a view called
SampleView based on the table
CREATE VIEW SampleDB.dbo.SampleView AS SELECT id, name, date FROM SampleDB.dbo.SampleTable;
View based on an extraction
Subsequent queries against
SearchLog.tsv can now be performed against the view without the need to constantly execute
CREATE DATABASE IF NOT EXISTS TestReferenceDB; USE DATABASE TestReferenceDB; DROP VIEW IF EXISTS SearchlogView; CREATE VIEW SearchlogView AS EXTRACT UserId int, Start DateTime, Region string, Query string, Duration int?, Urls string, ClickedUrls string FROM "/Samples/Data/SearchLog.tsv" USING Extractors.Tsv();