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

Miss Zhu 2022-02-13 08:38:47 阅读数:653

hive3 hive detailed tutorial vi

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;

 Insert picture description here

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

 Insert picture description here

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;

 Insert picture description here

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 .

 Insert picture description here

copyright:author[Miss Zhu],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130838449809.html