前提条件:已经写好自定义函数的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;