Sometimes I have to load csv-files to database. These files might be really huge (> 1TB).
I did not export them to this file and I cannot change anything about the format. The files just "fly in". In most of the cases the files are seperated by "pipe". Sometimes text is qualified. Within qualified text there might be "pipes", as well as there might be CR/NL-characters... Sometimes text is not qualified. Within the text pipes are escaped with an backslash. A backslash might be escaped with another backslash...
The first thing I do to load these files is to get all content changed so that one line for the table is within on line of text. The row delimiter must not remain within any textfield. I do this job with a commandline script using sed and/or tr.
The next step is to split the file in hany pices (< 50 MB). They would fit into any editor (more confort than less) and I can proceed them parallel. (For a short time I need double the space of the original file in the filesystem).
Depending on the text formating I convert special characters within the text fields using commandline script using sed, if it gets more complex I use awk. As the original file is split to hundreds of small files I can do this parallel. To run (lets say 20) tasks parallel I use another commandline script.
The next step is to load these files into the database. For loading into MS-SQL-Server I use an SSIS-package with a for-each loop over all files within a filesystem directory. For each file a table is created and the data of this file is loaded. If successfull the file is moved to a subfolder "success", if an error occured the file is moved to a subfolder "error". So if something would stop the process, you could always restart at a defined point.
I build some copies of this package and filesystem-structures (load-folder_x, load-folder_x/success, load-folder_x/error) to start as many packages in parallel as my I/O would accept.
When every file is in "success" I am almost done: At least data is inside the database. So I can loop through all the tables and do whatever I can do within a database. All in all I need about 3 times the size of the original file for filesystem data and database file while the process is running. A single process from file in filesystem to table in database would run with about a 20 to 30 MB/s. It does scale up to the maximum I/O of the server/disk-subsystem/network-connection.
My question is: How to improve this process?
The used tools are all "handmade", not documented and not so easy to understand for third parties. errorhandling and logging is all "handmade" ... I guess there is a lot of potential improvement and I guess I am not alone with this kind of tasks.