User Defined Functions allow you to extend the capabilities of CrateDB by adding reusable functions written in JavaScript. In this video, I'll demonstrate how to use a user defined function to solve an issue with our Chicago Beach Weather Stations data set.
Here are some example rows from a Chicago Beach weather data table. What's unusual about this data is that temperatures are in degrees Celsius. In the United States, it's more common to express temperatures as degrees Fahrenheit. Let's solve this by adding a user defined function that takes a Celsius value and converts it to Fahrenheit. It's a JavaScript function that converts Celsius to Fahrenheit. It uses a standard formula to do this, then manipulates the result to return a floating point number with two decimal places.
When called with the input 22.5, it returns 72.5 which is the equivalent temperature in Fahrenheit. This is the same function wrapped up in a CrateDB CREATE FUNCTION SQL statement.
Here we're specifying that the function expects a double precision value from CrateDB as its only parameter and that it returns a double precision value. The LANGUAGE JAVASCRIPT clause tells CrateDB to expect the source code for this function to be in JavaScript. At the moment only JavaScript is supported when writing user defined functions. Let's run the CREATE statement, adding the function to CrateDB. Now we can test it with a simple SELECT statement.
We'll select c_to_f the function name, pass in 22.5 as a Celsius value and expect to get a Fahrenheit value back. As we see here, the Fahrenheit value for 22.5 Celsius is 72.5 as a double. Next, I'll use our user defined function to produce a column in a result set. Here we're calling the function and renaming the value returned as airtemp_f, or air temperature in Fahrenheit. When we run this query, we can see the results here. So airtemp on the left is the original Celsius value. airtemp_f on the right has been calculated to be the equivalent Fahrenheit value.
One important thing to bear in mind when writing user defined functions is that they must be deterministic. What do we mean by deterministic? When given the same inputs, your function must always produce the same output. This is necessary as CrateDB may cache the output of your function for performance reasons.
In this video you learnt what user defined functions are in CrateDB, how to create one and how to use it in a SQL statement. For more information about user defined functions, check out the CrateDB documentation.