侧视图
将employee表中的work_place(array)列 炸裂开
0: jdbc:hive2://192.168.153.139:10000> select explode(work_place) from employee;
0: jdbc:hive2://192.168.153.139:10000> select name, wp from employee lateral view explode(work_place) work_place as wp;
将employee表中的skills_score(map)列 炸裂开
0: jdbc:hive2://192.168.153.139:10000> select explode(skills_score) from employee;
0: jdbc:hive2://192.168.153.139:10000> select name,skill,score from employee lateral view explode(skills_score) sk_sc as skill,score;
0: jdbc:hive2://192.168.153.139:10000> select name,wp,skill,score from employee
0: jdbc:hive2://192.168.153.139:10000> lateral view explode(work_place) wo_pl as wp
0: jdbc:hive2://192.168.153.139:10000> lateral view explode(skills_score) sk_sc as skill, score;
WordCount案例 explode始用
0: jdbc:hive2://192.168.153.139:10000> create table wordcount(line string);
[root@gree139 tmp]# vi ./wc.txt
hello world
hello java
hello hadoop
hello hive
are you ok?
no i'm very bad
0: jdbc:hive2://192.168.153.139:10000> load data local inpath '/opt/tmp/wc.txt' overwrite into table wordcount;
0: jdbc:hive2://192.168.153.139:10000> select * from wordcount;
0: jdbc:hive2://192.168.153.139:10000> select split(line,"\\s") from wordcount;
0: jdbc:hive2://192.168.153.139:10000> select explode(split(line,"\\s")) from wordcount;
+---------+--+
| col |
+---------+--+
| hello |
| world |
| hello |
| java |
0: jdbc:hive2://192.168.153.139:10000> select w.word,count(1) num from
. . . . . . . . . . . . . . . . . . .> (select explode(split(line,"\\s")) word(列名) from wordcount) w
. . . . . . . . . . . . . . . . . . .> group by w.word order by num desc;
0: jdbc:hive2://192.168.153.139:10000> with
. . . . . . . . . . . . . . . . . . .> t1 as (select explode(split(line,"\\s")) word from wordcount)
. . . . . . . . . . . . . . . . . . .> select t1.word,count(1) num from t1 group by word order by num desc;
CASE WHEN THEN ELSE END
将年龄分段 int 转成 字符串
0: jdbc:hive2://192.168.153.139:10000> select name,age,case when age>=20 then 'old' when age<=18 then 'young' else 'middle' end as tag from studentp;
+------------+------+---------+--+
| name | age | tag |
+------------+------+---------+--+
| zhansan1 | 20 | old |
| zhansan2 | 20 | old |
| zhansan3 | 18 | young |
| zhansan4 | 18 | young |
| zhansan5 | 30 | old |
| zhansan6 | 19 | middle |
| zhansan7 | 19 | middle |
| zhansan8 | 19 | middle |
| zhansan9 | 12 | young |
| zhansan10 | 12 | young |
| zhansan11 | 12 | young |
+------------+------+---------+--+
将性别转数字 boy->1 girl->0
0: jdbc:hive2://192.168.153.139:10000> select name,gender, case when gender='boy' then 1 when gender='girl' then 0 else -1 end as gendertag from studentp;
+------------+---------+------------+--+
| name | gender | gendertag |
+------------+---------+------------+--+
| zhansan1 | boy | 1 |
| zhansan2 | girl | 0 |
| zhansan3 | boy | 1 |
| zhansan4 | girl | 0 |
| zhansan5 | boy | 1 |
| zhansan6 | girl | 0 |
| zhansan7 | girl | 0 |
| zhansan8 | boy | 1 |
| zhansan9 | girl | 0 |
| zhansan10 | girl | 0 |
| zhansan11 | girl | 0 |
+------------+---------+------------+--+
select name,gender,
case when gender='boy' then 1 else 0 end as boytag,
case when gender='girl' then 1 else 0 end as girltag,
case when gender is null then 1 else 0 end as taijiantag
from studentp;
0: jdbc:hive2://192.168.153.139:10000> create temporary table tmp_gender_tag as select name,gender,
. . . . . . . . . . . . . . . . . . .> case when gender='boy' then 1 else 0 end as boytag,
. . . . . . . . . . . . . . . . . . .> case when gender='girl' then 1 else 0 end as girltag,
. . . . . . . . . . . . . . . . . . .> case when gender is null then 1 else 0 end as taijiantag
. . . . . . . . . . . . . . . . . . .> from studentp;
+----------------------+------------------------+------------------------+-------------------------+----------------------------+--+
| tmp_gender_tag.name | tmp_gender_tag.gender | tmp_gender_tag.boytag | tmp_gender_tag.girltag | tmp_gender_tag.taijiantag |
+----------------------+------------------------+------------------------+-------------------------+----------------------------+--+
| zhansan1 | boy | 1 | 0 | 0 |
| zhansan2 | girl | 0 | 1 | 0 |
| zhansan3 | boy | 1 | 0 | 0 |
| zhansan4 | girl | 0 | 1 | 0 |
| zhansan5 | boy | 1 | 0 | 0 |
| zhansan6 | girl | 0 | 1 | 0 |
| zhansan7 | girl | 0 | 1 | 0 |
| zhansan8 | boy | 1 | 0 | 0 |
| zhansan9 | girl | 0 | 1 | 0 |
| zhansan10 | girl | 0 | 1 | 0 |
| zhansan11 | girl | 0 | 1 | 0 |
+----------------------+------------------------+------------------------+-------------------------+----------------------------+--+
0: jdbc:hive2://192.168.153.139:10000> select sum(boytag) as boysum,sum(girltag) as girlnum,sum(taijiantag) as tjnum from tmp_gender_tag;
+---------+----------+--------+--+
| boysum | girlnum | tjnum |
+---------+----------+--------+--+
| 4 | 7 | 0 |
+---------+----------+--------+--+
行转列 concat_ws collect_set collect_list
select gender,concat_ws(",",collect_set(name)) as stu_col from studentp group by gender; 去重
select gender,concat_ws(",",collect_list(name)) as stu_col from studentp group by gender; 不去重
导出表数据到指定文件夹,必须要求导出文件夹为空文件夹,否则会报错
0: jdbc:hive2://192.168.153.139:10000> export table studentp to '/tmp/out1';
导出表数据到本地文件夹,要确保目标文件夹存在,否导出失败
[root@gree139 tmp]# hive -e "select * from greedemo.studentp1" >> /opt/tmp/out/student.txt
order by 全局排序
sort by 分区内部有序,整体无序
distribute by mapTask分区 一般与sort by合作使用
cluster by 如果分区和排序字段相同并且升序,等同与distrubute by age sort by age asc
0: jdbc:hive2://192.168.153.139:10000> select * from studentp order by age;
0: jdbc:hive2://192.168.153.139:10000> select * from studentp sort by age;
0: jdbc:hive2://192.168.153.139:10000> create table dissorstudenttp as select * from studentp distribute by id sort by age ;
0: jdbc:hive2://192.168.153.139:10000> create table dissorstudenttp2 as select * from studentp distribute by age sort by age ;
0: jdbc:hive2://192.168.153.139:10000> insert overwrite table dissorstudenttp2 select * from studentp distribute by age sort by age desc ;
0: jdbc:hive2://192.168.153.139:10000> create table dissorstudenttp3 as select * from studentp cluster by age ;
侧视图、行转列、列转行、排序
于 2021-11-11 15:37:48 首次发布