Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Access to JSON file content using T-SQL
/*
Create the Database Master Key, if needed
*/
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My-L0ng&Str0ng_P4ss0wrd!';
GO
/*
Create database scoped credentials to store the Shared Access Signature (SAS)
needed to access the Azure Blob Container. More info on Azure Blob SAS here:
https://docs.microsoft.com/en-us/azure/storage/storage-dotnet-shared-access-signature-part-1
SAS can be created right from Azure Portal or Azure CLI
*/
CREATE DATABASE SCOPED CREDENTIAL [Azure-Storage-Credentials]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<your SAS signature, WITHOUT any leading "?">';
GO
/*
Now create the external data source and point it to the Azure Blob Store
*/
CREATE EXTERNAL DATA SOURCE [Azure-Storage]
WITH
(
TYPE = BLOB_STORAGE,
LOCATION = 'https://<storage-account>.blob.core.windows.net',
CREDENTIAL= [Azure-Storage-Credentials]
);
GO
/*
Access the file content via SINGLE_BLOB BULK OPENROWSET
and pass it to OPENJSON
*/
WITH cte AS
(
SELECT
CAST(BulkColumn AS NVARCHAR(MAX)) AS JsonData
FROM
OPENROWSET(BULK '<container>/<blob>', DATA_SOURCE = 'Azure-Storage', SINGLE_CLOB) AS AzureBlob
)
SELECT
j.*
FROM
cte
CROSS APPLY
OPENJSON(cte.JsonData) j
go
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment