Hive3 detailed tutorial (VI) word frequency statistics based on hive3

1. Prepare word file words.txt

apple orange pear apple
banana peach pear
orange apple

2. newly build word_r surface

create table word_r(
line string
)

3.load Enter data into table

load data local inpath '/apps/words.txt' overwrite into table word_r;

4. verification

select * from word_r;

The above is essentially to txt Row data in , Conversion to database line In one cell of the column .

5. Write a query sql：
explode Use of functions , Its function is to organize an array in the form of columns

You can start with beeline Execute the following statement in , take line The cell data of the column is displayed in the form of a column

select explode(split(line,' ')) as word from word_r

And then group count Aggregate statistics , And sort the words in ascending order

select word,count(1) as count from
(select explode(split(line,' ')) as word from word_r) w
group by word
order by word;

Write this... Every time you execute it sql, More complicated , Can pass create select To create a new table ：

create table word_count as
select word,count(1) as count from
(select explode(split(line,' ')) as word from word_r) w
group by word
order by word;

Use later

select * from word_count

You can query .