自定义presto函数详解编程语言

1、引入依赖jar包

        <dependency> 
            <groupId>com.facebook.presto</groupId> 
            <artifactId>presto-main</artifactId> 
            <version>0.220</version> 
            <scope>test</scope> 
        </dependency> 

2、实现代码，实现两个数相除保留小数点的函数。

import com.facebook.presto.spi.Plugin; 
import com.facebook.presto.udf.*; 
import com.google.common.collect.ImmutableSet; 
import java.util.Set; 
 
public class TdPlugin implements Plugin { 
    public Set<Class<?>> getFunctions() { 
        return ImmutableSet.<Class<?>>builder() 
                .add(DivideFunc.class) 
                .build(); 
    } 
} 
 

package com.facebook.presto.udf; 
 
import com.facebook.presto.spi.function.Description; 
import com.facebook.presto.spi.function.ScalarFunction; 
import com.facebook.presto.spi.function.SqlType; 
import com.facebook.presto.spi.type.StandardTypes; 
 
import java.math.RoundingMode; 
import java.text.NumberFormat; 
 
/** 
 * Created by zheng on 2019-09-09. 
 */ 
public class DivideFunc { 
    @ScalarFunction(value = "divide") 
    @Description("两个数相除") 
    @SqlType(StandardTypes.DOUBLE) 
    public static double divide(@SqlType(StandardTypes.DOUBLE) double num1, @SqlType(StandardTypes.DOUBLE) double num2) { 
        return num1 / num2; 
    } 
 
	//保留point位小数点 
    @ScalarFunction(value = "divide") 
    @Description("两个数相除") 
    @SqlType(StandardTypes.DOUBLE) 
    public static double divide(@SqlType(StandardTypes.DOUBLE) double num1, @SqlType(StandardTypes.DOUBLE) double num2, @SqlType(StandardTypes.INTEGER) long point) { 
        double result = num1 / num2; 
 
        return format(result, point); 
    } 
 
    @ScalarFunction(value = "divide") 
    @Description("两个数相除") 
    @SqlType(StandardTypes.DOUBLE) 
    public static double divide(@SqlType(StandardTypes.INTEGER) long num1, @SqlType(StandardTypes.DOUBLE) double num2) { 
        return Double.valueOf(String.valueOf(num1)) / num2; 
    } 
 
    @ScalarFunction(value = "divide") 
    @Description("两个数相除") 
    @SqlType(StandardTypes.DOUBLE) 
    public static double divide(@SqlType(StandardTypes.INTEGER) long num1, @SqlType(StandardTypes.DOUBLE) double num2, @SqlType(StandardTypes.INTEGER) long point) { 
        double result = Double.valueOf(String.valueOf(num1)) / num2; 
 
        return format(result, point); 
    } 
 
    @ScalarFunction(value = "divide") 
    @Description("两个数相除") 
    @SqlType(StandardTypes.DOUBLE) 
    public static double divide(@SqlType(StandardTypes.DOUBLE) double num1, @SqlType(StandardTypes.INTEGER) long num2) { 
        return num1 / Double.valueOf(String.valueOf(num2)); 
    } 
 
    @ScalarFunction(value = "divide") 
    @Description("两个数相除") 
    @SqlType(StandardTypes.DOUBLE) 
    public static double divide(@SqlType(StandardTypes.DOUBLE) double num1, @SqlType(StandardTypes.INTEGER) long num2, @SqlType(StandardTypes.INTEGER) long point) { 
        double result = num1 / Double.valueOf(String.valueOf(num2)); 
        return format(result, point); 
    } 
 
    @ScalarFunction(value = "divide") 
    @Description("两个数相除") 
    @SqlType(StandardTypes.DOUBLE) 
    public static double divide(@SqlType(StandardTypes.INTEGER) long num1, @SqlType(StandardTypes.INTEGER) long num2) { 
        return Double.valueOf(String.valueOf(num1)) / Double.valueOf(String.valueOf(num2)); 
    } 
 
    @ScalarFunction(value = "divide") 
    @Description("两个数相除") 
    @SqlType(StandardTypes.DOUBLE) 
    public static double divide(@SqlType(StandardTypes.INTEGER) long num1, @SqlType(StandardTypes.INTEGER) long num2, @SqlType(StandardTypes.INTEGER) long point) { 
        double result = Double.valueOf(String.valueOf(num1)) / Double.valueOf(String.valueOf(num2)); 
        return format(result, point); 
    } 
 
    private static double format(double num, long point) { 
        NumberFormat nf = NumberFormat.getNumberInstance(); 
        nf.setMaximumFractionDigits((int) point); 
        // 如果不需要四舍五入，可以使用RoundingMode.DOWN 
        nf.setRoundingMode(RoundingMode.UP); 
        return Double.valueOf(nf.format(num)); 
    } 
} 
 

presto官方示例文档：https://prestodb.github.io/docs/current/develop/functions.html
注意：
（1）、DivideFunc类为什么看起来这么多重载类？presto对传参要求比较严格，为了兼容用户不同类型的传参
（2）、例如传入divide(2,4)，看起来2和4在代码里用int类型就可以接了，但是会报java兼容问题，自定义函数里需要long类型才能接
（3）、传参，返回值为字符串类型，字符串类型需要 io.airlift.slice.Slice类接，示例：

    @LiteralParameters("x") 
    @SqlType(StandardTypes.VARCHAR) 
    public static Slice dateFormatWithString(@SqlType("varchar(x)") Slice dateTime, @SqlType("varchar(x)") Slice formatString) { 
        String date = DateUtils.formateStr(dateTime.toStringUtf8(), formatString.toStringUtf8()); 
        return utf8Slice(date); 
    } 
 

3、打包。
打包需要把依赖的jar包都打进去，pom.xml文件

   <build> 
        <finalName>tdplugin</finalName> 
        <plugins> 
            <plugin> 
                <groupId>org.apache.maven.plugins</groupId> 
                <artifactId>maven-compiler-plugin</artifactId> 
                <configuration> 
                    <source>1.8</source> 
                    <target>1.8</target> 
                    <encoding>UTF-8</encoding> 
                    <maxmem>1024m</maxmem> 
                    <fork>true</fork> 
                    <compilerArgs> 
                        <arg>-Xlint:all,-serial,-path</arg> 
                    </compilerArgs> 
                </configuration> 
            </plugin> 
 
            <plugin> 
                <groupId>com.facebook.presto</groupId> 
                <artifactId>presto-maven-plugin</artifactId> 
                <version>0.3</version> 
                <extensions>true</extensions> 
            </plugin> 
 
            <plugin> 
                <groupId>org.apache.maven.plugins</groupId> 
                <artifactId>maven-shade-plugin</artifactId> 
                <version>3.2.1</version> 
                <executions> 
                    <execution> 
                        <phase>package</phase> 
                        <goals> 
                            <goal>shade</goal> 
                        </goals> 
                    </execution> 
                </executions> 
                <configuration> 
                    <artifactSet> 
                        <excludes> 
                            <exclude>com.fasterxml.jackson.*:*</exclude> 
                        </excludes> 
                    </artifactSet> 
                </configuration> 
            </plugin> 
        </plugins> 
    </build> 

4、执行打包命令，生成名为customPlugin.jar的文件

 mvn clean package -Dmaven.test.skip=true 

5、当前测试presto版本为0.220版本，
在/presto-server-0.220/plugin目录下创建名为customPlugin的文件夹，再把customPlugin.jar上传到customPlugin文件夹下。重启presto服务

6、测试结果：

presto> select divide(2,4),divide(0.5,4),divide(0.2,0.5),divide(2,0.6),divide(2,0.6,3); 
 _col0 | _col1 | _col2 |       _col3        | _col4 
-------+-------+-------+--------------------+------- 
   0.5 | 0.125 |   0.4 | 3.3333333333333335 | 3.334 
(1 row)