1

I have the following scenario:

  • A large database (postgresql).
  • Many organizations that need to extract data from this database, each one with its own schedule and data requirements.

I want an application that:

  • Provides me a sort of calendar where I can schedule different query executions.
  • For each execution I can specify a simple sql file, a format for the resulting file and some sort of automatic uploading, for example, to an ftp server.
  • Has a web interface so I can use it from another computer over the internet.
  • Can manage several users.
  • Preferably "software libre" and linux-compatible.

Thanks!

  • Are the schedules "fixed" (i.e. always at the same time of a day, say "daily at 7am"), or do you need to place them dynamically by hand? Is a GUI required, or does command-line do? What OS is the server running on and, if the scheduler is supposed to run on a different machine, what OS should be supported on that end? – Izzy Nov 22 '14 at 14:12
  • Can a built-in OS scheduler suffice? Like a Windows task scheduler o Linux Cron job running a command line tool to run the query? – Alejandro Nov 22 '14 at 23:20
  • @Izzy: the schedules could be fixed, but the other possibility sounds nice. The GUI is required (web, if possible) and it needs to run on linux. Thanks. – lufte Nov 23 '14 at 18:05
  • @Alejandro: cron is my first option at the moment, but I would have to write some extra code to upload the files and I wouldn't have a GUI. Thanks. – lufte Nov 23 '14 at 18:07
  • Cron was what I had in mind, too. There are GUI frontends to it. Though most would require to be logged-in on the host directly, Webmin could be an option for that. Of course, that means some shell-scripting to do the real job. // May I suggest to edit your question to include some of the facts collected in the comments, to make them easier to spot? ;) – Izzy Nov 23 '14 at 18:14
1

I do something similar, but in reverse (ingesting large amounts of information from several different companies into an enterprise data warehouse).

For this job, I'd recommend that you look at an ETL tool. If you're using PostgreSQL, I'd recommend that you look at either Pentaho Data Integration (PDI) or Talend. I've chosen to use PDI where I work and I couldn't be happier.

The way it works, you build out your jobs (which run several transformations, handle data transfer to things like FTP/SFTP, email, syslog entries, etc.) and your transformations (the bits that do the data extracts). You can save them to a file system, or in my case a database so that they can be accessed from multiple computers.

You can schedule the jobs to execute using a simple cron job from any computer that can run Java (I'm using OpenJDK with Ubuntu 14.04 and it's working great). A nice side effect of this when working in production is that your DB and ETL servers are separate, that way if for some reason ETL goes down it won't take your DB with it.

  • I took a look at the tool (the Pentaho one) and it seems to have everything I need. Scheduling is only for the Enterprise edition, but I'll try to use cron to accomplish that as you say. – lufte Nov 28 '14 at 2:45
  • My cron entries look something like this: 0 6 * * * cd /opt/data-integration && ./kitchen.sh -rep=REPO_NAME -user=USER_NAME -pass=PASSWORD -dir=/FOLDER_ON_DB_REPOSITORY -job=JOB_NAME – Nathan Clayton Dec 1 '14 at 19:57
  • One thing that I didn't find in the documentation is to copy over the repositories.xml file from your desktop installation folder to PDI install folder on the server running the cron job - this contains all of the repository connection info. – Nathan Clayton Dec 1 '14 at 19:58
  • Thanks for the cron entry, it will come in handy when implementing the solution. – lufte Dec 5 '14 at 12:58

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.