测试数据:
1 zhang3 sing-pingpong-running beijing:daxing
2 li4 sing-pingpong-running beijing:daxing
3 wang5 sing-pingpong-running beijing:daxing
建表:
create table psn
(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by (age int)
row format delimited
fields terminated by '\t'
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';
导入数据:
load data local inpath '/root/a.txt' into table psn partition(age=10);
结构函数测试
hive> select * from psn;
OK
1 zhang3 ["sing","pingpong","running"] {"beijing":"daxing"} 10
2 li4 ["sing","pingpong","running"] {"beijing":"daxing"} 10
3 wang5 ["sing","pingpong","running"] {"beijing":"daxing"} 10
hive> select likes[0] from psn;
OK
sing
hive> select address['beijing'] from psn;
OK
daxing
收集函数测试
hive> select size(address) from psn;
OK
1
hive> select size(likes) from psn;
OK
3
类型转换函数测试
hive> select cast('123' as int) from psn;
OK
123
日期函数测试
hive> select from_unixtime(1556362949) from psn;
OK
2019-04-27 19:02:29
hive> select to_date('1970-01-31 10:20:10') from psn;
OK
1970-01-31
hive> select year('1970-01-31 10:20:10') from psn;
OK
1970
hive> select month('1970-01-31 10:20:10') from psn;
OK
1
hive> select day('1970-01-31 10:20:10') from psn;
OK
31
hive> select dayofmonth('1970-01-31 10:20:10') from psn;
OK
31
hive> select hour('1970-01-31 10:20:10') from psn;
OK
10
hive> select minute('1970-01-31 10:20:10') from psn;
OK
20
hive> select second('1970-01-31 10:20:10') from psn;
OK
10
hive> select weekofyear('1970-01-31 10:20:10') from psn;
OK
5
hive> select datediff('1970-01-31','1970-2-22') from psn;
OK
-22
hive> select date_add('1970-01-31 10:20:10',10) from psn;
OK
1970-02-10
hive> select date_sub('1970-01-31 10:20:10',10) from psn;
OK
1970-01-21
条件函数测试:行转列
原stu表:
zs yuwen 91
zs shuxue 92
zs yingyu 93
ls yuwen 94
ls shuxue 95
ls yingyu 96
ww yuwen 97
ww shuxue 98
ww yingyu 99
转换过后的trans_stu表:
name yuwen shuxue yingyu
zs 91 92 93
ls 94 95 98
ww 97 98 99
SQL语句:
1、第一种方式:
select t1.name,t1.yuwen,t2.shuxue,t3.yingyu from
(select name,if(subj='yuwen',score,null) yuwen from stu)t1
inner join
(select name,if(subj='shuxue',score,null) shuxue from stu)t2
on t1.name=t2.name
inner join
(select name,if(subj='yingyu',score,null) yingyu from row2col)t3
on t2.name=t3.name
where t1.yuwen is not null and t2.shuxue is not null and t3.yingyu is not null
结果:
Total MapReduce CPU Time Spent: 3 seconds 370 msec
OK
zs 91 92 93
ls 94 95 96
ww 97 98 99
Time taken: 41.81 seconds, Fetched: 3 row(s)
2、第二种方式:
select t1.name,t1.score yuwen,t2.score shuxue,t3.score yingyu from
(select name,score from student where subject = 'yuwen')t1
inner join
(select name,score from student where subject = 'shuxue')t2
on t1.name = t2.name
inner join
(select name,score from student where subject = 'yingyu')t3
on t1.name=t3.name;
结果:
Total MapReduce CPU Time Spent: 3 seconds 870 msec
OK
zs 91 92 93
ls 94 95 96
ww 97 98 99
Time taken: 44.53 seconds, Fetched: 3 row(s)
字符串函数测试
hive> select length('likes') from psn;
OK
5
hive> select reverse('I LOVE YOU') from psn;
OK
UOY EVOL I
hive> select concat('I',' LOVE', ' YOU') from psn;
OK
I LOVE YOU
hive> select concat_ws('-','I','LOVE', 'YOU') from psn;
OK
I-LOVE-YOU
hive> select substr('I LOVE YOU',7) from psn;
OK
YOU
hive> select substring('I LOVE YOU',7) from psn;
OK
YOU
hive> select substring('I LOVE YOU',7,9) from psn;
OK
YOU
hive> select substr('I LOVE YOU',7,9) from psn;
OK
YOU
hive> select upper('I Love yoU') from psn;
OK
I LOVE YOU
hive> select ucase('I Love yoU') from psn;
OK
I LOVE YOU
hive> select lower('I Love yoU') from psn;
OK
i love you
hive> select lcase('I Love yoU') from psn;
OK
i love you
hive> select trim(' I LOVE YOU ') from psn;
OK
I LOVE YOU
hive> select ltrim(' I LOVE YOU ') from psn;
OK
I LOVE YOU
hive> select rtrim(' I LOVE YOU ') from psn;
OK
I LOVE YOU
hive> select regexp_replace('I LOVE YOU','YOU','CHINA') from psn;
OK
I LOVE CHINA
hive> select concat(space(10),'I') from psn;
OK
I #####space(10)返回10个空格
hive> select repeat(' I LOVE YOU',3) from psn;
OK
I LOVE YOU I LOVE YOU I LOVE YOU
hive> select ascii(' I LOVE YOU') from psn;
OK
32
hive> select ascii('I LOVE YOU') from psn;
OK
73
hive> select lpad(name,10,'*') from psn;
OK
****zhang3
*******li4
*****wang5
hive> select rpad(name,10,'*') from psn;
OK
zhang3****
li4*******
wang5*****
聚合函数测试
count() 返回记录的条数
sum() 求和
avg() 求平均值
min() 求最小值
max() 求最大值
#####聚合函数还有很多,比如求方差,平均差等同。他们全部会执行mapreduce程序
内置表生成函数
hive> select explode(likes) from psn;
OK
sing
pingpong
running
sing
pingpong
running
sing
pingpong
running
Time taken: 0.393 seconds, Fetched: 9 row(s)
explode函数可以将一行数据拆分成多行
它接受一个map或array参数,常以explode(split('I love you',' '))形式出现
因为split函数返回一个数组
更多函数参考文档:
链接:https://pan.baidu.com/s/14MTnu4oC6Xh6khsofiHRfw
提取码:3cqj
复制这段内容后打开百度网盘手机App,操作更方便哦