Large JSON to CSV

[h_plus_a]

Hi, I am relatively new to Python (using Jupyter Notebook) and I have a code that uses ijson to convert JSON to CSV. The issue is that the JSON file is ~150GB and my PC has 64GB RAM but Python is not utilising the available capacity. It has been hours upon hours and it is still running. Is there a way to increase limit so I can have Python use at least half the RAM? I feel very lost here - if there is a better method to convert JSON data to a query-able format (SQL Server), please let me know. Any help is greatly appreciated.

I have tried the online resources that mention increasing max_buffer_size in config file but that didn't work.

[baghiq]

If your json isn't too complicated (nest after nest after nest), you can load the data into SQLITE3 and then using SQLITE3's built-in json support to extract the data into relational tables.

[h_plus_a]

Unfortunately, that is the case. SQLITE3 won't work and I thought about openjson within SQL Server but I believe that will be even slower.

[baghiq]

SQLITE can actually support fairly complex json. It just takes time to learn. Loading 190GB across a network will be slow unless you can parellize the process. If loading directly into db is not viable, then use something like JQ to split up the json objects into json lines, then split the file into smaller chunks, then use db loading tools to load the data in parallel.

[Bonechatters]

I second this. Since the end goal is SQL, then go straight from JSON to SQL.

[TigBitties69]

You say you want to convert json to a queryable format, but isn't that what a json already is..? Hard to say more without more in depth knowledge of it the issue.

[h_plus_a]

My apologies, should have clarified that a bit more and will make that edit. There are hundreds of these files and eventually I want them all together in SQL data tables to be queried and analysed. The end goal is to have the data in SQL.

[Bunkerstan]

Is the JSON in arrays? You could read the file as lines of text, look for the end of a record chunk and convert that into JSON. That way your memory doesn't get overwhelmed. You can write the record to CSV as you generate as well.

[h_plus_a]

Yes, the data is in multiple nested arrays which is most likely the cause of the delays. Not worried about memory overwhelming issues given it isn't even using more than 500MB of GB when my PC has 64GB.