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)