ZachStagers

Zach Stagers

A quick look at U-SQL Database Projects

U-SQL Database Projects were released today (see here for the announcement from Microsoft) and it promises easier development and deployment. I’ve blogged in the past about using PowerShell to deploy U-SQL scripts, and I’ve worked with the older “U-SQL Project” project quite a bit, so I wanted to try the new project out as quickly as I could to see what it has to offer.

Once you’ve updated to the latest version of ADL Tools, you’ll be able to select the new project type:

clip_image002

Developing

The project contains a number of database object templates, allowing you to get off the mark quickly with development, and the project allows all of the usual visual studio organization with folders, and intellisense in the code editor.

Object templates available:

clip_image004

Here’s the template of a Procedure Script item:

clip_image006

Source Control

The projects integrates much more nicely with TFS than the older “U-SQL Project” does.

It actually gives you the icons (padlock, check mark, etc..) in the solution explorer, so it actually looks like it’s under source control!

Something that I’d really hoped had been fixed, but hasn’t, is when copying and renaming an existing item, it doesn’t recognize the rename. You have to undo the checkout of the non-existent object (the copy, before being renamed):

clip_image008

Something that has been fixed is that with the older project style, you wouldn’t be able to edit the “Procedure2.usql” item from my previous example until you’d checked it in – now you can!

Deployment

Where you’re deploying to is controlled by the Cloud Explorer in Visual Studio and which Data Lakes you have access to. By right clicking and selecting deploy on the project, you are presented with a simple dialogue box where you select which Data Lake Analytics account, making it nice and easy to deploy to development vs UAT, etc…

clip_image010

The Database Name specified in this textbox is the name of the database your project will be deployed to. Unfortunately, it is just a free form textbox, so be careful of any typos!

Deploying Multiple U-SQL Procedures with PowerShell


If you’d like to deploy multiple U-SQL procedures without having to open each one in Visual Studio and submit the job to Data Lake Analytics manually, here’s a PowerShell script which you can point at a folder location containing your .USQL files to loop through them and submit them for you.

This method relies on the Login-AzureRmAccount command with a service principal, which you can learn more about here.

The Script

$azureAccountName = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$azurePassword = ConvertTo-SecureString "xxxxxxxxxxxxxxxxxxxx/xxxxxxxxxxxxxxx/xxxxxx=" -AsPlainText -Force
$psCred = New-Object System.Management.Automation.PSCredential($azureAccountName, $azurePassword)
$psTenantID = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$adla = "zachstagersadla"
$fileLocation = "C:\SourceControl\USQL.Project\*.usql"

Login-AzureRmAccount -ServicePrincipal -TenantID $psTenantID -Credential $psCred | Out-Null

ForEach ($file in Get-ChildItem -Path $fileLocation)
	{
		$scriptContents = [IO.File]::ReadAllText($file.FullName)
		Submit-AzureRmDataLakeAnalyticsJob `
			-AccountName $adla `
			-Name $file.Name `
			-Script $scriptContents | Out-Null;
		
		Write-Host "`n" $file.Name "submitted."
	} 

Parameter Configuration

Where these various GUID’s can be found within the Azure portal is liable to change, but at time of writing I have provided a path to follow to find each of them.

$azureAccountName – This is the Application Id of your Enterprise Application, which can be found by navigating to Azure Active Directory > Enterprise Applications > All Applications > Selecting your application > Properties > Application Id.

$azurePassword – This is the secret key of your Enterprise Application, and would have been generated during application registration. If you’ve created your application, but have not generated a secret key, you can do so by navigating to: Enterprise Applications > New Application > Application You’re Developing > OK, take me to App Registration > Change the drop down from ‘My Apps’ to ‘All Apps’ (may not be required) > Select your application > Settings > Keys > Fill in the details and click save. Note the important message about the key only being available to copy until before navigating away from the page!

$psTenantID – From within the Azure Portal, go to Azure Active Directory, open the Properties blade, and copy the ‘Directory ID’.

$adla – The name of the data lake analytics resource you’re deploying to.

$fileLocation – The file location on your local machine which contains the USQL scripts you wish to deploy. Note the “\*.usql” on the end in the example, this is a wildcard search for all files ending in .usql.

Permissions

The service principal needs to be given the following permissions to successfully execute against the lake:

· Owner of the Data Lake Analytics resource you’re deploying to. This is configured via the Access Control blade.

· Owner of the Data Lake Store resource associated to the Analytics resource you’re deploying to. This is configured via the Access Control blade.

· Read, Write, and Execute permissions against the sub-folders within the Data Lake Store. Ensure you select ‘This folder and all children’ and ‘An access permission entry and a default permission entry’. This is configured by entering the Data Lake Store, selecting Data Explorer, then Access.