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

Introduction

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.

https://www.visualstudio.com/vs/community/

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'.

Description:

Could not load file or assembly 'ScopeEngineManaged.dll' or one of its dependencies. The specified module could not be found.

Resolution:

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)”.

http://www.microsoft.com/downloads/details.aspx?familyid=cdd5866a-43b3-409e-8fd7-e37b0a9ccc63

Hands-on Practice for Testing

Finding the Root directory for Input file

Tools à Options

In my case the Data Root directory is:

C:\Users\Joydeep\AppData\Local\USQLDataRoot

In my case the Output Directory is:

C:\Users\Joydeep\AppData\Local\USQLDataRoot\Output

Now download a TSV file from the given link

https://raw.githubusercontent.com/Azure/usql/master/Examples/Samples/Data/SearchLog.tsv

*** 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