14

I have an inventory of products stored in Postgres. I need to be able to take a CSV file and get a list of changes—the things in the CSV file that are different to what is in the database. The CSV file has about 1.6 million rows.

The naive approach is to simply take each row, retrieve that product from the database using the key field, make the comparison, emit the changes (including updating the database), then move on to the next row. However, that many round trips causes the whole process to take a long time (upwards of two minutes). I've tried locally caching the inventory in an off-heap map (using MapDB), which improved the performance a lot, since I only needed to hit the database to write changed data, but I didn't figure out a way to make that scale. There will be many inventories for different customers. Perhaps some kind of sharding approach would be needed, but then I have to deal with nodes going on- and offline. Maybe Akka Cluster could help here too.

Are there some good approaches that I'm overlooking?

10
  • 10
    Could you create a temporary table and import the CSV content? -- stackoverflow.com/questions/2987433/… Aug 31 at 6:28
  • 9
    How big are each of your rows - or more directly, why can you not just load all ~3M items into memory at once? Aug 31 at 6:44
  • 4
    If the database contains something which the CSV does not then should it be deleted? If so then the CSV is your master list; the table can possibly be cleared and repopulated. You can achieve this with some sort of temp table rename scheme.
    – MonkeyZeus
    Aug 31 at 15:26
  • can't timestamps filter out rows you don't care about -- i.e., records since last comparison
    – BurnsBA
    Aug 31 at 18:29
  • 2
    Is your CSV file sorted by some unique value, such as the ID? If so, you could query all of the rows, ordered the same way, and stream the result and stream reading the CSV, keeping one of each in memory at a time.
    – duckbrain
    Sep 1 at 18:07

5 Answers 5

22

Since the roundtrip seems to be the issue, you could:

  • either opt for a local solution, with the scaling issue you mentioned. (You could still try to split the task across several local nodes, each responsible of a subrange of the index space).
  • or opt to the db solution, bulk uploading your csv in a temporary table, and let the db server work very efficiently on (indexed) tables. The benefit of this approach is that you’d reach the scalability of the db itself. You could fine tune the approach for any distribution scheme that woukd already be in place, if it’s already a distributed database.

Some more thoughts:

  • if you have many columns/fields to compare, you may consider adding a hash code on each row, in the csv as well as in the db (updated at every row change). The hash code would be calculated using the fields that are relevant for the comparison. Finding the diff is then reduced to finding the new rows and the existing rows with a difference on the hash.
  • ultimately, it would be more efficient to handle the problem at the source, i.e intercepting the events that would cause the csv to change, or using some kind of timestamp of the last change. But ok, this is not always possible.
3
  • 1
    Unfortunately it's not possible at this point to get those events—they happen in someone else's system. Using a combination of answers, pulling all the digests from the database and comparing those locally might be the way to go.
    – Isvara
    Aug 31 at 10:43
  • 4
    I'd personally let the DB compute the hashes during the loading of the CSV into a temporary table. Seems faster and makes the process simpler, since in that way it remains an internal detail inside the DB and doesn't require any kind of external change.
    – GACy20
    Aug 31 at 13:25
  • @GACy20 I agree for the scenario of the temporary db table, since it moreover allows to share the same code for calculating the hash. In ly answer I was more neutral, since this approach can also be used to accelerate the local scenario. Thanks for making it more explicit.
    – Christophe
    Sep 1 at 6:14
39

Since your goal is to produce a list of changes, not to change the stored records, the way to go is to simply export the database to text and wrangle it once to get into exactly the same CSV format. Provided you have a functional sorting criterion, the standard diff tool will have no problem processing millions of lines.

3
  • I'm not going to shell out to a diff tool. I don't even know of a tool that knows how to diff CSV files—columns can be in any order, quoting is optional in some cases, etc. And yes, the stored records will be updated with the changes.
    – Isvara
    Sep 7 at 16:38
  • @isvara Just save the 2 files with the same column order and the same sort, and use one of the many free diff tools out there. e.g. git has one you can use easily. For formatting issues, you can achieve parity by importing then exporting both through the same third party csv tool, e.g. Google sheets. I did this exact thing last week with excellent results.
    – ogoldberg
    Sep 7 at 18:45
  • @ogoldberg That's too complicated a solution: read the data from the database, format it into a CSV file that has the same format as the incoming one (CSV isn't a well standardized format), save the two CSV files to local disk, spawn another process to run a diff tool, parse the output of the diff tool to see which data changed, publish the changes and write them back to the database, delete the two CSV files. It might be fine for a one-off manual process, but that's not how to write a program.
    – Isvara
    Sep 7 at 19:35
9

There are already some good answers, but here's another possibility: if you could sort the CSV file by key and add a similar ORDER BY to the SQL query, you could compare the rows obtained from the query to the rows obtained from the CSV file without loading any of them fully into memory. (If key matches, compare the row. If it doesn't match, you can deduce if it's a removal or insertion from the ordering. Then, move the cursor.)

(Not sure what language or library you want to use, but plain JDBC allows you to fetch the next rows without loading the whole result into memory.)

3

For me the most intelligent and simple way will be to put the files in database and write some SQL script to compare what you want.

2

To make this scale both for size of CSV and the amount of products in the database, you could do:

  1. Read e.g. 10 000 rows from CSV to memory. Collect all the keys.
  2. Fetch the 10 000 corresponding database rows. Depending on database, it can make sense to create a temporary table with the keys and perform a JOIN with that to fetch the rows. If the CSV file is sorted, you may be able to select keys between min and max value from the CSV.
  3. Collect the required updates into an array in RAM and execute a single query to write to database. Typically INSERT INTO table VALUES (row1),(row2),...,(rowN) ON DUPLICATE KEY UPDATE x=VALUES(x).

This will reduce the time overhead of waiting for database to respond, while still limiting the length of individual queries and required RAM.

Not the answer you're looking for? Browse other questions tagged or ask your own question.