Connect to azure blob storage from azure sql

Question

Greetings!!!

I'm working on a project where we have to query data from json documents stored on azure datalake storage blobs. We are trying to query and load the below json document into an azure sql database.

Query:

declare @doc varchar(max) = '[{  
        "value": [{  
                "Id": 123,  
                "Year": 2001,  
                "Name": "abc"  
            },  
            {  
                "Id": 234,  
                "Year": 2001,  
                "Name": "bcd"  
            }  
        ],  
        "Count": 2  
    },  
    {  
        "value": [{  
                "Id": 123,  
                "Year": 2002,  
                "Name": "abc"  
            },  
            {  
                "Id": 234,  
                "Year": 2002,  
                "Name": "bcd"  
            }  
        ],  
        "Count": 2  
    },  
    {  
        "value": [{  
                "Id": 123,  
                "Year": 2003,  
                "Name": "abc"  
            },  
            {  
                "Id": 234,  
                "Year": 2003,  
                "Name": "bcd"  
            },  
            {  
                "Id": 345,  
                "Year": 2003,  
                "Name": "cde"  
            }  
        ],  
        "Count": 3  
    }  
]';  
  
select jsond.*  
from OPENJSON (@doc)   
with (  
  value nvarchar(max) as json  
) as v  
cross apply openjson(v.value)  
WITH (  
  [Id] INT,  
  [Year] INT,  
  [Name] NVARCHAR(200)  
  ) AS jsond;  

This query works fine, but if I were to provide the datalake storage container path I'm not sure how to accomplish that.

storage container name: data

ADLS Gen2 storage path:

https://adls.blob.core.windows.net/data/test/test.json  

Thank you

Answer 1

Hello @Raj D ,

Thanks for the question and using MS Q&A platform.

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

Here is an example on how to use OPENROWSET with SINGLE_CLOB to import JSON data from a Blob Storage using a query like below -

SELECT id,[name],author FROM OPENROWSET(  
   BULK  'input/books.json',  
   DATA_SOURCE = 'storageadftutorial',  
    SINGLE_CLOB  
    ) AS DataFile  
CROSS APPLY OPENJSON(BulkColumn)  
WITH (id NCHAR(1000),  
[name] NCHAR(500),  
author NCHAR(500)) as book  

Please note here that the data from JSON file is returned as BulkColumn which you can join with OPENJSON which returns each JSON array object as a single row, which you can insert into the required table.

books.json sample content

[
{
"id" : "978-0641723445",
"cat" : ["book","hardcover"],
"name" : "The Lightning Thief",
"author" : "Rick Riordan",
"series_t" : "Percy Jackson and the Olympians",
"sequence_i" : 1,
"genre_s" : "fantasy",
"inStock" : true,
"price" : 12.50,
"pages_i" : 384
}
,
{
"id" : "978-1423103349",
"cat" : ["book","paperback"],
"name" : "The Sea of Monsters",
"author" : "Rick Riordan",
"series_t" : "Percy Jackson and the Olympians",
"sequence_i" : 2,
"genre_s" : "fantasy",
"inStock" : true,
"price" : 6.49,
"pages_i" : 304
}
]

SQL Table:
Create table books( Id NCHAR(1000), [Name] NCHAR(500), Author NCHAR(500) )

Use below to insert into books table

INSERT INTO books with (TABLOCK) (id,[name],author)  
SELECT id,[name],author FROM OPENROWSET(  
   BULK  'input/books.json',  
   DATA_SOURCE = 'storageadftutorial',  
    SINGLE_CLOB  
    ) AS DataFile  
CROSS APPLY OPENJSON(BulkColumn)  
WITH (id NCHAR(1000),  
[name] NCHAR(500),  
author NCHAR(500)) as book  

Please refer to Import JSON documents into SQL Server for details.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators