Searching for a Better Way to Handle Bulk Inserts

[michaeljeffreycollins]

Hello Discussion Forum,

My issue is I need to parse through a JSON file that has 10k records each with ~70 fields of data. When written to disk the file in question is only ~18 mb in size. Holding it all in memory isn't an issue. My destinations are a single chain of 3 steps
Order of Operations

1. Clear a staging table in a SQL SERVER database
2. Loop using a Java Prepared statement and .addBatch() to create a massive insert statement to act as a "Bulk" insert.
3. Perform a SQL Merge after all 10k records are written to the stage table.

ISSUES
This is producing several issues.

1. The input file is so large Connect will not display the raw data. Which seems odd as it is only 18 mb.
2. Due to the clearing of the WIP table this cannot be performed as a batch process.
3. addBatch() and Java PreparedStatement isn't really doing a BULK INSERT. But it is keeping my SQL transaction log much smaller.

Is there a better way of handling a situation where 1000's of records need to be added to a table in a single go?

Things I have tried

1. Make the entire message an attachment and only read the file in the transformer step 2. Saves memory and display power.
2. Attempted to use BULK Insert function of SQL Server to read a file from disk. But certain fields need to be nullString and other need to be database Null. Bulk insert is limited and introduces network drive availability issues.
3. Increased the HEAP size of Java through the roof so I could display RAW messages and Attachments. Did not work, this is not a heap memory issue.
4. Switched the entire channel processing level to just RAW.

Extra Info - Edited
The JSON File is obtained from a web server call and is held in-memory during processing. Currently we do not rely on any network drive.
The JSON isn't a direct conversion to the table. Field names <> Column names. There are nested arrays and only one value is chosen from those arrays if they even exist.
Due to sensitive nature of the data I cannot post an example of the JSON File. SSN, Address, Full Names, Salary Info, HR benefit data. Multiple job positions, multiple sub jobs, multiple existing work experience It is a private info nightmare.

[pacmano1]

I'd suggest don't do it in Mirth at all. For very large inserts command line DB tools are much faster. You can shell out of Mirth to do that of course or just cron something.

Network drive problems would present a problem irrespective of how you do the insert.

[michaeljeffreycollins]

The speed of the tool isn't an issue at this time. We obtain the data from a web service call to our HR system. That happens in a different channel and the response contents are forwarded to the channel where I am having my issues. The json file has multiple nested arrays of objects and only 1 of those get written to the database if they are present in the object at all. I didn't explain the extensive json manipulation required in this process because that is working currently. Right now I can avoid all networking drive issues since we don't write or read from a file. And that is my own fault as well. I stated JSON FILE and it is, except the entire contents are held in memory and we don't need to read/write from a network drive. Same goes for the C# program mentioned below

The DB command line tools may be faster processing but would require extensive coding, maintenance, and design in a new direction to get to work in the same manner. Plus, the db tools would just cover step 2. Step 3 still needs to happen after step 2 has completed. I am attempting to avoid a channel in mirth, a scheduled SQL job on a server, and a cron job on a server all working to complete 1 process.

The way this is poorly handled today is a Scheduled Task on a windows server runs a C# program that parses the json ( incorrectly), and bultInserts to a SQL.server. We cannot review the json downloaded by the program, the status of the job doesn't match the actual result, and we get emailed generic errors as part of a catch all solution. Using Mirth fixes all of that by giving is visibility into the downloaded content and a list of errors against said content. By refactoring the C# code we could obtain better visibility but not as much as Mirth could give us.

[pacmano1]

Well, all you had to say was Windows :)

[michaeljeffreycollins]

So true. SSIS as an ETL solution is less than stellar in my opinion. Too many missing features out of the box. I dislike having to patch those holes with multiple 3rd party products and configurations. And why is there almost no visibility into the ongoing process if it messes up.

[jonbartels]

Do a two-stage insert to your DB.

1. Create a db table to the effect of raw_hr_data whose only columns are your JSON data and maybe some timestamps to show when the data was inserted
2. Write SQL SELECTS which reads the JSON data in a table format. The reference below says "Transform arrays of JSON objects into table format.". Do this on the bench to see that you're SELECTing out the correct information.
3. INSERT the results of step 2 to your actual tables.
4. Package steps 2 and 3 such that they can be called by MC. It can run the raw SQL but a stored procedure might be reasonable here too.
5. The outcome is that you have your JSON data in tables but the DB did all the heavy lifting in the DB and not in your MC instance.

This pushes some of the logic into your db, but if MC is just making SQL calls to poke in the JSON then SQL to SELECT+INSERT the JSON it keeps the heavy lifting in SQL Server but the logic is still visible to MC.

You'll want to review https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15 . I know PG has native JSON column types but it doesn't look like SQL Server does, though it does have the query tools to read the JSON. Apologies if I'm over looking any SQL Server quirks, I dont use it as often as I do other SQL engines.

[michaeljeffreycollins]

Well, the JSON is nested and has several 1..M relationships. Handling that JSON via a stored procedure is a lengthy mess for sure. But this is a good way to move data and have all the execution steps in Mirth. I do dislike SQL Server's json functions. They are a bit clunky to say the least.