CrateDB用户自定义函数(UDF)全面指南-优快云博客

本文链接：https://blog.youkuaiyun.com/gitblog_00395/article/details/148548629

CrateDB用户自定义函数(UDF)全面指南

crate CrateDB is a distributed and scalable SQL database for storing and analyzing massive amounts of data in near real-time, even with complex queries. It is PostgreSQL-compatible, and based on Lucene. 项目地址: https://gitcode.com/gh_mirrors/cr/crate

什么是用户自定义函数

用户自定义函数(User-Defined Functions, UDF)是CrateDB提供的一项强大功能，允许开发者在数据库层面扩展SQL功能。通过UDF，你可以使用JavaScript编写自定义逻辑，直接在SQL查询中调用这些函数，实现复杂的数据处理需求。

创建和替换函数

基础创建语法

在CrateDB中创建UDF的基本语法如下：

CREATE FUNCTION 函数名(参数类型1, 参数类型2)
RETURNS 返回类型
LANGUAGE JAVASCRIPT
AS 'JavaScript函数体';

例如创建一个简单的减法函数：

CREATE FUNCTION my_subtract_function(integer, integer)
RETURNS integer
LANGUAGE JAVASCRIPT
AS 'function my_subtract_function(a, b) { return a - b; }';

替换已有函数

使用CREATE OR REPLACE FUNCTION可以创建新函数或替换已有函数：

CREATE OR REPLACE FUNCTION log10(bigint)
RETURNS double precision
LANGUAGE JAVASCRIPT
AS 'function log10(a) {return Math.log(a)/Math.log(10); }';

命名参数

CrateDB支持在函数签名中使用命名参数，虽然这些名称仅用于文档目的，不能在函数体内引用：

CREATE OR REPLACE FUNCTION calculate_distance("start" geo_point, "end" geo_point)
RETURNS real
LANGUAGE JAVASCRIPT
AS 'function calculate_distance(start, end) {
      return Math.sqrt(
           Math.pow(end[0] - start[0], 2),
           Math.pow(end[1] - start[1], 2));
   }';

函数重载

CrateDB支持函数重载，即在同一个schema下可以定义多个同名但参数不同的函数：

-- 第一个版本：两个整数相乘
CREATE FUNCTION my_multiply(integer, integer)
RETURNS integer
LANGUAGE JAVASCRIPT
AS 'function my_multiply(a, b) { return a * b; }';

-- 第二个版本：两个大整数相乘
CREATE FUNCTION my_multiply(bigint, bigint)
RETURNS bigint
LANGUAGE JAVASCRIPT
AS 'function my_multiply(a, b) { return a * b; }';

-- 第三个版本：三个大整数相乘
CREATE FUNCTION my_multiply(bigint, bigint, bigint)
RETURNS bigint
LANGUAGE JAVASCRIPT
AS 'function my_multiply(a, b, c) { return a * b * c; }';

重要提示：避免创建与CrateDB内置函数同名的UDF，这可能导致不可预期的行为。

函数确定性

CrateDB要求UDF必须是确定性的，即对于相同的输入参数，函数必须始终返回相同的结果。这是因为CrateDB可能会缓存函数结果以提高性能。如果函数包含非确定性逻辑（如随机数生成），可能会导致意外的缓存行为。

删除函数

删除函数的语法如下：

-- 基本删除
DROP FUNCTION doc.log10(bigint);

-- 安全删除（函数不存在时不报错）
DROP FUNCTION IF EXISTS doc.log10(integer);

-- 使用命名参数删除
DROP FUNCTION IF EXISTS doc.calculate_distance(start_point geo_point, end_point geo_point);

-- 删除特定schema中的函数
DROP FUNCTION my_schema.log10(bigint);

JavaScript函数实现

语言特性支持

CrateDB使用GraalVM JavaScript引擎执行UDF，兼容ECMAScript 2019规范。需要注意的是：

浏览器特有的对象（如window、console）不可用
执行环境有安全限制，不能访问系统资源
默认情况下只有部分原型函数可用

数据类型处理

JavaScript函数可以处理所有CrateDB数据类型，但某些类型需要特别注意返回值格式：

地理点(geo_point)类型

可以返回以下格式之一：

双精度数组（长度为2）
WKT字符串
GeoJSON对象

-- 返回双精度数组示例
CREATE FUNCTION rotate_point(point geo_point, angle real)
RETURNS geo_point
LANGUAGE JAVASCRIPT
AS 'function rotate_point(point, angle) {
      var cos = Math.cos(angle);
      var sin = Math.sin(angle);
      var x = cos * point[0] - sin * point[1];
      var y = sin * point[0] + cos * point[1];
      return [x, y];
   }';

-- 返回WKT字符串示例
CREATE FUNCTION symmetric_point(point geo_point)
RETURNS geo_point
LANGUAGE JAVASCRIPT
AS 'function symmetric_point (point, angle) {
      var x = - point[0],
          y = - point[1];
      return "POINT (" + x + ", " + y +")";
   }';

地理形状(geo_shape)类型

应返回GeoJSON对象或WKT字符串：

CREATE FUNCTION line("start" array(double precision), "end" array(double precision))
RETURNS object
LANGUAGE JAVASCRIPT
AS 'function line(start, end) {
       return { "type": "LineString", "coordinates" : [start, end] };
   }';

数值处理注意事项

JavaScript引擎会将数字解释为Double、Long或Integer类型。当处理时间戳时需要特别注意：

-- 错误示例：直接返回Date.UTC结果会导致时间戳错误
CREATE FUNCTION utc(bigint, bigint, bigint)
RETURNS TIMESTAMP WITH TIME ZONE
LANGUAGE JAVASCRIPT
AS 'function utc(year, month, day) {
      return Date.UTC(year, month, day, 0, 0, 0);
   }';

-- 正确示例：需要除以1000
CREATE FUNCTION utc(bigint, bigint, bigint)
RETURNS TIMESTAMP WITH TIME ZONE
LANGUAGE JAVASCRIPT
AS 'function utc(year, month, day) {
      return Date.UTC(year, month, day, 0, 0, 0)/1000;
   }';