概述
最近在全职负责一款数据产品的升级改造。因旧版平台的代码写得太乱,简直惨不忍睹;别说增加功能,已有问题的定位与修复都无从下手。用户提交的,在旧版平台能执行的SQL语句,在新版平台执行报错。
此为背景。
UDF ERROR: Cannot divide decimal by zero
报错信息如下:java.lang.RuntimeException: UDF ERROR: Cannot divide decimal by zero\n
。
问题很明显,就是select查询语句里面含有/
符号,并且分母为0。
这个报错,在不同的数据集任务,即至少两个SQL中都有出现;且在旧版平台都是执行成功的。
Impala数据源
报错的SQL精简如下:
select name as 城市
,dt as 日期
,sum(price_11) as 补单已支付金额
,sum(price_10) as 补单未支付金额
,sum(price_11)/(sum(price_11) + sum(price_10)) as 补单回款率
from t group by name, dt
报错SQL是impala数据源:
在公司内部搭建可视化查询平台执行成功,执行结果截图
下面这个截图是尝试复现问题的SQL,当分母为0.0
时,就会出现UDF ERROR: Cannot divide decimal by zero
问题。
kudu数据源
另有SQL,相同的报错:
select c.*, c.cost/youe_count as cpcredit,
c.cost/all_youe_count as cpallcredit, c.cost/all_youe_amount as fyl
from b left join c on b.touch_date = c.dt
其数据源为:
在可视化查询平台执行失败,在旧版程序能够成功,
经过排查,和使用的hive-jdbc
依赖组件无关。旧版平台使用的hive-jdbc
版本为:
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>1.1.0-cdh5.7.1</version>
</dependency>
新版平台使用的hive-jdbc版本为2.1.1-cdh6.2.1
:
而是在提交hive类SQL(impala和kudu都是基于hive)之前未执行语句:SET DECIMAL_V2=FALSE
:
// 解决换新集群 select 1/0.0 报错
if ("org.apache.hive.jdbc.HiveDriver".equals(driver)) {
ps.executeUpdate("SET DECIMAL_V2=FALSE");
}
RuntimeException: AnalysisException: Incompatible return types ‘DECIMAL(38,15)’ and ‘DECIMAL(38,4)’ of exprs ‘’ and ‘’
另有一个SQL报错信息:
java.lang.RuntimeException: AnalysisException: Incompatible return types 'DECIMAL(38,15)' and 'DECIMAL(38,4)' of exprs 'edw.insure_policy.commission_rate' and 'project_b.insure_policy_test.commission_rate'.
报错SQL如下:
select * from edw_bx.insure_policy union all select * from project_b.insure_policy_test
这个报错会出现在select a from b union all select a from c
,且当b表和c表的a字段的类型不同时。
UDF ERROR: Decimal expression overflowed
报错SQL简化如下:
select
to_date(now()) as batchId
,cast(gmv as decimal(38, 5)) as gmv
from xyg.wf_counter_MRR_month_220517_01 a
where a.mon = '2022-04';
这个报错会出现在cast(a as decimal(38, 5)) as a
时,本地复现此问题的SQL如下:
UDF ERROR: String to Decimal parse failed
本地复现此问题的SQL如下:
SET DECIMAL_V2=FALSE
A query option that changes behavior related to the DECIMAL data type.
Important:
This query option is currently unsupported. Its precise behavior is currently undefined and might change in the future.
Type: Boolean; recognized values are 1 and 0, or true and false; any other value interpreted as false
Default: false (shown as 0 in output of SET statement)
参考
Query Options for the SET StatementDECIMAL_V2 Query Option
CDH6中Impala3报错UDF ERROR: String to Decimal parse failed
impala-cast-as-decimal-errors-out-for-null-values