vertica-java-自定义函数类(UDX)的部署

前提条件：已经写好自定义函数的java类了,这里是AddAnyIntsInfo.java 

具体如何写UDX见：https://github.com/vertica/UDx-Examples.git

vertica官方文档：9.3.x Documentation | OpenText™ Vertica™

在写udx的时候注意：

当SDK将Vertica时间戳转换为Java时间戳时，它使用JVM的时区。如果JVM运行的时区与Vertica使用的时区不同，那么结果可能会令人困惑。

Vertica在数据库中以UTC格式存储时间戳。（如果设置了数据库时区，则在查询时进行转换。）要防止来自JVM时区的错误，请将以下代码添加到UDx的处理方法中：

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

/* Copyright (c) 2005 - 2016 Hewlett Packard Enterprise Development LP  -*- Java -*-*/
/*
 *
 * Description: Example User Defined Scalar Function: Add any number of ints
 *
 * Create Date: June 01, 2013
 */


package com.unisinsight.CustomUDXs;

import com.vertica.sdk.*;

import java.util.ArrayList;
import java.util.TimeZone;

/*
 * This is a simple function that adds any numebr of integers and returns the result
 */
public class AddAnyIntsInfo extends ScalarFunctionFactory {
    @Override
    public void getPrototype(ServerInterface srvInterface,
                             ColumnTypes argTypes,
                             ColumnTypes returnType) {
        argTypes.addAny();
        returnType.addInt();
    }

    public class AddAnyInts extends ScalarFunction {
        /*
         * This method processes a block of rows in a single invocation.
         *
         * The inputs are retrieved via argReader
         * The outputs are returned via resWriter
         */
        @Override
        public void processBlock(ServerInterface srvInterface,
                                 BlockReader arg_reader,
                                 BlockWriter res_writer)
                throws UdfException, DestroyInvocation {
            // 防止jvm时区影响
            TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
            SizedColumnTypes inTypes = arg_reader.getTypeMetaData();
            ArrayList<Integer> argCols = new ArrayList<Integer>(); // Argument column indexes.
            inTypes.getArgumentColumns(argCols);
            // While we have inputs to process
            do {
                long sum = 0;
                for (int i = 0; i < argCols.size(); ++i) {
                    long a = arg_reader.getLong(i);
                    sum += a;
                }
                res_writer.setLong(sum);
                res_writer.next();
            } while (arg_reader.next());
        }
    }

    @Override
    public ScalarFunction createScalarFunction(ServerInterface srvInterface) {
        return new AddAnyInts();
    }
}

idea中必须要 import VerticaSDK.jar

在vertica所在节点正式部署:

1. 安装Java 环境

yum localinstall jdk-8u202-linux-x64.rpm

2. 创建临时目录，并编译VerticaSDK.jar和BuildInfo.java，生成相关的.class文件A

[dbadmin@V001 ~]$ mkdir -p /home/soft/customUDXs/fisrtFunc/
[dbadmin@V001 ~]$ cd /home/soft/customUDXs/fisrtFunc/

javac -g -cp /opt/vertica/bin/VerticaSDK.jar /opt/vertica/sdk/BuildInfo.java -d .

3.编译自定义java udx类 生成相关的.class文件B

-- 上传AddAnyIntsInfo.java类到该目录，然后编译
javac -g -cp /opt/vertica/bin/VerticaSDK.jar AddAnyIntsInfo.java -d .

4.将所有A和B打包为AddAnyIntLib.jar

jar cf AddAnyIntLib.jar com/unisinsight/CustomUDXs/*.class com/vertica/sdk/*.class    

5.切换到vertica的dbadmin账户

[root@V001 fisrtFunc]# su - dbadmin
[dbadmin@V001 ~]$ cd /home/soft/customUDXs/fisrtFunc/

6.登录需要安装自定义函数的数据库(viid)

[dbadmin@V001 fisrtFunc]$ vsql -w dbadmin -U dbadmin -d viid -h 192.168.xxx.xx
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit
viid=> CREATE LIBRARY AddAnyIntLib AS '/home/soft/customUDXs/fisrtFunc/AddAnyIntLib.jar'  LANGUAGE 'JAVA';

ROLLBACK 5699:  Cannot find java binary: neither the Linux environment variable JAVA_HOME nor Vertica config parameter JavaBinaryForUDx is set
HINT:  Please set JAVA_HOME environment variable or JavaBinaryForUDx config parameter to help vertica find java binary

7.声明JAVA_HOME路径

viid=> SELECT SET_CONFIG_PARAMETER('JavaBinaryForUDx','/usr/bin/java');
    SET_CONFIG_PARAMETER
----------------------------
 Parameter set successfully
(1 row)

8.创建LIBRARY

viid=> CREATE LIBRARY AddAnyIntLib AS '/home/soft/customUDXs/fisrtFunc/AddAnyIntLib.jar'  LANGUAGE 'JAVA';
CREATE LIBRARY

9.创建FUNCTION

viid=> CREATE FUNCTION AddAnyInt AS language 'java' NAME 'com.unisinsight.CustomUDXs.AddAnyIntsInfo' LIBRARY AddAnyIntLib;
CREATE FUNCTION

10.测试函数功能

viid=> select AddAnyInt(1,2,3);
 AddAnyInt
-----------
         6
(1 row)

11.退出

viid=>\q;