Get gigabytes of JSON file data into SQL

Bigbacon

[H]F Junkie
Joined
Jul 12, 2007
Messages
16,383
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.
 

modi123

Supreme [H]ardness
Joined
Sep 6, 2006
Messages
5,664
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.
 

Bigbacon

[H]F Junkie
Joined
Jul 12, 2007
Messages
16,383
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:

Wiseguy2001

2[H]4U
Joined
Nov 28, 2001
Messages
3,466
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).
 

ChristianVirtual

[H]ard DCOTM x3
Joined
Feb 23, 2013
Messages
2,522
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.
 

v6maro

[H]ard|Gawd
Joined
Oct 10, 2002
Messages
1,549
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.
 
Top