次の方法で共有

Parsing 4GB JSON with SQL Server

  • [アーティクル]

SQL Server 2016 and Azure SQL Database enable you to parse JSON text and transform it into tabular format. In this post, you might see that JSON functions can handle very large JSON text - up to 4GB.

First, I would need very large JSON document. I'm using TPCH database so I will export the content of lineitem table in a file. JSON can be exported using the bcp.exe program:

 D:\Temp>bcp "select (select * from lineitem for json path)" queryout lineitems.json -d tpch -S .\SQLEXPRESS -T -w

Starting copy...


1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 103438 Average : (0.01 rows per sec.)

 

The query will format all rows from lineitem table, format them as JSON text and return them as a single cell. I'm using Unicode format (-w flag). As a result, bcp.exe will generate 4.35 GB (4,677,494,824 bytes) file containing one big JSON array.

Now I will load the content of this file using OPENROWSET(BULK) and pass content to OPENJSON function that will parse it, take the values from l_discount key, and find the average value:

 select avg([l_discount])
from openrowset(bulk 'D:\Temp\lineitems.json', SINGLE_NCLOB) f
 cross apply openjson(f.BulkColumn) with([l_discount] [money])

In my SQL Server 2016 Express edition this query is finished in 1min 53 sec.

Conclusion

Functions that can parse JSON in SQL Server 2016 do not have any constraint regarding the size of JSON document. As you might see in this example, I can successfully parse 4GB JSON document, which is 2x bigger than maximum size of NVARCHAR(MAX) that can be stored in tables.

Comments

  • Anonymous
    March 22, 2017
    I am looking for a good place to post this to get it seen:SSRS really should i think get some new features to match what is happening with data in the real world:1) support web services as a first class data source. right now it's very hard to call a service and almost unusable.it needs to support passing parameters to the web serives and to suport rest style services.2) JSON as a dataset that can be an object that contains a collection of object that can contain collections.
  • Anonymous
    December 13, 2017
    Greate artіcle. Keep writinbg such kkind of info on your site. Im really imрreased by your blog.Hey there, Yoս've perfformed a ɡreat joЬ. I'll definitｅly digg it ɑnd personally recommend to my friendѕ.I am confident tһey will be benefited from this website.
    • Anonymous
      December 22, 2017
      Thanks, I'm glad that it helps :)
  • Anonymous
    December 22, 2017
    Hi therе, Ι read your blogs regularly. Yοur writing ѕtyle iss witty, keep doing what you're doing!
    • Anonymous
      December 22, 2017
      Thanks, I'm glad that you find it useful.