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.- Set enable_user_defined_functions to
true
- allow users to create custom functions. Only Java is allowed in codeblocks if enable_scripted_user_defined_functions isfalse
. - (Optional ) Set enable_scripted_user_defined_functions to
true
- allow function codeblocks to use Javascript. - (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 $$ ;
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 EXISTS
- 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 are monotonic if they are 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
orjavascript
.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
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 OR REPLACE FUNCTION cycling.left ( column text, num int ) RETURNS NULL ON NULL INPUT RETURNS text LANGUAGE javascript AS $$ column.substring(0, num) $$ ;
SOURCE 'left-function.cql'; CAPTURE 'cyclist_name-select_left.results'; SELECT left(firstname, 1), lastname FROM cycling.cyclist_name; CAPTURE OFF;
cycling.left(firstname, 1) | lastname
----------------------------+-----------------
A | FRAME
null | MATTHEWS
null | VOS
P | TIRALONGO
M | VOS
S | KRUIKSWIJK
A | VAN DER BREGGEN
(7 rows)