How to create a unix script to loop a Hive SELECT query by taking table names as input from a file?

Question

It's pretty straightforward what I'm trying to do. I just need to count the records in multiple Hive tables.

I want to create a very simple hql script that takes a file.txt with table names as input and count the total number of records in each of them:

SELECT COUNT(*) from <tablename>

Output should be like:

table1 count1
table2 count2
table3 count3

I'm new to Hive and not very well versed in Unix scripting, and I'm unable to figure out how to create a script to perform this.

Can someone please help me in doing this? Thanks in advance.

leftjoin · Accepted Answer · 2019-07-18 17:44:43Z

4

Simple working shell script:

 db=mydb

 for table in $(hive -S -e "use $db; show tables;") 
 do 
 #echo "$table"
 hive -S -e "use $db; select '$table' as table_name, count(*) as cnt from $table;"
 done

You can improve this script and generate file with select commands or even single select with union all, then execute file instead of calling Hive for each table.

If you want to read table names from file, use this:

for table in filename
do 
...
done

edited Jul 18, 2019 at 17:44

answered Jul 18, 2019 at 9:11

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

LearneR Over a year ago

What am I missing? Is there something else that I need to pass to the script?

leftjoin Over a year ago

Are you executing it in a shell? If you are trying to call beeline (not hive cli) from shell script, look at this: community.hortonworks.com/questions/64438/…

LearneR Over a year ago

I'm executing it in a shell.

LearneR Over a year ago

After modification, my code is as follows: for table in table_list do echo "$table" eval 'hive -e "select count(*) as cnt from dbname.$table;"' done and I execute it by calling: sh loop.sh < list_of_tables.txt but now it gives me the error: Line 1:28 Table not found 'table_list' Any idea why it's unable to associate the input file with the variable table_list in for loop? Also, I did not know how to get the table name in the select query, because the quotes gets messed up.

leftjoin Over a year ago

@LearneR why do you need eval? Script in my answer works, I have tested it. Look also at this: cyberciti.biz/faq/bash-loop-over-file

Collectives™ on Stack Overflow

How to create a unix script to loop a Hive SELECT query by taking table names as input from a file?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related