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





5 comments:

  1. Azure Data Lake so far with SQL Knowledge Bank


    1. Azure Data Lake Introductory
    http://sqlknowledgebank.blogspot.in/2018/01/azure-data-lake-introductory.html

    2. Azure Data Lake – Running U-SQL Script
    http://sqlknowledgebank.blogspot.in/2018/02/azure-data-lake-running-u-sql-script.html

    3. Azure Data Lake – U-SQL Script Construction
    http://sqlknowledgebank.blogspot.in/2018/02/azure-data-lake-u-sql-script.html

    4. Azure Data Lake – U-SQL – SELECT Transformation Rowsets
    http://sqlknowledgebank.blogspot.in/2018/02/azure-data-lake-u-sql-select.html

    5. Azure Data Lake – U-SQL – Aggregation of Rowset
    http://sqlknowledgebank.blogspot.in/2018/02/azure-data-lake-u-sql-aggregation-of.html

    6. Azure Data Lake – U-SQL – Built-in UDOs
    http://sqlknowledgebank.blogspot.in/2018/02/azure-data-lake-u-sql-built-in-udos.html
    7. Azure Data Lake – U-SQL – A small Hands-on with VS 2017
    http://sqlknowledgebank.blogspot.in/2018/02/azure-data-lake-u-sql-small-hands-on.html


    ReplyDelete
  2. New Parking Software for Condominiums and Apartments:
    WEBPARKINGSOFTWARE.COM has a new product on the internet nowadays that can provide maximum satisfaction to all businessmen and vehicle owners who want to find the perfect parking area for their vehicles in a very convenient way especially while in the city. This powerful parking software is something that vehicle owners and businessmen can use in condominiums, apartments and other business establishments that were designed to accommodate several consumers or clients every day. It’s already available in the official website of the online company that has been stated.
    The software can be used by vehicle owners for free for a limited period of time only. It has been reinvented by the company to show to the world the most systematic and effective way of organizing and managing all parking areas that are available in licensed condominiums and apartments all over the country nowadays. People can easily use it with the use of their smart phones. Therefore, it was designed to make the process of providing a vehicle with an excellent parking area in outdoor places more convenient and much easier to accomplish. This product can help businessmen to experience all benefits that an excellent parking management method can provide.
    The new parking software for condominiums and apartments at WEBPARKINGSOFTWARE.COM will be very useful in the daily business operations of all businessmen who want to make sure that the parking areas in their business establishments will be managed perfectly every day even if their time for their additional tasks and relaxation is very limited. The product comes with a set of innovative features that can make parking management more convenient and full of satisfying results for all businessmen every day. This software can also assist a vehicle owner in securing an excellent parking area for a vehicle in one of the most popular condominiums or apartments in the country.
    The product is equipped with an advanced parking management system that’s highly compatible for all types of building sizes that can be found in the different parts of the country nowadays. It provides professional parking permit to all vehicle owners who will never hesitate to use it regularly. In addition to that, this software has an amazing ability to assign parking spaces for tenants. This software possesses everything that can make parking management more enjoyable and free from stressful moments every day. One of the things that people will surely appreciate in the design of this product is its amazing ability to detect ticket violators.
    The new parking software of WEBPARKINGSOFTWARE.COM will always be an excellent choice for all businessmen and vehicle owners who can’t afford to be victimized by the negative effects of stress and misunderstandings while aiming to have a better understanding of the idea of parking management. The product embodies an internet-based cloud model that makes it far better than any other brand of parking software that can be downloaded on the internet nowadays. One of its best advantages is the fact that it’s compatible for all brands of smart phones and modern electronic gadgets. This app has been created to make parking management a paperless procedure that can be accomplished in just few seconds.

    Parking Management Software
    online parking software
    Cloud based parking solutions

    ReplyDelete
  3. good example , i have one observation if i mention input rootdirectory in tools option , then it is not mendatory in usql script FROM "C:/Users/Joydeep/AppData/Local/USQLDataRoot/SearchLog.tsv" i can write simply
    FROM "SearchLog.tsv" it will pick from root directory if my source file is oter then root directory then i have to mention

    thanks Joydeep inspired me to start U-sql

    ReplyDelete
  4. i am getting the same issue for my Windows 10 32 bit version .please suggest in my case what to do ?

    Could not load file or assembly 'ScopeEngineManaged, Version=10.2.0.0, Culture=neutral, PublicKeyToken=a06e40edc83d4f79' or one of its dependencies. An attempt was made to load a program with an incorrect format.
    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.

    ReplyDelete