I am putting together a system that collects data from Quandl and stores it in a database. I should note that there is no commercial aspect to what I am doing (I have no customer/employer). I am doing this as a hobby and to hopefully learn a thing or two.
Anyway, the challenge I have set myself is to build a system that automatically downloads data from Quandl and stores it in a database, without ever saving zip or csv files to disk.
Quandl provides daily 'delta' files which can be downloaded as zip files. The zip files are extracted to csv files. I have managed to get as far as downloading the zip files and extracting the csv files all in memory, using a MemoryStream, ZipArchive, and StreamReader in .Net (F# specifically - happy to provide a code snippet if required).
Now the challenge I am stuck on is how to get this over to my database. The database I am using is MariaDB (which is essentially the same as MySQL). I am using this because this is the only type of database my NAS supports.
Options are
- Give up on my objective of not ever saving to disk and save the csv to disk, then pass the file path to a stored procedure as in this answer.
- I can convert the csv data into JSON or XML and pass it to a stored procedure and have the server parse the string into a tempory table. I have done this before using SQL Server and am assuming something similar is possible here.
- Read the csv line by line and pass to the database line by line. This is really a non option as it would be very slow.
Seems like 2 is the best option I know of. Is there a more direct way that does not involve converting csv to JSON or XML?