CREATE FUNCTION

Executes user-provided Java or Javascript code in SELECT, UPDATE, or INSERT Or, provides a building block for user-defined aggregate.

Functions are only available in the keyspace where they are created.

Functions are executed against data stored in a table as part of a query result. The function must be created prior to its use in a SELECT statement. When called, the function is performed on each row of the table. An exception during function execution fails the entire statement.

UDF supports Java generic methods or Javascript in the user-provided code block. UDFs are susceptible to all of the normal issues that can occur with the chosen programming language. Safe guard against exceptions, such as null pointer exceptions, illegal arguments, or any other potential sources of problems.

Enable UDFs

By default, UDFs are disabled. To enable UDFs, set following parameters in the cassandra.yaml file, and then restart all nodes:

Set enable_user_defined_functions to true to allow users to create custom functions.
Optional: Set enable_scripted_user_defined_functions to true to allow function code blocks to use Java or Javascript. If false, function code blocks allow Java only.
Optional: Set enable_user_defined_functions_threads to false to allow functions in GROUP BY clauses.

Synopsis

CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ] [<keyspace_name>]<function_name> (<argument_list> [ , ... ])
  ( CALLED | RETURNS NULL ) ON NULL INPUT RETURNS <type>
  [ DETERMINISTIC ]
  [ MONOTONIC [ ON <argument_name> ] ]
  LANGUAGE ( java | javascript ) AS $$ <code_block> $$ ;

Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`< >`	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.

Options

<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

Allows upsert by checking for existing functions with the same name. If a match is found, the operation overwrites the existing function. If no match is found, the operation creates a new function.

Cannot be used with IF NOT EXISTS.

IF NOT EXISTS

Allows silent skip by checking for existing functions with the same name. If a match is found, the operation silently does nothing (no error is thrown). If no match is found, the operation creates the function.

Cannot be used with OR REPLACE.

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

Always run the function even if the input value is null or missing.

RETURNS NULL ON NULL INPUT

If an input argument is null, the function 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).

GROUP BY only supports functions that are both deterministic and monotonic.

MONOTONIC [ ON <argument_name> ]

All arguments or the specified argument are monotonic if they are either entirely non-increasing or non-decreasing.

GROUP BY only supports functions that are both deterministic and monotonic.

LANGUAGE <language_name>

Supported types are java or javascript.

Java is the only supported language if enable_scripted_user_defined_functions is false and enable_user_defined_functions is true in cassandra.yaml.

'<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 an fLog() function that computes the logarithm of an input value. This is a built-in Java function that is used to generate linear plots of non-linear data.

CALLED ON NULL INPUT ensures that the function always runs.

CREATE OR REPLACE FUNCTION cycling.flog ( input double )
  CALLED ON NULL INPUT
  RETURNS double
  LANGUAGE java AS
    $$ return Double.valueOf(Math.log(input.doubleValue())); $$
;

CREATE 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 prevents the function from running if an input argument is null.

CREATE OR REPLACE FUNCTION 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
                       null |        MATTHEWS
                       null |             VOS
                          P |       TIRALONGO
                          M |             VOS
                          S |      KRUIKSWIJK
                          A | VAN DER BREGGEN

(7 rows)

CREATE FUNCTION

Enable UDFs

Synopsis

Options

Examples

Was this helpful?

Give Feedback