How to create custom UDF for Apache Drill

Env:

Drill 1.4
MapR 5.0

Goal:

How to create custom UDF for Apache Drill

Solution:

1. Create UDF java code

Refer to documentation: https://drill.apache.org/docs/develop-custom-functions-introduction/
Sample code is: https://github.com/viadea/DrillUDF
a. Make sure UDF code has the annotation which contains the function name.
For example in DupString.java

@FunctionTemplate(name = "dup_string", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)

b. Make sure UDF code package contains a drill-module.conf with the classpath inside.

[root@v1 DrillUDF]# cat src/main/resources/drill-module.conf
drill.classpath.scanning.packages += "openkb.drill.udf"

c. Build

mvn clean package

2. Put the JAR file into /jars/3rdparty directory on all Drill nodes.

clush -a cp /xxx/target/*.jar /opt/mapr/drill/drill-1.4.0/jars/3rdparty/

3. Restart all drill-bits.

maprcli node services -name drill-bits -action restart -filter csvc=="drill-bits"

4. Test the UDF in Drill

> select myaddints(1.2, 3.2) from sys.version;
+---------+
| EXPR$0  |
+---------+
| 4.4     |
+---------+
1 row selected (0.409 seconds)

> select dup_string('abc',3) from sys.version;
+------------+
|   EXPR$0   |
+------------+
| abcabcabc  |
+------------+
1 row selected (2.839 seconds)