First published on MSDN on Mar 24, 2018
The answer is Yes!
but your database needs to have in the
compatibility level 130
or above. If your database
compatibility level
is less than 130 you could get an error about "incorrect syntax near the keywork with" or "invalid syntax".
Please, follow these steps to read a JSON file from a Azure Blob Storage from SQL Server Management Studio:
-
Download the latest version of
SQL Server Management Studio
.
-
Connect to your Azure SQL Database and run the following TSQL in order to identify the
compatibility level version
of your database:
select * from sys.databases
-
If your database has the compatibility level than 130 you need to change it, if not you will not be able to use OPENJSON. We need to change running the following TSQL command:
ALTER DATABASE DotNetExample SET COMPATIBILITY_LEVEL = 130
. Remember that Azure SQL Database supports right now the compatibility level of 140 ( SQL SERVER 2017 ).
-
Create your JSON file and upload it to the Azure Blob Storage. You could use, for example,
Microsoft Azure Storage Explorer
-
Give the permissions to read data in the Azure Blob Storage and copy the key.
-
Using SQL Server Management Studio:
Create a new credential:
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2017-04-17&ss=bfqt&srt=sco&sp=rwdl&st=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
Create a new external data.
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://xxxxx.blob.core.windows.net',
CREDENTIAL= MyAzureBlobStorageCredential);
Run the following TSQL to read the data from a JSON file. (In my case, I created a JSON with the results of sys.dm_db_resource_stats execution
)
SELECT book.* FROM OPENROWSET (BULK 'ResourceDB.json',DATA_SOURCE = 'MyAzureBlobStorage', SINGLE_cLOB) as j
CROSS APPLY OPENJSON(BulkColumn)
WITH( [end_time] datetime , [avg_cpu_percent] [decimal](5, 2), [avg_data_io_percent] [decimal](5, 2) , [avg_log_write_percent] [decimal](5, 2) , [avg_memory_usage_percent] [decimal](5, 2), [xtp_storage_percent] [decimal](5, 2), [max_worker_percent] [decimal](5, 2) , [max_session_percent] [decimal](5, 2) , [dtu_limit] [int] ) AS book
Enjoy!
