2

In analyzing some of our business data, we're realizing that we need to join data across disparate data sources. For example, our application data is warehoused in Postgres (ported from MongoDB via MoSQL), while our purchase info (subscriptions, financial info, etc.) is archived in SQL Server.

I've written a basic command line tool using Python to prove that I can move data from Postgres to Postgres and from SQL Server to Postgres, after which I would manipulate the data using plain SQL. This looks like it could work, but I'm quickly realizing that I'll probably run into lots of data translation issues (e.g. None needs to be \N) along the way and there's probably a tool that does this.

I'm having a hard time searching for tools that can be run from the command line. CloverETL showed up in several searches, but that seems pretty complicated for what I need (who knows—maybe I need something complicated). Rather, I'm looking for something where I can give the SQL for one data source and have the tool compensate for the differences and insert that data into a disparate data source. I want to develop on Mac and deploy on *nix.

Are there good, lightweight, scriptable ETL tools that would help with this process?

closed as off-topic by Philᵀᴹ, Paul White, RolandoMySQLDBA, RLF, Michael Green Jan 24 '15 at 10:07

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." – Philᵀᴹ, Paul White, RolandoMySQLDBA, RLF, Michael Green
If this question can be reworded to fit the rules in the help center, please edit the question.

3

Pentaho have an open-source tool called Kettle that we use for ETL development with Postgres. It is quite good and very comprehensive.

http://community.pentaho.com/projects/data-integration/

2

First of all: Why the heck would you move data FROM SQL Server TO Postgres in order to use SQL to manipulate it? Do you realise that Postgres does not have a parallel query executor while SQL Server has? This means that even on a tiny server, a data warehouse data manipulation query will run several times faster on SQL Server than it does on Postgres . There is a good reason that you don't find any decent TPC-H benchmarks that run Postgres. The other way around, using SQL Server as the data manipulator, would make more sense. If you do decide to move from Postgres to SQL, using SQL Server Linked servers via the ODBC Postgres driver would allow you to write queries directly in SQL Server that access table in Postgres and move data between the two databases to get co-location when needed.

If you have some hard requirement (apart from not liking SQL Server) to move data from SQL Server into Postgres and you are concerned about speed, the easiest way to do this is probably to use SQL Server's BCP utility (which you can invoke from a *Nix command line) to dump data out to a CSV file and use Postgres COPY to import the result. Each database will then handle the casting between data types using its native functionality and you don't need to worry to much about implementation details. If you generally stick to ANSI compliant data types (supported by both SQL Server and Postgres) you should be fine moving things via CSV files like this.

You can use an SMB share as the place to dump the data, assuming you can find a decent Samba implementation that can run SMB fast enough to keep up with the speed SQL Server can dump at (SQL Server will easily dump at in the range GB/sec to an SMB share when set up right). Alternatively, you can use an iSCSI target as the place to dump the CSV files.

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