User Defined Function in Cassandra

In this article, we will discuss how to create a User Defined Function in Cassandra. also, we will see how can modify it. Let’s have a look.

First, we will create a User-Defined Function (UDF).
Let’s take an example where we are going to create Max_value UDF which we will use for calculating maximum value by simply passing the values in a UDF. Let’s have a look.

Creating an UDF : Max_value –



CREATE FUNCTION Max_value(value1 int, value2 int)
CALLED ON NULL INPUT
RETURNS int
LANGUAGE java
AS $$return Math.max(value1, value2);$$; 

Now, for passing the value as input we are going to create a simple table namely as UDF_Function_test in which function_id, num1, and num2 are the field values.
Let’s have a look.

CREATE TABLE UDF_Function_test 
    (
    function_id int,
    num1 int,
    num2 int,
    PRIMARY KEY(function_id)
    ); 

Now, here we are going to insert some data which we will use in UDF function for calculating the Maximum value where function_id will be in range of 101 to 104.
Let’s have a look.

INSERT INTO UDF_Function_test(function_id, num1, num2) 
VALUES(101, 400, 600);

INSERT INTO UDF_Function_test(function_id, num1, num2) 
VALUES(102, 500, 400);

INSERT INTO UDF_Function_test(function_id, num2) 
VALUES(103, 900);

INSERT INTO UDF_Function_test(function_id, num1) 
VALUES(104, 500); 

Let’s verify the results and calculate the maximum value.

SELECT function_id, num1, num2, Max_value(num1, num2) 
FROM UDF_Function_test 
WHERE function_id IN(101, 102, 103, 104); 

Output:

function_id num1 num2 udf.Max_value(num1, num2)
101 400 600 600
102 500 400 500
103 null 900 null
104 500 null null

As we can see in the output table if one of the input values is null then it will return null because we are using clause RETURNS NULL ON NULL INPUT.

Now, we will try to change the clause RETURNS NULL ON NULL INPUT to CALLED ON NULL INPUT and will see the results.
Let’s have a look.

CREATE OR REPLACE FUNCTION Max_value(value1 int, value2 int)
CALLED ON NULL INPUT 
RETURNS int
LANGUAGE java
AS 'return Math.max(value1, value2);'; 

When we will try to execute then it will give the following error.
Let’s have a look.

InvalidRequest: code=2200 [Invalid query] 
message="Function udf.Max_value : 
(int, int) -> int can only be replaced with CALLED ON NULL INPUT" 

We will see once we chosed RETURNS NULL ON NULL INPUT it will not possible to change it. To modify first we need to drop and then again create the UDF function. let’s have a look.

DROP FUNCTION Max_value; 

Recreating to modify the UDF –

CREATE OR REPLACE FUNCTION Max_value(value1 int, value2 int)
CALLED ON NULL INPUT 
RETURNS int
LANGUAGE java
AS 'return Math.max(value1, value2);'; 

Now, let’s execute and see the results of the UDF.

SELECT function_id, num1, num2, Max_value(num1, num2) 
FROM UDF_Function_test WHERE function_id IN(1, 2, 3); 

Here we can see error is expected because of no null check in our UDF.

FunctionFailure: code=1400 [User Defined Function failure] 
message="execution of 'udf.Max_value[int, int]' 
failed: java.lang.NullPointerException" 

GeeksforGeeks has prepared a complete interview preparation course with premium videos, theory, practice problems, TA support and many more features. Please refer Placement 100 for details

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.