The pom.xml File

In the root directory of udf_templates/simple you will find pom.xml, which contains instructions that Maven uses to build the UDF. There is a section at the top of the pom.xml file that looks like this:

<groupId>org.apache.drill.contrib.function</groupId>
<artifactId>udf_template</artifactId>
<version>1.0</version>
<name>Drill Function Template</name>

You can change the name of the project and the version here, although it does not affect the functionality of the function.

<build>
  <plugins>
    <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-source-plugin</artifactId>
      <version>2.4</version>
      <executions>
        <execution>
          <id>attach-sources</id>
          <phase>package</phase>
          <goals>
            <goal>jar-no-fork</goal>
          </goals>
        </execution>
      </executions>
    </plugin>
    <plugin>
      <artifactId>maven-compiler-plugin</artifactId>
      <version>3.0</version>
      <configuration>
        <verbose>true</verbose>
        <compilerVersion>1.7</compilerVersion>
        <source>1.7</source>
        <target>1.7</target>
      </configuration>
    </plugin>
  </plugins>
</build>

<dependencies>
  <dependency>
    <groupId>org.apache.drill.exec</groupId>
    <artifactId>drill-java-exec</artifactId>
    <version>1.14.0</version>
    <scope>provided</scope>
  </dependency>
  <dependency>
    <groupId>commons-validator</groupId>
    <artifactId>commons-validator</artifactId>
    <version>1.6</version>
    <scope>provided</scope>
  </dependency>
</dependencies>

The Function File

The actual functionality of the UDF is implemented in a file called UDFTemplate.java, which you can find in /src/main/org/apache/drill/contrib/function. This file contains a template for a simple UDF with a lot of the boilerplate code, but it is important to understand that Drill doesn’t directly execute this code. This code is a template that Drill uses to implement the method directly in Drill’s generated code—that’s why Drill also needs your function’s source code.

The first part of the UDF is the function template, which looks like the following snippet:

@FunctionTemplate(
  name = "is_valid_credit_card",  //Your function name here 
  scope = FunctionTemplate.FunctionScope.SIMPLE, 
  nulls = FunctionTemplate.NullHandling.NULL_IF_NULL
)
public static class ValidCreditCardFunction implements DrillSimpleFunc {

  @Param
  VarCharHolder creditCardNumber;

  @Output
  BitHolder isValid;

  @Override
  public void setup() {
  }

  @Override
  public void eval() {
	//Your function logic here
  }
}

In the class that implements DrillSimpleFunc, there are three elements in your function template annotation that must be set for your UDF to work properly. The first, name, is the name that will be used to call your function in a Drill query. Drill function names may not be Drill reserved words, nor may they start with a digit. If you have multiple versions of the same function that accept different inputs, you may use the same name for multiple functions. For instance, suppose that you have a Drill function that converts pounds to kilograms. You might want one version of your function to accept an INT as an input and another to accept a FLOAT, and so on. (One note about data types: in Drill, JSON always generates BIGINT and DOUBLE types, so UDFs that work with numbers should at least provide these data types.)

If you would like to define aliases for your function, you can also do this as shown here:

names = {"name_1", "name_2"}

The scope variable specifies to Drill whether the function is a simple or aggregate function. For this example, this needs to be set to SIMPLE.

The nulls variable defines how the function will deal with null values. Setting this to NULL_IF_NULL configures Drill to skip null rows, instead of having to handle them in the function body. In this case, query results will contain NULL for null rows.

After you’ve defined these variables, the next step is to actually begin writing the function body.

public int foo(int x, int y)

nulls = FunctionTemplate.NullHandling.NULL_IF_NULL

@Param
VarCharHolder rawCardNumber;

@Param
VarCharHolder inputTextA; 

@Param
IntHolder integer_parameter; 

@Param
Float8Holder float_parameter; 

public int foo(int x) {
      int result;
      // Do something
      return result;
}

@Output
BitHolder isValid;

//Read integer holder
int myInt = intParam1.value;

//Set integer output 
outputInteger.value = myInt;

String creditCardNumber = org.apache.drill.exec.expr.fn.impl.
    StringFunctionHelpers.toStringFromUTF8(rawCardNumber.start,
    rawCardNumber.end, rawCardNumber.buffer);

byte outBytes[] = outputValue.getBytes(com.google.common.base.Charsets.UTF_8);
outputBuf = outputBuf.reallocIfNeeded(outBytes.length); 
outputBuf.setBytes(0, outBytes); 
output.buffer = outputBuf; 
output.start = 0;
output.end = outBytes.length; 

The Simple Function API

Now that you have defined your input parameters, the next step is to implement the DrillSimpleFunc interface, which consists of two void functions: setup() and eval(). In some instances, you might have variables or objects that you want to initialize at the beginning of the function’s execution and reuse in every function call. If that is the case, you can initialize these variables in the setup() function. If you do not have any variables like this, you can simply leave the setup() function blank, but you must include it because it is part of the DrillSimpleFunc interface.

The eval() function is where you actually implement the function’s logic and where you assign a value to the holder for your output. The eval() function itself is a void function, meaning that it does not return anything.

The pseudocode for our example function might look something like this:

function is_valid_credit_card (credit_card_number) {
   return luhn(credit_card_number);
}

Putting It All Together

To put this all together, the following code demonstrates how to convert the simple function into a Drill UDF:

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

public class IsValidCreditCardFunction implements DrillSimpleFunc {

  @Param
  VarCharHolder rawCardNumber;

  @Workspace
  org.apache.commons.validator.routines.CreditCardValidator ccv;

  @Output
  BitHolder out;

  public void setup() {
     org.apache.commons.validator.routines.CreditCardValidator ccv = 
       new org.apache.commons.validator.routines.CreditCardValidator();
  }
  
  public void eval() {
    String creditCardNumber = org.apache.drill.exec.expr.fn.impl 
         .StringFunctionHelpers 
         .toStringFromUTF8(rawCardNumber.start, 
                           rawCardNumber.end,  
                           rawCardNumber.buffer);
    if (ccv.isValid(creditCardNumber)) {
      out.value = 1;
    } else {
      out.value = 0;
    }
  }
}

The previous UDF uses a BitHolder as the output value, where 1 is true and 0 is false. This UDF also has a dependency on the Apache commons-validator package, which you need to include in the dependencies section of the pom.xml file, as shown in the following snippet:

<dependencies>
  <dependency>
    <groupId>org.apache.drill.exec</groupId>
    <artifactId>drill-java-exec</artifactId>
    <version>1.13.0</version>
  </dependency>
  <dependency>
    <groupId>commons-validator</groupId>
    <artifactId>commons-validator</artifactId>
    <version>1.6</version>
  </dependency>
</dependencies>

There is one additional file that you will need in order for Drill to recognize your UDF. In your function package, in the /src/main/resources folder, you will need to create a file called drill-module.conf, which contains the following:

drill.classpath.scanning.packages += "org.apache.drill.contrib.function"

You might need to adjust the package name to reflect your UDF.

Statically Installing a UDF

Let’s look at static installation first. If you are using Drill in embedded mode in a nonshared environment, simply copy the aforementioned files into your Drill installation in the $DRILL_HOME/jars/3rdparty/ folder. If you are using Drill in a cluster, copy the JAR files into the jars folder in your site directory.²

Dynamically Installing a UDF

As of Drill version 1.9, you can also register and unregister UDFs dynamically without having to restart Drillbits. To do this, you need to first configure a few directories. Because these directories must be visible to all Drillbits, they must be in a distributed filesystem such as HDFS:

Staging directory

This is where you will copy the JAR files before they are registered.

Local directory

This is a temporary directory for dynamic UDF JAR files. This directory is cleaned out when Drill is closed.

Registry directory

This is where the JAR files are copied after they are registered.

tmp directory

This contains the backup JAR files.

You can set the locations of all these directories in $DRILL_HOME/conf/drill-override.conf, however usually you just set the base and let Drill define the UDF directories as subdirectories under the base directory.

drill.exec.udf: {
   retry-attempts: 5, 
   directory: {
      # Override this property if custom file system should be used to create 
      # remote directories
      # instead of default taken from Hadoop configuration
      fs: "hdfs:///",
      # Set this property if custom absolute root should be used for remote 
      # directories
      root: "/app/drill"

  }
}

After configuring the filesystem and base directory, you are ready to dynamically register a UDF. First copy the JAR files to the staging directory. After that, you can register the UDF by running the following command:

CREATE FUNCTION USING JAR 'jar_file';

If you want to unregister a UDF, you can do so with the following command:

DROP FUNCTION USING JAR 'jar_file';

After you have installed your UDF, you can use it in queries just as you would any other Drill built-in function.

Example: Extracting User Agent Metadata

Often, you will encounter data artifacts from which you can extract many different pieces of metadata. For instance, user agent strings are strings of text that are sent to a web server when a user requests a web page. These strings are very useful for web servers because they can determine what version of a website a user receives. They’re also useful for analyzing the type of traffic that is hitting a website. Although user agent strings are extremely useful, they are difficult to parse, and it is not really possible to simply split them by some delimiter. The example user agent string that follows illustrates why this is the case:

Mozilla/5.0 (Windows NT 5.1; rv:35.0) Gecko/20100101 Firefox/35.0

From this user agent string, it is possible to extract the following metadata:

{
"DeviceClass":"Desktop",
"DeviceName":"Desktop",
"DeviceBrand":"Unknown",
"DeviceCpuBits":"32",
"OperatingSystemClass":"Desktop",
"OperatingSystemName":"Windows NT",
"OperatingSystemVersion":"Windows XP",
"OperatingSystemNameVersion":"Windows XP",
"LayoutEngineClass":"Browser",
"LayoutEngineName":"Gecko",
"LayoutEngineVersion":"35.0",
"LayoutEngineVersionMajor":"35",
"LayoutEngineNameVersion":"Gecko 35.0",
"LayoutEngineNameVersionMajor":"Gecko 35",
"LayoutEngineBuild":"20100101",
"AgentClass":"Browser",
"AgentName":"Firefox",
"AgentVersion":"35.0",
"AgentVersionMajor":"35",
"AgentNameVersion":"Firefox 35.0",
"AgentNameVersionMajor":"Firefox 35"
}

Instead of writing a function to extract each piece of metadata individually, you might find it convenient to write a function that extracts all this metadata all at once and returns a map of these fields, and then extract the individual fields of interest. But to do that, you need to write a UDF that returns a map of all the fields.

The ComplexWriter

The ComplexWriter object enables you to write maps and arrays and return them as output from your Drill UDF. The first step in writing a UDF that returns a complex result is setting the ComplexWriter as the output parameter, as shown in the snippet that follows:

@Output
BaseWriter.ComplexWriter out Writer;

Fundamentally, functions that return complex results are no different from the simple UDFs that you have already written, with the exception of the output parameter. In the example here, we are setting the output parameter of the UDF to be a ComplexWriter called outWriter. The next step is to access either a MapWriter or ListWriter object—depending on whether you want to create a map or an array—from the ComplexWriter object by calling the rootAsMap() or rootAsList() function:

public void eval() {
  MapWriter queryMapWriter = outWriter.rootAsMap();
  ...
}

In regular Drill UDFs, the output is contained in holder variables. In a UDF that returns a complex data type, the MapWriter created in the previous snippet³ will contain holder objects for the various fields that will populate the result. You can create a holder separately or you can use the various writer functions to indirectly create a field, as shown in the following snippets:

//Write a floating-point field
queryMapWriter.float4("field_name")
            .writeFloat8(floating_point_value);
queryMapWriter.float8("field_name")
            .writeFloat8(floating_point_value);

//Write an integer field
queryMapWriter.bigInt("field_name").writeInt(int_value);
queryMapWriter.integer("field_name").writeInt(int_value);

//Write a date field
queryMapWriter.date("field_name").writeDate(long_date);

Adding a string to a map is a little more complicated, but the essential process is the same. It is a little clearer if you create the holder object outside of the MapWriter object and then pass the MapWriter object the holder. This code snippet demonstrates how to write a string to a MAP:

VarCharHolder rowHolder = new VarCharHolder();
String field = <text>;

byte[] rowStringBytes = field.getBytes(com.google.common.base.Charsets.UTF_8)
outBuffer.reallocIfNeeded(rowStringBytes.length);
outBuffer.setBytes(0, rowStringBytes);

rowHolder.start = 0;
rowHolder.end = rowStringBytes.length;
rowHolder.buffer = outBuffer;

queryMapWriter.varChar(field_name).write(rowHolder);

Here is the complete code for the function to parse user agent strings:

public void eval() {

 org.apache.drill.exec.vector.complex.writer.BaseWriter.MapWriter 
    queryMapWriter = outWriter.rootAsMap();
 String userAgentString = 
  org.apache.drill.exec.expr.fn.impl
  .StringFunctionHelpers
  .toStringFromUTF8(input.start, input.end, input.buffer);

  if (userAgentString.isEmpty() || userAgentString.equals("null")) {
    userAgentString = "";
  }

  nl.basjes.parse.useragent.UserAgent agent = uaa.parse(userAgentString);

  for (String fieldName : agent.getAvailableFieldNamesSorted()) {

   org.apache.drill.exec.expr.holders.VarCharHolder rowHolder = 
      new org.apache.drill.exec.expr.holders.VarCharHolder();
   String field = agent.getValue(fieldName);

   byte[] rowStringBytes = field.getBytes(com.google.common.base.Charsets.UTF_8);
   outBuffer.reallocIfNeeded(rowStringBytes.length);
   outBuffer.setBytes(0, rowStringBytes);

   rowHolder.start = 0;
   rowHolder.end = rowStringBytes.length;
   rowHolder.buffer = outBuffer;

   queryMapWriter.varChar(fieldName).write(rowHolder);
  }
}

The Aggregate Function API

Writing an aggregate function is not significantly different than a simple Drill UDF; however, there are four additional functions that you must implement:

setup()

This function is executed once when the function is first called and is where you initialize any variables that will be used throughout the function iterations.

add()

This function processes every record and is roughly equivalent to the eval() function in a simple Drill UDF. This is where the actual logic of the function will be implemented.

output()

This function is where the final output will be calculated and output. This is executed at the end of every segment of data that the function encounters.

reset()

This function is similar to the setup() function and is called at the beginning of each new section of data or when a column changes from one data type to another. You should reset your counters or totals in this method.

Example Aggregate UDF: Kendall’s Rank Correlation Coefficient

To demonstrate an aggregate UDF, let’s write a UDF that implements the Kendall’s rank correlation coefficient, otherwise known as Kendall’s tau. Kendall’s tau is a measurement of correlation between two vectors that ranges from 1, which indicates perfect correlation, to –1, which indicates perfect negative correlation.

Kendall’s tau is calculated by comparing pairs of numbers in the two vectors; if both pairs of numbers share the same Boolean relationship, they are considered a concordant pair, and if not, they are a discordant pair:

To implement this as a Drill function, you need to first initialize a counter of concordant and discordant pairs as well as a counter of the number of items. You initialize these variables in the reset() function. The add() function will compare the rows, make the determination as to whether the pair is concordant or discordant, and increment the appropriate counter as well as the row counter. Finally, the output() function will perform the calculation and output the result.

The code snippet that follows demonstrates how to write an aggregate UDF in Drill:

@Param
Float8Holder xInput;

@Param
Float8Holder yInput;

@Workspace
Float8Holder prevXValue;

@Workspace
Float8Holder prevYValue;

@Workspace
IntHolder concordantPairs;

@Workspace
IntHolder discordantPairs;

@Workspace
IntHolder n;

@Output
Float8Holder tau;

In the first part of this UDF, we define the input parameters, the workspace variables, and the output variable. This function needs two input variables, xInput and yInput, which are both floating-point numbers. For these, the UDF uses Float8Holders. Next, the UDF defines several @Workspace variables. The prevXValue and prevYValue variables hold the values from the previous iteration, and the concordantPairs and discordantPairs holders contain the running totals of pairs. Finally, n contains the number of pairs in the grouping. Notice that the pair holders and n are integers and the others are all double holders. The last parameter is the output parameter tau, which also uses a Float8Holder.

Now that the inputs and outputs are all defined, as well as several workspace variables, the next steps are to implement the four functions of the aggregate function API. The first function is the add() function, which performs the bulk of the calculations. This function is called on every row, and for each row, it compares the current pair with the previous pair that it saw and increments the appropriate counter based on whether the pair is concordant or discordant:

@Override
public void add() {
  double xValue = xInput.value;
  double yValue = yInput.value;

  if (n.value > 0) {
    if ((xValue > prevXValue.value 
         && yValue > prevYValue.value) 
       || (xValue < prevXValue.value 
         && yValue < prevYValue.value)) {
      concordantPairs.value = concordantPairs.value + 1;
      prevXValue.value = xInput.value;
      prevYValue.value = yInput.value;
      n.value = n.value + 1;
    } else if ((xValue > prevXValue.value 
               && yValue < prevYValue.value) 
              || (xValue < prevXValue.value 
                 && yValue > prevYValue.value)) {
      discordantPairs.value = discordantPairs.value + 1;
      prevXValue.value = xInput.value;
      prevYValue.value = yInput.value;
      n.value = n.value + 1;
    } else {
      //Tie...
      prevXValue.value = xInput.value;
      prevYValue.value = yInput.value;
    }

  } else if (n.value == 0) {
    prevXValue.value = xValue;
    prevYValue.value = yValue;
    n.value = 1;
  }
}

The next function in the API is the setup() function, which in this case is not needed and left blank:

@Override
public void setup() {
}

The reset() function is called before each group’s execution. In this snippet, we use the reset() function to initialize all the variables and counters to zero:

@Override
public void reset() {
  prevXValue.value = 0;
  prevYValue.value = 0;
  concordantPairs.value = 0;
  discordantPairs.value = 0;
  n.value = 0;
}

The last function in the API that we must implement is the output() function, which calculates the difference between the pairs, divides it by (n * n - 1) / 2, and sets the value of the output holder, tau:

@Override
public void output() {
  double result = 0.0;
  result = (concordantPairs.value - discordantPairs.value) / 
      (0.5 * n.value * (n.value - 1));
  tau.value = result;
}