Sunday, April 10, 2016

How to use C# Code behind in U-SQL

The role of C# code behind class file is very similar to having C# code behind in asp.net web forms application. The purpose of the code behind file in a USQL project is to store C# code that can be used in your U-SQL script. Every U-SQL Script in Visual Studio will have a Code-Behind file associated with it. In the case of the Script.usql file w’ve created so far, the code behind looks like this.


Clicking on the code behind file will show the following code.

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

namespace MyUSQLApp
{
}
using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;

namespace MyUSQLApp
{
    public static class Helpers
    {
        public static Double AddDuration(int duration)
        {
            return duration + 1.0;
        }
    }
}


To use the helper method in the script, follow the below code snippet. Please note that we have to use the fully qualified name while referring the function in the script, failing to do that will cause run time error.

@searchlog = 
    EXTRACT UserId          int, 
            Start           DateTime, 
            Region          string, 
            Query           string, 
            Duration        int, 
            Urls            string, 
            ClickedUrls     string
    FROM @"/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();

@output = 
    SELECT 
        Region,
        MyUSQLApp.Helpers.AddDuration( Region ) AS Region2
    FROM @searchlog;

OUTPUT @output
    TO @"/Samples/Output/SearchLog_output.tsv"
    USING Outputters.Tsv();