doris udf -- 避免使用递归CTE
一、背景
这里有一个场景，在部门表里有 部门id (dept_id) 和 父部门id (parent_id) ，父部门id同时也是部门id 。现在要查部门id下所有的子部门id，但是不知道部门层级，部门关系可能也会调整。
二、需求分析
基于以上背景，是无法写一个固定的临时查询，因为无法确定要套几层 。
有些数据库支持递归CTE，然而doris 不支持 。
因此只能写udf实现了。
三、doris java udf
关于udf 有两个思路
① 父部门id 作为入参，在udf中查数据库，递归出来所有子部门
② 把子-父部门id作为整体参数 + 目标父部门id 两个参数，然后通过udf计算出所有的子部门
如果在udf中查数据库会性能很慢，且需要依赖connection，导致依赖包很大，因此不合适 。
好在doris有map 数据类型，把子-父部门 做成key-value 作为参数，这样udf只需要进行计算，能大大提高效率 。
3.1 实现
使用 Java 代码编写 UDF，UDF 的主入口必须为 evaluate 函数。
然后用 evaluate 调用 递归函数 。这里递归函数不是重点，不赘述。
public class get_sub_departments extends UDF {
// 入口
public String evaluate(HashMap<Integer, Integer> deptMap, Integer parentDeptId) {
if (deptMap == null || parentDeptId == null) {
return null;
}
List<Integer> result = new ArrayList<>();
result = findDeptsByParentId(deptMap, parentDeptId);
if (result.size()>0){
Collections.sort(result);
}
return result.toString();
}
// 递归，查询父部门是 parentDeptId 的所有子部门
public List<Integer> findDeptsByParentId(HashMap<Integer, Integer> deptMap, Integer parentDeptId) {
List<Integer> result = new ArrayList<>();
for (Integer deptId : deptMap.keySet()) {
if (deptMap.get(deptId).equals(parentDeptId)) {
result.add(deptId);
// 递归查找子部门
result.addAll(findDeptsByParentId(deptMap, deptId));
}
}
return result;
}
}
3.2 pom
这里附项目pom文件
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>2.3.5</version>
<exclusions>
<exclusion>
<groupId>org.pentaho</groupId>
<artifactId>*</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<finalName>doris_java_udf</finalName>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-jar-plugin</artifactId>
<version>3.2.2</version>
<configuration>
<archive>
<manifest>
<mainClass>org.apache.doris.udf.AddOne</mainClass>
</manifest>
</archive>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-assembly-plugin</artifactId>
<version>3.3.0</version>
<configuration>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
<archive>
<manifest>
<mainClass>org.apache.doris.udf.AddOne</mainClass>
</manifest>
</archive>
</configuration>
<executions>
<execution>
<phase>package</phase>
<goals>
<goal>single</goal>
</goals>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
3.3 打包上传
依次 clean package
上传时，只需要使用瘦包即可 。
3.4 创建udf
上传jar时，实现的 jar 包可以放在本地也可以存放在远程服务端通过 HTTP 下载，但必须让每个 FE 和 BE 节点都能获取到 jar 包。
CREATE FUNCTION get_sub_departments(map<int,int>,int) RETURNS String PROPERTIES (
"file"="file:///opt/doris/udf/doris_java_udf.jar",
"symbol"="org.apache.doris.udf.get_sub_departments",
"always_nullable"="true",
"type"="JAVA_UDF"
);
这里需要明确函数的名字，入参类型，jar路径和入口
3.5 使用UDF
用户使用 UDF 必须拥有对应数据库的 SELECT 权限。
UDF 的使用与普通的函数方式一致，唯一的区别在于，内置函数的作用域是全局的，而 UDF 的作用域是 DB 内部。
当链接 Session 位于数据内部时，直接使用 UDF 名字会在当前 DB 内部查找对应的 UDF。否则用户需要显示的指定 UDF 的数据库名字，例如 dbName.funcName。
在生产中，建议建一个udf库，用于存放所有的udf 。
SELECT
get_sub_departments ( tt.dept_parent, 1276 ) AS r
FROM
(
SELECT
tmp.a,
map_agg ( tmp.dept_id, tmp.parent_id ) AS dept_parent
FROM
( SELECT dept.dept_id, dept.parent_id, 1 AS a FROM my_table AS dept ) AS tmp
GROUP BY
tmp.a
) AS tt
这里使用map_agg 函数，把部门id 父部门id转换成 map类型 作为入参，就能动态查询 1276 下所有的子部门id 。
到此就实现了。
原文地址：https://blog.csdn.net/weixin_45399602/article/details/143772595
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！