CREATE FUNCTION

Creates custom function to execute user provided code.

cassandra.yaml

The location of the cassandra.yaml file depends on the type of installation:
Package installations /etc/dse/cassandra/cassandra.yaml
Tarball installations installation_location/resources/cassandra/conf/cassandra.yaml

Executes user-provided Java or Javascript code in SELECT, UPDATE, INSERT or provides a building block for user-defined aggregate. Functions are only available in the keyspace where they were created.

UDF supports Java generic methods or Javascript in the user provided codeblock. UDFs are susceptible to all of the normal issues that may occur with the chosen programming language. Safe guard against exceptions, such as null pointer exceptions, illegal arguments, or any other potential sources of problems. An exception during function execution results in the entire statement failing.
Note: By default, the database does not allow UDFs. To enable, change the following settings in the cassandra.yaml and restart all nodes:

Synopsis

CREATE [OR REPLACE] FUNCTION function_name ( argument_list )
(CALLED | RETURNS NULL ) ON NULL INPUT 
RETURNS type 
   [DETERMINISTIC] 
   [MONOTONIC [ON argument_name]]
LANGUAGE (java | javascript ) AS  
   $$ code_block $$; 

CREATE FUNCTION function_name ( arg_name arg_type [,...] )
[IF NOT EXISTS]
(CALLED | RETURNS NULL ) ON NULL INPUT 
RETURNS type 
[DETERMINISTIC] 
   [MONOTONIC [on argument_name]]
LANGUAGE (java | javascript ) AS 
$$ code_block $$; ;
Table 1. Legend
Syntax conventions Description
UPPERCASE Literal keyword.
Lowercase Not literal.
Italics Variable value. Replace with a user-defined value.
[] Optional. Square brackets ( [] ) surround optional command arguments. Do not type the square brackets.
( ) Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.
| Or. A vertical bar ( | ) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
... Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.
'Literal string' Single quotation ( ' ) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
{ key : value } Map collection. Braces ( { } ) enclose map collections or key value pairs. A colon separates the key and the value.
<datatype1,datatype2> Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
cql_statement; End CQL statement. A semicolon ( ; ) terminates all CQL statements.
[--] Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.
' <schema> ... </schema> ' Search CQL only: Single quotation marks ( ' ) surround an entire XML schema declaration.
@xml_entity='xml_entity_type' Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.
function_name
Specify a keyspace-qualified function name. Names must start with a letter or number. To preserve case, enclose the name in double-quotes.
OR REPLACE
Overwrite the function, if one already exists with the same name.
Note: Cannot be used with the IF NOT EXISTS option.
IF NOT EXITS
Performs no operation and suppresses the error message if a function with the same name already exists.
argument_list
Comma separated list of arguments with data types passed to the code block for processing:
arg_name cql_type [,...]

In the list, specify an argument name followed by the CQL data type.

For requests, an argument value can be read from a column with the corresponding data type or manually entered (literal).

CALLED ON NULL INPUT
Executes the user-provided code block even if the input value is null or missing.
RETURNS NULL ON NULL INPUT
Does not execute the user-provided code block on null values; returns null.
RETURNS cql_data_type
Map the expected output from the code block to a compatible CQL data type.
DETERMINISTIC
Specify for functions that always returns the same output for a certain input. For example, toJson() is a deterministic function, while now() and currentDate() are not.

Default: false (non-deterministic).

Note: GROUP BY only supports functions that are both deterministic and monotonic.
MONOTONIC [ON argument_name]
All arguments or the specified argument is monotonic if it is either entirely non-increasing or non-decreasing.
Note: GROUP BY only supports functions that are both deterministic and monotonic.
LANGUAGE language_name
Supported types are java or javascript.
Tip: When enable_scripted_user_defined_functions is false and enable_user_defined_functions is true, Java is the only supported language.
'code_block' | $$ code_block $$

Enclose the code block in single quotes or if the code block contains any special characters enclose it in double dollar signs ($$). The code is wrapped as a function and applied to the target variables.

Examples

Use Java to create FLOG function

Overwrite or create the fLog function that computes the logarithm of an input value. CALLED ON NULL INPUT ensures that the function will always be executed.
CREATE OR REPLACE FUNCTION cycling.fLog (input double) 
   CALLED ON NULL INPUT 
   RETURNS double 
   LANGUAGE java AS 
   $$ return Double.valueOf(Math.log(input.doubleValue())); $$ 
;

Use Javascript to create SQL-like LEFT function

Create a function that returns the first N characters from a text field in Javascript. RETURNS NULL ON NULL INPUT ensures that if the input value is null then the function is not executed.
CREATE FUNCTION IF NOT EXISTS cycling.left (column TEXT,num int) 
   RETURNS NULL ON NULL INPUT 
   RETURNS text 
   LANGUAGE javascript AS 
   $$ column.substring(0,num) $$ ;
Use the function in requests:
SELECT left(firstname,1), lastname from cycling.cyclist_name;
 cycling.left(firstname, 1) | lastname
----------------------------+-----------------
                          A |           FRAME
                          A |         PIETERS
                          M |        MATTHEWS
                          M |             VOS
                          P |       TIRALONGO
                          S |      KRUIKSWIJK
                          A | VAN DER BREGGEN