Export data from Azure SQL database as json document on to Azure Datalake Gen2 using Powershell

Raj D 581 Reputation points
2022-04-13T20:39:58.333+00:00

Greetings!!!

I am using the Powershell code below to connect to an Azure SQL Database and would like to export data as json document on to Azure Datalake Gen2. Could you please guide me how to accomplish this. 

$InstanceName = "SQLTEST\DEMO"
$connectionString = "Server=$InstanceName;Database=sqldb;Integrated Security=True;"

$query = "SELECT * FROM dbo.Inventory"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()

$table = new-object "System.Data.DataTable"

$table.Load($result)

$table | select $table.Columns.ColumnName | ConvertTo-Json

$connection.Close()

Thank you

Azure SQL Database
Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,338 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,362 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Saurabh Sharma 23,671 Reputation points Microsoft Employee
    2022-04-15T14:40:11.14+00:00

    Hi @Raj D ,

    Thanks for using Microsoft Q&A !!
    I understand correctly, you want to export your SQL query results from an Azure SQL Database to ADLS Gen2 storage as a Json file.
    If that's the case, you can achieve this by using your machine's local drive as an interim storage location to generate the file and then upload it to ADLS Gen2 Storage account. Please find below the updated code to upload query results to ADLS Gen2. 

    $InstanceName = "{ServerName}.database.windows.net"  
$connectionString = "Server=$InstanceName;Database={DB Name};Authentication=Sql Password;User={UserName};Password={Password}"  
      
$query = "select * from [dbo].[Test]"  
      
$connection = New-Object System.Data.SqlClient.SqlConnection  
$connection.ConnectionString = $connectionString  
      
$connection.Open()  
$command = $connection.CreateCommand()  
$command.CommandText = $query  
      
$result = $command.ExecuteReader()  
      
$table = new-object "System.Data.DataTable"  
      
$table.Load($result)  
   
$inputFilePath = "c:\input_data.json"  
$table | select $table.Columns.ColumnName | ConvertTo-Json | Out-File $inputFilePath  
      
$connection.Close()  
  
$ctx = New-AzStorageContext -StorageAccountName '{Storage Account Name}' -StorageAccountKey '{Storage Account Key}'  
  
$filesystemName = "input"  
$dirname = "/"  
$destPath = $dirname + (Get-Item $inputFilePath).Name  
New-AzDataLakeGen2Item -Context $ctx -FileSystem $filesystemName -Path $destPath -Source $localSrcFile -Force

    Here I am storing query results as a file on system's local drive using Out-File and later connecting to respective Gen2 Storage account using New-AzStorageContext with storage account key and password. Once connection is established, I am using New-AzDataLakeGen2Item to upload the exported json file to ADLS Gen2 storage account.

    Please refer to the documentation to get details on using PowerShell cmdlets on Storage account.
    Please let me know if you have any questions.

    Thanks
    Saurabh

    ----------

    Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.