Hive中分组取前N个值 -
假设有一个学生各门课的成绩的表单，应用hive取出每科成绩前100名的学生成绩。
这个就是典型在分组取Top N的需求。
对于取出每科成绩前100名的学生成绩，针对学生成绩表，根据学科，成绩做order by排序,然后对排序后的成绩，执行自定义函数row_number(),必须带一个或者多个列参数，如ROW_NUMBER(col1, ....)，它的作用是按指定的列进行分组生成行序列。在ROW_NUMBER(a,b) 时，若两条记录的a，b列相同，则行序列+1，否则重新计数。
只要返回row_number()返回值小于100的的成绩记录，就可以返回每个单科成绩前一百的学生。
成绩表结构
create table score_table (
partitioned by (date string)
如果要查询2012年每科成绩前100的学生成绩，sql如下
create temporary function row_number as 'com.blue.hive.udf.RowNumber';
select subject,score,student from
(select subject,score,student from score where dt='2012'
order by subject,socre desc) order_score
where row_number(subject) &= 100;
com.blue.hive.udf.RowNumber是自定义函数，函数的作用是按指定的列进行分组生成行序列。这里根据每个科目的所有成绩，生成序列，序列值从1开始自增。
假设成绩表的记录如下:
经过order by全局排序后，记录如下
接着执行row_number函数，返回值如下
row_number
因为hive是基于MAPREADUCE的，必须保证row_number执行是在reducer中执行。上述的语句保证了成绩表的记录，按照科目和成绩做了全局排序，然后在reducer端执行row_number函数，如果在map端执行了row_number，那么结果将是错误的。
要查看row_number函数在map端还是reducer端执行，可以查看hive的执行计划:
create temporary function row_number as 'com.blue.hive.udf.RowNumber';
explain select subject,score,student from
(select subject,score,student from score where dt='2012'
order by subject,socre desc) order_score
where row_number(subject) &= 100;
explain不会执行mapreduce计算，只会显示执行计划。
只要row_number函数在reducer端执行，除了使用order by全局排序配合，也可以使用distribute by + sort by。distribute by可以让相同科目的成绩记录发送到同一个reducer,而sort by可以在reducer端对记录做排序。
而使用order by全局排序，只有一个reducer，未能充分利用资源，相比之下，distribute by + sort by在这里更有性能优势，可以在多个reducer做排序，再做row_number的计算。
create temporary function row_number as 'com.blue.hive.udf.RowNumber';
select subject,score,student from
(select subject,score,student from score where dt='2012'
distribute by subject sort by subject asc, socre desc) order_score
where row_number(subject) &= 100;
如果成绩有学院字段college，要找出学院里,单科成绩前一百的学生，解决方法如下:
create temporary function row_number as 'com.blue.hive.udf.RowNumber';
explain select college,subject,score,student from
(select college,subject,score,student from score where dt='2012'
order by college asc,subject asc,socre desc) order_score
where row_number(college,subject) &= 100;
如果成绩有学院字段college，要找出学院里,总成绩前一百的学生，解决方法如下:
create temporary function row_number as 'com.blue.hive.udf.RowNumber';
explain select college,totalscore,student from
(select college,student,sum(score) as totalscore from score where dt='2012'
group by college,student
order by college asc,totalscore desc) order_score
where row_number(college) &= 100;
row_number的源码
函数row_number(),必须带一个或者多个列参数，如ROW_NUMBER(col1, ....)，它的作用是按指定的列进行分组生成行序列。在ROW_NUMBER(a,b) 时，若两条记录的a，b列相同，则行序列+1，否则重新计数。下面的源码就是查出行序列
package com.blue.hive.
import org.apache.hadoop.hive.ql.exec.UDF;
public class RowNumber extends UDF {
private static int MAX_VALUE = 50;
private static String comparedColumn[] = new String[MAX_VALUE];
private static int rowNum = 1;
public int evaluate(Object... args) {
String columnValue[] = new String[args.length];
for (int i = 0; i & args. i++) 『
columnValue[i] = args[i].toString();
if (rowNum == 1) {
for (int i = 0; i & columnValue. i++)
comparedColumn[i] = columnValue[i];
for (int i = 0; i & columnValue. i++) {
if (!comparedColumn[i].equals(columnValue[i])) {
for (int j = 0; j & columnValue. j++) {
comparedColumn[j] = columnValue[j];
rowNum = 1;
return rowNum++;
return rowNum++;
编译后，打包成一个jar包，如/usr/local/hive/udf/blueudf.jar
然后在hive shell下使用，如下:
add jar /usr/local/hive/udf/blueudf.
create temporary function row_number as 'com.blue.hive.udf.RowNumber';
select subject,score,student from
(select subject,score,student from score where dt='2012'
order by subject,socre desc) order_score
where row_number(subject) &= 100;
文章 - 6632955人阅读
hadoop（1）
比如说有两个班级，A班和B班
Class &score
A&&&&&& 10
所谓的开窗函数的开窗就是把所有的A和所有的B往两个地方进行处理，像是送到两个窗口进行处理。
Hadoop会把所有A和所有B两块数据进行处理，保证每块数据都在同一个reduce上面进行处理，为什么会这样呢，因为使用了分区函数partition by class
不过两块数据也可能在同一个reduce上面执行，也可能不会
这里的作用就是partition by这个函数做到的
合起来的一个字段就当作一个分区进行处理，然后对该分区的asso_access_num进行排序处理，然后每个分区进行排序后再合在起来，然后rownum进行依次的判断，
第一个是A则rownum=1
第二个是A则rownum=2
第三个是A则rownum=3
第四个是B，因为class变了，所以rownum又变为1，rownum=1
第五个是B则rownum=2，依次类推
如果你要得到前面两条的话，就写rownum&=2即可，那么rownum=3的这条数据就不会进行获取
可以参考：
/zh-cn/library/ms186734.aspx
//archive//2137277.html
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1039096次
积分：9644
积分：9644
排名：第1236名
原创：180篇
转载：129篇
评论：156条
(2)(8)(6)(3)(1)(3)(1)(2)(5)(2)(9)(11)(8)(4)(4)(18)(24)(1)(19)(4)(2)(3)(11)(14)(2)(7)(18)(4)(5)(11)(5)(6)(46)(7)(20)(13)hive limit怎么使用_百度知道mysql取前几行数据limit用法
取前几行数据limit用法
在mysql中是没有top关键字的，在mysql中可以用limit来完成功能。
order by id desc limit 10 按照id的倒序排序 取出前10条
order by id desc limit 0,10 按照id的倒序排序 取出前10条
order by id limit 5,10 按照id的正序排序 从第5条开始取10条
SELECT cat_id FROM shop_goods_type order by cat_id desc limit 1
(window.slotbydup=window.slotbydup || []).push({
id: '2467140',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467141',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467143',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467148',
container: s,
size: '1000,90',
display: 'inlay-fix'hive.optimize.cp=true：列裁剪hive.optimize.prunner：分区裁剪hive.limit.optimize.enable=true：优化LIMIT n语句hive.limit.row.max.size=1000000：hive.limit.optimize.limit.file=10：最大文件数
1. 本地模式(小任务)：需要满足以下条件：　　1.job的输入数据大小必须小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)　　2.job的map数必须小于参数：hive.exec.mode.local.auto.tasks.max(默认4)　　3.job的reduce数必须为0或者1hive.exec.mode.local.auto.inputbytes.max=hive.exec.mode.local.auto.tasks.max=4hive.exec.mode.local.auto=truehive.mapred.local.mem：本地模式启动的JVM内存大小
2. 并发执行：hive.exec.parallel=true ，默认为falsehive.exec.parallel.thread.number=8
3.Strict Mode：hive.mapred.mode=true，严格模式不允许执行以下查询：分区表上没有指定了分区没有limit限制的order by语句笛卡尔积：JOIN时没有ON语句
4.动态分区：hive.exec.dynamic.partition.mode=strict：该模式下必须指定一个静态分区hive.exec.max.dynamic.partitions=1000hive.exec.max.dynamic.partitions.pernode=100：在每一个mapper/reducer节点允许创建的最大分区数DATANODE：dfs.datanode.max.xceivers=8192：允许DATANODE打开多少个文件
5.推测执行：mapred.map.tasks.speculative.execution=truemapred.reduce.tasks.speculative.execution=truehive.mapred.reduce.tasks.speculative.execution=
6.Single MapReduce MultiGROUP BYhive.multigroupby.singlemar=true：当多个GROUP BY语句有相同的分组列，则会优化为一个MR任务
7. hive.exec.rowoffset：是否提供虚拟列
8. 分组两个聚集函数不能有不同的DISTINCT列，以下表达式是错误的：INSERT OVERWRITE TABLE pv_gender_agg SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip) FROM pv_users GROUP BY pv_users.SELECT语句中只能有GROUP BY的列或者聚集函数。
9.hive.map.aggr=在map中会做部分聚集操作，效率更高但需要更多的内存。hive.groupby.mapaggr.checkinterval：在Map端进行聚合操作的条目数目
10.hive.groupby.skewindata=true：数据倾斜时负载均衡，当选项设定为true，生成的查询计划会有两个MRJob。第一个MRJob 中，Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的GroupBy Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MRJob再根据预处理的数据结果按照GroupBy Key分布到Reduce中（这个过程可以保证相同的GroupBy Key被分布到同一个Reduce中），最后完成最终的聚合操作。
11.Multi-Group-By Inserts：FROM testINSERT OVERWRITE TABLE count1SELECT count(DISTINCT test.dqcode)GROUP BY test.zipcodeINSERT OVERWRITE TABLE count2SELECT count(DISTINCT test.dqcode)GROUP BY test.
12.排序ORDER BY colName ASC/DESChive.mapred.mode=strict时需要跟limit子句hive.mapred.mode=nonstrict时使用单个reduce完成排序SORT BY colName ASC/DESC ：每个reduce内排序DISTRIBUTE BY(子查询情况下使用 )：控制特定行应该到哪个reducer，并不保证reduce内数据的顺序CLUSTER BY ：当SORT BY 、DISTRIBUTE BY使用相同的列时。
13.合并小文件hive.merg.mapfiles=true：合并map输出hive.merge.mapredfiles=false：合并reduce输出hive.merge.size.per.task=256*：合并文件的大小hive.mergejob.maponly=true：如果支持CombineHiveInputFormat则生成只有Map的任务执行mergehive.merge.smallfiles.avgsize=：文件的平均大小小于该值时，会启动一个MR任务执行merge。
14.map/reduce数目减少map数目：　　set mapred.max.split.size　　set mapred.min.split.size　　set mapred.min.split.size.per.node　　set mapred.min.split.size.per.rack　　set hive.input.format=org.apache.hadoop.hive.bineHiveInputFormat增加map数目：当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。假设有这样一个任务：　　select data_desc, count(1), count(distinct id),sum(case when &),sum(case when ...),sum(&) from a group by data_desc如果表a只有一个文件，大小为120M，但包含几千万的记录，如果用1个map去完成这个任务，肯定是比较耗时的，这种情况下，我们要考虑将这一个文件合理的拆分成多个，这样就可以用多个map任务去完成。　　set mapred.reduce.tasks=10;　　create table a_1 as select * from a distribute by rand(123);这样会将a表的记录，随机的分散到包含10个文件的a_1表中，再用a_1代替上面sql中的a表，则会用10个map任务去完成。每个map任务处理大于12M（几百万记录）的数据，效率肯定会好很多。
reduce数目设置：　参数1：hive.exec.reducers.bytes.per.reducer=1G：每个reduce任务处理的数据量　参数2：hive.exec.reducers.max=999(0.95*TaskTracker数)：每个任务最大的reduce数目　reducer数=min(参数2,总输入数据量/参数1)　set mapred.reduce.tasks：每个任务默认的reduce数目。典型为0.99*reduce槽数，hive将其设置为-1，自动确定reduce数目。
15.使用索引：hive.optimize.index.filter：自动使用索引hive.optimize.index.groupby：使用聚合索引优化GROUP BY操作
阅读(...) 评论()}