Functions

CQL supports 2 main categories of functions:

• scalar functions that take a number of values and produce an output
• aggregate functions that aggregate multiple rows resulting from a SELECT statement In both cases, CQL provides a number of native “hard-coded” functions as well as the ability to create new user-defined functions. A function is identifier by its name:

  function_name ::= [ keyspace_name'.' ] name

  Scalar functions

  Native functions

  Cast

  cast function can be used to converts one native datatype to another. cast function. Cassandra will silently ignore any cast converting a datatype into its own datatype. The conversions rely strictly on Java’s semantics. For example, the double value 1 will be converted to the text value ‘1.0’. For instance:

  SELECT avg(cast(count as double)) FROM myTable

  Token

  token function computes the token for a given partition key. The exact signature of the token function depends on the table concerned and the partitioner used by the cluster. token depend on the partition key column type. The returned type depends on the defined partitioner: For example, consider the following table:

  CREATE TABLE users (    userid text PRIMARY KEY,    username text,);

  token function uses the single argument text, because the partition key is userid of text type. The returned type will be bigint.

  Uuid

  uuid function takes no parameters and generates a random type 4 uuid suitable for use in INSERT or UPDATE statements.

  Timeuuid functions

  now

  now function takes no arguments and generates, on the coordinator node, a new unique timeuuid at the time the function is invoked. Note that this method is useful for insertion but is largely non-sensical in WHERE clauses. For example, a query of the form:

  SELECT * FROM myTable WHERE t = now();

  now() is guaranteed to be unique. current_timeuuid is an alias of now.

  min_timeuuid and max_timeuuid

  min_timeuuid function takes a timestamp value t, either a timestamp or a date string. It returns a fake timeuuid corresponding to the smallest possible timeuuid for timestamp t. The max_timeuuid works similarly, but returns the largest possible timeuuid. For example:

  SELECT * FROM myTable WHERE t > max_timeuuid('2013-01-01 00:05+0000')   AND t < min_timeuuid('2013-02-02 10:00+0000');

  timeuuid column t is later than '2013-01-01 00:05+0000' and earlier than '2013-02-02 10:00+0000'. The clause t >= maxTimeuuid('2013-01-01 00:05+0000') would still not select a timeuuid generated exactly at ‘2013-01-01 00:05+0000’, and is essentially equivalent to t > maxTimeuuid('2013-01-01 00:05+0000').

  Datetime functions

  Retrieving the current date/time

  The following functions can be used to retrieve the date/time at the time where the function is invoked: For example the last two days of data can be retrieved using:

  SELECT * FROM myTable WHERE date >= current_date() - 2d;

  Time conversion functions

  timeuuid, a timestamp or a date into another native type.

  Blob conversion functions

  blob. For every type supported by CQL, the function type_as_blob takes a argument of type type and returns it as a blob. Conversely, the function blob_as_type takes a 64-bit blob argument and converts it to a bigint value. For example, bigint_as_blob(3) returns 0x0000000000000003 and blob_as_bigint(0x0000000000000003) returns 3.

  Math Functions

  abs, exp, log, log10, and round. The return type for these functions is always the same as the input type.

  Collection functions

  A number of functions are provided to operate on collection columns.

  Data masking functions

  A number of functions allow to obscure the real contents of a column containing sensitive data.

  Vector similarity functions

  A number of functions allow to obtain the similarity score between vectors of floats.

  User-defined functions

  Java. overloaded, so that multiple UDFs with different argument types can have the same function name. For example:

  CREATE FUNCTION sample ( arg int ) ...;CREATE FUNCTION sample ( arg text ) ...;

  SELECT, INSERT and UPDATE statements. Complex types like collections, tuple types and user-defined types are valid argument and return types in UDFs. Tuple types and user-defined types use the DataStax Java Driver conversion functions. Please see the Java Driver documentation for details on handling tuple types and user-defined types. Arguments for functions can be literals or terms. Prepared statement placeholders can be used, too. Note the use the double dollar-sign syntax to enclose the UDF source code. For example:

  CREATE FUNCTION some_function ( arg int )    RETURNS NULL ON NULL INPUT    RETURNS int    LANGUAGE java    AS $$ return arg; $$;SELECT some_function(column) FROM atable ...;UPDATE atable SET col = some_function(?) ...;CREATE TYPE custom_type (txt text, i int);CREATE FUNCTION fct_using_udt ( udtarg frozen )    RETURNS NULL ON NULL INPUT    RETURNS text    LANGUAGE java    AS $$ return udtarg.getString("txt"); $$;

  udfContext field (or binding for script UDFs) provides the necessary functionality to create new UDT and tuple values:

  CREATE TYPE custom_type (txt text, i int);CREATE FUNCTION fct\_using\_udt ( somearg int )    RETURNS NULL ON NULL INPUT    RETURNS custom_type    LANGUAGE java    AS $$        UDTValue udt = udfContext.newReturnUDTValue();        udt.setString("txt", "some string");        udt.setInt("i", 42);        return udt;    $$;

  UDFContext interface can be found in the Apache Cassandra source code for org.apache.cassandra.cql3.functions.UDFContext.

  public interface UDFContext{    UDTValue newArgUDTValue(String argName);    UDTValue newArgUDTValue(int argNum);    UDTValue newReturnUDTValue();    UDTValue newUDTValue(String udtName);    TupleValue newArgTupleValue(String argName);    TupleValue newArgTupleValue(int argNum);    TupleValue newReturnTupleValue();    TupleValue newTupleValue(String cqlDefinition);}

  Java UDFs already have some imports for common interfaces and classes defined. These imports are:

  import java.nio.ByteBuffer;import java.util.List;import java.util.Map;import java.util.Set;import org.apache.cassandra.cql3.functions.UDFContext;import com.datastax.driver.core.TypeCodec;import com.datastax.driver.core.TupleValue;import com.datastax.driver.core.UDTValue;

  Please note, that these convenience imports are not available for script UDFs.

  CREATE FUNCTION statement

  CREATE FUNCTION statement:

  create_function_statement::= CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS]    function_name '(' arguments_declaration ')'    [ CALLED | RETURNS NULL ] ON NULL INPUT    RETURNS cql_type    LANGUAGE identifier    AS string arguments_declaration: identifier cql_type ( ',' identifier cql_type )*

  For example:

  CREATE OR REPLACE FUNCTION somefunction(somearg int, anotherarg text, complexarg frozen<someUDT>, listarg list)    RETURNS NULL ON NULL INPUT    RETURNS text    LANGUAGE java    AS $$        // some Java code    $$;CREATE FUNCTION IF NOT EXISTS akeyspace.fname(someArg int)    CALLED ON NULL INPUT    RETURNS text    LANGUAGE java    AS $$        // some Java code    $$;

  CREATE FUNCTION with the optional OR REPLACE keywords creates either a function or replaces an existing one with the same signature. A CREATE FUNCTION without OR REPLACE fails if a function with the same signature already exists. If the optional IF NOT EXISTS keywords are used, the function will only be created only if another function with the same signature does not exist. OR REPLACE and IF NOT EXISTS cannot be used together. null input values must be defined for each function:
• RETURNS NULL ON NULL INPUT declares that the function will always return null if any of the input arguments is null.
• CALLED ON NULL INPUT declares that the function will always be executed.

  Function Signature

  Signatures are used to distinguish individual functions. The signature consists of a fully-qualified function name of the . and a concatenated list of all the argument types. Note that keyspace names, function names and argument types are subject to the default naming conventions and case-sensitivity rules. Functions belong to a keyspace; if no keyspace is specified, the current keyspace is used. User-defined functions are not allowed in the system keyspaces.

  DROP FUNCTION statement

  DROP FUNCTION statement:

  drop_function_statement::= DROP FUNCTION [ IF EXISTS ] function_name [ '(' arguments_signature ')' ]arguments_signature::= cql_type ( ',' cql_type )*

  For example:

  DROP FUNCTION myfunction;DROP FUNCTION mykeyspace.afunction;DROP FUNCTION afunction ( int );DROP FUNCTION afunction ( text );

  DROP FUNCTION with the optional IF EXISTS keywords drops a function if it exists, but does not throw an error if it doesn’t.

  Aggregate functions

  Aggregate functions work on a set of rows. Values for each row are input, to return a single value for the set of rows aggregated. normal columns, scalar functions, UDT fields, writetime, or ttl are selected together with aggregate functions, the values returned for them will be the ones of the first row matching the query.

  Native aggregates

  Count

  count function can be used to count the rows returned by a query. For example:

  SELECT COUNT (*) FROM plays;SELECT COUNT (1) FROM plays;

  It also can count the non-null values of a given column:

  SELECT COUNT (scores) FROM plays;

  Max and Min

  max and min functions compute the maximum and the minimum value returned by a query for a given column. For example:

  SELECT MIN (players), MAX (players) FROM plays WHERE game = 'quake';

  Sum

  sum function sums up all the values returned by a query for a given column. The returned value is of the same type as the input collection elements, so there is a risk of overflowing if the sum of the values exceeds the maximum value that the type can represent. For example:

  SELECT SUM (players) FROM plays;

  The returned value is of the same type as the input values, so there is a risk of overflowing the type if the sum of the values exceeds the maximum value that the type can represent. You can use type casting to cast the input values as a type large enough to contain the type. For example:

  SELECT SUM (CAST (players AS VARINT)) FROM plays;

  Avg

  avg function computes the average of all the values returned by a query for a given column. For example:

  SELECT AVG (players) FROM plays;

  The average of an empty collection returns zero. collection_avg([1, 2]) returns 1 instead of 1.5. You can use type casting to cast to a type with the desired decimal precision. For example:

  SELECT AVG (CAST (players AS FLOAT)) FROM plays;

  User-Defined Aggregates (UDAs)

  SELECT statement. initial state of type STYPE defined with the INITCONDvalue (default value: null). The first argument of the state function must have type STYPE. The remaining arguments of the state function must match the types of the user-defined aggregate arguments. The state function is called once for each row, and the value returned by the state function becomes the new state. After all rows are processed, the optional FINALFUNC is executed with last state value as its argument. STYPE value is mandatory in order to distinguish possibly overloaded versions of the state and/or final function, since the overload can appear after creation of the aggregate. USE statement):

  CREATE OR REPLACE FUNCTION test.averageState(state tuple<int,bigint>, val int)    CALLED ON NULL INPUT    RETURNS tuple    LANGUAGE java    AS $$        if (val != null) {            state.setInt(0, state.getInt(0)+1);            state.setLong(1, state.getLong(1)+val.intValue());        }        return state;    $$;CREATE OR REPLACE FUNCTION test.averageFinal (state tuple<int,bigint>)    CALLED ON NULL INPUT    RETURNS double    LANGUAGE java    AS $$        double r = 0;        if (state.getInt(0) == 0) return null;        r = state.getLong(1);        r /= state.getInt(0);        return Double.valueOf(r);    $$;CREATE OR REPLACE AGGREGATE test.average(int)    SFUNC averageState    STYPE tuple    FINALFUNC averageFinal    INITCOND (0, 0);CREATE TABLE test.atable (    pk int PRIMARY KEY,    val int);INSERT INTO test.atable (pk, val) VALUES (1,1);INSERT INTO test.atable (pk, val) VALUES (2,2);INSERT INTO test.atable (pk, val) VALUES (3,3);INSERT INTO test.atable (pk, val) VALUES (4,4);SELECT test.average(val) FROM atable;

  CREATE AGGREGATE statement

  CREATE AGGREGATE statement:

  create_aggregate_statement ::= CREATE [ OR REPLACE ] AGGREGATE [ IF NOT EXISTS ]                                function_name '(' arguments_signature')'                                SFUNC function_name                                STYPE cql_type:                                [ FINALFUNC function_name]                                [ INITCOND term ]

  See above for a complete example. CREATE AGGREGATE command with the optional OR REPLACE keywords creates either an aggregate or replaces an existing one with the same signature. A CREATE AGGREGATE without OR REPLACE fails if an aggregate with the same signature already exists. The CREATE AGGREGATE command with the optional IF NOT EXISTS keywords creates an aggregate if it does not already exist. The OR REPLACE and IF NOT EXISTS phrases cannot be used together. STYPE value defines the type of the state value and must be specified. The optional INITCOND defines the initial state value for the aggregate; the default value is null. A non-null INITCOND must be specified for state functions that are declared with RETURNS NULL ON NULL INPUT. SFUNC value references an existing function to use as the state-modifying function. The first argument of the state function must have type STYPE. The remaining arguments of the state function must match the types of the user-defined aggregate arguments. The state function is called once for each row, and the value returned by the state function becomes the new state. State is not updated for state functions declared with RETURNS NULL ON NULL INPUT and called with null. After all rows are processed, the optional FINALFUNC is executed with last state value as its argument. It must take only one argument with type STYPE, but the return type of the FINALFUNC may be a different type. A final function declared with RETURNS NULL ON NULL INPUT means that the aggregate’s return value will be null, if the last state is null. FINALFUNC is defined, the overall return type of the aggregate function is STYPE. If a FINALFUNC is defined, it is the return type of that function.

  DROP AGGREGATE statement

  DROP AGGREGATE statement:

  drop_aggregate_statement::= DROP AGGREGATE [ IF EXISTS ] function_name[ '(' arguments_signature ')']

  For instance:

  DROP AGGREGATE myAggregate;DROP AGGREGATE myKeyspace.anAggregate;DROP AGGREGATE someAggregate ( int );DROP AGGREGATE someAggregate ( text );

  DROP AGGREGATE statement removes an aggregate created using CREATE AGGREGATE. You must specify the argument types of the aggregate to drop if there are multiple overloaded aggregates with the same name but a different signature. DROP AGGREGATE command with the optional IF EXISTS keywords drops an aggregate if it exists, and does nothing if a function with the signature does not exist.