Sunday, 18 February 2018

Azure Data Lake – U-SQL – A small Hands-on with VS 2017


In this article we are trying to discuss install Visual Studio 2017 that is integrated with Azure Data Lake and trying to execute a small U-SQL script as testing purpose.

Hope it will be informative and influence you to start your first U-SQL script.


Installing Visual Studio 2017

We are using visual studio 2017 community edition for that. It’s free.

Now when we are going to run the U-SQL Script (if OS is Win 7), probably get an compile error.

Error E_RUNTIME_USER_LOADENGINEASSEMBLY: .Net cannot load runtime assembly 'ScopeEngineManaged.dll'.
Could not load file or assembly 'ScopeEngineManaged.dll' or one of its dependencies. The specified module could not be found.
Ensure the file exists and that all dependencies are available, including ScopeEngine.dll.
This runtime assembly requires a 64-bit process and depends upon the VC14 CRT runtime being installed on the local machine.             TestApplication                E:\u-SQL\TestApplication\TestApplication\Script.usql

To solve this we need to run another patch called “Universal C for Runtime for Windows (64 bit)”.

Hands-on Practice for Testing

Finding the Root directory for Input file

Tools à Options

In my case the Data Root directory is:

In my case the Output Directory is:

Now download a TSV file from the given link

*** Put the file in Root directory (just copy and paste)

Now open the U-SQL Editor and put the U-SQL Script

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "C:/Users/Joydeep/AppData/Local/USQLDataRoot/SearchLog.tsv"
    USING Extractors.Tsv();

OUTPUT @searchlog 
    TO "C:/Users/Joydeep/AppData/Local/USQLDataRoot/output/SearchLog-first-u-sql.csv"
    USING Outputters.Csv();

Note: Input and output directory is marked by yellow marking.

Now run the script by clicking Submit button in U-SQL Editor

Observing Output

Hope you like it.

Posted by: MR. JOYDEEP DAS