Import sql query as json document in azure data lake storage

Question

Import data from a SQL Database hosted on Azure virtual machine, convert sql query data as a json document and store it in Azure Data Lake Storage. I am using powershell to create the json document.

Hit a roadblock on how to import json documents into data lake store and to automate the import.

$InstanceName = "SQLDB\TST"
$connectionString = "Server=$InstanceName;Database=dbadb;Integrated Security=True;"
$query = "SELECT * FROM Employee"
$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 | Set-Content "C:\JsonDocs\result.json"

$connection.Close()

Could you please describe your issue in details and provide the error message? — Jim Xu
I am hitting a roadblock. I have the json document created but not sure how to import the json document into Azure Data Lake Store. — paone

Jim Xu Jim Xu · Accepted Answer · 2020-07-21T05:13:50

If you want to store JSON file in Azure data lake store with PowerShell, you can use the PowerShell command Import-AzDataLakeStoreItem

For example

Connect-AzAccount
Import-AzDataLakeStoreItem -AccountName $dataLakeStorageGen1Name `
   -Path "you json file path" `
   -Destination "the path in data lake store"

For more details, please refer to the document

Import sql query as json document in azure data lake storage

1 Answers