{"id":83781,"date":"2019-04-09T14:55:03","date_gmt":"2019-04-09T14:55:03","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=83781"},"modified":"2022-04-24T15:41:54","modified_gmt":"2022-04-24T15:41:54","slug":"processing-data-using-azure-data-lake-azure-data-analytics-and-microsofts-big-data-language-u-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/cloud\/azure\/processing-data-using-azure-data-lake-azure-data-analytics-and-microsofts-big-data-language-u-sql\/","title":{"rendered":"Processing Data Using Azure Data Lake, Azure Data Analytics, and Microsoft\u2019s Big Data Language U-SQL"},"content":{"rendered":"

Azure Data Lake Store is an extensive repository in Azure Cloud which can be thought of as a store of varied forms of data such as structured, unstructured, and semi-structured data. There are different ways this data can be loaded to the data store. You can use Azure Data Factory, Azure UI, using languages such as C# or Java SDKs, etc. Once the data is uploaded to Data Lake, you can use U-SQL scripts to process that data.<\/p>\n

When working on huge datasets, you might need more processing power and space. Azure Data lake provides an easy and simplified approach to improve development. The Azure Data Lake efficiently manages the data in HDFS (Hadoop Distributed File System). As you might be aware, HDFS brings in a lot of other benefits such as replication, scalability, and durability. This makes Azure Data Lake Store a very beneficial option when you have a huge amount of data being generated in your organization.<\/p>\n

I\u2019ll first explain what U-SQL is and how you can use this powerful big data query language to process the data.<\/p>\n

U-SQL<\/h2>\n

The data being stored needs to be processed and analyzed to understand the current and future statistics by various departments within the organization. One such solution is U-SQL, Microsoft\u2019s big data query language which unifies the benefits of T-SQL and C#. The use of C# types makes it even more powerful and easy to write. This allows the developer to conceptualize the way data will be processed right at the time of writing the query. You won\u2019t need expertise in T-SQL or C# to write these queries, just the fundamental understanding of both these languages should be enough to move forward.<\/p>\n

You can visualize how the ETL process works and, after you have the picture in mind, you will be able to write the U-SQL scripts easily. U-SQL helps you to extract and transform data in the required form. You can write the scripts to perform these operations and get the results\/output back the way you want. U-SQL supports the extraction of values from various types of files such as txt, csv, etc., by using the concept of extractors. You can write your own extractors depending on the type of file you are using. Just like the way extractors are used to extract the data, there are outputters to output the data. The built-in extractor for tab separated files is Extractors.Tsv<\/em> and for comma separated file it is Extractors.Csv<\/em>. Similar to this, you can use Outputters.Tsv<\/em> and Outputters.Csv<\/em> depending on the format you want your output to have.<\/p>\n

There are two ways to run the U-SQL scripts, the first is to execute the U-SQL locally and the second is U-SQL Cloud execution. When running locally, the data read and written by the scripts will be present on your local computer. On the other hand, if you are using Cloud execution, the data and script will be executed on the Azure Cloud which means you are using the Azure resources and thus paying for compute and storage resources. You may want to choose the local execution over cloud execution, especially during development, as it doesn\u2019t cost you anything. In this article, I am going to demonstrate both the local execution path and Azure Data Lake execution. The first section of the article will show how to use Visual Studio to write and execute U-SQL scripts, and in the later section, you will see how to run the same U-SQL job using Azure Data Analytics and Azure Data Lake.<\/p>\n

Creating and Executing the U-SQL Script Using Visual Studio<\/h2>\n

The first thing you should do is set up your environment, making sure that the proper workload is in place. Run the Visual Studio Installer. Navigate to the Workloads<\/em> tab, Data<\/em> storage<\/em> and<\/em> processing<\/em> section. Select Azure Data Lake and Stream Analytics Tools<\/em>. If this has not been installed, install it now.<\/p>\n

\"C:\\Users\\spande\\AppData\\Local\\Microsoft\\Windows\\INetCache\\Content.Word\\AZ1E.JPG\"<\/p>\n

After you have installed the Azure Data lake and Stream Analytics Tools<\/em>, workload you can create a new project in Visual Studio by selecting the U-SQL<\/em> option from the Azure Data Lake<\/em> section.<\/p>\n

<\/p>\n

You might have noticed that there are various project options available here that provide you the flexibility to create unit test projects or class library projects. The class library projects will be used when you want to create your own USQL objects in C#, like custom outputters or extracters. For the scope of this article, select the U-SQL Project and start writing your first U-SQL script.<\/p>\n

Before moving ahead and getting your hands dirty writing the U-SQL script, first, download the sample csv file<\/a>. This is how the csv file looks:<\/p>\n

<\/p>\n

To feed the data to your script, you can add a physical path to your SQL script, but this might not be a good idea if you want to deploy this script over to the Azure Cloud later. Because any paths that exist in your local file system won\u2019t exist in the Azure Data Lake account, this would eventually cause your script to fail. So instead of a physical path, you may want to use the relative paths which will help to run the paths locally as well as in Azure Data Lake. While running the script locally, the data file needs to be copied to an Azure Data Lake Catalogue which you can find by navigating to the Azure Data Lake Toolbar and clicking the options\/settings from the menu.<\/p>\n

<\/p>\n

The file needs to be copied to this location motioned in Data root folder path highlighted below.<\/p>\n

\"C:\\Users\\spande\\AppData\\Local\\Microsoft\\Windows\\INetCache\\Content.Word\\AZ4E.JPG\"<\/p>\n

After you navigate to USQLDataRoot<\/em> folder, create a folder called InputFiles<\/em> and copy the EmployeeInput.csv<\/em> file there. It can then be referenced by the U-SQL script.<\/p>\n

Another thing that you might notice here is that your U-SQL file comes with a C# code-behind file Script.usql.cs<\/em> which can be used to add custom functions that can be used in your scripts.<\/p>\n

The next step is to rename the Script.usql<\/em> file to TestUsql.usql<\/em>. Here is the code for the script:<\/p>\n

\/\/Extract values from input \r\n@employeedetails = EXTRACT EmployeeID int,\r\n                      EmployeeName string,\r\n                      State string,\r\n                      Salary decimal,\r\n                      JoiningYear int,\r\n                      Title string\r\nFROM \"\/InputFiles\/EmployeeInput.csv\"\r\n\/\/Use in-built CSV extractor to read the CSV file and skip the first row\r\nUSING Extractors.Csv(skipFirstNRows:1);\r\n\/\/Query for calculating average on Salary field\r\n@average = SELECT State,\r\nAVG(Salary) AS AverageSalary  \r\nFROM @employeedetails\r\nGROUP BY State;\r\n\/\/specify output file and write the headers to the output file\r\nOUTPUT @average TO \"\/OutputFiles\/AverageSalaryResults.csv\"\r\nUSING Outputters.Csv(outputHeader:true);<\/pre>\n
Understanding the U-SQL Script<\/h2>\n
The first step is to extract the data from csv file and then perform the required transformations on the data. After these transformations are complete, the results are finally written to the output file. For extraction of the data, U-SQL provides various extractors depending on the file type. Here, the input file is in csv format, so a csv extractor is required. To extract the data, supporting C# types are used depending on the type of data elements.<\/p>\n
For collecting the extracted values, the script uses a row-set variable @employeedetails<\/code>. You might notice that the naming convention for variable declaration is similar to T-SQL naming convention with the @<\/code> sign.<\/p>\n
A row-set variable @employeedetails<\/code> will store the extracted values<\/p>\n
@employeedetails = EXTRACT EmployeeID int,\r\n                      EmployeeName string,\r\n                      State string,\r\n                      Salary decimal,\r\n                      JoiningYear int,\r\n                      Title string\r\nFROM \"\/InputFiles\/EmployeeInput.csv\"<\/pre>\n
A CSV extractor is used, and to help the extractor to detect that row headers are present on the first row of the file you have to set skipFirstNRows<\/code> to 1.<\/p>\n
USING Extractors.Csv(skipFirstNRows:1); <\/pre>\n
For transformation, the SQL average function calculates the state-wide average. The results will then be collected into the @average<\/code> variable.<\/p>\n
@average = SELECT State,\r\nAVG(Salary) AS AverageSalary  \r\nFROM @employeedetails\r\nGROUP BY State;<\/pre>\n
The contents are written out to AverageSalaryResults.csv<\/em> that will be created in the OutputFiles<\/em> folder<\/p>\n
OUTPUT @average TO \"\/OutputFiles\/AverageSalaryResults.csv\"<\/pre>\n
Outputters.Csv<\/code> is specified with the formatting using the Outputter command:<\/p>\n
USING Outputters.Csv(outputHeader:true);<\/pre>\n
Running the Script<\/h2>\n
Normally, for running any solution, you use the Start button in the Visual Studio toolbar, but, in this case, you will use a U-SQL toolbar to run the U-SQL script. By doing this, you will see the step-by-step execution of the script.<\/p>\n
\"C:\\Users\\suhas\\Downloads\\image.png\"<\/p>\n
After the script runs, you will see the compile summary shown below.<\/p>\n
<\/p>\n
By running with the Submit<\/em> button, you will see detailed information about the job. For instance, the compile view will show you how the data has been processed from step1 to step n. Also, it shows the compile time details and easy to navigate to script options.<\/p>\n
<\/p>\n
As mentioned, the output data should be written to the file \/OutputFiles\/AverageSalaryResults.csv<\/em>. You can now go and check the same root directory where you created the InputFiles<\/em> folder. You can also access the file using the local run results window just by right-clicking the output result path and selecting the Preview<\/em> option to view the file.<\/p>\n
As you can see, the resulting file, AverageSalaryResults.csv<\/em>, contains the state-wide average salary as shown in the image below.<\/p>\n
<\/p>\n
Create and Run U-SQL Scripts Using Azure Data Lake Analytics Account<\/h2>\n
Running the scripts locally is often a good option since Visual Studio doesn\u2019t cost you anything for using the resources. In a real-life scenario, there might be many situations where you will want to use the Azure Portal to execute the U-SQL script. In the next section, I will show you how to execute a U-SQL script with an Azure Cloud account.<\/p>\n
You will load your input file into Azure Data Lake Storage and then run a U-SQL script job using an Azure Data Lake Analytics account.<\/p>\n
First login to Azure Portal<\/a> and select the All services from the left options panel. Now choose the Analytics<\/em> options. Select Data Lake Storage Gen1<\/em> and create a new Data Lake Storage<\/em> account.<\/p>\n
<\/p>\n
Configure your new Data Lake Storage giving it a Name<\/em>, your Subscription<\/em>, and a Resource<\/em> Group<\/em>. Note that the name must be unique across Azure. You might want to create a new Resource Group so that cleaning up the resources is easy when you are done experimenting.<\/p>\n
<\/p>\n
Once the Data Lake Storage has been deployed, you\u2019ll see it in the list.<\/p>\n
<\/p>\n
Now that the storage is created, you will learn how to create an Azure Data Lake Analytics account. From the Analytics<\/em> menu, select the Data Lake Analytics<\/em> option.<\/p>\n
<\/p>\n
Now add a new Data<\/em> Analytics<\/em> account<\/em> and select the Data<\/em> Lake<\/em> Storage<\/em> that you just created.<\/p>\n
\"C:\\Users\\suhas\\Downloads\\image<\/p>\n
Once completed, you will notice your new account has been added to the Data Analytics section.<\/p>\n
<\/p>\n
 <\/p>\n
Uploading the Input File to Azure Data Lake Storage<\/h2>\n
Navigate back to the Azure Data Lake Storage that you just created and click on the Data<\/em> explorer<\/em> option. You will notice that there are two default folders (catalog<\/em> and system<\/em>) already present in the storage account. Add a new folder named InputFiles<\/em> to upload the file so that the experiment files are separated from system files.<\/p>\n
<\/strong><\/p>\n
After creating the new folder, click Upload<\/em> and select the file and click Add selected files<\/em>.<\/p>\n
<\/p>\n
The next step is to create your first job with the Data Analytics account.<\/p>\n
Adding a New Job to Azure Data Analytics<\/h2>\n
After you navigate to the Azure Data Analytics account and create a new job, be sure to name your new job so that you can keep track of it in the future.<\/p>\n
<\/p>\n
Just for demonstration purposes, copy the same U-SQL script that you used in the previous section of the article. Make sure you use the correct path for the input and output files.<\/p>\n
<\/p>\n
Once everything is ready, click on Submit<\/em>. You will see the status of the job on the left-hand side section of the screen. This section contains all the details that you might want to know such as estimated cost, efficiency and the timestamp for all the steps involved in the process. It will take a few seconds to process the results. After every step has successfully executed, you will start seeing the Job graph. Yay! The job has run successfully, and you can see that everything has turned green.<\/p>\n
<\/em><\/p>\n
The job graph will show you the details of the job, where the input file is transformed, and that the results are written over to AverageSalaryResults.csv file. The easiest way to access the file is by clicking the AverageSalaryResults.csv in the graph.<\/p>\n
You can now download the file and edit it as per your business requirements. There are various options that are available such as providing access to people who need to download the file which are very helpful when you need to share your results within your organization.<\/p>\n
<\/p>\n
Whenever you need to access the output file in the future, you can simply navigate to the Azure Data Lake Storage and then access the file in OutputFiles folder that was created through the U-SQL job.<\/p>\n
\"C:\\Users\\suhas\\Downloads\\unnamed.png\"<\/p>\n
If you created a new Resource Group when you set everything up, you can delete the resources after you are done experimenting. For this, you can navigate to the Resource Group you have created and click the Delete resource group<\/em> option. Make sure you have a copy of the input and output files downloaded before you delete the resource group.<\/p>\n
\"C:\\Users\\suhas\\Downloads\\image<\/p>\n
Summary<\/h2>\n
In this article, I showed how you can conveniently write U-SQL scripts using SQL and C# language types and constructs. ETL is made easy with these new approaches of writing the U-SQL script and running it locally or on the Azure Cloud. You have seen how you can build your script locally using the local Visual Studio setup which is free of cost as it does not require any cloud resources. On the other hand, you can write and run the U-SQL script on the Azure Data Analytics account which works based on a pay-per-use basis, depending on resources used while running the job and for using the storage as well.<\/p>\n
References<\/h2>\n
https:\/\/docs.microsoft.com\/en-us\/azure\/data-lake-analytics\/data-lake-analytics-u-sql-get-started<\/a><\/p>\n
https:\/\/azure.microsoft.com\/en-us\/solutions\/data-lake\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Data analytics has become one of the powerful domains in the world of data science. An enormous amount of data is being generated by each organization in every sector. Computer science has found solutions to store and process this data in a smart way through a distributed file system. One such example is Azure Data Lake. It uses the Hadoop Distributed File System, and to perform analytics on this data, Azure Data Lake storage is integrated with Azure Data Analytics Service and HDInsight. In this article, Suhas Pande will explain how to store data using Azure Data Lake and how to perform data analysis on it using U-SQL, a big data SQL and C# language. …<\/p>\n","protected":false},"author":320462,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[137091],"tags":[],"coauthors":[60465],"class_list":["post-83781","post","type-post","status-publish","format-standard","hentry","category-azure"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83781","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/320462"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=83781"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83781\/revisions"}],"predecessor-version":[{"id":83819,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83781\/revisions\/83819"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=83781"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=83781"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=83781"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=83781"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}