3

I am trying to find a robust and correct solution for syncing data from different sources to my local relational database.

I have different sources of data for eg: CSV, REST API and probably network shared file system. These sources at any point of time gives the snapshot of the system and there is a script which runs weekly and updates my local database with all the changes. For more detailed example suppose there is an API which provides me the information of all the database names active in a VM, the next time I read data there may be the case that there are more or less number of databases depending on whether they were added or remove from last read. The remote system doesn't maintain that historic information.

In my local system I have to record and maintain these information.

Right I am reading the data from the API and then reading database from my local database make a set of the both the database names and doing set operation to check those information using operations like intersection, difference etc. I don't know if this approach would scale well.

As discussed in first line, what is the correct and robust way to do it?

-2

Consider using rsync and make.

You were unclear on a few points:

  1. appends of "new" data vs. updates that overwrite data
  2. availability of high-quality timestamps describing "freshness"
  3. cost of remote operations (e.g. REST downloads) vs. cost of local operations

I will assume:

  1. We can always do an idempotent "delete existing rows if any" plus "insert current rows".
  2. Timestamps are accurately reported by webservers, and are NTP sync'd.
  3. We can cheaply obtain a HEAD timestamp, and then transfer data if needed.

You have certainly seen remote fileservers that update their files from time to time. Now think of your CSV, REST, and other data sources as essentially updating mirrored server files in /tmp. Crucially, file timestamps will not be altered if there has been no change in the data on the remote fileserver.

Now construct a Makefile that keeps .upd "update" files up-to-date with respect to .csv and similar source files. The make rule would notice .csv's that are newer than corresponding zero-length .upd files, and would start a DB job which deletes old rows (if any) and inserts .csv rows. Repeat for other filetypes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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