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?