0

We have an application that can work with Oracle or MySql or SQLServer databases. Also, there is a 3rd party data source that we need to have it's latest data records in our database every 15 minutes. We can call some API or raw data endpoint to pull data from 3rd party source.

If we write an application to do this job, it should do the following operations:

  1. Call the 3rd party endpoint every 15 minutes (Calling the endpoint)
  2. Convert the data to our database format (Data integration)
  3. Insert data in our tables (Oracle or MySql or SQLServer)

So, is there any predefined tool to do this job for us or we should develop this application in-house? This is some kind of database sync job but it should be able to work with different databases and also it should be able to do the data integration.

closed as off-topic by McNets, Marco, mustaccio, Vérace, Michael Green Feb 7 at 0:24

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – McNets, Marco, mustaccio, Vérace
If this question can be reworded to fit the rules in the help center, please edit the question.

2

Solution

Your solution contains two parts

  • Schedule a Job
  • Extract, Transform, and Load (ETL) the data

Job Schedulers

  • OSes have them (cron / task scheduler)
  • some databases have schedulers (oracle DBMS_SCHEDULER)
  • and there are enterprise COTS (Common Of The Shelf) software for inter-job scheduling and resource control

ETL

The simplest form would be a combination of shell scripts and stored procedures.

  1. The shell script first copies (Extract) the data ( ftp,scp, REST call via curl ).
  2. Then, it Loads the data into the database. All RDBMS have tools to load CSV data.
  3. After that, a stored procedures and/or SQL script is called to Transform the data into its final state.
    • These would most likely use INSERT...SELECT or MERGE statements.
  4. Finally, the shell script archives the data file.

On the high end, COTS Software is available.

Build or Buy?

That's up to you. You will need to do much more research.

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