Share via

CREATE VIEW (U-SQL)

  • Article

Summary

The 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.

Syntax

Create_View_Statement :=                                                                                 
    'CREATE' 'VIEW' ['IF' 'NOT' 'EXISTS'] Identifier 'AS' Query_Expression.

Remarks

  • Identifier
    Specifies the name of the view to be defined. If the Identifier is 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 EXISTS is 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.

  • Query_Expression
    The query expression defines the view’s schema and defines the query that will be executed every time the view gets referenced in a query.

    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.

Examples

View based on a table
The following example creates a view called SampleView based on the table SampleTable in SampleDB.

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 EXTRACT.

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();

See Also