Hive聚合函数和表生成函数

最新推荐文章于 2024-06-04 15:28:50 发布

原创最新推荐文章于 2024-06-04 15:28:50 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Hive

Hive 专栏收录该内容

34 篇文章

订阅专栏

本文介绍了Hive中常用的聚合函数，如count、sum、min、max和avg，并通过实例展示了这些函数的具体用法。此外，还介绍了表生成函数explode的使用方法，并提供了相应的例子。

一 聚合函数

1、count

2、sum

3、min

4、max

5、avg

二 实例

hive> select count(*),sum(sal),max(sal),min(sal),avg(sal) from emp;
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapred.reduce.tasks=<number>
Starting Job = job_201709011948_0004, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201709011948_0004
Kill Command = /opt/hadoop-1.2.1/libexec/../bin/hadoop job  -kill job_201709011948_0004
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2017-09-01 20:43:25,420 Stage-1 map = 0%,  reduce = 0%
2017-09-01 20:43:42,444 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.72 sec
2017-09-01 20:44:10,270 Stage-1 map = 100%,  reduce = 67%, Cumulative CPU 3.44 sec
2017-09-01 20:44:12,320 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 9.34 sec
MapReduce Total cumulative CPU time: 9 seconds 340 msec
Ended Job = job_201709011948_0004
MapReduce Jobs Launched: 
Job 0: Map: 1  Reduce: 1   Cumulative CPU: 9.34 sec   HDFS Read: 635 HDFS Write: 35 SUCCESS
Total MapReduce CPU Time Spent: 9 seconds 340 msec
OK
16	104236.0	12111.0	4800.0	6514.75
Time taken: 86.66 seconds, Fetched: 1 row(s)

三 表生成函数

explode

四 实例

hive> select explode(map(1,'Tom',2,'Mary',3,'Mike'));
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201709011948_0005, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201709011948_0005
Kill Command = /opt/hadoop-1.2.1/libexec/../bin/hadoop job  -kill job_201709011948_0005
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2017-09-01 20:46:33,297 Stage-1 map = 0%,  reduce = 0%
2017-09-01 20:46:47,588 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.27 sec
2017-09-01 20:46:55,793 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.27 sec
MapReduce Total cumulative CPU time: 3 seconds 270 msec
Ended Job = job_201709011948_0005
MapReduce Jobs Launched: 
Job 0: Map: 1   Cumulative CPU: 3.27 sec   HDFS Read: 270 HDFS Write: 20 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 270 msec
OK
1	Tom
2	Mary
3	Mike
Time taken: 42.023 seconds, Fetched: 3 row(s)