hiveSql 相互关注问题

hiveSql 相互关注问题

说明

普遍社交软件上会有关注功能,如何知道自己的关注是否也是关注了自己呢?

需求

求关注结果数据中,相互关注的用户对。
数据如下:
follow表;from_user:关注用户,to_user:被关注用户,timestamp:关注时间。

from_userto_usertimestamp
AB2022-11-28 12:12:12
AC2022-11-28 12:12:13
AD2022-11-28 12:12:14
BA2022-11-28 12:12:16
BE2022-11-28 12:12:16
CA2022-11-28 12:12:17
DA2022-11-28 12:12:18

分析

如果是互相关注,很容易想到表自关联,a.from_user 等于 b.to_user 且 a.to_user = b.from_user即可。具体sql如下:

select 
	a.from_user,
	a.to_user
from follow a join follow b 
on a.from_user  = b.to_user 
and a.to_user = b.from_user  

这种方式确实可行,结果如下:
在这里插入图片描述

但是当数据量达到很大时候运行时相当慢的,有没优化的方法实现呢?看到这可以思考下如何优化!


优化

其实思路还是一样,知道是from_user 和 to_user正好两个用户互换字段而已。如果是互关用户 AB,在表中有如下数据:

from_userto_user
AB
BA

如果在表中能将其中一条数据用户名互换位置,另一条不动,再按照这两个字段分组计算数据条数,组内数据条数是2的就是互关用户了。这个思路不涉及到join操作。

from_userto_user
AB
AB

实现

有了上述优化思路,下面来实现下,如何将其中一条数据用户名互换位置呢,其实可以想到简单的一个真假逻辑判断就行了。如果一条满足逻辑则不变,另一条要确保一定不满足逻辑,交换位置即可,sql如下:

select 
	from_user,
	to_user,
	if(from_user > to_user, concat(from_user,to_user),concat(to_user,from_user)) as concat_users
from follw

上述sql的if()中,这两条数据一定是一个满足一个不满足。即将其中一条数据用户名交换位置,完整sql如下:

select 
	t1.from_user,
	t1.to_user
from
	(select 
		t.from_user,
		t.to_user,
		sum(1) over(partition by t.concat_users) as flag
	from
		(select 
			from_user,
			to_user,
			if(from_user > to_user, concat(from_user,to_user),concat(to_user,from_user)) as concat_users
		from follw
		) t
	) t1
	where t1.flag = 2

结果如下:
在这里插入图片描述

最后

喜欢的点赞、关注、收藏吧~ 感谢支持~~

<think>我们被要求回答关于HiveSQL中使用大量CASE WHEN导致执行失败的原因及解决方案。根据提供的引用和系统指令,我们需要结构化地分析问题。首先,引用[3]详细介绍了Hive中CASEWHEN的两种用法和注意事项,但没有直接提到使用大量CASE WHEN会导致失败。引用[4]提到Hive执行select语句报错,但没有具体说明原因。因此,我们需要结合Hive的工作原理和常见问题来分析。 可能的原因:1.**查询过于复杂,超出编译器或执行引擎的处理能力**:当CASE WHEN语句过多时,生成的查询计划可能非常复杂,导致编译时间过长或内存溢出。2.**表达式复杂度限制**:Hive对单个查询的表达式数量可能有内部限制,超过限制会导致失败。 3. **资源不足**:处理大量CASEWHEN需要更多内存和计算资源,如果资源不足,查询会失败。4.**代码生成器限制**:Hive使用代码生成来优化查询,但过多的分支可能导致代码生成失败。 解决方案: 1. **简化查询**:尝试将部分CASEWHEN逻辑转移到数据预处理阶段(如使用MapReduce或Spark作业预处理数据),或者在Hive中创建中间表分步处理。 2. **拆分查询**:将一个大查询拆分成多个子查询,每个子查询处理一部分逻辑,然后通过临时表存储中间结果,最后合并结果。3.**优化CASE WHEN表达式**:检查是否有可以合并的条件,或者使用其他方式(如查找表)来替代大量CASEWHEN。4.**调整Hive配置**:增加Hive编译器和执行引擎的资源,例如增加堆内存。相关配置参数包括:-`hive.auto.convert.join=false`(避免自动转换join消耗过多资源)-增加`mapreduce.map.memory.mb`和`mapreduce.reduce.memory.mb`-调整`hive.heapsize`(Hive服务端堆内存)-设置`hive.map.aggr=true`(在map阶段进行聚合,减少reduce阶段数据量)5.**使用临时表**:将每个CASE WHEN逻辑分散到多个步骤中,每一步生成一个临时表,最后再汇总。 引用[2]中提到了一种优化countdistinct的方法,即通过子查询先分组再聚合。这种思路同样适用于优化大量CASE WHEN:通过将数据先按多个条件分组,然后再进行汇总,可以避免在一个SELECT中写大量CASE WHEN。 例如,原始查询可能是: ```sqlSELECTid,CASE WHENcondition1THEN result1 ...(50个CASE WHEN) FROMtable;``` 可以改写为: ```sql--第一步:将每个条件单独计算,生成多个临时字段(或使用多个临时表) CREATETABLE temp1ASSELECTid,CASE WHENcondition1THEN result1 ENDasresult1,CASEWHEN condition2 THENresult2END asresult2,...--但这里仍然有50个,所以可能还是需要拆分FROMtable; --然后第二步再合并(如果需要合并)或者分多次计算```但是,如果50个CASE WHEN是相互独立的,那么可以尝试将50个CASE WHEN拆分成多个子查询,然后通过JOIN来组合。不过,这种方法可能会增加数据读取次数,需要权衡。另一种思路:将条件逻辑用其他方式表示,例如使用映射表(字典表)。如果条件是基于某个字段的多个值映射,可以创建一个映射表,然后通过JOIN来替代CASE WHEN。例如,原始查询: ```sqlSELECTid,CASE WHENtype='A' then1WHEN type='B' then2... --50个类型END astype_codeFROMtable;``` 可以改为: ```sql--创建一个映射表(可以临时创建) CREATETEMPORARYTABLE type_mapping (typestring,type_code int);--插入映射关系(这里用示例数据,实际需要插入50条)INSERT INTOTABLE type_mappingVALUES('A',1),('B',2),...; --然后使用JOINSELECTt.id,m.type_codeFROMtable tJOINtype_mapping mON t.type =m.type; ```这样避免了CASEWHEN,但前提是条件都是等值判断(如方法二所示)。如果是非等值条件(如范围判断),则映射表可能难以使用。 如果条件是非等值的,比如:```sqlCASE WHENscore >=90then 'A'WHENscore >=80 then'B'... ```则无法直接使用映射表JOIN,但可以通过创建区间映射表(包含下限和上限)然后JOIN来实现,但这样JOIN条件会变成非等值连接,效率可能不高。因此,针对非等值条件,如果CASE WHEN数量过多,还是建议拆分查询或者增加资源。 另外,引用[3]提到,在CASEWHEN中不能使用子查询,所以如果条件里需要子查询,那么可能无法避免,只能优化子查询。总结解决方案:1.**拆分大查询**:将50个CASEWHEN拆分成多个步骤,使用中间表。2.**优化表达式**:合并相似条件,减少分支数量。3.**使用映射表**:对于等值条件,用JOIN代替CASEWHEN。4.**调整配置**:增加Hive内存设置,避免编译和执行时内存溢出。5.**升级Hive版本**:新版本可能优化了复杂表达式的处理。 最后,根据引用[4]的提示,可以查阅Hive常见故障案例,但具体问题需要具体分析。 根据以上分析,生成相关问题:1.HiveSQL查询复杂度过高有哪些表现? 2.如何优化Hive中的复杂条件判断?3.Hive中除了CASE WHEN,还有哪些条件表达式可以使用? 4.如何调整Hive配置以处理大查询?</think>### Hive SQL 中使用大量 CASE WHEN 导致执行失败的原因及解决方案 #### 原因分析 1. **表达式复杂度超限** Hive 对单个查询的表达式数量有内部限制(通常约 1000 个)。50 个 `CASE WHEN` 可能生成数百个中间表达式,超出编译器阈值[^4]。 *示例报错*:`FAILED: SemanticException [Error 10055]: Expression not in GROUP BY key` 2. **代码生成器崩溃** Hive 的代码生成器(CodeGen)会将表达式编译为 Java 类。过多 `CASE WHEN` 会导致: - 生成的 Java 方法超过 JVM 方法长度限制(64KB) - 触发 JVM `java.lang.ClassFormatError` 错误[^4] 3. **资源耗尽** 复杂表达式需要更多内存和 CPU: - **编译器内存不足**:`Container killed by YARN for exceeding memory limits` - **执行引擎超时**:`Query timed out during execution` 4. **优化器失效** 大量分支条件会使优化器无法有效裁剪数据分区,导致全表扫描[^3]。 --- #### 解决方案 ##### 方法 1:拆分查询(推荐) ```sql -- 步骤1:创建中间表存储基础字段 CREATE TABLE tmp_intermediate AS SELECT id, col1, col2, ... -- 保留必要字段 FROM source_table; -- 步骤2:分批处理CASE WHEN INSERT INTO result_table SELECT id, /* 第一批10个CASE WHEN */ CASE WHEN cond1 THEN res1 ... END AS result_part1, ... FROM tmp_intermediate; INSERT INTO result_table SELECT id, /* 第二批10个CASE WHEN */ CASE WHEN cond11 THEN res11 ... END AS result_part2, ... FROM tmp_intermediate; ``` ##### 方法 2:使用 MAP 替代 CASE WHEN ```sql -- 创建映射表 CREATE TEMPORARY MAP condition_map AS SELECT key_col, MAP( 'cond1', 'res1', 'cond2', 'res2', ... -- 最多支持50个键值对 ) AS condition_res FROM mapping_table; -- 通过MAP访问结果 SELECT a.*, condition_map[condition_key] AS result -- 直接映射结果 FROM source_table a; ``` ##### 方法 3:调整 Hive 配置 ```shell # 增加编译器内存 SET hive.heapsize=4096m; # 关闭代码生成优化(规避CodeGen崩溃) SET hive.optimize.codegen=false; # 提高执行超时阈值 SET hive.execution.engine=tez; SET tez.session.am.dag.submit.timeout.secs=3600; ``` ##### 方法 4:UDF 封装逻辑 ```java // 创建Java UDF public class ComplexCaseUDF extends UDF { public String evaluate(String input) { // 在UDF内部实现50个条件判断 if (input.equals("cond1")) return "res1"; else if (...) ... } } ``` ```sql -- 注册UDF后简化查询 ADD JAR complex_case.jar; CREATE TEMPORARY FUNCTION custom_case AS 'ComplexCaseUDF'; SELECT custom_case(col) FROM table; ``` --- #### 最佳实践建议 1. **预处理数据**:在数据接入层(如 Flink/Spark)提前处理复杂逻辑 2. **分区剪枝**:对条件字段预先分区,减少扫描量[^3] 3. **向量化执行**:启用 `SET hive.vectorized.execution.enabled=true;` 提升批量处理性能 4. **监控规避**:通过 `EXPLAIN` 分析执行计划,重点关注 `Expression Tree` 深度[^4] > 关键原则:**当 SQL 行数超过 20 行或包含 10+ 分支逻辑时,优先考虑拆分为多步骤计算** [^2][^3]。 ---
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

@nanami

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值