data cleansing: externally or internally?

#1

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

#2

On 11/04/2011 07:51 AM, geos wrote:

Quote:

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"?

Quote:

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

#3

On Nov 4, 7:51*am, geos <g... (AT) nowhere (DOT) invalid> wrote:

Quote:

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

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

#4

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

Quote:

there is a big text file with dirty data.

How big is "big"?

Quote:

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.

#5

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 hadthe 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 --

#6

On Nov 3, 11:51*pm, geos <g... (AT) nowhere (DOT) invalid> wrote:

Quote:

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

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/artic...taxonomyId=154

#7

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.

#8

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

#9

On Nov 4, 12:51*am, geos <g... (AT) nowhere (DOT) invalid> wrote:

Quote:

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

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

#10

On 11/10/2011 02:48 PM, Ramon F Herrera wrote:

Quote:

On Nov 4, 12:51 am, geos<g... (AT) nowhere (DOT) 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.

Quote:

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