We use Oracle's external tables to process the files (so no need to look for the other alternatives you mentioned). We do not have access to the data providers' network to access the files. That is why we process them after we receive the files.
If there is a database implementation of 'external tables' in the way you mentioned (CSV, probably excel, RSS feeds, web services, etc) at db level, that would be great. Most ETL tools already do accept feeds of the type you mentioned and load those into tables.
Jayadevan
From: Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>
To: Jayadevan M <Jayadevan.Maymala@xxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Date: 01/19/2010 05:31 PM
Subject: Re: postgres external table
On 19 Jan 2010, at 12:16, Jayadevan M wrote:
> Yes. We get quite a few files as 'feeds' from external systems. Once the files are in our network, we know that no changes will happen to those files. We access them using Oracle external tables and process them (the data, after some processing, end up in other real tables). If external tables were not there, we would have had to schedule some job to load these files.
I'm surprised you even need to download them to your network. I suspect it would be quite possible to implement 'external tables' so that they could be read from eg. an RSS feed. That would be a pretty slick feature.
There's a gazillion number of data formats that people would want support for though; CSV, probably excel, RSS feeds, web services, etc.
Most of that should probably be implemented by means of stored procedures (using the unsafe languages - pl/pythonu, pl/perlu, etc) returning TABLE (...), which means this is in fact already possible I think? It's just that nobody's (publicly) thought of doing this so far.
Alban Hertroys
--
Screwing up is the best way to attach something to the ceiling.
!DSPAM:1019,4b559f7b10604920110887!
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
