Query Statements and Expressions (U-SQL)

 

Updated: April 26, 2017

The core processing capability of U-SQL is to transform between rowsets using query expressions. This is done by combining query statements in a U-SQL script.

Since the current version of the Azure Data Lake Analytics service is only executing batch scripts, a query expression cannot be returned directly. It always has to be either output into a table with an INSERT statement or a file using an OUTPUT statement, or assigned to a rowset variable.

Syntax U-SQL Query Statements
Query_Statement :=                                                                                       
    Rowset_Variable '=' Query_Expression.

Rowset_Variable := '@' + Unquoted_Identifier.

Semantics of Syntax Elements

  • Rowset_Variable
    The rowset variable is the name given to the provided Query_Expression. Note that this rowset variable is not containing the result of the query expression. It is a reference to the expression itself, similar to the SQL Common-Table Expression names or the names of functional lambda expressions in functional languages.

    The name can be used in subsequent query expressions to refer to the named expression that then will be inlined.

  • Query_Expression
    Is the actual transformation expression.

    Syntax U-SQL Query Expressions
    Query_Expression :=
         Primary_Rowset_Expression
    |   '(' Query_Expression ')'.                                                                       

    A query expression can either be a primary rowset query or a query expression enclosed in parenthesis.

Examples

  • The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
  • The scripts can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.
@someBooks = 
    SELECT * FROM 
        ( VALUES
        ("The Book Thief", "Markus Zusak", "2005"),
        ("The Girl with the Dragon Tattoo", "Stieg Larsson", "2005"),
        ("The Silver Linings Playbook", "Matthew Quick", "2008"),
        ("Sarah's Key", "Tatiana de Rosnay", "2006")
        ) AS T(Book, Author, [Publication Year]);
        
@rowsetVariable =
    SELECT Book, Author
    FROM @someBooks;

OUTPUT @rowsetVariable
TO "/ReferenceGuide/DML/QSE/exampleA.txt"
USING Outputters.Tsv();

See Also

Community Additions

ADD
Show: