THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Microsoft Data & Analytics consultant and Microsoft Data Platform MVP from the Netherlands

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;

...


 

Published Thursday, September 29, 2016 4:22 PM by jorg

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft Data & Analytics consultant from the Netherlands.
Privacy Statement