Count(*)in Hive

A simpe count Query

select count(*) from stack_overflow_tags;

Group by in Hive

The output is too big. We need to give the output file path. This command can be executed in a non-hive terminal

hive -e "select tag1, count(*) as tag1_count from stack_overflow_tags group by tag1 order by tag1_count DESC" > /home/hduser/Output/hive_out_tag1.txt

Have a look at the exported file. Do this in a new (non-hive) terminal

cat /home/hduser/Output/hive_out_tag1.txt

Word count in Hive

This command can be executed in a non-hive terminal

hive -e "SELECT word, count(1) AS count FROM (SELECT explode(split(tag1, '\s')) AS word FROM stack_overflow_tags) w GROUP BY word ORDER BY count DESC" > /home/hduser/Output/hive_out_wordcount.txt

Have a look at the exported file. Do this in a new (non-hive) terminal

cat /home/hduser/Output/hive_out_wordcount.txt

Executing a script from a file

Create a hive query script file. Fill file with queries

mkdir /home/hduser/codes/   

gedit /home/hduser/codes/my_query.hql

fill the above file with below query

select * from stack_overflow_tags LIMIT 3;

use source to execute it from hive

hive
source /home/hduser/codes/my_query.hql;