hive case when 和osort by 和group by使用记录

最新推荐文章于 2024-03-31 13:30:27 发布

原创最新推荐文章于 2024-03-31 13:30:27 发布 · 4.3k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#hive #hql

本文探讨了Hive中的order by、sort by和distribute by的区别，强调了在不需要全局排序时sort by的高效性。通过示例展示了如何使用distribute by确保相同年份的行被分配到同一个reducer。同时，针对ID与多个name的情况，介绍了如何利用Hive的case when和collect_set函数处理数据，确保ID唯一并获取任意name。最后，提到可以通过数组下标访问collect_set函数返回的数组元素，实现特定需求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、当某个字段应用了case when 条件并且使用了or，且where查询条件的里正好有该字段，查询结果会出错

针对查询一出现问题，修改下SQL，将case when then else end, 改成case when then when then else end即可

2、

Order by 能够预期产生完全排序的结果，但是它是通过只用一个reduce来做到这点的。所以对于大规模的数据集它的效率非常低。在很多情况下，并不需要全局排序，此时可以换成Hive的非标准扩展sort by。Sort by为每个reducer产生一个排序文件。在有些情况下，你需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。Hive的distribute by 子句可以做这件事。

[sql]view plaincopyprint? 
    
 // 根据年份和气温对气象数据进行排序，以确保所有具有相同年份的行最终都在一个reducer分区中  
   
 From record2  
 select year, temperature  
 distribute by year  
 sort by year asc, temperature desc;  

因此，distribute by 经常和 sort by 配合使用。

3、

遇到这么一个需求，输入数据为一个ID对应多个name，要求输出数据为ID是唯一的，name随便取一个就可以。

执行以下hive ql语句：

SELECT sid,
  class_id FROM table2 GROUP BY sid ;

SELECT

sid ,

class_id

FROM

table2

GROUP BY sid ;

会报错：

FAILED: Error in semantic analysis: Line 1:18 Expression not in GROUP BY key 'class_id'

FAILED : Error in semantic analysis : Line 1 : 18 Expression not in GROUP BY key'class_id'

查了一下，HIVE有这么一个函数：

collect_set(col)
返回类型：array
解释：返回一个去重后的对象集合

collect_set ( col )

返回类型： array

解释：返回一个去重后的对象集合

将上述的QL语句改一下：

select sid,collect_set(class_id) from table2 group by sid;

select sid , collect_set ( class_id ) from table2 group by sid ;

结果是这样的：

1 [11,12,13] 2 [11,14] 3 [12,15] 4 [12,13] 5 [16,14] 7 [13,15]

1 [ 11 , 12 , 13 ]

2 [ 11 , 14 ]

3 [ 12 , 15 ]

4 [ 12 , 13 ]

5 [ 16 , 14 ]

7 [ 13 , 15 ]

这个时候，我们就可以针对第二列做一些计数、求和操作，分别对应到Hive的聚合函数count、sum。

对应到本文的目的，直接从数组获取第一个元素就达到目的了，这样做：

select sid,collect_set(class_id)[0] from table2 group by sid;

select sid , collect_set ( class_id ) [ 0 ] from table2 group by sid ;

结果如下：

1 11

2 11

3 12

4 12

5 16

7 13

总结：

Hive不允许直接访问非group by字段；
对于非group by字段，可以用Hive的collect_set函数收集这些字段，返回一个数组；
使用数字下标，可以直接访问数组中的元素；