Open Source ETL

[Chad Cottle]

Hi all.  Anyone have any experience using Open Source ETL tools?  From my limited research there doesn't seem to be any true open source offerings as they all have some sort of pay structure attached to them.  That stated, we need to begin some large-scale data integration projects and don't want to spend a great deal of money (obv.) but need to solve this problem.

Anything else out there that I might not know about?  The data that needs to be wrangled ranges from SQL Server, Oracle, Excel, Access and CSV/Tab-delimited.

The first cut of this is collecting simple information (fields) from all of these systems to aggregate things like:  Date, Time, Address, Issue (i.e. What?), Business Unit.

The goal is to track incidents/requests occurring around the city that exist in isolation now because of separate databases.

For example, a call comes in to the city's 311 about a sewer overflow.  This is recorded as an incident and is logged, tracked and worked.  In aggregating existing data that now exists in silos, they could say "oh yeah, 123 Anywhere St has a known sewer main break."

Would love to hear some thoughts on this.

Thanks, Chad

[Chase Southard]

Chad, 

Check out Google Refine. http://code.google.com/p/google-refine/

Not sure if it can connect to SQL servers. 

Chase

--
You received this message because you are subscribed to the Google Groups "Collexion" group.
To post to this group, send email to coll...@googlegroups.com.
To unsubscribe from this group, send email to collexion+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/collexion?hl=en.

--

R. Chase Southard

http://about.me/southard

[Chad Cottle]

Cool.  It's a start...and I never knew it existed.  Step in the right direction.  Thx Chase!!!

[Andrew Wyllie]

Are you thinking about an application that can query all of your datasources in real time , or are you thinking about creating
a master database that will be updated from all of the possible sources? If you want to gather the data in a big
database, I might consider using a postgresql database and importing the data using something like perl - specifically the
perl DBI code - which can talk to lots of different databases. You'd have to write some code for each data source, but I
think you'll wind up doing that regardless of what solution you chose.


Andrew

[Daniel Cotter]

I was going to suggest Perl, too. My experience with Microsoft's ETL tool, Business Intelligence Data Studio (BIDS), has been that it is at least as much work, if not more, to learn the tool and its idiosyncrasies as it would be just to write the equivalent script, with the added disadvantage of its only working on one particular system, and a proprietary one at that. That said, I've never used Google Refine, so it may be totally awesome.

Daniel

[nx]

Yeah, I also agree with just building a custom system, rather than
working within the limitations of some ETL app.

- build or use an existing system that displays the data
- build or use an existing system that will store the imported data for display
- write importers for each data source
- automate data import for each source using protocols like SSH, HTTP,
FTP or an external JSON API

[Janine Hempy]

Holy moly, someone finally started speaking my language.

I concur with Andrew: knowing a little more about the destination of the data, the format it will be in, and the way it will be accessed will make the difference between random suggestions and solid ideas for how to tackle this task.

Perl is awesome for transforming data. It looks like at least some of your data sources are Windows-based, and Perl works just dandy on Windows. We use it heavily for accessing SQL Server via the ODBC DBD. ODBC will also let you connect to Access, and Oracle with a little finesse (though there's a separate DBD for Oracle). CSV files can be handled straight up. There are modules for reading from Excel spreadsheets.

I think your primary challenge - but I'm only guessing because I don't know the full details of all the systems you are trying to tie together - is going to be timing. Is your master data source supposed to stay up-to-the-moment current with all it's feeders? If so, good luck because you'll need it to program around so many changing databases and text files. If you can update the master at predictable, scheduled times, then you are golden. 

The devil in the details is likely to be conversion of memo/large text fields and dates. This is primarily why I favor a custom solution. You're going to have to do so much research to map datatypes from your sources into your master that you might as well spend a little more time and code it out, too. That will also give you an easy way to handle idiosyncrasies of the contents of the fields in addition to doing simple type conversion.

On Wed, May 16, 2012 at 9:49 AM, Andrew Wyllie <wyl...@dilex.net> wrote:

[Andrew Wyllie]

Another issue is going to be tracking data.   You will need some way to relate the data in the various database to

your master database so that if someone updates the data somewhere else, you know which field to update

in the master.  For some sources this won't be a big deal as they will have some type of primary key, but staying

synced up with someones Excel spread sheet may be challenging.

Andrew

[Chad Cottle]

Wow Collexioners rock!!!!

A little more detail:
***********************
The data presentation/master view will be read-only as it is for the Mayor's senior staff to look at development and current issues....a one-way only update.

Real time in a perfect world...current to a daily level or near time is just dandy.

No large blobs, clobs, etc.  It is merely a few simple fields like ADDRESS, DATE, INCIDENT DESCRIPTION (no more than 256 characters), OWNER.

It is going to answer the question:  what is being built today and where are the problems with development today.

I agree that ultimately it will be custom as this is only the beginning but I will look at this Google thing later today.  This is not data mining yet.  It's more like simple aggregation.
*************************

I appreciate EVERY BIT of feedback.  Love the discussion...keep it coming.

Also, I have professional services money available if someone is interested in picking up some spending money to get this completed on a real quick timeline.  It's pretty simple methinks but I haven't touched PERL in 8 years.

[nx]

What are the environments like for these data sources? Are they hosted
on servers that are always on where a daemon like cron could run
something to export the data to the master app?

[Andrew Wyllie]

I think if you want it real time (or really close time) you will need to somehow modify each of the source databases so that they know they need to update the master when committing changes.  Otherwise, you could just pull from all the source databases at some scheduled time in such a way that you can live with the currentness of your data.  You could also mix and match so that some of the systems update in real time, while others come in daily. 

One problem will be whether the sources will be up when you query them - like a spread sheet on someone's desktop may not always be available.

Andrew

[Chad Cottle]

Good question Chase.  Almost all are on a vmware farm running windows except for the Oracle stuff which runs on AIX.  All are on the same LAN so there are generally no connectivity issues.  The big 3 that are needed first are all SQL Server (which is the easiest fruit to pick)...it's setting ourselves up to successfully get the other "less available" datasets that will make this successful.  And it may make it easier to have the data that someone is keeping in Access/Excel just loaded into a small footprint SQL db.

If you are interested hit me up off-list, FB or at my work account and we can discuss.  I think an hour face to face consult to explain the parts and pieces would be all that you would need to get this going.

Again, this is a real quick bandaid that feeds into the city's ability to eventually get grant funding to the tune of multiple millions of dollars.  This proof of concept aggregation step will both help manage the larger problem, demonstrate the need for a real fix and ultimately make Lexington better.  :)

Oh, and lest I forget, another important piece will be adding a flat file of geo-located crime data to this master presentation.  This will answer the question is development affecting crime and vice-versa (i.e safe by design theory).

I could work up a simple requirements definition if that would help.

Thanks, Chad

[Chad Cottle]

Andrew, good comments.  For the RDMS-based stuff we can just add a trigger that would push new data.  For the other stuff, yep...you got it right.  There is not an easy way to dump AS400, Excel and other data out when a certain condition is met without a good deal of work.  So I guess the bar is being lowered.  How about as recent as you can damn well get it?  :)  I just need a clever acronym for that.

[nx]

Yeah, you really need to know the whole story behind how the
spreadsheets are maintained. There may be a Ticket # column already.
If there is no unique ID and all the tickets are on a single sheet and
inserted from the top the last row could be ID#1. Another option is to
just hose all records stored by that spreadsheet importer and reimport
them fresh each time.

[Chad Cottle]

You are absolutely correct sir.

[Aaron Arnett]

Someone has already mentioned using PostgreSQL... Specifically you may want to take a look at the Foreign Data Wrapper feature...

I'm not terribly familiar with it, but AFAIK it allows you to wrap a variety of external (specifically non-Postgres, and often non-relational) data sources and treat them as Read-Only/Queriable tables as if they were native within the database.

Results would then be "ARAYCDWGI" since they can be aggregated at query time.

http://wiki.postgresql.org/wiki/Foreign_data_wrappers

Aaron

[Chad Cottle]

Aaron, you use of that acronym wins the Internet today.  I love it.  Great suggestion.

[ben jeffries]

Aggragating Data from the DBs (sql, ora, access) shouldn't be a big deal regardless of how you go about doing it.  Reading the data out of  file is easy enough, but in my experences the integrity of that data comes into question alot more often. 

 

 I know this is beyond the scope of you question, (which was just "how can this be done")   but what is the user story for aggragating data from a file?  By this i mean who is doing the aggragating? and who is the owner/originator of the file?   and how accurate is the data coming in from Files? and whats the scenario for getting the aggragator access to the files?

 

I've had to deal with alot of this with some of our clients where they are using Files as perminant data storage mechanisms.  When you get into using Files for data storage, you can really start running into problems with data integrity (stale data, multiple owners, conflicting records, etc) that Databases don't get into by virtue of there very nature.  

 

I'm not sure if that is what your dealing with here; but if it is, in my experiance, it tends to create its own set of problems which should probably be taken into account (as much as possible) from the start.

 

-ben

 

On Wed, May 16, 2012 at 3:59 PM, nx <n...@nu-ex.com> wrote:

[Andrew Wyllie]

Whoah! That Foreign Data Wrapper stuff looks really cool.  My concerns would be speed and how gracefully the thing

fails when one of the sources is offline.  Still, even just as a mirroring tool, this could be a very effective solution.

Andrew

[Chad Cottle]

Ben, I completely agree with your comments as they are spot on.  Fortunately, the one or two spreadsheets that are needed are owned by one person and the integrity of the data is not so much an issue because it's, well, it's kind of a development barometer that we are going for and not meant to do heavy duty informed decision maker.  It's more of painting with a roller and not a really fine brush.

I hope that makes sense.

[Chris Harn]

If it was mine to do I would of course use the tools I’m familiar with.

 

In this case, either:

1. A Sybase SQL Anywhere database with an event configured to periodically call a set of stored procedures written as SQL that would select some or all of the data out of a “remote server” and insert it into the local database.  Remote Servers are typically ODBC connections which I believe can even be as simple as an Excel spreadsheet.
2. Some type of custom fat client program that would open one data set and write to the other.  In my case we use Clarion RAD, but I doubt you would want to pick up such an old tool.
3. Off the shelf... this may work for you as well: http://www.dmc-fr.com/key_features_en.php (I would ensure it works in demo before paying for the program though, this programmer is very accessible via skype.)

>> > mailto:collexion%2Bunsu...@googlegroups.com.

>> > For more options, visit this group at
>> > http://groups.google.com/group/collexion?hl=en.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Collexion" group.
>> To post to this group, send email to coll...@googlegroups.com.
>> To unsubscribe from this group, send email to

>> mailto:collexion%2Bunsu...@googlegroups.com.

>> For more options, visit this group at
>> http://groups.google.com/group/collexion?hl=en.
>>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Collexion" group.
> To post to this group, send email to coll...@googlegroups.com.
> To unsubscribe from this group, send email to

> mailto:collexion%2Bunsu...@googlegroups.com.

> For more options, visit this group at
> http://groups.google.com/group/collexion?hl=en.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Collexion" group.
> To post to this group, send email to coll...@googlegroups.com.
> To unsubscribe from this group, send email to

> mailto:collexion%2Bunsu...@googlegroups.com.

> For more options, visit this group at
> http://groups.google.com/group/collexion?hl=en.

--
You received this message because you are subscribed to the Google Groups "Collexion" group.
To post to this group, send email to coll...@googlegroups.com.

To unsubscribe from this group, send email to mailto:collexion%2Bunsu...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/collexion?hl=en.

 

 

--
You received this message because you are subscribed to the Google Groups "Collexion" group.
To post to this group, send email to coll...@googlegroups.com.

To unsubscribe from this group, send email to mailto:collexion%2Bunsu...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/collexion?hl=en.

 

 

--
You received this message because you are subscribed to the Google Groups "Collexion" group.
To post to this group, send email to coll...@googlegroups.com.

To unsubscribe from this group, send email to mailto:collexion%2Bunsu...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/collexion?hl=en.

 

 

--
You received this message because you are subscribed to the Google Groups "Collexion" group.
To post to this group, send email to coll...@googlegroups.com.
To unsubscribe from this group, send email to collexion+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/collexion?hl=en.

--
You received this message because you are subscribed to the Google Groups "Collexion" group.
To post to this group, send email to coll...@googlegroups.com.

To unsubscribe from this group, send email to mailto:collexion%2Bunsu...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/collexion?hl=en.

[Thomas D'Andrea Jr.]

Talend Open Studio

Pretty darn nice, I used it several times at the city because you can export the code (it's in java) and customize it completely.

[Thomas D'Andrea Jr.]

Also, yes there is a lot to learn about ETL processing, but once you understand it, you can kind of run with other ETL tools. I am using BIDS and SSIS now and though I do like the way TOS and DataStage look a bit better, the fact that you can fully customize SSIS with .NET architecture has been a big bonus.

[Chad Cottle]

Just a quick love note to all you guys...the Collexion geeks are absolutely the best.  I love all the ideas thrown out here.

I spoke to "The Beard" about this tonight at the ODC gathering and he is willing to jump in and help given that this proof of concept could lead to Lexington getting some 7 figure lovin' via a grant.

I am going to look into what all you guys and gals (hugs to Jeanine) have suggested and I will update everyone on progress.
Chad

On Wed, May 16, 2012 at 10:21 PM, Chad Cottle <opend...@gmail.com> wrote:

Hi Andrew.  Sounds good.  We can set up a time to meet over coffee or a pint.  I too am a, um, er, mature student.  ;)  I love these guys on the list but most of them are 10 to 20 years my junior.

You have some time next week?

Chad

On Wed, May 16, 2012 at 5:40 PM, Andrew Wyllie <wyl...@dilex.net> wrote:

Hi Chad,

I'm interested in this project you are working on.  I'm currently back in school as a "Mature Student" doing a Ph.D. in Economics,
but I have worked as a software developer for about 20 years now - pretty much all internet and backend web programming.
Anyway, let me know if you need some help or just want to bounce ideas around.  I have a major set of exams coming up
but after the first week of June I will have a lot of free time on my hands (well, maybe not a lot as my wife has a list of stuff
I have been putting off for a few months now).

thanks,
Andrew