Manage Azure Data Lake Analytics using Azure PowerShell

Learn how to manage Azure Data Lake Analytics accounts, data sources, jobs, and catalog items using Azure PowerShell.


When creating a Data Lake Analytics account, you need to know:

  • Subscription ID: The Azure subscription ID under which your Data Lake Analytics account resides.
  • Resource group: The name of the Azure resource group that contains your Data Lake Analytics account.
  • Data Lake Analytics account name: The account name must only contain lowercase letters and numbers.
  • Default Data Lake Store account: Each Data Lake Analytics account has a default Data Lake Store account. These accounts must be in the same location.
  • Location: The location of your Data Lake Analytics account, such as "East US 2" or other supported locations. Supported locations can be seen on our pricing page.

The PowerShell snippets in this tutorial use these variables to store this information

$subId = "<SubscriptionId>"
$rg = "<ResourceGroupName>"
$adla = "<DataLakeAnalyticsAccountName>"
$adls = "<DataLakeStoreAccountName>"
$location = "<Location>"

Log in

Log in using a subscription id.

Login-AzureRmAccount -SubscriptionId $subId

Log in using a subscription name.

Login-AzureRmAccount -SubscriptionName $subname 

The Login-AzureRmAccount cmdlet always prompts for credentials. You can avoid being prompted by using the following cmdlets:

# Save login session information
Save-AzureRmProfile -Path D:\profile.json  

# Load login session information
Select-AzureRmProfile -Path D:\profile.json 

Managing accounts

Create a Data Lake Analytics account

If you don't already have a resource group to use, create one.

New-AzureRmResourceGroup -Name  $rg -Location $location

Every Data Lake Analytics account requires a default Data Lake Store account that it uses for storing logs. You can reuse an existing account or create an account.

New-AdlStore -ResourceGroupName $rg -Name $adls -Location $location

Once a Resource Group and Data Lake Store account is available, create a Data Lake Analytics account.

New-AdlAnalyticsAccount -ResourceGroupName $rg -Name $adla -Location $location -DefaultDataLake $adls

Get information about an account

Get details about an account.

Get-AdlAnalyticsAccount -Name $adla

Check the existence of a specific Data Lake Analytics account. The cmdlet returns either $true or $false.

Test-AdlAnalyticsAccount -Name $adla

Check the existence of a specific Data Lake Store account. The cmdlet returns either $true or $false.

Test-AdlStoreAccount -Name $adls

Listing accounts

List Data Lake Analytics accounts within the current subscription.


List Data Lake Analytics accounts within a specific resource group.

Get-AdlAnalyticsAccount -ResourceGroupName $rg

Managing firewall rules

List firewall rules.

Get-AdlAnalyticsFirewallRule -Account $adla

Add a firewall rule.

$ruleName = "Allow access from on-prem server"
$startIpAddress = "<start IP address>"
$endIpAddress = "<end IP address>"

Add-AdlAnalyticsFirewallRule -Account $adla -Name $ruleName -StartIpAddress $startIpAddress -EndIpAddress $endIpAddress

Change a firewall rule.

Set-AdlAnalyticsFirewallRule -Account $adla -Name $ruleName -StartIpAddress $startIpAddress -EndIpAddress $endIpAddress

Remove a firewall rule.

Remove-AdlAnalyticsFirewallRule -Account $adla -Name $ruleName

Allow Azure IP addresses.

Set-AdlAnalyticsAccount -Name $adla -AllowAzureIpState Enabled
Set-AdlAnalyticsAccount -Name $adla -FirewallState Enabled
Set-AdlAnalyticsAccount -Name $adla -FirewallState Disabled

Managing data sources

Azure Data Lake Analytics currently supports the following data sources:

When you create an Analytics account, you must designate a Data Lake Store account to be the default data source. The default Data Lake Store account is used to store job metadata and job audit logs. After you have created a Data Lake Analytics account, you can add additional Data Lake Store accounts and/or Storage accounts.

Find the default Data Lake Store account

$adla_acct = Get-AdlAnalyticsAccount -Name $adla
$dataLakeStoreName = $adla_acct.DefaultDataLakeAccount

You can find the default Data Lake Store account by filtering the list of datasources by the IsDefault property:

Get-AdlAnalyticsDataSource -Account $adla  | ? { $_.IsDefault } 

Add a data source

# Add an additional Storage (Blob) account.
$AzureStorageAccountName = "<AzureStorageAccountName>"
$AzureStorageAccountKey = "<AzureStorageAccountKey>"
Add-AdlAnalyticsDataSource -Account $adla -Blob $AzureStorageAccountName -AccessKey $AzureStorageAccountKey

# Add an additional Data Lake Store account.
$AzureDataLakeStoreName = "<AzureDataLakeStoreAccountName"
Add-AdlAnalyticsDataSource -Account $adla -DataLakeStore $AzureDataLakeStoreName 

List data sources

# List all the data sources
Get-AdlAnalyticsDataSource -Name $adla

# List attached Data Lake Store accounts
Get-AdlAnalyticsDataSource -Name $adla | where -Property Type -EQ "DataLakeStore"

# List attached Storage accounts
Get-AdlAnalyticsDataSource -Name $adla | where -Property Type -EQ "Blob"

Submit U-SQL jobs

Submit a string as a U-SQL script

$script = @"
@a  = 
            ("Contoso", 1500.0),
            ("Woodgrove", 2700.0)
        ) AS D( customer, amount );
    TO "/data.csv"
    USING Outputters.Csv();

$scriptpath = "d:\test.usql"
$script | Out-File $scriptpath 

Submit-AdlJob -AccountName $adla -Script $script -Name "Demo"

Submit a file as a U-SQL script

$scriptpath = "d:\test.usql"
$script | Out-File $scriptpath 
Submit-AdlJob -AccountName $adla –ScriptPath $scriptpath -Name "Demo"

List jobs in an account

List all the jobs in the account.

The output includes the currently running jobs and those jobs that have recently completed.

Get-AdlJob -Account $adla

List the top N jobs

By default the list of jobs is sorted on submit time. So the most recently submitted jobs appear first. By default, The ADLA account remembers jobs for 180 days, but the Get-AdlJob cmdlet by default returns only the first 500. Use -Top parameter to list a specific number of jobs.

$jobs = Get-AdlJob -Account $adla -Top 10

List jobs based on the value of job property

Using the -State parameter. You can combine any of these values:

  • Accepted
  • Compiling
  • Ended
  • New
  • Paused
  • Queued
  • Running
  • Scheduling
  • Start
# List the running jobs
Get-AdlJob -Account $adla -State Running

# List the jobs that have completed
Get-AdlJob -Account $adla -State Ended

# List the jobs that have not started yet
Get-AdlJob -Account $adla -State Accepted,Compiling,New,Paused,Scheduling,Start

Use the -Result parameter to detect whether ended jobs completed successfully. It has these values:

  • Cancelled
  • Failed
  • None
  • Succeeded
# List Successful jobs.
Get-AdlJob -Account $adla -State Ended -Result Succeeded

# List Failed jobs.
Get-AdlJob -Account $adla -State Ended -Result Failed

The -Submitter parameter helps you identify who submitted a job.

Get-AdlJob -Account $adla -Submitter ""

The -SubmittedAfter is useful in filtering to a time range.

# List  jobs submitted in the last day.
$d = [DateTime]::Now.AddDays(-1)
Get-AdlJob -Account $adla -SubmittedAfter $d

# List  jobs submitted in the last seven day.
$d = [DateTime]::Now.AddDays(-7)
Get-AdlJob -Account $adla -SubmittedAfter $d

Analyzing job history

Using Azure PowerShell to analyze the history of jobs that have run in Data Lake analytics is a powerful technique. You can use it to gain insights into usage and cost. You can learn more by looking at the Job History Analysis sample repo

Get information about pipelines and recurrences

Use the Get-AdlJobPipeline cmdlet to see the pipeline information previously submitted jobs.

$pipelines = Get-AdlJobPipeline -Account $adla
$pipeline = Get-AdlJobPipeline -Account $adla -PipelineId "<pipeline ID>"

Use the Get-AdlJobRecurrence cmdlet to see the recurrence information for previously submitted jobs.

$recurrences = Get-AdlJobRecurrence -Account $adla

$recurrence = Get-AdlJobRecurrence -Account $adla -RecurrenceId "<recurrence ID>"

Get information about a job

Get job status

Get the status of a specific job.

Get-AdlJob -AccountName $adla -JobId $job.JobId

Examine the job outputs

After the job has ended, check if the output file exists by listing the files in a folder.

Get-AdlStoreChildItem -Account $adls -Path "/"

Manage running jobs

Cancel a job

Stop-AdlJob -Account $adls -JobID $jobID

Wait for a job to finish

Instead of repeating Get-AdlAnalyticsJob until a job finishes, you can use the Wait-AdlJob cmdlet to wait for the job to end.

Wait-AdlJob -Account $adla -JobId $job.JobId

Manage compute policies

List existing compute policies

The Get-AdlAnalyticsComputePolicy cmdlet retrieves info about compute policies for a Data Lake Analytics account.

$policies = Get-AdlAnalyticsComputePolicy -Account $adla

Create a compute policy

The New-AdlAnalyticsComputePolicy cmdlet creates a new compute policy for a Data Lake Analytics account. This example sets the maximum AUs available to the specified user to 50, and the minimum job priority to 250.

$userObjectId = (Get-AzureRmAdUser -SearchString "").Id

New-AdlAnalyticsComputePolicy -Account $adla -Name "GaryMcDaniel" -ObjectId $objectId -ObjectType User -MaxDegreeOfParallelismPerJob 50 -MinPriorityPerJob 250

Check for the existence of a file.

Test-AdlStoreItem -Account $adls -Path "/data.csv"

Uploading and downloading

Upload a file.

Import-AdlStoreItem -AccountName $adls -Path "c:\data.tsv" -Destination "/data_copy.csv" 

Upload an entire folder recursively.

Import-AdlStoreItem -AccountName $adls -Path "c:\myData\" -Destination "/myData/" -Recurse

Download a file.

Export-AdlStoreItem -AccountName $adls -Path "/data.csv" -Destination "c:\data.csv"

Download an entire folder recursively.

Export-AdlStoreItem -AccountName $adls -Path "/" -Destination "c:\myData\" -Recurse


If the upload or download process is interrupted, you can attempt to resume the process by running the cmdlet again with the -Resume flag.

Manage catalog items

The U-SQL catalog is used to structure data and code so they can be shared by U-SQL scripts. The catalog enables the highest performance possible with data in Azure Data Lake. For more information, see Use U-SQL catalog.

List items in the U-SQL catalog

# List U-SQL databases
Get-AdlCatalogItem -Account $adla -ItemType Database 

# List tables within a database
Get-AdlCatalogItem -Account $adla -ItemType Table -Path "database"

# List tables within a schema.
Get-AdlCatalogItem -Account $adla -ItemType Table -Path "database.schema"

List all the assemblies in all the databases in an ADLA Account.

$dbs = Get-AdlCatalogItem -Account $adla -ItemType Database

foreach ($db in $dbs)
    $asms = Get-AdlCatalogItem -Account $adla -ItemType Assembly -Path $db.Name

    foreach ($asm in $asms)
        $asmname = "[" + $db.Name + "].[" + $asm.Name + "]"
        Write-Host $asmname

Get details about a catalog item

# Get details of a table
Get-AdlCatalogItem  -Account $adla -ItemType Table -Path "master.dbo.mytable"

# Test existence of a U-SQL database.
Test-AdlCatalogItem  -Account $adla -ItemType Database -Path "master"

Create credentials in a catalog

Within a U-SQL database, create a credential object for a database hosted in Azure. Currently, U-SQL credentials are the only type of catalog item that you can create through PowerShell.

$dbName = "master"
$credentialName = "ContosoDbCreds"
$dbUri = ""

New-AdlCatalogCredential -AccountName $adla `
          -DatabaseName $db `
          -CredentialName $credentialName `
          -Credential (Get-Credential) `
          -Uri $dbUri

Get basic information about an ADLA account

Given an account name, the following code looks up basic information about the account

$adla_acct = Get-AdlAnalyticsAccount -Name "saveenrdemoadla"
$adla_name = $adla_acct.Name
$adla_subid = $adla_acct.Id.Split("/")[2]
$adla_sub = Get-AzureRmSubscription -SubscriptionId $adla_subid
$adla_subname = $adla_sub.Name
$adla_defadls_datasource = Get-AdlAnalyticsDataSource -Account $adla_name  | ? { $_.IsDefault } 
$adla_defadlsname = $adla_defadls_datasource.Name

Write-Host "ADLA Account Name" $adla_name
Write-Host "Subscription Id" $adla_subid
Write-Host "Subscription Name" $adla_subname
Write-Host "Defautl ADLS Store" $adla_defadlsname

Write-Host '$subname' " = ""$adla_subname"" "
Write-Host '$subid' " = ""$adla_subid"" "
Write-Host '$adla' " = ""$adla_name"" "
Write-Host '$adls' " = ""$adla_defadlsname"" "

Working with Azure

Get details of AzureRm errors

Resolve-AzureRmError -Last

Verify if you are running as an administrator

function Test-Administrator  
    $user = [Security.Principal.WindowsIdentity]::GetCurrent();
    $p = New-Object Security.Principal.WindowsPrincipal $user

Find a TenantID

From a subscription name:

function Get-TenantIdFromSubcriptionName( [string] $subname )
    $sub = (Get-AzureRmSubscription -SubscriptionName $subname)

Get-TenantIdFromSubcriptionName "ADLTrainingMS"

From a subscription id:

function Get-TenantIdFromSubcriptionId( [string] $subid )
    $sub = (Get-AzureRmSubscription -SubscriptionId $subid)

$subid = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
Get-TenantIdFromSubcriptionId $subid

From a domain address such as ""

function Get-TenantIdFromDomain( $domain )
    $url = "" + $domain + "/.well-known/openid-configuration"
    return (Invoke-WebRequest $url|ConvertFrom-Json).token_endpoint.Split('/')[3]

$domain = ""
Get-TenantIdFromDomain $domain

List all your subscriptions and tenant ids

$subs = Get-AzureRmSubscription
foreach ($sub in $subs)
    Write-Host $sub.Name "("  $sub.Id ")"
    Write-Host "`tTenant Id" $sub.TenantId

Create a Data Lake Analytics account using a template

You can also use an Azure Resource Group template using the following PowerShell script:

$subId = "<Your Azure Subscription ID>"

$rg = "<New Azure Resource Group Name>"
$location = "<Location (such as East US 2)>"
$adls = "<New Data Lake Store Account Name>"
$adla = "<New Data Lake Analytics Account Name>"

$deploymentName = "MyDataLakeAnalyticsDeployment"
$armTemplateFile = "<LocalFolderPath>\azuredeploy.json"  # update the JSON template path 

# Log in to Azure
Login-AzureRmAccount -SubscriptionId $subId

# Create the resource group
New-AzureRmResourceGroup -Name $rg -Location $location

# Create the Data Lake Analytics account with the default Data Lake Store account.
$parameters = @{"adlAnalyticsName"=$adla; "adlStoreName"=$adls}
New-AzureRmResourceGroupDeployment -Name $deploymentName -ResourceGroupName $rg -TemplateFile $armTemplateFile -TemplateParameterObject $parameters 

For more information, see Deploy an application with Azure Resource Manager template and Authoring Azure Resource Manager templates.

Example template

Save the following text as a .json file, and then use the preceding PowerShell script to use the template.

  "$schema": "",
  "contentVersion": "",
  "parameters": {
    "adlAnalyticsName": {
      "type": "string",
      "metadata": {
        "description": "The name of the Data Lake Analytics account to create."
    "adlStoreName": {
      "type": "string",
      "metadata": {
        "description": "The name of the Data Lake Store account to create."
  "resources": [
      "name": "[parameters('adlStoreName')]",
      "type": "Microsoft.DataLakeStore/accounts",
      "location": "East US 2",
      "apiVersion": "2015-10-01-preview",
      "dependsOn": [ ],
      "tags": { }
      "name": "[parameters('adlAnalyticsName')]",
      "type": "Microsoft.DataLakeAnalytics/accounts",
      "location": "East US 2",
      "apiVersion": "2015-10-01-preview",
      "dependsOn": [ "[concat('Microsoft.DataLakeStore/accounts/',parameters('adlStoreName'))]" ],
      "tags": { },
      "properties": {
        "defaultDataLakeStoreAccount": "[parameters('adlStoreName')]",
        "dataLakeStoreAccounts": [
          { "name": "[parameters('adlStoreName')]" }
  "outputs": {
    "adlAnalyticsAccount": {
      "type": "object",
      "value": "[reference(resourceId('Microsoft.DataLakeAnalytics/accounts',parameters('adlAnalyticsName')))]"
    "adlStoreAccount": {
      "type": "object",
      "value": "[reference(resourceId('Microsoft.DataLakeStore/accounts',parameters('adlStoreName')))]"

Next steps