question Re: How to get the total number of rows from multiple tables in a database in Archives of Support Questions (Read Only)

How to get the total number of rows from multiple tables in a database?

Abhinay — Fri, 16 Sep 2022 09:41:00 GMT

In my db i have many tables that start with 'bza' in table name.

I wrote all those table names into a text file name as tables.txt

Now using a shell script (count_row.sh) I am reading each line from tables.txt and execuing the hive commnad:

while IFS='' read -r line || [[ -n "$line" ]]; do
echo ""
echo ""
echo ""
echo "Counting the table : $line"
eval "hive -e 'select count(*) from $line'"
done < "$1"

This results in total rows from each table, which I am writting it into a text file rows.txt as

$ ./count_row.sh tables.txt > row.txt

Now my task is I want to count the total number of rows from all the tables by summing the individual result.

Is there any way to achieve that ? Or any alternate way to count the rows from mutliple tables.

Re: How to get the total number of rows from multiple tables in a database?

Harsh J — Sun, 20 Sep 2015 14:37:01 GMT

Should be a simple extension in bash, if that is what you're looking for:

./count_row.sh tables.txt | paste -s -d+ - | bc

Ref: http://stackoverflow.com/questions/450799/shell-command-to-sum-integers-one-per-line#comment12469220_451204

P.s. It may be more efficient to generate a list of queries and run it via a single hive command, cause each command runs a whole new JVM.

Re: How to get the total number of rows from multiple tables in a database?

Abhinay — Tue, 22 Sep 2015 08:39:29 GMT

@Harsh J
By saying "Generate a list of queries" you mean, creating a list of queries and separate by ;(semicolon) in hive command like
hive -e 'query1;query2;...queryN' ?

Re: How to get the total number of rows from multiple tables in a database

Harsh J — Tue, 22 Sep 2015 09:26:55 GMT

Yes that could work too (or a file with them, passed via -f or such).

Re: How to get the total number of rows from multiple tables in a database

Abhinay — Tue, 22 Sep 2015 13:17:37 GMT

@Harsh J
Using hive -f a file can be passed, but the main purpose of writing the loop in script is to automate the process. I have to apply the same command for 250 tables.

Re: How to get the total number of rows from multiple tables in a database

Harsh J — Tue, 22 Sep 2015 13:19:55 GMT

Right, it was suggested as an optimisation aside of the summing question,
given the described example. Does the bc command not solve your original
question?

Re: How to get the total number of rows from multiple tables in a database

Abhinay — Tue, 22 Sep 2015 17:05:24 GMT

I was just asking it. yes it solved using bc

Re: How to get the total number of rows from multiple tables in a database

Harsh J — Tue, 22 Sep 2015 17:17:55 GMT

Glad to know! Please consider marking the thread resolved, so others with a
similar question can find a solution quicker.

Feel free to post a new thread with any further questions.

Re: How to get the total number of rows from multiple tables in a database?

PasLeChoix — Sat, 06 May 2017 01:22:30 GMT

It doesn't work for me, here is the screenshot:

What am I missing here?

Thank you very much.

Re: How to get the total number of rows from multiple tables in a database?

SatyamKumar — Wed, 31 Jul 2019 11:26:32 GMT

Done keyword is missing in your script. In your loop body use done at last.