如何简化 SQL 语句之 UDF 实践

赵兴成
2019年 8月 31日

UDF(User Defined Function 用户自定义函数)是 SQL 环境中很关键的特性。通过写 UDF,开发者可以方便地插入常用的处理代码并在查询中使用。Apache Kylin 支持持久化的 UDF。来自华安保险的赵兴成特别带来了 Kylin 中 UDF 的分享,快跟着兴成一探究竟吧~

背景

Apache Kylin 作为 OLAP 神器,在海量数据的多维分析方面优势明显,给我们的工作提供了很大帮助,也一直是华安保险 OLAP 系统的后台的主要支撑。在我们的系统中,把指标分成基础指标和衍生指标两大类,基础指标即不可再分解,由业务直接生成的指标,衍生指标则是由基础指标通过混合计算得出。

一直以来,为了节省空间,增加构建速度,Kylin 的 Cube 中我们只保留了基础指标,而衍生指标基本是通过应用层的 SQL 来计算解决,这样带来两个问题:

  1. SQL 复杂,可读性差,比如需要加入 case when 和类似 (commamt+devamt+servamt)/grossprm as ** 的语法;
  2. 易出错,如果有开发人员对指标定义不熟悉或者理解有偏差,极易造成计算结果错误。

如何解决?

用过 Hive 的人都清楚,Hive 中有个 UDF(User Defined Function)功能,非常好用。那么 Kylin 中是否有这样的功能呢?有的,只有三个:三个类都位于 org.apache.kylin.query.udf; 包下面:ConcatUDF,MassInUDF,VersionUDF。很显然这三个是无法满足要求的(可能 Kylin 考虑到通用性,UDF 本身就是个性化的东西,不便开发太多)。

经与 Kyligence 公司史少锋大侠咨询,得到一个可以自己开发 UDF 的方法,下面举一个小例子来说明操作步骤:

第一步:明确函数作用,个人建议如果不是很通用的或者用的不频繁的计算是没必要做成 UDF,一些行业标准的或者公司内通用的定义比较合适。目前 UDF 还只是 one-to-one row mapping 的,不能做多行,多行后面再研究。

第二步:创建 maven 工程,工程引入 Calcite 包,这个是 Kylin 的 SQL 语法解析器,Kylin 源码中版本是 1.13.0-kylin-r3,这里引用 1.13 并不影响

<dependency>
      <groupId>org.apache.calcite</groupId>
      <artifactId>calcite-linq4j</artifactId>
      <version>1.13.0</version>
  </dependency>

第三步:编写函数,必须实现函数 eval,这里对代码做简化处理。

import org.apache.calcite.linq4j.function.Parameter;
  public class getMultiRatioUDF {
      public String eval(@Parameter(name = "str1") double col1, @Parameter(name = "str2") double col2, @Parameter(name = "str3") double col3, @Parameter(name = "str4") double col4) {
        if (col3 !=0 && col4 != 0 )
        {
            return String.format("%.4f",(col1 + col2) / (col1 / col3 + col2 / col4));
        }
        else
        {
            return "null";
        }
    }
}
第四步:打包,部署。工程打包后,部署的 Kylin 安装目录的 lib 目录下

第五步:修改配置文件。修改 kylin.properties,在文件尾巴上添加 kylin.query.udf.getMultiRatio=com.sinosafe.udf.getMultiRatioUDF。这里 kylin.query.udf. 为固定路径,getMultiRatio 是 UDF 的名字,也就是在写 SQL 时使用的名字,这个尽量根据指标名字命名,比如 get***,这样便于管理和理解。com.sinosafe.udf.getMultiRatioUDF 则是类的路径。

第六步:每个节点进行同样操作,重启节点服务,就可以使用了。

  • 使用前 SQL:select round(case when (sum(t.SUMPAIDAMOUNT) <> 0 and sum(t.OUSTDCLMFEE) <> 0) then (sum(t.devamt)+sum(t.servamt))/(sum(t.devamt)/sum(t.SUMPAIDAMOUNT) + sum(t.servamt)/sum(t.OUSTDCLMFEE)) else 0 end,4) as ratio from app_underwrite_risk t 
  • 使用后 SQL:select getMultiRatio(sum(t.devamt),sum(t.servamt),sum(t.SUMPAIDAMOUNT),sum(t.OUSTDCLMFEE)) as ratio from app_underwrite_risk t

测试结果

两种写法得到的结果完全一致,代码节省二分之一,经过大约 20 次左右的测试,在两个查询都有缓存的情况下,时间消耗原 SQL 耗时 0.17 s,使用 UDF 平均耗时 0.13 s,使用 UDF 更快一些。

总结

Kylin 对 UDF 的支持较好,不光可以对指标进行操作,对维度也是有效的,比如针对字符串的一些判断操作,都可以写成 UDF,可以有效减少代码复杂度,并增加查询速度,对于某些行业来说,一些固定的指标计算逻辑或者一些维度处理逻辑,完全可以固化到 UDF 中,完成 SQL 的简化,让代码更加优雅,减少出错。

作者介绍:赵兴成,就职于华安保险,有9年大数据平台建设和开发经验,主要负责公司整个大数据平台的建设,为业务数据分析及经营决策提供数据支持。

申请试用
关注我们