PERL Beginners - Moving Data from one table to another table

Author

Moving Data from one table to another table

Chris Lyon

2004-12-28, 8:56 pm

Maybe this is a little OT but since I am a beginner @ perl, I thought that I
would post it!

So, I am trying to move data from one sql table to another table called
logs_archive. It seems that I am running into some problems with memory as I
am moving this much data from one table to another. This is just syslog data
but we need to archive it in some way. Can anybody give any advice? Code is
below.

$sth = $dbh->prepare( "select * from logs where ( timestamp <=
'$start_date$start_time' )" );

$sth->execute();

while ( my $ref = $sth->fetchrow_hashref() ) {

$msg_field = $ref->{'msg'};

if ( $msg_field =~ /\'/ ) {

$msg_field =~ s/\'/\\'/g;

}

$string = "INSERT INTO logs_archive ( timestamp, host, facility,
priority, level, tag, date, time, program, msg ) VALUES (
'$ref->{'timestamp'}', '$ref->{'host'}', '$ref->{'facility'}',
'$ref->{'priority'}', '$ref->{'level'}', '$ref->{'tag'}', '$ref->{'date'}',
'$ref->{'time'}', '$ref->{'program'}', '$msg_field' )";

$dbh->do( $string );

}

$string_delete = "delete from logs where ( timestamp <=
'$start_date$start_time' )";

Chris Devers

2004-12-28, 8:56 pm

On Tue, 28 Dec 2004, Chris Lyon wrote:

> So, I am trying to move data from one sql table to another table
> called logs_archive. It seems that I am running into some problems
> with memory as I am moving this much data from one table to another.
> This is just syslog data but we need to archive it in some way. Can
> anybody give any advice? Code is below.

You're archiving syslog data?

Then why pump it into a database to begin with?

Log data `bzip`s wonderfully well, you know...

> $sth = $dbh->prepare( "select * from logs where ( timestamp <=
> '$start_date$start_time' )" );

If you stub in values for $start_date and $start_time, what happens when
you run this statement directly to your database?

You don't mention what database server you're running, but in the SQL
dialects I'm familiar with, '<=' isn't a valid operator I'm aware of.

In any case, you don't really spell out what the problem is. Does this
code work, but run too slowly, or does it not work at all? What happens
when you run this script?

--
Chris Devers

Scott Pham

2004-12-29, 3:56 pm

Depending on what database platform you are using, you can easily do

"SELECT * into new_table from table;

On Tue, 28 Dec 2004 15:50:41 -0500 (EST), Chris Devers
<cdevers@pobox.com> wrote:
> On Tue, 28 Dec 2004, Chris Lyon wrote:
>
>
> You're archiving syslog data?
>
> Then why pump it into a database to begin with?
>
> Log data `bzip`s wonderfully well, you know...
>
>
> If you stub in values for $start_date and $start_time, what happens when
> you run this statement directly to your database?
>
> You don't mention what database server you're running, but in the SQL
> dialects I'm familiar with, '<=' isn't a valid operator I'm aware of.
>
> In any case, you don't really spell out what the problem is. Does this
> code work, but run too slowly, or does it not work at all? What happens
> when you run this script?
>
> --
> Chris Devers
>
> --
> To unsubscribe, e-mail: beginners-unsubscribe@perl.org
> For additional commands, e-mail: beginners-help@perl.org
> <http://learn.perl.org/> <http://learn.perl.org/first-response>
>
>

Chris Lyon

2004-12-29, 3:56 pm

> -----Original Message-----
> From: Chris Devers [mailto:cdevers@pobox.com]
> Sent: Tuesday, December 28, 2004 12:51 PM
> To: Christopher Lyon
> Cc: Perl Beginners List
> Subject: Re: Moving Data from one table to another table
>
> On Tue, 28 Dec 2004, Chris Lyon wrote:
>
>
> You're archiving syslog data?
>
> Then why pump it into a database to begin with?
>
> Log data `bzip`s wonderfully well, you know...

We have a couple of scripts that pull over the data in the DB to gather
stats look for certain things and we only need this data for a day or so.
Then it gets moved to other tables for reference for up to a w

. Once the
w

is over all the data gets moved to a file and the tables get hacked.

>
>
> If you stub in values for $start_date and $start_time, what happens when
> you run this statement directly to your database?
>

Runs just fine. When I vmstat while this is running the free memory goes
down and down until there is almost nothing left.

> You don't mention what database server you're running, but in the SQL
> dialects I'm familiar with, '<=' isn't a valid operator I'm aware of.
>
> In any case, you don't really spell out what the problem is. Does this
> code work, but run too slowly, or does it not work at all? What happens
> when you run this script?
>

MySQL is what we are running and the operator was something left over from
another script.

As for what the real problem is: Resources and slowness is the main problem
and every so often the script won't complete.

>
>
> --
> Chris Devers

Chris Lyon

2004-12-29, 3:56 pm

> -----Original Message-----
> From: Scott Pham [mailto:scott.pham@gmail.com]
> Sent: Tuesday, December 28, 2004 12:55 PM
> To: Perl Beginners List
> Cc: Christopher Lyon
> Subject: Re: Moving Data from one table to another table
>
> Depending on what database platform you are using, you can easily do
>
> "SELECT * into new_table from table;
>

So, just the select * into.... will replace the entire select * from... and
then a insert into....?
I can rip out the while statement and just do this select and the delete.

That might fix the problem.

[color=darkred]
>
> On Tue, 28 Dec 2004 15:50:41 -0500 (EST), Chris Devers
> <cdevers@pobox.com> wrote:

Sponsored Links