# User-Defined Functions (UDFs)

Pinot currently supports two ways for you to implement your own functions:

* Groovy Scripts
* Scalar Functions

### Groovy Scripts

Pinot allows you to run any function using [Apache Groovy](https://groovy-lang.org/) scripts. The syntax for executing Groovy script within the query is as follows:

**`GROOVY('result value metadata json', ''groovy script', arg0, arg1, arg2...)`**

This function will execute the groovy script using the arguments provided and return the result that matches the provided result value metadata. The function requires the following arguments: 

* `Result value metadata json` - json string representing result value metadata. Must contain non-null keys `resultType` and `isSingleValue`. 
* `Groovy script to execute`- groovy script string, which uses `arg0`,  `arg1`, `arg2` etc to refer to the arguments provided within the script
* `arguments` - pinot columns/other transform functions that are arguments to the groovy script

**Examples**

* Add colA and colB and return a single-value INT\
  `groovy(  '{"returnType":"INT","isSingleValue":true}',   'arg0 + arg1',   colA, colB)`<br>
* Find the max element in mvColumn array and return a single-value INT

  `groovy('{"returnType":"INT","isSingleValue":true}', 'arg0.toList().max()', mvColumn)`<br>
* Find all elements of the array mvColumn and return as a multi-value LONG column

  `groovy('{"returnType":"LONG","isSingleValue":false}', 'arg0.findIndexValues{ it > 5 }', mvColumn)`<br>
* Multiply length of array mvColumn with colB and return a single-value DOUBLE

  `groovy('{"returnType":"DOUBLE","isSingleValue":true}', 'arg0 * arg1', arraylength(mvColumn), colB)`<br>
* Find all indexes in mvColumnA which have value `foo`, add values at those indexes in mvColumnB

  `groovy( '{"returnType":"DOUBLE","isSingleValue":true}', 'def x = 0; arg0.eachWithIndex{item, idx-> if (item == "foo") {x = x + arg1[idx] }}; return x' , mvColumnA, mvColumnB)`<br>
* Switch case which returns a FLOAT value depending on length of mvCol array&#x20;

  `groovy('{\"returnType\":\"FLOAT\", \"isSingleValue\":true}', 'def result; switch(arg0.length()) { case 10: result = 1.1; break; case 20: result = 1.2; break; default: result = 1.3;}; return result.floatValue()', mvCol)` <br>
* Any Groovy script which takes no arguments

  `groovy('new Date().format( "yyyyMMdd" )', '{"returnType":"STRING","isSingleValue":true}')`

### Scalar Functions

Since the 0.5.0 release, Pinot supports custom functions that return a single output for multiple inputs. Examples of scalar functions can be found in [StringFunctions](https://docs.pinot.apache.org/release-0.9.0/users/supported-transformations#string-functions) and [DateTimeFunctions ](https://docs.pinot.apache.org/release-0.9.0/users/supported-transformations#datetime-functions)

Pinot automatically identifies and registers all the functions that have the `@ScalarFunction`  annotation.&#x20;

Only Java methods are supported.

#### Adding user defined scalar functions

You can add new scalar functions as follows:

* Create a new java project. Make sure you keep the package name as `org.apache.pinot.scalar.XXXX`
* In your java project include the dependency

{% tabs %}
{% tab title="Maven" %}

```
<dependency>
  <groupId>org.apache.pinot</groupId>
  <artifactId>pinot-common</artifactId>
  <version>0.5.0</version>
 </dependency>
```

{% endtab %}

{% tab title="Gradle" %}

```
include 'org.apache.pinot:pinot-common:0.5.0'
```

{% endtab %}
{% endtabs %}

* Annotate your methods with `@ScalarFunction` annotation. Make sure the method is `static` and returns only a single value output. The input and output can have one of the following types -&#x20;
  * Integer
  * Long
  * Double
  * String

```
//Example Scalar function

@ScalarFunction
static String mySubStr(String input, Integer beginIndex) {
  return input.substring(beginIndex);
}
```

* Place the compiled JAR in the `/plugins` directory in pinot. You will need to restart all Pinot instances if they are already running.
* Now, you can use the function in a query as follows:

```
SELECT mysubstr(playerName, 4) 
FROM baseballStats
```

Note that the function name in SQL is the same as the function name in Java. The SQL function name is case-insensitive as well.