Example : Get all tables record count from Old DB & New DB

##Script

##--------->>GET ALL TABLES LIST FROM OLD DATABASE

##--------->>STORE IN TXT FILE(Old_db_tables.txt)

hive -e "use mhtprabhu;show tables;">Old_db_tables.txt
files="$(<Old_db_tables.txt)"

##--------->>GET COUNT FOR OLD DB TABLES(Oldtables.recordcount1.txt)

for tbl1 in $files;
do
recordcount1=`hive -e "select count(*) from mhtprabhu.$tbl1;"`
echo "the table name: $tbl1: Record count:$recordcount1">>Oldtables.recordcount1.txt
done

##--------->>CREATE NEW DATABASE
hive -e "create database mhtprabhu_new;use mhtprabhu_new; "

##--------->>CREATE TABLES IN NEW DATA DATABASE FROM OLD DATABASE
for file1 in $files;
do
hive -e "create table mhtprabhu_new.$file1 as select * from mhtprabhu.$file1;"
done

##--------->>GET ALL TABLES LIST FROM NEW DATABASE, STORE IN TXT FILE(New_db_tables.txt)
hive -e "use mhtprabhu_new;show tables; ">New_db_tables.txt
files2="$(<New_db_tables.txt)"

##--------->>GET COUNT FOR NEW DB TABLES(newtables.recordcount2.txt)
for tbl2 in $files2;
do
recordcount2=`hive -e "select count(*) from mhtprabhu_new.$tbl2;"`
echo "the table name: $tbl2: Record count:$recordcount2">>newtables.recordcount2.txt
done

##--------->>All Details stored in text file
head Old_db_tables.txt Oldtables.recordcount1.txt New_db_tables.txt newtables.recordcount2.txt>final_output.txt

Ouput Verify

[adimulamvenkat19851609@cxln4 ~]$ cat final_output.txt

==> Old_db_tables.txt <==

emp_sample

employee_data

hari_tab

prabhu_tab

student

student4

student5

student_part

==> Oldtables.recordcount1.txt <==

the table name: emp_sample: Record count:20

the table name: employee_data: Record count:40

the table name: hari_tab: Record count:0

the table name: prabhu_tab: Record count:8

the table name: student: Record count:8

the table name: student4: Record count:8

the table name: student5: Record count:5

the table name: student_part: Record count:5

==> New_db_tables.txt <==