PL/Java自定义函数

使用限制

Java UDF可以实现一些java逻辑计算，强烈建议不要在Java UDF中封装业务

• 强烈建议不要在Java函数中使用任何方式连接数据库，包括但不限于JDBC。
• 暂不支持的数据类型：除表1内容之外的数据类型，包括自定义类型，复杂数据类型（Java Array类及派生类）。
• 暂不支持UDAF，UDTF。

示例

使用PL/Java函数时，需要首先将Java方法的实现打包为jar包并且部署到数据库中，然后使用数据库管理员账号创建函数，考虑兼容性问题，请使用1.8.0_201版本的JDK进行编译。

1. 编译jar包。

   Java方法的实现和出包可以借助IDE来实现，以下是一个通过命令行来进行编译和出包的简单的示例，通过这个简单示例可以创建出一个包含单个方法的jar包文件。

   首先，编写一个Example.java文件，在此文件中实现子字符串大写转换的方法，本例中类名为Example，方法名为upperString，内容如下：

   1 2 3 4 5 6 7

   public class Example { public static String upperString (String text, int beginIndex, int endIndex) { return text.substring(beginIndex, endIndex).toUpperCase(); } }

   然后，创建manifest.txt清单文件，文件内容如下：

   1 2 3 4 5 6

   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

   其中，Manifest-Version定义了manifest文件的版本，Main-Class定义了jar文件的入口类，Specification-Title和Specification-Version属于包的扩展属性，Specification-Title定义了扩展规范的标题，Specification-Version定义了扩展规范的版本，Created-By声明了该文件的生成者，Build-Date声明了该文件构建日期。

   最后，编译java文件并打包得到javaudf-example.jar

   1 2	javac Example.java jar cfm javaudf-example.jar manifest.txt Example.class

   

   jar包的命名规则应符合JDK命名要求，如果含有非法字符，在部署或者使用函数时将出错。

2. 部署jar包。

   Jar包首先需要放置到OBS服务器中，放置方法具体请参见《对象存储服务控制台指南》的上传文件章节。接着创建访问密钥AK/SK，获取访问密钥，请登录管理控制台，将鼠标移至右上角的用户名，单击“我的凭证”，然后在左侧导航树单击“访问密钥”。在访问密钥页面，可以查看已有的访问密钥ID（即AK），如果要同时获取AK和SK，可以单击“新增访问密钥”创建并下载访问密钥。 登录数据库运行gs_extend_library函数，将文件导入到DWS中：

   1	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请用户根据实际所在的区域名称替换。

3. 使用PL/Java函数。

   首先，使用拥有sysadmin权限的数据库用户（例如：dbadmin）登录数据库并创建java_upperstring函数如下：

   1 2 3 4

   CREATE FUNCTION java_upperstring(VARCHAR, INTEGER, INTEGER) RETURNS VARCHAR AS 'Example.upperString' LANGUAGE JAVA;

   

   • 函数java_upperstring中定义的数据类型为DWS的数据类型。该数据类型需要和步骤1中java定义的方法upperString中数据类型一一对应。DWS与Java数据类型的对应关系，请参见表1。
   • AS子句用于指定该函数所调用的Java方法的类名和static方法名，格式为“类名.方法名”。该字段需要和步骤1中java定义的类名和方法名一致。
   • 使用PL/Java函数时，LANGUAGE字段应指定为JAVA。
   • CREATE FUNCTION更多说明，请参见创建函数。

   然后，执行java_upperstring函数：

   1	SELECT java_upperstring('test', 0, 1);

   得到预期结果为：

   1 2 3 4

   java_upperstring --------------------- T (1 row)

4. 授权普通用户使用PL/Java函数。

   创建普通用户，名称为udf_user，密码为'GAUSS@123'。

   1	CREATE USER udf_user PASSWORD 'GAUSS@123';

   授权普通用户udf_user对java_upperstring函数的使用权限。注意，此处需要把函数所在模式和函数的使用权限同时赋予给用户，用户才可以使用此函数。

   1 2	GRANT ALL PRIVILEGES ON SCHEMA public TO udf_user; GRANT ALL PRIVILEGES ON FUNCTION java_upperstring(VARCHAR, INTEGER, INTEGER) TO udf_user;

   以普通用户udf_user登录数据库。

   1	SET SESSION SESSION AUTHORIZATION udf_user PASSWORD 'GAUSS@123';

   执行java_upperstring函数：

   1	SELECT public.java_upperstring('test', 0, 1);

   得到预期结果为：

   1 2 3 4

   java_upperstring --------------------- T (1 row)

5. 删除函数。
   如果不再使用该函数可以进行删除：

   1	DROP FUNCTION java_upperstring;

6. 卸载jar包。

   使用gs_extend_library函数卸载jar包：

   1	SELECT gs_extend_library('rmjar', 'libraryname=example');

SQL定义与使用

• 管理jar包和文件

  拥有sysadmin权限的数据库用户可以使用gs_extend_library函数来部署、查看和删除数据库中的jar包，函数语法如下：

  1	SELECT gs_extend_library('[action]', '[operation]');

  

  • 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封装类名 （详见NULL值处理）。
  • 更多语法说明，请参见CREATE FUNCTION。

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

    CREATE [ OR REPLACE ] FUNCTION function_name ( [ { argname [ argmode ] argtype [ { DEFAULT | := | = } expression ]} [, …] ]) [ RETURNS rettype [ DETERMINISTIC ] ] LANGAUGE JAVA [ { IMMUTABLE | STATBLE | 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。

  DROP FUNCTION [ IF EXISTS ] function_name [ ( [ {[ argmode ] [ argname ] argtype} [, ...] ] ) [ CASCADE | RESTRICT ] ];

  需要注意的是，如果所删除的函数为重载函数（详见重载函数），则删除时需要指明该函数的参数类型，如为非重载函数，则可直接指定函数名进行删除。

• 函数授权

  非sysadmin户无法创建PL/Java函数，sysadmin用户可以赋予其他类型用户使用函数的权限。更多语法说明，请参见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 ];

基本数据类型映射关系

数组类型处理

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)

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)

重载函数

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)

   

   隐式转换的数据类型包括：

   • 可以默认转换为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);

相关GUC参数

• pljava_vmoptions

  会话级别的GUC参数，该参数用于设置JVM的启动参数，例如：

  SET pljava_vmoptions='-Xmx64m –Xms2m –XX:MaxMetaspaceSize=8m';

  pljava_vmoptions可接受的参数包括：

  • JDK8 JVM启动参数
  • JDK8 JVM系统属性参数（以–D开头，如：–Djava.ext.dirs）。
    

    不建议用户设置任何包含目录的参数，可能会导致不可预期的行为。

  • 用户自定义参数（以–D开头，如：–Duser.defined.option）
    

    如果所设置的参数不在上述范围内，视为非法参数，会在调用函数时报错。

    SET pljava_vmoptions=' illegal.option';
    SET
    SELECT java_dummy(5::int);
    ERROR:  UDF Error:cannot use PL/Java before successfully completing its setup.Please check if your pljava_vmoption is set correctly,since we do not ignore illegal parameters.Or check the log for more messages.

• udf_memory_limit

  系统级别的GUC参数，用于限制每个CN、DN执行UDF可以使用的物理内存量，默认为200MB。可通过修改postgresql.conf文件进行配置，配置后需要重启数据库服务后才可生效。

  

  • 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为200MB。查询pv_total_memory_detail视图时可以发现，process_used_memory永远不会超过max_process_memory - (udf_memory_limit - 200MB)。

  • 一个CN执行最简单的Java UDF函数，使用的物理内存量大约为50MB，用户可以根据自己Java函数的内存使用量和并发度设置此参数。新增此参数后，不再建议用户设置UDFWorkerMemHardLimit和FencedUDFMemoryLimit。

• FencedUDFMemoryLimit

  会话级别的GUC参数，用户限制会话发起的单个Fenced UDF Worker进程的最大虚拟内存使用量，设置方法如下：

  SET FencedUDFMemoryLimit='512MB';

  该参数的取值范围为 (150MB, 1G]，当设置大于1G时会立即报错，当设置小于等于150MB时，则会在调用函数时报错。

  

  • 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的日志级别对应关系见下表。

用户可以通过以下方式更改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函数只能由数据库sysdamin用户进行创建，通过GRANT方式赋予其他用户使用权限（详见函数授权）。

同时PL/Java控制用户对文件系统的访问权限，不允许用户在Java方法中对大部分的系统文件进行读操作，不允许所有的写、删除和执行操作。