U-SQL Functions

  • Article

Summary

U-SQL supports scalar functions and table-valued functions. Functions generally take 0 to n arguments and will return a value as a result. While they should be deterministic and side-effect free to not negatively affect U-SQL’s declarative semantics, there is no guarantee that all functions will satisfy this requirement. For more details refer to the function categories below.

Scalar functions – as their names imply – return values that are instances of a type that is not a table type. Currently U-SQL scalar functions fit into three categories: user-defined functions written in C#, general C# functions from system provided assemblies, and built-in U-SQL functions.

U-SQL table-valued functions return tables and are written in U-SQL.

Examples

Function dt_TryParse_USQL
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.IO;
using System.Linq;
using System.Text;

namespace ReferenceGuide_Examples
{
    public class MyClass
    {
        public static DateTime? dt_TryParse_USQL(string dateString)
        {
            DateTime dateValue;

            if (DateTime.TryParse(dateString, out dateValue))
                return dateValue;
            else
                return null;
        }
    }
}

Using Function dt_TryParse_USQL
Using above code-behind and calling function. Function consumes a string and attempts to convert the string to a DateTime value using DateTime.TryParse. Using the Code-Behind above.

@employees = 
    SELECT * FROM 
        ( VALUES
        (1, "Noah",   "2/16/2008"),
        (2, "Sophia", "2/16/2008 12:15:12 PM"),
        (3, "Liam",   "16/02/2008 12:15:12"),
        (4, "Amy",    "2017-01-11T16:52:07"),
        (5, "Justin", "")
        ) AS T(EmpID, EmpName, StartDate);

@result = 
    SELECT  
        EmpID,
        EmpName,
        ReferenceGuide_Examples.MyClass.dt_TryParse_USQL(StartDate) AS validated_StartDate
    FROM @employees;

OUTPUT @result
TO "/Output/ReferenceGuide/DDL/Functions/dt_TryParse_USQL.csv"
USING Outputters.Csv(outputHeader: true);

Using inline function expression
Similar as above except here the function is defined inline.

@result = 
    SELECT
        EmpID,
        EmpName,
        (
            (Func<string, DateTime?>)
            (dateString =>  // input_paramater
                { 
                    DateTime dateValue;
                    return DateTime.TryParse(dateString, out dateValue) ? (DateTime?)dateValue : (DateTime?)null;
                }
             )
        ) (StartDate) AS validated_StartDate
    FROM @employees;

OUTPUT @result
TO "/Output/ReferenceGuide/DDL/Functions/inlineFunctionExpression.csv"
USING Outputters.Csv(outputHeader: true);

Function GetFiscalPeriod
c# code is placed in the associated Code-Behind .cs file. See usage in next section, below.

using System;

namespace ReferenceGuide_Examples
{
    public class MyClass
    {
        public static string GetFiscalPeriod(DateTime dt)
        {
            int FiscalMonth = 0;
            if (dt.Month < 7)
            {
                FiscalMonth = dt.Month + 6;
            }
            else
            {
                FiscalMonth = dt.Month - 6;
            }

            int FiscalQuarter = 0;
            if (FiscalMonth >= 1 && FiscalMonth <= 3)
            {
                FiscalQuarter = 1;
            }
            if (FiscalMonth >= 4 && FiscalMonth <= 6)
            {
                FiscalQuarter = 2;
            }
            if (FiscalMonth >= 7 && FiscalMonth <= 9)
            {
                FiscalQuarter = 3;
            }
            if (FiscalMonth >= 10 && FiscalMonth <= 12)
            {
                FiscalQuarter = 4;
            }

            return "Q" + FiscalQuarter.ToString() + ":P" + FiscalMonth.ToString();
        }
    }
}

Using Function GetFiscalPeriod
Using above code-behind and calling function. Function calculates the fiscal month and quarter and returns a string value based on the passed DateTime value. For additional information, see U-SQL Programmability Guide: User-Defined Function.

@result = 
    SELECT 
        ReferenceGuide_Examples.MyClass.GetFiscalPeriod(DateTime.Now) AS dd
    FROM 
        (VALUES 
        (1)
        ) AS T(dummyTable);

OUTPUT @result
TO "/Output/ReferenceGuide/DDL/Functions/GetFiscalPeriod.txt"
USING Outputters.Tsv();

See Also