Open In App

User Defined Function in Cassandra

Improve
Improve
Like Article
Like
Save
Share
Report

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 choose 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" 

 


Last Updated : 20 Jul, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads