Skip to content
Related Articles

Related Articles

Improve Article
User Defined Function in Cassandra
  • Last Updated : 03 Jan, 2020

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_idnum1num2udf.Max_value(num1, num2)
101400600600
102500400500
103null900null
104500nullnull

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" 

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

 

My Personal Notes arrow_drop_up
Recommended Articles
Page :