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

本文探讨了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 子句可以做这件事。


  1. // 根据年份和气温对气象数据进行排序,以确保所有具有相同年份的行最终都在一个reducer分区中  
  2.   
  3. From record2  
  4. select year, temperature  
  5. distribute by year  
  6. 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
1 11
2 11
3 12
4 12
5 16
7 13

总结:

  1. Hive不允许直接访问非group by字段;
  2. 对于非group by字段,可以用Hive的collect_set函数收集这些字段,返回一个数组;
  3. 使用数字下标,可以直接访问数组中的元素;
### Hive Group By Join 数据倾斜解决方案 在Hive中,数据倾斜通常发生在某些键的分布不均匀时。当某些键对应的数据量远大于其他键时,会导致某些任务处理时间过长,从而影响整个查询性能。以下是针对Group ByJoin操作导致的数据倾斜问题的具体解决方案。 #### 1. Group By 数据倾斜解决方案 对于Group By操作导致的数据倾斜,可以采取以下措施: - **启用Skewed Join优化**:通过设置`hive.groupby.skewindata=true`,Hive会尝试将倾斜的分组数据分配到不同的Reducer中[^1]。然而需要注意的是,如果在使用`count(distinct ...)`时涉及多个不同列,可能会出现语义异常(如用户提供的例子中的错误信息)。因此需要调整SQL逻辑或避免同时对多列进行去重操作。 - **预聚合策略**:可以在Group By之前先进行一次局部聚合,减少数据传输量。例如: ```sql INSERT OVERWRITE TABLE temp_result PARTITION (trade_date) SELECT member_no, trade_date, COUNT(*) AS cnt FROM uiopdb.sx_trade_his_detail GROUP BY member_no, trade_date; SELECT COUNT(DISTINCT member_no), trade_date FROM temp_result GROUP BY trade_date; ``` - **采样分析与重新分区**:通过对数据进行采样,找出倾斜的Key,并对这些Key单独处理或重新分区。可以通过以下方式实现: ```sql SELECT member_no, trade_date, COUNT(*) FROM ( SELECT member_no, trade_date, CASE WHEN member_no IN ('hot_key1', 'hot_key2') THEN CONCAT(member_no, '_', MOD(HASH(member_no), 10)) ELSE member_no END AS new_member_no FROM uiopdb.sx_trade_his_detail ) t GROUP BY new_member_no, trade_date; ``` #### 2. Join 数据倾斜解决方案 对于Join操作导致的数据倾斜,可以采取以下措施: - **小表广播(Map-Side Join)**:如果参与Join的一方是小表,可以通过设置`hive.auto.convert.join=true`来启用Map-Side Join,将小表广播到每个Mapper节点上[^3]。 - **动态分区与抽样**:对于大表Join,可以先对数据进行抽样分析,找出倾斜的Key,并对其进行特殊处理。例如: ```sql SELECT a.userid, x.d* FROM log a LEFT JOIN ( SELECT d.* FROM ( SELECT DISTINCT userid FROM log WHERE userid NOT IN ('hot_key1', 'hot_key2') ) c JOIN users d ON c.userid = d.userid ) x ON a.userid = x.userid; ``` - **伪列拆分**:对于倾斜Key,可以引入伪列进行拆分,从而分散压力。例如: ```sql SELECT a.userid, x.d* FROM log a LEFT JOIN ( SELECT CASE WHEN c.userid IN ('hot_key1', 'hot_key2') THEN CONCAT(c.userid, '_', MOD(HASH(c.userid), 10)) ELSE c.userid END AS new_userid, d.* FROM ( SELECT DISTINCT userid FROM log ) c JOIN users d ON c.userid = d.userid ) x ON CASE WHEN a.userid IN ('hot_key1', 'hot_key2') THEN CONCAT(a.userid, '_', MOD(HASH(a.userid), 10)) ELSE a.userid END = x.new_userid; ``` #### 3. 综合优化建议 - **数据预处理**:在执行复杂查询前,先对数据进行清洗预处理,确保数据分布尽量均匀。 - **合理选择存储格式**:使用更高效的存储格式(如ORC、Parquet),可以显著提升查询性能。 - **监控与调优**:通过Hive的日志性能监控工具,分析任务执行情况,及时发现并解决数据倾斜问题。 ```sql -- 示例代码:综合优化方案 INSERT OVERWRITE TABLE temp_log PARTITION (userid) SELECT CASE WHEN userid IN ('hot_key1', 'hot_key2') THEN CONCAT(userid, '_', MOD(HASH(userid), 10)) ELSE userid END AS new_userid, * FROM log; SELECT a.new_userid, x.d* FROM temp_log a LEFT JOIN ( SELECT d.* FROM ( SELECT DISTINCT new_userid FROM temp_log ) c JOIN users d ON c.new_userid = d.userid ) x ON a.new_userid = x.new_userid; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值