Loading JSON files from Azure File Storage

Azure File Storage supports SMB protocol, so you can map your local virtual drive to the Azure File storage share using the following procedure:

1. Create file storage account (e.g. mystorage), file share (e.g. logs), and folder using Azure portal or Azure PowerShell SDK.
2. Create firewall outbound rule in Windows Firewall on your computer that allows port 445. Note that this port might be blocked by your internet provider. If you are getting DNS error (error 53) in the following step then you have not opened that port or it is blocked by your ISP.
3. Mount Azure File Storage share as local drive (e.g. t: ) using the following command:

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

Example that I have used is:

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

Storage account key and primary or secondary storage account access key can be found in the Keys section in Settings on Azure portal.

Now if you setup you application to log data to some log file into Azure File Storage (e.g. log-file.json), you can use queries above to analyze data loaded from path mapped to t: \\mystorage.file.core.windows.net\sharejson\log-file.json

Now, we can store some JSON file in this location. Example of file might be :

 [
 {"time":"2015-11-27T02:33:05.063","ip":"214.0.57.12","request":"/", "method":"GET", "status":"200"},
 {"time":"2015-11-27T02:33:06.003","ip":"224.12.07.25","request":"/",method":"GET", "status":"200"},
 {"time":"2015-11-27T02:33:06.032","ip":"192.10.81.115","request":"/contact", "method":"POST", "status":"500", "exception":"Object reference not set to an instance of object","stackTrace":"…"}, ……
 {"time":"2015-11-27T02:37:06.203","ip":"204.12.27.21","request":"/login",method":"GET", "status":"200"}, {"time":"2015-11-27T02:37:12.016","ip":"214.0.57.12","request":"/login", "method":"POST","status":"404", "exception":"Potentially dangerous value was detected in request"}
 ]

We can load this file using standard OPENROWSET(BULK) command that reads entire content using SINGLE_CLOB option, and parse it using OPENJSON function:

 SELECT log.*
 FROM OPENROWSET (BULK '\\mystorage.file.core.windows.net\sharejson\log-file.json', SINGLE_CLOB) as log_file
 CROSS APPLY OPENJSON(BulkColumn)
 WITH( time datetime, status varchar(5), method varchar(10),
 exception nvarchar(200)) AS log

• Anonymous
  May 17, 2016
  Hi Jovan,How to load json from file share or blob for Azure Sql Database?