CODEWITHSUNDEEP
perlcomparisoncompareteradata
Jshee
Jshee

Reputation: 2686

Compare Two Databases Perl

I want to compare the data structures/data types/lengths of two different TERADATA databases residing on two different servers, using Perl

Is it possible to do this?

Added detail I'm trying to compare these two databases (one PROD) and (one DEV) environment databases and compare PROD to DEV and record the differences, I can't export to a flat file, then import into another place because the size of the data being copied is upwards of 500gb.

I'm first trying to compare PROD to DEV server databases, then with the differences in the schemas/datatypes/length, import into DEV from PROD.

Upvotes: 0

Views: 2320

Answers (3)

Jack M.
Jack M.

Reputation: 32070

Doing this in pure Perl, per your question, might be difficult, but it could be done using DBI. Assuming MySQL on DB.YOURDOMAIN.COM and DB2.YOUROTHERDOMAIN.COM:

my $db1 = DBI->connect(
     'DBI:mysql:database=DATABASE1;host=DB.YOURDOMAIN.COM;port=3306',
     'username', 'password',
     );
my $db2 = DBI->connect(
     'DBI:mysql:database=DATABASE1;host=DB2.YOUROTHERDOMAIN.COM;port=3306',
     'username', 'password',
     );

This will get you your two connections. From there, you would just execute the queries to get the job done:

my $sth = $dbh->prepare("show tables");
while (my $row = $sth->fetchrow_hashref) {
   my $table_name = $row->{'Tables_in_DATABASE1'};
   ## Process this particular table.
}

For the individual tables you cal use describe:

my $sth = $dbh->prepare("DESCRIBE table1");
while (my $row = $sth->fetchrow_hashref) {
    my $type = $row->{'Type'}; ## 'int(12)' for example.
    ## Process from there.
}

Upvotes: 2

frezik
frezik

Reputation: 2316

A few different methods come to mind. If both databases are accessible from a single machine, then you can build the data into Perl datastructures, then use Test::More::is_deeply() to check differences.

If the databases aren't both accessible, then build the data in Perl as above, and then use Data::Dumper to print the structure to a file. Make sure to use the Sortkeys option, as well as use the same Indent/Purity/etc. options on both runs. Finally, copy the two output files to the same place and run 'diff' on them.

Upvotes: 1

user554546
user554546

Reputation:

Have you tried querying the metadata on the schemas, tables, and columns (and putting each into a relevant data structure)--for example:

{
   schema1=>
       [{table11=>[[column111,datatype111],[column112,datatype112],...],
        table12=>[[column121,datatype121],...
       ]
    ,schema2=>[{table21=>[[column211,datatype211],[column212,datatype212,...],
        etc...]
}

and then traverse each data structure, comparing each schema, table, and column? Unless you give us some more specifics, I don't think you'll get much more of a useful answer.

Upvotes: 0

Related Questions