Script Azure Data Lake Analytics assembly deployments to U-SQL Catalog

Registering your custom assemblies using Visual Studio Data Lake Tools is easy, just right click your U-SQL C# Class Library project, click “Register Assembly”, fill in the details and your assembly gets published and created in your U-SQL Catalog.

But what if you want to script this process to automate your deployments? That is not documented yet and as I follow the principle that we should be able to rebuild the whole environment with a press on the button, I found out how to do this.

Step 0 – Prerequisites
Install latest version of Azure PowerShell. The PowerShell script below won’t work with an older version. http://aka.ms/webpi-azps

Step 1 – Build your dll from Visual Studio.
We need the dll of your custom assembly, create it by right clicking your U-SQL Class Library project and choose to Build. You can see where the dll has been created on disk in the output window.

Step 2 – Create a U-SQL job file that will create the assembly.
Modify the U-SQL script below and store it in a file somewhere locally on disk. Name it createAssembly.usql

USE DATABASE yourUsqlCatalog;

CREATE ASSEMBLY IF NOT EXISTS yourCSharp
FROM @”/Assemblies/yourCSharp.dll”;

Step 3 – Upload the assembly to your ADLS and execute the U-SQL job using PowerShell.
Enter values for the variables in the PowerShell script below and execute it.

#Variables; modify 
$dataLakeStoreName = "yourAdlsAccount"
$dataLakeAnalyticsName = "yourAdlaAccount"
$assemblyLocalPath = "c:\yourCSharp.dll" #step 1
$usqlScriptLocalPath = "c:\createAssembly.usql" #step 2
$assemblyAdlsPath = "/Assemblies/yourCSharp.dll" #Assemblies folder will be created if not exists
$azureSubscriptionId = "00000000-0000-0000-0000-000000000000"

#Login (login pop up appears)
Login-AzureRmAccount

#Connect to the Azure Subscription in which your ADLA Catalog exists 
Set-AzureRMContext -SubscriptionId $azureSubscriptionId 

#Import dll to ADLS
Import-AzureRmDataLakeStoreItem -AccountName $dataLakeStoreName -Path $assemblyLocalPath -Destination $assemblyAdlsPath

#Submit new job to ADLA (createAssembly.usql)
$job = Submit-AzureRmDataLakeAnalyticsJob -Name "Create Assembly" -AccountName $dataLakeAnalyticsName –ScriptPath $usqlScriptLocalPath -DegreeOfParallelism 1

 While (($t = Get-AzureRmDataLakeAnalyticsJob -AccountName $dataLakeAnalyticsName -JobId $job.JobId).State -ne "Ended"){
         Write-Host "Job status: "$t.State"..."
         Start-Sleep -seconds 10
 }

 Get-AzureRmDataLakeAnalyticsJob -AccountName $dataLakeAnalyticsName -JobId $job.JobId

Step 4 – Validate.
Validate if your dll is uploaded to a folder in your ADLS called “Assemblies”, and next, if your assembly is created in your U-SQL Catalog with Visual Studio Server Explorer (Azure).

Step 5 – Reference your new assembly in your U-SQL scripts.
You can now start using your assembly by referencing it in the first lines of code in your U-SQL script.

USE DATABASE yourUsqlCatalog;

REFERENCE ASSEMBLY yourCSharp;

…