UDF自定义函数详解

本篇介绍如何在各数据库平台创建和使用自定义函数（UDF），涵盖Java UDF、Python UDF及内置函数扩展。

一、概述

UDF（User Defined Function）允许用户根据业务需求扩展SQL函数能力，适用于复杂计算逻辑、内置函数无法满足的场景。

1.1 UDF类型

二、MySQL UDF

2.1 C/C++ UDF开发

MySQL原生支持C/C++编写的UDF，需要编译为动态链接库。

基本结构：

// hello.cc
#include 
#include 

extern "C" {
    // 初始化函数
    my_bool hello_init(UDF_INIT *init, UDF_ARGS *args, char *message) {
        // 校验参数
        if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT) {
            strcpy(message, "Hello requires one string argument");
            return 1;
        }
        return 0;
    }

    // 销毁函数
    void hello_deinit(UDF_INIT *init) {
        // 清理资源
    }

    // 主函数
    char *hello(UDF_INIT *init, UDF_ARGS *args, char *result,
                unsigned long *length, char *is_null, char *error) {
        static char buffer[256];
        snprintf(buffer, sizeof(buffer), "Hello, %s!", args->args[0]);
        *length = strlen(buffer);
        return buffer;
    }
}

编译与部署：

# 编译
g++ -shared -fPIC -I/usr/include/mysql hello.cc -o libhello.so

# 部署到MySQL
sudo cp libhello.so /usr/lib/mysql/plugin/
sudo chmod 755 /usr/lib/mysql/plugin/libhello.so

# 注册函数
CREATE FUNCTION hello RETURNS STRING SONAME 'libhello.so';

# 测试
SELECT hello('World');  -- Hello, World!

# 查看已注册UDF
SELECT * FROM mysql.func;

2.2 MySQL 8.0 Java UDF

MySQL 8.0+支持Java编写的UDF（需要MySQL Shell或企业版）。

// MyJavaUDF.java
public class MyJavaUDF {
    public static String concatPrefix(String input, String prefix) {
        return prefix + input;
    }

    public static double calculateArea(double radius) {
        return Math.PI * radius * radius;
    }
}

-- 创建Java UDF
CREATE FUNCTION concat_prefix(input VARCHAR(255), prefix VARCHAR(255))
RETURNS VARCHAR(255)
SONAME 'MyJavaUDF.jar';

CREATE FUNCTION calculate_area(radius DOUBLE)
RETURNS DOUBLE
SONAME 'MyJavaUDF.jar';

-- 测试
SELECT concat_prefix('World', 'Hello ');  -- Hello World
SELECT calculate_area(5);  -- 78.5398

三、Oracle UDF

3.1 PL/SQL函数

Oracle使用PL/SQL创建自定义函数。

-- 创建函数
CREATE OR REPLACE FUNCTION get_discount_price(
    p_price IN NUMBER,
    p_discount_rate IN NUMBER DEFAULT 0.1
) RETURN NUMBER IS
BEGIN
    RETURN ROUND(p_price * (1 - p_discount_rate), 2);
END get_discount_price;
/

-- 调用
SELECT get_discount_price(100, 0.2) FROM dual;  -- 80
SELECT get_discount_price(100) FROM dual;       -- 90（使用默认值）

-- 在SQL中使用
SELECT product_name, price, get_discount_price(price, 0.15) AS discounted_price
FROM products;

3.2 Oracle Java UDF（Oracle JVM）

// MyOracleUDF.java
public class MyOracleUDF {
    public static String reverseString(String input) {
        if (input == null) return null;
        return new StringBuilder(input).reverse().toString();
    }
}

-- 加载Java类
loadjava -u username/password MyOracleUDF.class

-- 创建函数
CREATE OR REPLACE FUNCTION reverse_string(input VARCHAR2)
RETURN VARCHAR2 AS LANGUAGE JAVA
NAME 'MyOracleUDF.reverseString(java.lang.String) return java.lang.String';
/

-- 调用
SELECT reverse_string('Hello') FROM dual;  -- olleH

四、ClickHouse UDF

4.1 Lambda表达式（简单UDF）

ClickHouse支持在查询中直接定义Lambda函数。

-- 定义临时Lambda
SELECT arrayFilter(x -> x > 0, [-1, 2, -3, 4]) AS result;  -- [2,4]

-- 使用higherOrderFunction
SELECT transform(['a', 'b', 'c'], ['a', 'b'], ['A', 'B'], 'Unknown') AS result;
-- ['A', 'B', 'Unknown']

4.2 ClickHouse Java UDF

ClickHouse通过ExecutableUserDefinedFunction支持外部程序UDF。

JSON配置文件：

// /etc/clickhouse-server/udf/hello_udf.xml


    
        executable
        HELLO
        String
        String
        hello_udf.py
        JSONEachRow
    

Python脚本：

#!/usr/bin/env python3
# hello_udf.py
import sys
import json

for line in sys.stdin:
    data = json.loads(line.strip())
    result = f"Hello, {data.get('input', '')}!"
    print(json.dumps({"result": result}))

使用：

SELECT HELLO('World') AS greeting;
-- Hello, World!

4.3 ClickHouse SQL UDF

-- 创建SQL函数
CREATE FUNCTION get_domain AS (url) ->
    splitByString('/', url)[3];

CREATE FUNCTION is_mobile AS (ua) ->
    ua LIKE '%Mobile%' OR ua LIKE '%Android%';

-- 使用
SELECT get_domain('https://www.example.com/path') AS domain;
-- www.example.com

五、Hive UDF

5.1 Java UDF开发

简单UDF：

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class LowerCaseUDF extends UDF {
    public Text evaluate(Text input) {
        if (input == null) return null;
        return new Text(input.toString().toLowerCase());
    }
}

复杂类型UDF（处理Array/Map）：

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.ArrayWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.io.Writable;

public class ArrayLengthUDF extends UDF {
    public Integer evaluate(ArrayWritable input) {
        if (input == null) return null;
        Writable[] values = input.get();
        return values != null ? values.length : 0;
    }
}

5.2 聚合UDF（UDAF）

import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;

public class CollectUDAF extends UDAF {
    public static class Evaluator implements UDAFEvaluator {
        private List values = new ArrayList<>();

        public void init() {
            values.clear();
        }

        public boolean iterate(String value) {
            if (value != null) {
                values.add(value);
            }
            return true;
        }

        public List terminatePartial() {
            return values;
        }

        public boolean merge(List other) {
            if (other != null) {
                values.addAll(other);
            }
            return true;
        }

        public List terminate() {
            return values;
        }
    }
}

5.3 部署与注册

# 编译并打包
mvn clean package -DskipTests

# 上传JAR到HDFS
hdfs dfs -put /path/to/udf.jar /user/hive/udf/

# 注册临时函数（当前会话有效）
ADD JAR /path/to/udf.jar;
CREATE TEMPORARY FUNCTION lower_case AS 'com.example.LowerCaseUDF';

# 永久函数（需要元数据存储）
CREATE FUNCTION db.lower_case AS 'com.example.LowerCaseUDF'
USING JAR 'hdfs:///user/hive/udf/udf.jar';

# 使用
SELECT lower_case(name) FROM users;
SELECT collectUDAF(phone_number) FROM users;

六、MaxCompute UDF

6.1 Python UDF

MaxCompute支持直接编写Python UDF。

# odps_udf.py
from odps.udf import annotate

@annotate('string->string')
class LowerCase(object):
    def evaluate(self, input_str):
        if input_str is None:
            return None
        return input_str.lower()

@annotate('double,double->double')
class CalculateDistance(object):
    def evaluate(self, x1, y1, x2, y2):
        import math
        return math.sqrt((x2-x1)**2 + (y2-y1)**2)

-- 注册Python UDF
CREATE FUNCTION lower_case AS 'odps_udf.LowerCase' USING 'odps_udf.py';

-- 使用
SELECT lower_case('HELLO WORLD') FROM table1;

6.2 Java UDF

import com.aliyun.odps.udf.UDF;

public class GetDomain extends UDF {
    public String evaluate(String url) {
        if (url == null) return null;
        String[] parts = url.split("/");
        if (parts.length >= 3) {
            return parts[2];
        }
        return null;
    }
}

-- 注册Java UDF
CREATE FUNCTION get_domain AS 'com.example.GetDomain'
USING 'odps-udf.jar,odps-udf-dist.jar';

-- 使用
SELECT url, get_domain(url) AS domain FROM urls;

6.3 内置函数扩展

MaxCompute还支持通过GET_IDENTITY系列函数进行安全扩展。

七、Hologres UDF

7.1 PostgreSQL兼容函数

Hologres兼容PostgreSQL，支持多种方式创建函数。

-- SQL函数
CREATE OR REPLACE FUNCTION concat_with_prefix(prefix TEXT, input TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN prefix || input;
END;
$$ LANGUAGE plpgsql;

-- 测试
SELECT concat_with_prefix('Hello, ', 'World!');  -- Hello, World!

7.2 外部语言函数（如果有扩展）

-- 使用foreign data wrapper扩展
CREATE FOREIGN DATA WRAPPER custom_wrapper HANDLER custom_handler;

八、通用最佳实践

8.1 UDF设计原则

8.2 性能注意事项

-- ❌ 避免：在大数据集上使用复杂UDF
SELECT complex_udf(column) FROM huge_table;

-- ✅ 推荐：先过滤再应用UDF
SELECT simple_udf(column)
FROM huge_table
WHERE column IS NOT NULL AND filter_condition;

8.3 调试技巧

-- 添加日志（Oracle示例）
CREATE OR REPLACE FUNCTION debug_udf(p_input VARCHAR2)
RETURN VARCHAR2 IS
    v_result VARCHAR2(100);
BEGIN
    v_result := process_logic(p_input);
    DBMS_OUTPUT.PUT_LINE('Input: ' || p_input || ', Result: ' || v_result);
    RETURN v_result;
END;

九、总结

选择合适的UDF实现方式，可以显著提升复杂场景的开发效率和计算性能。