Word Count program in Hive

Question

I'm trying to learn Hive. Surprisingly, I can't find an example of how to write a simple word count job. Is the following correct?

Let's say I have an input file input.tsv:

hello, world
this is an example input file

I create a splitter in Python to turn each line into words:

import sys

for line in sys.stdin:
 for word in line.split():
   print word

And then I have the following in my Hive script:

CREATE TABLE input (line STRING);
LOAD DATA LOCAL INPATH 'input.tsv' OVERWRITE INTO TABLE input;

-- temporary table to hold words...
CREATE TABLE words (word STRING);

add file splitter.py;

INSERT OVERWRITE TABLE words 
  SELECT TRANSFORM(text) 
    USING 'python splitter.py' 
    AS word
  FROM input;

SELECT word, count(*) AS count FROM words GROUP BY word;

I'm not sure if I'm missing something, or if it really is this complicated. (In particular, do I need the temporary words table, and do I need to write the external splitter function?)

score 13 · Accepted Answer · answered Apr 08 '12 at 23:10

13

If you want a simple one see the following:

SELECT word, COUNT(*) FROM input LATERAL VIEW explode(split(text, ' ')) lTable as word GROUP BY word;

I use a lateral view to enable the use of a table valued function (explode) which takes the list that comes out of split function and outputs a new row for every value. In practice I use a UDF that wraps IBM's ICU4J word breaker. I generally don't use transform scripts and use UDFs for everything. You don't need a temporary words table.

answered Apr 08 '12 at 23:10

Steve Severance

6,611
1
33
44

looking at your comment involving explode and lateral view in HiveQL, Can you please take a look into this SO question, I am not able to find the solution for that, [http://stackoverflow.com/questions/11373543/explode-the-array-of-struct-in-hive](http://stackoverflow.com/questions/11373543/explode-the-array-of-struct-in-hive). Sorry for contacting you like this. – arsenal Jul 07 '12 at 22:44
@Steve - I have loaded the data into a table and when I run the command I get `FAILED: Error in semantic analysis: null`. Are there any prerequisites for running the command? – Praveen Sripati Sep 03 '12 at 01:35

score 3 · Answer 2 · edited Jun 25 '14 at 06:21

3

CREATE TABLE docs (line STRING);
LOAD DATA INPATH 'text' OVERWRITE INTO TABLE docs;
CREATE TABLE word_counts AS
SELECT word, count(1) AS count FROM
(SELECT explode(split(line, '\s')) AS word FROM docs) w
GROUP BY word
ORDER BY word;

edited Jun 25 '14 at 06:21

Shevliaskovic

1,562
4
26
43

answered Jun 25 '14 at 06:03

bigmakers

81
3

score 1 · Answer 3 · answered Dec 26 '14 at 08:47

You may sentences built-in UDF in hive as follows:

1) Step 1: Create a temp table with a single column named sentence of data type array

create table temp as select sentence from docs lateral view explode(explode(sentences(lcase(line)))) ltable as sentence

2) Step 2: Select your words from the temp table again exploding the column sentence

select words,count(words) CntWords from
(
select explode(words) words from temp
) i group by words order by CntWords desc

Word Count program in Hive

3 Answers3

Linked