How to import multiple json files present in a container of azure blob storage into Azure SQL Database using Openrowset

Question

I have a scenario where i have to load json files present in a folder of a container in azure blob storage to azure sql database. I tried passing wildcards in data_file_path but seems it's not working.

Can you please provide alternatives or an example.

Answer 1

If you are using Azure Storage Account you can create an Azure File Share and map it as a local drive on an on-premise computer or an Azure VM as explained here. You can also use the "net use" Windows command to map the Azure File share on Windows.

net use [drive letter] \\[storage name].file.core.windows.net\[share name] /u:[storage account name] [storage account access key]

Le's say you map the files share where the JSON documents are located to a t: drive on a computer.

net use t: \\mystorage.file.core.windows.net\sharejson /u:myaccount hb5qy6eXLqIdBj0LvGMHdrTiygkjhHDvWjUZg3Gu7bubKLg==

Then you can run the following T-SQL statement to import the JSON files.= from the mapped drive (t:)

SELECT book.* FROM
 OPENROWSET(BULK N't:\books\books.json', SINGLE_CLOB) AS json
 CROSS APPLY OPENJSON(BulkColumn)
 WITH( id nvarchar(100), name nvarchar(100), price float,
    pages_i int, author nvarchar(100)) AS book

Answer 2

@Bhanu Venkata Satya Shiva Sai Inturi Thank you for reaching out.

In addition to @Alberto Morillo

Import JSON documents from Azure Blob Storage

You can load files directly into Azure SQL Database from Azure Blob Storage with the T-SQL BULK INSERT command or the OPENROWSET function.

Note

This functionality is added in SQL Server 2017 (14.x) and Azure SQL.

First, create an external data source, as shown in the following example.

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
 WITH ( TYPE = BLOB_STORAGE,
        LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
        CREDENTIAL= MyAzureBlobStorageCredential);
Next, run a BULK INSERT command with the DATA_SOURCE option.

SQL

Copy
BULK INSERT Product
FROM 'data/product.dat'
WITH ( DATA_SOURCE = 'MyAzureBlobStorage');

https://learn.microsoft.com/en-us/sql/relational-databases/json/import-json-documents-into-sql-server?view=sql-server-2017

Regards,

Oury