Sunday, 18 February 2018

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






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


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