presto自定义UDF函数

最近在使用presto进行异步查询，但在本地数据库中的函数无法在presto中进行调用，后了解到presto可以直接书写UDF函数，非常方便。
新建一个mvn工程：pom.xml如下

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>unisinsight</groupId>
    <artifactId>PRESTO</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>com.facebook.presto</groupId>
        <artifactId>presto-root</artifactId>
        <version>0.216</version>
    </parent>
    <properties>
        <presto.verison>0.216</presto.verison>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.facebook.presto</groupId>
            <artifactId>presto-spi</artifactId>
            <version>${presto.verison}</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>io.airlift</groupId>
            <artifactId>slice</artifactId>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>io.airlift</groupId>
            <artifactId>units</artifactId>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.openjdk.jol</groupId>
            <artifactId>jol-core</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>
    <!-- 屏蔽parent中的plugin 避免打包报错 -->
    <build>
        <plugins>
            <plugin>
                <groupId>com.mycila</groupId>
                <artifactId>license-maven-plugin</artifactId>
                <configuration>
                    <skip>true</skip>
                </configuration>
            </plugin>
            <plugin>
                <groupId>pl.project13.maven</groupId>
                <artifactId>git-commit-id-plugin</artifactId>
                <configuration>
                    <skip>true</skip>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.gaul</groupId>
                <artifactId>modernizer-maven-plugin</artifactId>
                <configuration>
                    <skip>true</skip>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

函数代码如下：

package presto.function;

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 io.airlift.slice.Slice;
import io.airlift.slice.Slices;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 提取字段中 3=...后面的字符串
 */
public class CarNumber {
    @Description("Car Number Function")       //描述
    @ScalarFunction("extract_car")            //方法名称
    @SqlType(StandardTypes.VARCHAR)           //返回类型
    public static Slice extract_car(@SqlType(StandardTypes.VARCHAR) Slice str) {
        String carRegex = "3=(.*?)(\\s|and|or|$)";
        Pattern pattern=Pattern.compile(carRegex);
        Matcher isCar = pattern.matcher(str.toStringUtf8());

        String car;
        if (isCar.find() == true) {
            car = isCar.group(1);
            return Slices.utf8Slice(car);
        }
        else {
            return Slices.EMPTY_SLICE;
        }
    }
}

Presto包含许多内置功能。为了实现新功能，您可以编写一个插件，该插件可以从getFunctions()以下位置返回更多功能：

package presto.function;

import com.facebook.presto.spi.Plugin;
import com.google.common.collect.ImmutableSet;

import java.util.Set;

public class MDAFunctionPlugin implements Plugin {
    @Override
    public Set<Class<?>> getFunctions() {
        return ImmutableSet.<Class<?>>builder()
                .add(CarNumber.class)              //可添加多个函数方法名
                .add(ColourCode.class)            
                .build();
    }
}

在main/resources/META-INF/services下面新建一个com.facebook.presto.spi.Plugin文件，写入函数的方法名。
打包并上传至presto服务器的各个节点的plugin目录下，重启presto服务。
验证函数是否进行注册生效：

show functions;

使用函数：

presto> select extract_car('3=aaaaaaa    and 4=1');
  _col0  
---------
 aaaaaaa 
(1 row)

Query 20190222_064732_00007_7wnn3, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0:00 [0 rows, 0B] [0 rows/s, 0B/s]