次の方法で共有

Importing JSON files into SQL Server using OPENROWSET (BULK)

  • [アーティクル]

Importing JSON files in SQL Server

Currently you can find many JSON documents stored in files. Sensors generate information that are stored in files, applications log information in JSON files, etc. One important thing that you would need to do is to read JSON data stored in files, load them in SQL Server, and analyze them.

In this post we will see how you can import JSON files in SQL Server.

Importing files using OPENROWSET(BULK)

OPENROWSET (BULK) is a table value function that can read data from any file on the local drive or network if Sql Server has read access to that location. It returns a table with a single column (i.e. BulkColumn) that contains content of the file. In general case you can use various options with OPENROWSET (BULK) function, such as separators etc., but in the simplest case you can directly load entire content of a file as a text value (or single character large object a.k.a. SINGLE_CLOB in OPENROWSET “terminology”) and load content of that cell in any table or variable. An example of OPENROWSET (BULK) function that reads content of JSON file and return it to user as a query result is shown in the following example:

 SELECT BulkColumn
 FROM OPENROWSET (BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

OPENJSON( BULK) will read content of the file and return it via BulkColumn. You can also load content of file in some local variable or table, as it is shown in the following example:

 SELECT @json = BulkColumn
 FROM OPENROWSET (BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j
 
 SELECT BulkColumn
 INTO #temp
 FROM OPENROWSET (BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

 

Importing JSON content

In this post I will show you how to import content of JSON file directly into SQL Server tables using OPENROWSET(BULK). I will use the fact that JSON is regular text so it can be imported as any other text format. Therefore, I can leverage existing functions that work with text and in this case import text from a file. In this example I will use JSON file containing books taken from this site. Instead of reading entire JSON text, I want to parse it and return either books in the file, or their properties.

In the simplest example we can read JSON objects from the file:

 SELECT value
 FROM OPENROWSET (BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) as j
 CROSS APPLY OPENJSON(BulkColumn)

OPENROWSET will read single text value from a file, return it as a BulkColumn, and pass it to the applied OPENJSON function. OPENJSON will iterate through the array of JSON objects in the BulkColumn array and return one book formatted as JSON in each row:

Value
{"id" : "978-0641723445","cat" : ["book","hardcover"],"name" : "The Lightning Thief", …
{"id" : "978-1423103349","cat" : ["book","paperback"],"name" : "The Sea of Monsters", …
{"id" : "978-1857995879","cat" : ["book","paperback"],"name" : "Sophie's World : The Greek …
{"id" : "978-1933988177","cat" : ["book","paperback"],"name" : "Lucene in Action, Second …

 

With new OPENJSON function we can parse that JSON content and transform it to a table or result set. In this example we will load content, parse loaded JSON and return five fields as columns:

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

OPENROWSET(BULK) will read content of the file and we can pass that content to OPENJSON function with defined schema. OPENJSON will match properties in JSON objects using column names (e.g. price property will be returned as a price column and converted to float type). Results in this case would look like:

Id Name price pages_i Author
978-0641723445 The Lightning Thief 12.5 384 Rick Riordan
978-1423103349 The Sea of Monsters 6.49 304 Rick Riordan
978-1857995879 Sophie's World : The Greek Philosophers 3.07 64 Jostein Gaarder
978-1933988177 Lucene in Action, Second Edition 30.5 475 Michael McCandless

 

Now we can either return this table to the user, or load it into another table.

Loading data from Azure File Storage

You can use the same approach to read JSON files from any file that can be accessed by SQL Server. As an example, 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. sharejson), and folder using Azure portal or Azure PowerShell SDK. Upload some JSON file in the file storage share.
  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.

  1. Now you should be able to access your JSON file via share name, e.g.:

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

Comments

  • Anonymous
    October 07, 2015
    What version of SQL Server 2016 do you need for this? Does the OPENJSON command exist in CTP2?

  • Anonymous
    October 07, 2015
    OPENJSON and other JSON functions will be available in CTP3. Regards, Jovan

  • Anonymous
    October 05, 2016
    I don't know where this comes from, but SQL doesn't like it. Wish it worked though.OPENROWSET (BULK (@file))

    • Anonymous
      October 27, 2016
      Hi Jon,I am using SQL2005 EnterpriseHere is the syntax that work:select BulkColumnfrom OPENROWSET(BULK '\desktop1\sharefolder\test.json', SINGLE_CLOB) as j
      • Anonymous
        October 27, 2016
        This is what I am using to parse the json, since we are using a very old SQL Server.declare @data nvarchar(max)select BulkColumnfrom OPENROWSET(BULK ‘\desktop1\sharefolder\test.json’, SINGLE_CLOB) as jselect *from parseJSON(@data)Where parseJSON is from:https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

  • Anonymous
    November 11, 2016
    I am only getting one row returned after running the code. Any help will be appreciated.

    • Anonymous
      November 30, 2016
      You can use WITH to define how to parse your JSON.Like this:SELECT * FROM OPENJSON(@json)WITH ( id nvarchar(MAX) N'$.id', app_id nvarchar(MAX) N'$.app_id', crash_reason_id nvarchar(MAX) N'$.crash_reason_id', created_at nvarchar(MAX) N'$.created_at', ) Works for me.Regards,

  • Anonymous
    February 10, 2017
    How can I get to the nested attributes like ”cat” : [“book”,”hardcover”]? Thank youEvgeniya

  • Anonymous
    February 23, 2017
    Thanks Jovan! Had trouble to read a large JSON in Sql server. With the OPENROWSET and CROSSAPPLY OPENJSON it worked!

  • Anonymous
    April 04, 2017
    I'm trying to test selecting data from one table and pushing it to another in sqlserver in this case the source table looks like this DeviceId CreatedDt ModifiedDt Field1 Field2 Field3 1 01/01/2017 NULL 10 5 I would like to move some of the data out of this table and store it in an other table that would look like this.DeviceID CreateDt ModifiedDt JSONField [nvarchar(max)]1 01/01/2017 NULL [{"DeviceID":1,"field1":10,"Field2":5}] Is there a way to do this without writing some kind of loop logic to select each row as JSON individually?

  • Anonymous
    April 18, 2017
    The comment has been removed

    • Anonymous
      April 24, 2017
      The comment has been removed