最近在全职负责一款数据产品的升级改造。因旧版平台的代码写得太乱,简直惨不忍睹;别说增加功能,已有问题的定位与修复都无从下手。用户提交的,在旧版平台能执行的SQL语句,在新版平台执行报错。
此为背景。
UDF ERROR: Cannot divide decimal by zero报错信息如下:java.lang.RuntimeException: UDF ERROR: Cannot divide decimal by zeron,问题很明显,就是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,相同的报错:
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版本为:
org.apache.hive hive-jdbc 1.1.0-cdh5.7.1
新版平台使用的hive-jdbc版本为:
org.apache.hive hive-jdbc 2.1.1-cdh6.2.1
而是在提交hive类SQL(impala和kudu都是基于hive)之前未执行语句:SET DECIMAL_V2=FALSE。
解决方法:
// 解决换新集群select 1/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'.n"
报错SQL如下:
select * from edw_bx.insure_policy union all select * from project_b.insure_policy_testSET DECIMAL_V2=FALSE
参考DECIMAL_V2 Query Option
参考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 Statement
DECIMAL_V2 Query Option



