Deploying and using UDFs

Writing your own functions

Presto has a guide to writing UDFs which can be found here. In the GeoSpock examples repository there are two example UDFs which cover scalar and aggregate functions.

Once you have written your UDFs, they need to be packaged in a jar file.

Deploying your UDFs in a SQL cluster

Once you have you jar file, this needs to be uploaded to an S3 bucket. When deploying your GeoSpock DB SQL Presto cluster, you will need to add the location of this file to the presto_udf_jar_locations list variable. For example:

# List of s3 locations for any jar files containing UDFs to import
# default: []
presto_udf_jar_locations = ["s3://example-bucket/example-folder/geospock-udfs-1.0.0.jar"]

Using your UDFs in a SQL query

When writing your UDFs, you will have specified the sql command to type with either the @ScalarFunction or @AggregationFunction tags.

In the example UDFs these were @ScalarFunction("is_pasta") and @AggregationFunction("avg_between_limits"), and examples of the way to call these functions within SQL are as follows:

presto> SELECT is_pasta('pasta'), is_pasta('cabbage');
 _col0 | _col1 
-------+-------
 true  | false 
(1 row)

presto> SELECT avg_between_limits(overdraft, 100, 150) FROM geospock.default.banks;
 _col0 
-------
 115.5  
(1 row)