# DWS PL/Java语言函数 DWS支持Java UDF (用户自定义函数) ，它是一种在数据处理系统中扩展功能的方式，允许开发者使用Java语言编写自定义函数，然后在SQL查询或其他数据处理场景中调用这些函数。 #### 使用限制 Java UDF可以实现一些Java逻辑计算，强烈建议不要在Java UDF中封装业务 - 强烈建议不要在Java函数中使用任何方式连接数据库，包括但不限于JDBC。 - 暂不支持的数据类型：除[表2]内容之外的数据类型，包括自定义类型，复杂数据类型（Java Array类及派生类）。 - 暂不支持UDAF，UDTF。 - DWS PL/Java基于开源PL/Java 1.5.5开发，所使用的JRE版本为1.8.0.432。 #### Java UDF使用流程 使用PL/Java函数时，用户可以使用Java IDE编写Java方法，并将包含这些方法的jar包文件部署到DWS数据库中，然后使用数据库管理员账号创建函数。考虑兼容性问题，请使用1.8.0.432版本的JRE进行编译。 1. **编译jar包** 。 Java方法的实现和出包可以借助IDE来实现，以下是一个通过命令行来进行编译和出包的简单的示例，通过这个简单示例可以创建出一个包含单个方法的jar包文件。 首先，编写一个Example.java文件（通常需要使用static关键字），在此文件中实现子字符串大写转换的方法，本例中类名为Example，方法名为upperString，内容如下 ``` public class Example { public static String upperString (String text, int beginIndex, int endIndex) { return text.substring(beginIndex, endIndex).toUpperCase(); } } ``` 然后，创建manifest.txt清单文件，文件内容如下，各参数的解释如[表1]所示： ``` Manifest-Version: 1.0 Main-Class: Example Specification-Title: "Example" Specification-Version: "1.0" Created-By: 1.6.0_35-b10-428-11M3811 Build-Date: 08/14/2018 10:09 AM ``` 表1manifest.txt清单文件 | 参数项 | 描述 | 示例 | |:---|:---|:---| | Manifest-Version | 表示清单文件的版本，固定为1.0，与JAR规范兼容。 无需修改，保持默认即可。 | 1.0 | | Main-Class | 指定JAR包的入口类（包含main方法的类），即主应用程序类的类名。当通过java -jar执行JAR包时，会调用此类。 | Example | | Specification-Title | 项目或模块的名称标识符，用于描述JAR包的功能，例如本例：子字符串大写转换。 | Example | | Specification-Version | 项目的规范版本号，遵循语义化版本，用户自定义，例如首次发布为1.0，后续迭代为2.0。 | 1.0 | | Created-By | 定义了生成此清单文件所基于的Java实现的版本和供应商，此属性由jar工具生成，格式为 JDK版本号-构建号。 - 默认由构建工具（如 Maven）自动填充，通常无需手动修改。 - 确保构建环境和运行环境的JDK版本一致。 | 1.6.0_35-b10-428-11M3811 | | Build-Date | JAR 的构建时间戳，用于追踪构建历史，便于回滚或调试。 | 08/14/2018 10:09 AM | 最后，编译Java文件并打包得到javaudf-example.jar ``` javac Example.java jar cfm javaudf-example.jar manifest.txt Example.class ``` ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/notice_3.0-zh-cn.png) jar包的命名规则应符合JDK命名要求，如果含有非法字符，在部署或者使用函数时将出错。 2. **部署jar** **包** 。 Jar包首先需要放置到OBS服务器中，放置方法具体请参见*《对象存储服务控制台指南》* 的上传文件章节。接着创建访问密钥AK/SK，获取访问密钥的具体步骤，请参见[创建访问密钥（AK和SK）](https://support.huaweicloud.com/migration-dws/dws_15_0007.html)章节。登录数据库，使用具有管理员权限用户运行gs_extend_library函数，将文件导入到DWS中： ``` SELECT gs_extend_library('addjar', 'obs://bucket/path/javaudf-example.jar accesskey=access_key_value_to_be_replaced secretkey=secret_access_key_value_to_be_replaced region=region_name libraryname=example'); ``` gs_extend_library函数如何使用请参见[管理jar包和文件]。函数中的AK/SK值，请用户根据实际获取值替换。region_name请用户根据实际所在的区域名称替换。bucket为OBS服务器上的桶名称。 3. **使用PL/Java** **函数** 。 首先，使用拥有sysadmin权限的数据库用户（例如：dbadmin）登录数据库并创建java_upperstring函数如下： ``` CREATE FUNCTION java_upperstring(VARCHAR, INTEGER, INTEGER) RETURNS VARCHAR AS 'Example.upperString' LANGUAGE JAVA; ``` ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png) - 函数java_upperstring中定义的数据类型为DWS的数据类型。该数据类型需要和[步骤1]中Java定义的方法upperString中数据类型一一对应。DWS与Java数据类型的对应关系，请参见[表2]。 - AS子句用于指定该函数所调用的Java方法的类名和static方法名，格式为"类名.方法名"。该字段需要和[步骤1]中Java定义的类名和方法名一致。 - 使用PL/Java函数时，LANGUAGE字段应指定为JAVA。 - CREATE FUNCTION更多说明，请参见[创建函数]。 然后，执行java_upperstring函数： ``` SELECT java_upperstring('test', 0, 1); ``` 得到预期结果为： ``` java_upperstring --------------------- T (1 row) ``` 4. **授权普通用户使用** **PL/Java** **函数** **。** 创建普通用户，名称为udf_user。 ``` CREATE USER udf_user PASSWORD 'password'; ``` 授权普通用户udf_user对java_upperstring函数的使用权限。注意，此处需要把函数所在模式和函数的使用权限同时赋予给用户，用户才可以使用此函数。 ``` GRANT ALL PRIVILEGES ON SCHEMA public TO udf_user; GRANT ALL PRIVILEGES ON FUNCTION java_upperstring(VARCHAR, INTEGER, INTEGER) TO udf_user; ``` 以普通用户udf_user登录数据库。 ``` SET SESSION AUTHORIZATION udf_user PASSWORD 'password'; ``` 执行java_upperstring函数： ``` SELECT public.java_upperstring('test', 0, 1); ``` 得到预期结果为： ``` java_upperstring --------------------- T (1 row) ``` 5. **删除函数。** 如果不再使用该函数可以进行删除： ``` DROP FUNCTION java_upperstring; ``` 6. **卸载jar** **包** 。 使用gs_extend_library函数卸载jar包： ``` SELECT gs_extend_library('rmjar', 'libraryname=example'); ``` #### 基本数据类型映射关系 表2**PL/Java默认数据类型映射关系** | DWS | **Java** | |:---|:---| | BOOLEAN | boolean | | "char" | byte | | bytea | byte\[\] | | SMALLINT | short | | INTEGER | int | | BIGINT | long | | FLOAT4 | float | | FLOAT8 | double | | CHAR | java.lang.String | | VARCHAR | java.lang.String | | TEXT | java.lang.String | | name | java.lang.String | | DATE | java.sql.Timestamp | | TIME | java.sql.Time (stored value treated as local time) | | TIMETZ | java.sql.Time | | TIMESTAMP | java.sql.Timestamp | | TIMESTAMPTZ | java.sql.Timestamp | #### UDF示例：SQL定义与使用 - **管理jar包和文件** 拥有sysadmin权限的数据库用户可以使用gs_extend_library函数部署、查看和删除数据库中的jar包，语法如下： ``` SELECT gs_extend_library('[action]', '[operation]'); ``` ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/note_3.0-zh-cn.png) - **action** 表明操作动作，值可以为： - ls表示查看数据库中jar包，会对各节点的文件进行MD5值一致性检查。 - addjar表示将OBS服务器中的jar包部署到数据库中。 - rmjar表示将数据库中的jar包删除。 - **operation** 表明操作字符串，格式为： obs://\[bucket\]/\[source_filepath\] accesskey=\[accesskey\] secretkey=\[secretkey\] region=\[region\] libraryname=\[libraryname\] - bucket: OBS文件所属桶名，不可缺省。 - source_filepath: OBS服务器上的文件路径，仅支持jar文件。 - accesskey: obs服务获得的accesskey，不可缺省。 - secret_key: obs服务获得的secretkey，不可缺省。 - region: 自定义函数jar包所存放的OBS桶所属的区域，不可缺省。 - libraryname: 自定义库名，此自定义命名用于DWS内对jar文件的调用。当action为addjar和rmjar时，该参数不可缺省，当action为ls时，该参数可以缺省。注意，自定义库名不允许含有/\|;\&$\<\>\\'{}"()\[\]\~\*?!等字符。 - **创建函数** PL/Java函数通过CREATE FUNCTION语法创建，并且定义为LANGUAGE JAVA，且包含RETURNS和AS子句。 - CREATE FUNCTION时指定所创建函数的名称，以及参数类型； - RETURNS子句用于指定该函数的返回类型； - AS子句与用于指定该函数所调用的Java方法的类名和static方法名，如果需要向Java方法传递NULL值作为入参，还需要指定该参数类型所对应的Java封装类名 （详见[UDF示例：NULL值处理]）。 - 更多语法说明，请参见《数据仓库服务SQL语法参考》中"CREATE FUNCTION"章节。 ``` CREATE [ OR REPLACE ] FUNCTION function_name ( [ { argname [ argmode ] argtype [ { DEFAULT | := | = } expression ]} [, …] ]) [ RETURNS rettype [ DETERMINISTIC ] ] LANGUAGE JAVA [ { IMMUTABLE | STABLE | VOLATILE } | [ NOT ] LEAKPROOF | WINDOW | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT |STRICT } | {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER} | { FENCED } | COST execution_cost | ROWS result_rows | SET configuration_parameter { {TO |=} value | FROM CURRENT} ] […] { AS 'class_name.method_name' ( { argtype } [, …] ) } ``` - **使用函数** 执行时，PL/Java会根据jar包名的字母序列，在所有部署的jar包中寻找函数指定的Java类，并调用首次找到的类中函数所指定的Java方法，并返回调用结果。 - **删除函数** PL/Java函数通过DROP FUNCTION语法删除函数。 ``` DROP FUNCTION [ IF EXISTS ] function_name [ ( [ {[ argmode ] [ argname ] argtype} [, ...] ] ) [ CASCADE | RESTRICT ] ]; ``` 需要注意的是，如果所删除的函数为重载函数（详见[重载函数]），则删除时需要指明该函数的参数类型，如为非重载函数，则可直接指定函数名进行删除。 - **函数授权** 非sysadmin用户无法创建PL/Java函数，sysadmin用户可以赋予其他类型用户使用函数的权限。详细的语法说明，可参考《数据仓库服务SQL语法参考》中"GRANT"章节。 ``` GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...] | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]; ``` #### UDF示例：数组类型处理 DWS支持基础数组类型的转换，只需要在创建函数时在数据类型后追加 \[\] 即可，例如： ``` CREATE FUNCTION java_arrayLength(INTEGER[]) RETURNS INTEGER AS 'Example.getArrayLength' LANGUAGE JAVA; ``` Java代码类似于： ``` public class Example { public static int getArrayLength(Integer[] intArray) { return intArray.length; } } ``` 那么下面的调用的语句后： ``` SELECT java_arrayLength(ARRAY[1, 2, 3]); ``` 得到预期结果应该如下所示： ``` java_arrayLength --------------------- 3 (1 row) ``` #### UDF示例：NULL值处理 对于默认与Java的简单类型进行映射转换的那些DWS数据类型，是无法处理NULL值的，如果希望在Java方法里能够获得并处理从DWS中传入的NULL值，可以使用Java的封装类，并通过以下方式在AS子句中指定该Java封装类： ``` CREATE FUNCTION java_countnulls(INTEGER[]) RETURNS INTEGER AS 'Example.countNulls(java.lang.Integer[])' LANGUAGE JAVA; ``` Java代码类似于： ``` public class Example { public static int countNulls(Integer[] intArray) { int nullCount = 0; for (int idx = 0; idx < intArray.length; ++idx) { if (intArray[idx] == null) nullCount++; } return nullCount; } } ``` 那么下面的调用的语句后： ``` SELECT java_countNulls(ARRAY[null, 1, null, 2, null]); ``` 得到的预期结果应该如下所示： ``` java_countNulls -------------------- 3 (1 row) ``` #### UDF示例：重载函数 PL/Java支持重载函数，因此可以创建同名函数，或者调用Java代码中的重载方法。步骤如下： 1. **创建重载函数** 例如，在Java中可以实现两个方法名相同，输入参数类型不同的方法dummy(int) 和dummy(String) ``` public class Example { public static int dummy(int value) { return value*2; } public static String dummy(String value) { return value; } } ``` 并在DWS中创建两个同名函数分别指定为上述两个方法： ``` CREATE FUNCTION java_dummy(INTEGER) RETURNS INTEGER AS 'Example.dummy' LANGUAGE JAVA; CREATE FUNCTION java_dummy(VARCHAR) RETURNS VARCHAR AS 'Example.dummy' LANGUAGE JAVA; ``` 2. **调用重载函数** 在调用重载函数时，DWS会根据输入的参数类型去调用匹配该类型的Java方法。因此上述两个函数的调用结果如下所示： ``` SELECT java_dummy(5); java_dummy ----------------- 10 (1 row) SELECT java_dummy('5'); java_dummy --------------- 5 (1 row) ``` 需要注意的是，由于DWS对数据类型存在隐式转换的情况，因此建议在调用重载函数时，指定输入参数的类型，例如： ``` SELECT java_dummy(5::varchar); java_dummy ---------------- 5 (1 row) ``` 此时会优先匹配所指定的参数类型，如果不存在指定参数类型的Java方法，则会对参数进行隐式转换匹配转换后的参数类型对应的Java方法。 ``` SELECT java_dummy(5::INTEGER); java_dummy ----------------- 10 (1 row) DROP FUNCTION java_dummy(INTEGER); SELECT java_dummy(5::INTEGER); java_dummy ---------------- 5 (1 row) ``` ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/notice_3.0-zh-cn.png) 隐式转换的数据类型包括： - 可以默认转换为INTEGER类型的包括：SMALLINT - 可以默认转换为BIGINT类型的包括：SMALLINT、INTEGER - 可以默认转换为BOOL类型的包括：TINYINT、SMALLINT、INTEGER、BIGINT - 可以默认转换为TEXT类型的包括：CHAR、NAME、BIGINT、INTEGER、SMALLINT、 TINYINT、RAW、FLOAT4、FLOAT8、BPCHAR、VARCHAR、NVARCHAR2、DATE、TIMESTAMP、TIMESTAMPTZ、NUMERIC、SMALLDATETIME - 可以默认转换为VARCHAR类型的包括：TEXT、CHAR、BIGINT、INTEGER、SMALLINT、TINYINT、RAW、FLOAT4、FLOAT8、BPCHAR、DATE、NVARCHAR2、TIMESTAMP、NUMERIC、SMALLDATETIME 3. **删除重载函数** 对于重载函数，删除时需要指定函数的参数类型，否则无法删除。 ``` DROP FUNCTION java_dummy(INTEGER); ``` #### UDF相关GUC参数 - **udf_memory_limit** 系统级别的GUC参数，用于限制每个CN、DN执行UDF可以使用的物理内存量，默认为0.05 \* max_process_memory。可通过修改postgresql.conf文件进行配置，配置后需要重启数据库服务后才可生效。 ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/notice_3.0-zh-cn.png) - udf_memory_limit是max_process_memory的一部分。每个CN、DN启动时，会预留（udf_memory_limit - 200MB）内存供UDF Worker进程使用。CN、DN和UDF Worker是不同的进程，但CN、DN自动少用一部分内存，把这部分内存节省下来供UDF Worker进程使用。 例如：在某DN上把max_process_memory设置为10GB，udf_memory_limit设置为4GB，则此DN最多使用10GB - (4GB - 200MB)=6.2GB内存。即使用户没有执行任何UDF，则此DN也最多只能使用6.2GB内存。默认情况下，udf_memory_limit为0.05 \* max_process_memory。查询pv_total_memory_detail视图时可以发现，process_used_memory永远不会超过max_process_memory - (udf_memory_limit - 200MB)。 - UDF进程断连时会有报错提示。当出现"memory in UDF Work Process is limited by cgroup: \[usage: xxx, max_usage_history: xxx, limit: xxx\]"时，可以根据提示判断当前内存使用情况。报错中的usage表示UDF进程被KILL后剩余UDF进程的总物理内存使用量；max_usage_history表示UDF实例启动后历史上使用过的最大内存量，limit表示UDF进程使用内存的最大限制量。如果max_usage_history值接近limit值说明当前集群存在内存超过限制的风险，需要优化业务或按实际情况调整udf_memory_limit参数限制。 - 一个CN执行最简单的Java UDF函数，使用的物理内存量大约为50MB，用户可以根据自己Java函数的内存使用量和并发度设置此参数。新增此参数后，不再建议用户设置UDFWorkerMemHardLimit和FencedUDFMemoryLimit。 - 当UDF进程并发度过大，内存超出udf_memory_limit设置值时会导致进程退出等非预期情况，该场景下执行结果可能不可靠，强烈建议根据实际情况进行参数设置，保留足够内存余量。如果系统记录有/var/log/messages，可查看该日志文件是否存在因超过cgroup内存限制而造成内存不足。内存严重不足时，甚至可能导致UDF master进程退出，可以查看UDF日志进行分析，默认的UDF日志路径在$GAUSSLOG/cm/cm_agent/pg_log下。例如，出现以下日志时就说明内存资源严重不足，导致了UDF master进程退出，需要检查udf_memory_limit参数设置。 0 \[BACKEND\] FATAL: poll() failed: Bad address, please check the parameter:udf_memory_limit to make sure there is enough memory. - **FencedUDFMemoryLimit** 会话级别的GUC参数，用户限制会话发起的单个Fenced UDF Worker进程的最大虚拟内存使用量，设置方法如下： ``` SET FencedUDFMemoryLimit='512MB'; ``` 该参数的取值范围为 (150MB, 1G\]，当设置大于1G时会立即报错，当设置小于等于150MB时，则会在调用函数时报错。 ![](https://support.huaweicloud.com/devg-dws/public_sys-resources/notice_3.0-zh-cn.png) - FencedUDFMemoryLimit设置为0，表示不控制Fenced UDF Worker的虚拟内存使用量。 - 建议通过设置udf_memory_limit控制Fenced UDF Worker使用的物理内存量。不建议用户使用FencedUDFMemoryLimit，尤其在使用Java UDF时不建议用户设置此参数。但是如果用户非常清楚设置该参数带来的影响，可以参考下列信息进行设置： - C UDF worker启动之后，占用的虚拟内存约为200MB，占用的物理内存约为16MB。 - Java UDF worker启动之后，占用的虚拟内存约为2.5GB，占用的物理内存约为50MB。 #### 异常处理 如果在JVM中发生异常，PL/Java的异常处理机制会将异常时JVM的堆栈信息输出到客户端。 #### 日志 PL/Java使用标准的Java Logger。 因此，用户可以通过如下方式记录日志： ``` Logger.getAnonymousLogger().config( "Time is " + new Date(System.currentTimeMillis())); ``` 初始化的Java Logger类会默认设置为CONFIG级别，对应为DWS的LOG级别。Java Logger类输出的日志消息都会重定向到DWS后端，并写入到服务器日志或显示在用户界面上。MPPDB服务器日志将记录LOG、WARNING、ERROR级别的信息，而SQL用户界面将显示WARNING和ERROR级别的日志消息。Java Logger级别与DWS的日志级别对应关系见下表。 表3**PL/Java日志级别** | **java.util.logging.Level** | **DWS** **日志级别** | |:---|:---| | **SERVER** | ERROR | | **WARNING** | WARNING | | **CONFIG** | LOG | | **INFO** | INFO | | **FINE** | DEBUG1 | | **FINER** | DEBUG2 | | **FINEST** | DEBUG3 | 用户可以通过以下方式更改Java Logger的记录级别。例如通过下面的Java代码修改Java Logger级别为SEVERE，此时再记录WARNING级别的日志时，日志消息（msg）就不会再写入到DWS日志中。 ``` Logger log = Logger.getAnonymousLogger(); Log.setLevel(Level.SEVERE); log.log(Level.WARNING, msg); ``` #### 安全问题 在DWS中，PL/Java是一种untrusted语言，PL/Java函数只能由数据库sysadmin用户进行创建，通过GRANT方式赋予其他用户使用权限（详见[函数授权]）。 同时PL/Java控制用户对文件系统的访问权限，不允许用户在Java方法中对大部分的系统文件进行读操作，不允许所有的写、删除和执行操作。