Get gigabytes of JSON file data into SQL

Bigbacon

Fully [H]
Joined
Jul 12, 2007
Messages
21,249
Trying to figure out how to do this. My approaches so far haven't worked and I need ideas.

So, i have 750gb of files in a JSON format that I need to get the data from into SQL...

I wrote an app to try and do it one JSON file at a time, which was actually very quick except...at some point in the process, SQL would either timeout or just close the connection for no reason even though a timeout had not elapsed. I tried multiple different methods to deal with the bulk insert always with the same result.

I know SQL 2016 is supposed to have some native JSON support but I'm working with 2012.

Some of the JSON files are upwards of 500mb.

there are 5704 files total.
 
What sort of app were you using to dump it into SQL? Some C# or Vb.NET?

Maybe bump the timeout to infinity.. or work on taking the JSON and converting to a CSV for direct insert.
 
What sort of app were you using to dump it into SQL? Some C# or Vb.NET?

Maybe bump the timeout to infinity.. or work on taking the JSON and converting to a CSV for direct insert.

I upped the batch amount this morning and it went it just fine. I am wondering if there was something going on last night that kept the SQL busy or it was dropping connection or what. no problems at all so far today. I did two batches today (2 days worth of data) at 43gb of JSON per day, takes an hour.

6 million rows of data i tink it was.
 
Last edited:
Have you tried converting the data to CSV and then importing that? You could go one step further and slice the files into more manageable sizes.

Depending on what you're using, there's some horrible json libraries out there (in terms of speed & memory usage).
 
Maybe turnoff index while inserting data. And reindex afterwards. Could you save some time. Look also to increase memory cache if you have enough RAM.
 
I'd import it all into a document db (mongo/redis/whatever) then write something to move it to sql, that'll be fast in my opinion.
 
Back
Top