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, Jul 20 '20 at 02:28
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, Jul 20 '20 at 18:55

score 1 · Accepted Answer · answered Jul 21 '20 at 05:13

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 Answers1