Writing the data to Azure Data Lake Store - Powershell Scripting

Question

I need to write the data to Azure Data Lake Storage rather than my local D:\ Drive. I am trying to fetch the ADF triggers information via PowerShell and want to load the data to Azure Data Lake Container in a directory rather than in a blob storage.

ADF -> PowerShell -> Azure Data Lake

I want to load the data in Azure Data Lake Directory inside container in YYYY (Folder) -> MM (Folder) -> DD (Folder) -> Data File in .CSV

Here is my code to write the data to local Machine, I need to convert it to load the data to Data Lake Storage. For hiding the username & password I have used a mechanism with Passowrd & AES Encryption File.

Any help and suggestions will be appreciated?

CODE :

# 1- Connect to Azure Account

$username = "[email protected]"
$password = Get-Content D:\Powershell\new\passwords\password.txt | ConvertTo-SecureString -Key (Get-Content D:\Powershell\new\passwords\aes.key)
$credential = New-Object System.Management.Automation.PsCredential($username,$password)


#Connect-AzureRmAccount -Credential $credential | out-null

Connect-AzAccount -Credential $credential | out-null

# 2 - Input Area

$subscriptionName = 'Data Analytics'
$resourceGroupName = 'DataLake-Gen2'
$dataFactoryName = 'dna-production-gen2'


# 3 - (All Triggers Information)


$ErrorActionPreference="SilentlyContinue"
Stop-Transcript | out-null
$ErrorActionPreference = "Continue"
Start-Transcript -path D:\Powershell\new\TriggerInfo.txt -append
Get-AzDataFactoryV2Trigger -ResourceGroupName $resourceGroupName -DataFactoryName $dataFactoryName
Stop-Transcript 

# read the file as a single, multiline string using the -Raw switch

$triggers = Get-Content "D:\Powershell\new\TriggerInfo.txt" -Raw

# split the text in 'trigger' text blocks on the empty line

# loop through these blocks (skip any possible empty textblock)

$triggers = ($triggers -split '(\r?\n){2,}'| Where-Object {$_ -match '\S'}) | ForEach-Object {

    # and parse the data into Hashtables
    $today = Get-Date
    $yesterday = $today.AddDays(-1)

    $data  = $_ -replace ':', '=' | ConvertFrom-StringData

    $splat = @{ 
        ResourceGroupName       = $data.ResourceGroupName
        DataFactoryName         = $data.DataFactoryName
        TriggerName             = $data.TriggerName
        TriggerRunStartedAfter  = $yesterday
        TriggerRunStartedBefore = $today
   }
    
   Get-AzDataFactoryV2TriggerRun @splat 

} | Export-Csv -Path 'D:\Powershell\new\Output.csv' -Encoding UTF8 -NoTypeInformation 

# 4 - To extract the final output from the Output File.

Import-Csv D:\Powershell\new\Output.csv -DeLimiter "," | 
Select-Object 'TriggerRunTimestamp', 'ResourceGroupName','DataFactoryName','TriggerName','TriggerRunId','TriggerType','Status' | 
Export-Csv -Path 'D:\Powershell\new\Finalresult.csv' -Encoding UTF8 -NoTypeInformation -Force

Code tried to upload the file from local system:

$storageAccount = Get-AzStorageAccount -ResourceGroupName "DataLake-Gen2" -AccountName "dna2020gen2"
>> $ctx = $storageAccount.Context
PS C:\Windows\system32> $filesystemName = "dev"
>> $dirname = "triggers/"
>> New-AzDataLakeGen2Item -Context $ctx -FileSystem $filesystemName -Path $dirname -Directory

$localSrcFile =  "D:\Powershell\new\passwords\password.txt"
>> $filesystemName = "dev"
>> $dirname = "triggers/"
>> $destPath = $dirname + (Get-Item $localSrcFile).Name
>> New-AzDataLakeGen2Item -Context $ctx -FileSystem $filesystemName -Path $destPath -Source $localSrcFile -Force

I am able to upload the file but not able to write the command output to datalake.

The command Export-Csv just can write connect to local driver or network driver. So I think we cannot directly write content to azure data lake store. I suggest you use azure data lake gen2 rest API to store csv content directly. — Jim Xu
– Jim Xu, Commented Oct 26, 2020 at 5:11

Jim Xu · Accepted Answer · 2020-10-27 01:50:32Z

1

Regarding the issue, please refer to the following script

$username = "[email protected]"
$password =ConvertTo-SecureString "" -AsPlainText -Force
$credential = New-Object System.Management.Automation.PsCredential($username,$password)


#Connect-AzureRmAccount -Credential $credential | out-null

Connect-AzAccount -Credential $credential
$dataFactoryName=""
$resourceGroupName=""
# get dataFactory triggers
$triggers=Get-AzDataFactoryV2Trigger -DataFactoryName $dataFactoryName  -ResourceGroupName $resourceGroupName
$datas=@()
foreach ($trigger in $triggers) {
    # get the trigger run history
    $today = Get-Date
    $yesterday = $today.AddDays(-1)
     $splat = @{ 
        ResourceGroupName       = $trigger.ResourceGroupName
        DataFactoryName         = $trigger.DataFactoryName
        TriggerName             = $trigger.Name
        TriggerRunStartedAfter  = $yesterday
        TriggerRunStartedBefore = $today
   }
    
   $historys =Get-AzDataFactoryV2TriggerRun @splat
   if($historys -ne $null){
     # create date
     foreach($history in $historys){
        $obj =[PsCustomObject]@{
            'TriggerRunTimestamp '     = $history.TriggerRunTimestamp
            'ResourceGroupName '   =$history.ResourceGroupName
            'DataFactoryName' =$history.DataFactoryName
            'TriggerName '  = $history.TriggerName
            'TriggerRunId'= $history.TriggerRunId
            'TriggerType'=$history.TriggerType
            'Status' =$history.Status

        }
        # add data to an array
        $datas += $obj
     }
   } 
   
  
 }
 #  convert data to csv string
 $contents =(($datas | ConvertTo-Csv -NoTypeInformation) -join [Environment]::NewLine)

 # upload to Azure Data Lake Store Gen2

 #1. Create a sas token
 $accountName="testadls05"
 $fileSystemName="test"
 $filePath="data.csv"
 $account = Get-AzStorageAccount -ResourceGroupName andywin7 -Name $accountName
 $sas= New-AzStorageAccountSASToken -Service Blob  -ResourceType Service,Container,Object `
      -Permission "racwdlup" -StartTime (Get-Date).AddMinutes(-10) `
      -ExpiryTime (Get-Date).AddHours(2) -Context $account.Context
$baseUrl ="https://{0}.dfs.core.windows.net/{1}/{2}{3}" -f $accountName ,  $fileSystemName, $filePath, $sas
#2. Create file
$endpoint =$baseUrl +"&resource=file"

Invoke-RestMethod -Method Put -Uri $endpoint -Headers @{"Content-Length" = 0} -UseBasicParsing

#3 append data
$endpoint =$baseUrl +"&action=append&position=0"
Invoke-RestMethod -Method Patch -Uri $endpoint -Headers @{"Content-Length" = $contents.Length} -Body $contents -UseBasicParsing

#4 flush data
$endpoint =$baseUrl + ("&action=flush&position={0}" -f $contents.Length)
Invoke-RestMethod -Method Patch -Uri $endpoint -UseBasicParsing

#Check the result (get data)

Invoke-RestMethod -Method Get -Uri $baseUrl -UseBasicParsing

For more details, please refer to here, here and here

answered Oct 27, 2020 at 1:50

Jim Xu

23.2k2 gold badges24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Saurabh Over a year ago

Hi Jim, Thanks for writing such a nice script ! It works for me, but I need to write the data file into a directory let's say "Triggers" inside the FileSystem "Dev". Here I need on a daily run of this script will create few folders like YYYY = 2020 -> MM = 10 -> DD = 28 -> Inside that I need to write the "Data.csv" for last 24 Hours run.

Jim Xu Over a year ago

@SaurabhShakyawar you can try to define fileName as 2020/10/28/{}.csv.

Saurabh Over a year ago

But that will be hardcoded, Right ? I want it to create the folders for YYYY = 2020 -> MM = 10 -> DD = 28 on a daily run and put the Data.csv inside this. I am trying something like this to create but its giving me complete date not folder. New-Item -ItemType Directory -Path ".\$((Get-Date).ToString('yyyy-MM-dd'))"

Jim Xu Over a year ago

@SaurabhShakyawar If you want to create directory in Azure data lake gen2, you can call the rest API https://{accountName}.{dnsSuffix}/{filesystem}/{path}?resource=directory.

Saurabh Over a year ago

Jim, I am not very familiar with PS. So not sure how to incorporate this with the script you have shown above. Sorry for the inconvenience.

|

Collectives™ on Stack Overflow

Writing the data to Azure Data Lake Store - Powershell Scripting

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related