Compare Table Row Counts Between Databases

Moderator: NorbertKrupa

Post Reply
debfawn
Beginner
Beginner
Posts: 47
Joined: Tue Jan 29, 2013 2:30 pm

Compare Table Row Counts Between Databases

Post by debfawn » Tue Sep 17, 2013 3:27 pm

Hello,

I recently copied a database from one cluster to another by copying all of the tables. Is there an easy way to compare the row counts for each table between the old and new clusters so that I can verify that the copy was successful?

Thank you

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Compare Table Row Counts Between Databases

Post by id10t » Tue Sep 17, 2013 4:22 pm

Hi!

Manually or script (or special apps like QualityGates - http://quality-gates.com/)

Example of script:

Code: Select all

#!/bin/bash

SRC_DB=localhost
TRG_DB=127.0.0.1

function sql
{
    HOST=$1
    QUERY=$2
    /opt/vertica/bin/vsql -h ${HOST} -Atqnc "${QUERY}"
}

echo
echo 'COMPARE TABLES ROW COUNT:'
echo '-------------------------'
echo

ALL_TABLES=`sql ${SRC_DB} "SELECT schema_name || '.' || table_name FROM all_tables WHERE table_type = 'TABLE'"`

for TABLE in ${ALL_TABLES}; do
  SRC_CNT=`sql ${SRC_DB} "select count(*) from ${TABLE}"`
  TRG_CNT=`sql ${TRG_DB} "select count(*) from ${TABLE}"`
  if [[ src_cnt -ne trg_cnt ]]; then
    echo -e "ERROR: ${TABLE}"
  else
    echo -e "OK   : ${TABLE}"
  fi
done

exit 0
Output (of cause everything will be equals, because I use in same DB):

Code: Select all

daniel@synapse:/tmp$ ./debfawn 

COMPARE TABLES ROW COUNT:
-------------------------

OK   : online_sales.call_center_dimension
OK   : online_sales.online_page_dimension
OK   : online_sales.online_sales_fact
OK   : public.all_types
OK   : public.char
OK   : public.customer_dimension
OK   : public.date_dimension
OK   : public.employee_dimension
OK   : public.int1
OK   : public.int2
OK   : public.inventory_fact
OK   : public.product_dimension
OK   : public.promotion_dimension
OK   : public.rand_nums
OK   : public.shipping_dimension
OK   : public.vchar
OK   : public.vendor_dimension
OK   : public.warehouse_dimension
OK   : store.store_dimension
OK   : store.store_orders_fact
OK   : store.store_sales_fact
Output should be as follow, if rows count not equals:

Code: Select all

COMPARE TABLES ROW COUNT:
-------------------------

ERR   : online_sales.call_center_dimension
ERR   : online_sales.online_page_dimension
ERR   : online_sales.online_sales_fact
...

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Compare Table Row Counts Between Databases

Post by id10t » Tue Sep 17, 2013 11:29 pm

HI!

2 Vertica servers...? PROD and DR? For DR may be next algorithm will be a better choice then incremental backup:
* CONNECT (Connects to another Vertica database to enable data import )
* COPY FROM VERTICA (Copies data from another Vertica database. )

All this you can automate by scheduled script (or External Procedure) that will update DR every "X time". In such way you will be sure all table have same rows.

Post Reply

Return to “Vertica Migration”