data cleansing: externally or internally?

[geos]

there is a big text file with dirty data. a company wants it to be
clean. there are some known patterns expressed as like or regexp. I
first thought about two approaches:
1) do this on the system level
2) or in a database
for the latter case it looks to me that I could use external tables or
load data into temporary table and then do the cleaning.

I am looking for pros and cons of each variant. my intuition tells me
that loading into temporary table would give the most flexibility but
also take additional space. I am not sure about the other methods. I
would appreciate your opinion about what I should pay attention to when
choosing the other methods. how are they restricted in terms of
performance, flexibility and capabilities (eg. multitable loading)? I am
also interested in good practices and your experience in similar cases
you can share.

thank you,
geos

--
NOTE: Follow Up set to comp.databases.oracle.misc

[Robert Klemme]

On 11/04/2011 07:51 AM, geos wrote:
> there is a big text file with dirty data. a company wants it to be
> clean. there are some known patterns expressed as like or regexp. I
> first thought about two approaches:
> 1) do this on the system level

What exactly do you mean by "system level"? Is this an alias for
"without database"?

> 2) or in a database
> for the latter case it looks to me that I could use external tables or
> load data into temporary table and then do the cleaning.
>
> I am looking for pros and cons of each variant. my intuition tells me
> that loading into temporary table would give the most flexibility but
> also take additional space. I am not sure about the other methods. I
> would appreciate your opinion about what I should pay attention to when
> choosing the other methods. how are they restricted in terms of
> performance, flexibility and capabilities (eg. multitable loading)? I am
> also interested in good practices and your experience in similar cases
> you can share.

There are a lot of questions to be asked before one can come up with
reasonable advice:

Is this a one off task or needs to be done on a regular basis?
What data is contained in the file?
What format is the file?
How large is the file?
Where does the data ultimately go (next processing stage)?
What does "dirty data" mean? Put differently: what kind of cleanup
activity do you need to do?
What other requirements are there (execution speed, development time /
cost)?

Cheers

robert

[mhoys]

Hi,
I've always done it with external/temp tables... but that's because
I'm more familiar with (PL/)SQL than with shell scripting. You could
write your cleaning functions/procedures once and put them in a
package for later reuse. And if you know APEX, it's easy to build a
web interface around them. Some things are also more easy to do with
SQL than with shell scripting, for example data aggregations. I'm not
sure about the performance. Maybe Perl might perform better on very
large flat files (this could also be dependent on the type of file
system).

HTH,
Matthias

[Deadly Dirk]

On Fri, 04 Nov 2011 07:51:49 +0100, geos wrote:

> there is a big text file with dirty data.

How big is "big"?

> a company wants it to be
> clean. there are some known patterns expressed as like or regexp. I
> first thought about two approaches:
> 1) do this on the system level
> 2) or in a database

Database is not well suited for things like that. Personally, I would use
Perl. Perl is my favorite tool because it's extremely versatile and fast
but any scripting language with regex support will probably do.





--
I don't think, therefore I am not.

[Mark D Powell]

Robert has listed some good points. The devil is always in the details. What does 'dirty' mean in this context. Is the data dirty cause someone had the shift key depresssed when he or she tried to enter a numeric digit or it is dirty because a related reocord or table row (FK) does not exist for some of the data? Perhaps both conditions are true.

You may in fact want to filter the data via shell scripts for some of the issues and then use Oracle tools for other issues.

HTH -- Mark D Powell --

[joel garry]

Out of database. I've done this so many times, whenever the database
is the limiting factor I take it out of the db, clean, and put it back
in. Two biggest factors are redo generation and memory limitations,
the latter of which are handled better by unix and pipelining. Plus,
it's easier to split transforms into little pieces and pipe them
together.

jg
--
@home.com is bogus.
http://www.computerworld.com/s/article/9221459/Oracle_s_best_of_breed_strategy_as_described_by_president_Mark_Hurd?taxonomyId=154

[Cliff]

My 2 cents and IMHO:

Database

CON

- additional storage space

- increased backup time as you'd basically be making multiple copes of
your data as you transform it

-version control - Do not do this in the database as it's easier to
keep track of your transformations via file names than via multiple
tables in the database.


PRO

- If you're transforming current production data being used every
day, you know you have the current dataset all of the time. However,
I would write some pre-code to verify my assumptions prior to each run.



External file


CON

- transforming external to the database can be more complex if you
have multiple decision points in your transformation rules.

- Some editing tools choke on huge file sizes

PRO


- easier to export out of the database and perform multiple small
transformations with your favorite tool ( sed and awk are mine) and
easier to debug.

- the additional file space used by the transformation iterations are
easier to recover from file manipulation than by shrinking a tablespace
or creating a hughe tablespace specifically for this task.


- Depending on whether this is a one-off or a monthly clean-up
script, consider having your script run in 2 modes, Proposed changes
and changes that were made; that way you can get business sigh-off on
what your script is actually doing.

[geos]

thank you for all your answers!

you pointed out many interesting things so that I have to think about'em
before I write more in the subject.

thank you,
geos

[Ramon F Herrera]

After more than a decade of experience my advice to you is: Use Oracle
as little as possible.

I wrote all my business logic in C/C++ making calls to the database
only as needed, and now my applications run much, much, much, much
faster. Not to mention the improved development and debug (can use a
debugger, not sure whether Oracle has something similar).

In essence, the only commands that I run in the database are basic
ones such as SELECT and UPDATE. No IFs or BUTs.

-Ramon

[Robert Klemme]

On 11/10/2011 02:48 PM, Ramon F Herrera wrote:
> On Nov 4, 12:51 am, geos<g...@nowhere.invalid> wrote:
>> there is a big text file with dirty data. a company wants it to be
>> clean. there are some known patterns expressed as like or regexp. I
>> first thought about two approaches:
>> 1) do this on the system level
>> 2) or in a database
>> for the latter case it looks to me that I could use external tables or
>> load data into temporary table and then do the cleaning.
>>
>> I am looking for pros and cons of each variant. my intuition tells me
>> that loading into temporary table would give the most flexibility but
>> also take additional space. I am not sure about the other methods. I
>> would appreciate your opinion about what I should pay attention to when
>> choosing the other methods. how are they restricted in terms of
>> performance, flexibility and capabilities (eg. multitable loading)? I am
>> also interested in good practices and your experience in similar cases
>> you can share.

You still did not disclose the type of processing you want to do.
Without that information advice cannot be targeted at your scenario.

> After more than a decade of experience my advice to you is: Use Oracle
> as little as possible.
>
> I wrote all my business logic in C/C++ making calls to the database
> only as needed, and now my applications run much, much, much, much
> faster. Not to mention the improved development and debug (can use a
> debugger, not sure whether Oracle has something similar).
>
> In essence, the only commands that I run in the database are basic
> ones such as SELECT and UPDATE. No IFs or BUTs.

This cannot be generalized as advice! You do not even mention the type
of application(s) you are talking about. What may work good for the
application types you work on may not work at all for other application
types.

Kind regards

robert

[joel garry]

I agree. All the answers added together are pretty good advice, I
could have qualified mine better with something about what database
limitations I was referring to.

When I first switched from VMS to the unix world, I took over
maintenance on a system that was like Ramon described, mostly because
of the limitations of the db engine (Unify, things like not being able
to join 5 tables). That is when I came to the conclusion that
whatever else you like about C, using it directly as an application
language is just a huge mistake. I can't judge C++, as I never
learned any good theoretical foundation for it (which could be my
shortcoming, or not). The only rational rationalization for these
languages is the large number of CS students that get trained in them,
and that could be a mistake too. And these mistakes keep getting made
over and over. People tend to favor what they know, and favor new
code or "paradigms" over long term maintenance.

jg
--
@home.com is bogus.

Talk about dirty data left behind...
http://www.signonsandiego.com/news/2011/nov/14/buyer-of-minivan-finds-hidden-500k-in-cocaine/?ap