如何简化 SQL 语句之 UDF 实践

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. 易出错，如果有开发人员对指标定义不熟悉或者理解有偏差，极易造成计算结果错误。
3. 
   

如何解决？

用过 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年大数据平台建设和开发经验，主要负责公司整个大数据平台的建设，为业务数据分析及经营决策提供数据支持。