COMBINE Expression (U-SQL)

Updated: March 10, 2017

U-SQL provides the ability to write custom rowset combiners in C# using the user-defined operator extensibility framework by implementing an ICombiner. See U-SQL Programmability Guide: User-Defined Combiner for more information.

A combiner provides a way to implement custom join operators that are more complex than the standard U-SQL join expressions.

A combiner is being invoked with the COMBINE expression that provide the necessary information about both the input rowsets, the join clause, the expected result schema as well as additional information.

A combiner provides limited optimization support, because an optimizer cannot reason about the procedural C# code defining the combiner For example, it cannot not push predicates through to earlier statements unless the column used in the predicate is marked as read only. Therefore, it is recommended to instead use the standard joins if possible or use the READONLY and REQURED clauses.

Syntax
Combine_Expression := 'COMBINE' Combine_Input 'WITH' Combine_Input Join_On_Clause Produce_Clause [Readonly_Clause] [Required_Clause] Using_Clause.

Semantics of Syntax Elements

Combine_Input
Specifies the input rowsets that the combiner will join.

Syntax
Combine_Input := Aliased_Rowset.

with

Aliased_Rowset
Aliased rowsets are rowsets that may or may not need a table alias.

Syntax
Aliased_Rowset := Rowset [Alias] \| Rowset_Expression Alias.

where

Alias
is the rowset alias given to the rowset that can be used in the remainder of the COMBINE expression to refer to that specific rowset. It can be a quoted or unquoted identifier:
Syntax
Alias := 'AS' Quoted_or_Unquoted_Identifier.
Rowset
The two simplest rowset sources are a rowset variable such as @rowset that has been defined in a previous statement of the script or a table that has been created in the account’s catalog:
Syntax
Rowset := Rowset_Variable | Identifier.
A table can be referenced either with its fully qualified 3-part name, within the current database context with a 2-part name, or within the current database and schema context with a single-part name. Optionally, a table alias can be provided for an input rowset variable or table which then can be used in the remainder of the COMBINE expression.
Providing a rowset alias is optional.

Syntax
Alias := 'AS' Quoted_or_Unquoted_Identifier.

Syntax
Rowset := Rowset_Variable \| Identifier.

Rowset_Expression
U-SQL also provides the ability to combine nested query expressions, table-valued function calls or querying external rowsets. Follow the links for more details on each.

Syntax
Rowset_Expression := '(' Query_Expression ')' \| Function_Call \| External_Rowset_Expression.

In these cases, a rowset alias has to be provided and cannot be left out.
The UDO programming model makes both the values and the schema of the input rowsets available in the context of the combiner's implementation.

Join_On_Clause
Specifies the join comparison that is being used to identify which of the rows from each rowset will be joined.
Syntax
Join_On_Clause := 'ON' Join_Comparison_Expression.
where Join_Comparison_Expression provides the join comparison expression for the combiner.

Syntax
Join_On_Clause := 'ON' Join_Comparison_Expression.

Produce_Clause
Specifies the rowset schema returned by the COMBINE expression.

Syntax
Produce_Clause := 'PRODUCE' Column_Definition_List.

where

Column_Definition_List
This list defines the schema of the combiner. The returned columns are defined as a pair of column names and column types:

Syntax
Column_Definition_List := Column_Definition { ',' Column_Definition}. Column_Definition := Quoted_or_Unquoted_Identifier Built_in_Type.

Each column has an identifier that can be either a quoted or unquoted identifier. A column is typed with one of the U-SQL types that the combiner supports.

The UDO programming model makes the specified rowset schema available to the implementation of the combiner. An error is raised if the combiner is producing a schema that is incompatible with the specified return schema.

Readonly_Clause
The optional READONLY clause can help the UDO programmer to write more efficient code. For more information on how the UDO programmer can take advantage of this hint, see the U-SQL C# Developer’s Guide.

The optional READONLY clause specifies the columns are read only for the combiner and will be passed through to the output using either the same name or the specified column name in parenthesis. Only columns in the combine expression’s ON clause can be marked READONLY, otherwise the error “E_CSC_USER_UDOREADONLYNOTKEYCOLUMN: Column '…' cannot be marked as READONLY” is raised.

Syntax
Readonly_Clause := 'READONLY' Star_Or_Readonly_Column_List. Star_Or_Readonly_Column_List := '*' \| Readonly_Column_List. Readonly_Column_List := Readonly_Column { ',' Readonly_Column }. Readonly_Column := Column_Identifier [Output_Column_Dependency_Alias]. Output_Column_Dependency_Alias := '(' Quoted_or_Unquoted_Identifier ')'.

Syntax

Readonly_Clause :=                                                                                  
    'READONLY' Star_Or_Readonly_Column_List.

Star_Or_Readonly_Column_List :=
    '*' | Readonly_Column_List.

Readonly_Column_List :=
    Readonly_Column { ',' Readonly_Column }.

Readonly_Column :=
    Column_Identifier [Output_Column_Dependency_Alias].

Output_Column_Dependency_Alias :=
    '(' Quoted_or_Unquoted_Identifier ')'.

Required_Clause
The optional REQUIRED clause can help the UDO programmer to write more efficient code. For more information on how the UDO programmer can take advantage of this hint, see the U-SQL C# Developer’s Guide.

The optional REQUIRED clause specifies that either all columns are required on input for the combiner (if specified with *) or the specified columns are required. If a specified column is followed by a list of columns in parenthesis, then the input column is only required if the columns in that list are referenced from the output.

Syntax
Required_Clause := 'REQUIRED' Star_Or_Required_Column_List. Star_Or_Required_Column_List := '*' \| Required_Column_List. Required_Column_List := Required_Column { ',' Required_Column}. Required_Column := Column_Identifier [Required_Output_Column_Dependency_List]. Required_Output_Column_Dependency_List := '(' Identifier_List ')'.

Syntax

Required_Clause :=                                                                                  
    'REQUIRED' Star_Or_Required_Column_List.

Star_Or_Required_Column_List :=  
    '*' | Required_Column_List.

Required_Column_List :=  
    Required_Column { ',' Required_Column}.

Required_Column :=  
    Column_Identifier [Required_Output_Column_Dependency_List].

Required_Output_Column_Dependency_List :=  
    '(' Identifier_List ')'.

Using_Clause
The USING clause specifies which combiner should be used to transform the input rowset.
Syntax
USING_Clause := 'USING' udo_expression.
The USING clause takes a C# expression that returns an instance of ICombiner. Users can write their own by implementing an ICombiner (see U-SQL Programmability Guide: User-Defined Combiner for more detail on how to write your own combiner). Most commonly, the UDO expression is either the instantiation of a combiner class of the form
Copy
USING new MyNameSpace.MyCombiner(parameter:"value")
or the invocation of a factory method
Copy
USING MyNameSpace.MyCombinerFactory(parameter:"value")
where parameter is a parameter of the combiner.

Syntax
USING_Clause := 'USING' udo_expression.

Example

The example can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
The script can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.
For simplicity, the example(s) with user-defined code make use of Code-Behind for assembly management. The main advantage of Code-Behind is that the tooling will register the assembly file and add the REFERENCE ASSEMBLY statement automatically. To use Assembly registration instead of Code-Behind, see Using Assemblies: Code-Behind vs. Assembly Registration Walkthrough.

User-Defined Combiner - CombinerEX
c# code is placed in the associated Code-Behind .cs file. See usage in next section, below.

using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ReferenceGuide_Examples
{
    [SqlUserDefinedCombiner]
    public class CombinerEX : ICombiner
    {
        public override IEnumerable<IRow> Combine(IRowset left, IRowset right, IUpdatableRow output)
        {
            var theRight = (from row in right.Rows
                            select new
                            {
                                Division = row.Get<string>("Division"),
                                carIDstart = row.Get<int>("carIDstart"),
                                carIDend = row.Get<int>("carIDend")
                            }).ToList();

            foreach (var row in left.Rows)
            {
                int carID = row.Get<int>("carID");

                var theLeft = (from w in theRight
                               where carID >= w.carIDstart && carID <= w.carIDend
                               select new
                               {
                                   Division = w.Division
                               }).ToList();

                output.Set<string>("currentModel", row.Get<string>("currentModel"));
                output.Set<string>("introYear", row.Get<string>("introYear"));
                output.Set<string>("Division", theLeft[0].Division);
                yield return output.AsReadOnly();
            }
        }
    }
}

Using User-Defined Combiner - CombinerEX
Using Code-Behind from previous section, above.

U-SQL

@right = 
    SELECT * FROM 
        ( VALUES
        (0, 99, "Ford Motor Company", "Ford"),
        (100, 199, "Ford Motor Company", "Lincoln"),
        (200, 299, "Ford Motor Company", "Motorcraft"),
        (300, 399, "General Motors Company", "Buick"),
        (400, 499, "General Motors Company", "Chevrolet"),
        (500, 599, "General Motors Company", "Cadillac"),
        (600, 699, "General Motors Company", "GMC"),
        (700, 799, "Fiat Chrysler", "Chrysler"),
        (800, 899, "Fiat Chrysler", "Dodge"),
        (900, 999, "Fiat Chrysler", "Jeep"),
        (1000, 1099, "Fiat Chrysler", "Ram"),
        (1100, 1199, "Fiat Chrysler", "Mopar"),
        (1200, 1299, "Fiat Chrysler", "SRT")
        ) AS T(carIDstart, carIDend, Automaker, Division);

@left = 
    SELECT * FROM 
        ( VALUES
        (3, 0, "Mustang", "1964"),
        (7, 0, "Fiesta", "1976"),
        (133, 100, "Navigator", "1998"),
        (160, 100, "Continental", "2017"),
        (639, 600, "Canyon", "2004"),
        (801, 800, "Challenger", "2008"),
        (802, 800, "Charger", "2006")
        ) AS T(carID, baseID, currentModel, introYear);

@result =
    COMBINE @left AS l
    WITH @right AS r
    ON l.baseID == r.carIDstart
    PRODUCE currentModel string,
            Division string,
            introYear string
    REQUIRED Division, carIDstart, carIDend, carID, currentModel, introYear
    USING new ReferenceGuide_Examples.CombinerEX();

OUTPUT @result
TO "/Output/ReferenceGuide/StatementsAndExpressions/PrimaryRowsetExpressions/Combine/ExampleA.csv"
USING Outputters.Csv(outputHeader: true);

Community Additions

ADD

English	Dansk	Deutsch	Español
Français	Italiano	Nederlands	Polski
Português	Svenska	Türkçe	Pусский
日本語	한국어	简体中文	繁體中文

COMBINE Expression (U-SQL)

Semantics of Syntax Elements

Example

See Also

Community Additions