This is a continuation on previous post that can be found here. As I stated primary programming language for Azure Data Lake Analytics will be U-SQL. If you like myself, come from SQL background you will find that many U-SQL queries lookn like SQL, however after a second look those of you familar with .NET and LINQ will notice that there are familiar constructs there as well.
There are two ways to run U-SQL scripts:
- You can run U-SQL scripts on your own machine. The data read and written by this script will be on you own machine. You aren’t using Azure resources to do this so there is no additional cost. This method of running U-SQL scripts is called “U-SQL Local Execution”
- You can run U-SQL scripts in Azure in the context of a Data Lake Analytics account. The data read or written by the script will also be in Azure – typically in an Azure Data Lake Store account. You pay for any compute and storage used by the script. This is called “U-SQL Cloud Execution”
If you wish to run scripts locally via your copy of Visual Studio 2015 you can install Data Lake Tools here – https://www.microsoft.com/en-us/download/details.aspx?id=49504 . With Visual Studio 2017 tool is part of Azure Development workload.
In the previous post I shown how to run U-SQL scripts on the cloud , but today we will use Visual Studio 2017 with Data Lake Tools to run U-SQL scripts.
Once you installed the tools you can connect to your Azure Data Lake Analytics account and run U-SQL Script via right clicking and picking Run U-SQL Script from the menu. Running such script will look like this in VS 2017:
You can see stats and timings just like you would in Azure Portal as well right in Visual Studio
When something is wrong with a U-SQL script it will not compile. Here some of the common things that may drive you mad and you should watch out for:
- Invalid case. U-SQL is case sensitive, unlike SQL. So following script will error out as it uses lower case from vs. upper case FROM:
@searchlog = EXTRACT UserId int, Start DateTime, Region string, Query string, Duration int, Urls string, ClickedUrls string from @"/SearchLog.tsv" USING Extractors.Tsv(); OUTPUT @searchlog TO @"/SearchLog_output.tsv" USING Outputters.Tsv();
- Bad path to input or otput file. Check your paths, this is one is self evident, but I spent hours debugging my path\folder issues
- Invalid C# expression due to typos, etc
When you develop U-SQL scripts, you can save time and expense by running the scripts locally on your machine before they are ready to be ran in the cloud. You can connect to your local folder path via Visual Studio Data Lake tools and run queries there:
A local data root folder is a local store for the local compute account. Any folder in the local file system on your local machine can be a local data root folder. It’s the same as the default Azure Data Lake Store account of a Data Lake Analytics account. Switching to a different data root folder is just like switching to a different default store account.
When you run a U-SQL script, a working directory folder is needed to cache compilation results, run logs, and perform other functions. In Azure Data Lake Tools for Visual Studio, the working directory is the U-SQL project’s working directory. t’s located under <U-SQL Project Root Path>/bin/debug. The working directory is cleaned every time a new run is triggered.
Now that you got the basics you can delve into U-SQL coinstructs following the laguage tutorials by Microsoft’s Michael Rys here – https://msdn.microsoft.com/en-us/azure/data-lake-analytics/u-sql/u-sql-language-reference , and Adjeta Sighal here – https://blogs.msdn.microsoft.com/azuredatalake/2018/05/26/get-started-with-u-sql-its-easy/
In my next installment I am hoping to go through typical ADLA analytics job from start to completion.
For more on Azure Data Lake Analytics see – https://channel9.msdn.com/blogs/Cloud-and-Enterprise-Premium/Azure-Data-Lake-Analytics-Deep-Dive, http://eatcodelive.com/2015/11/21/querying-azure-sql-database-from-an-azure-data-lake-analytics-u-sql-script/
Good Luck and Happy Coding!