Functions¶

Cast¶

The cast function can be used to converts one native datatype to another.

The following table describes the conversions supported by the 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¶

The token function allows to compute the token for a given partition key. The exact signature of the token function depends on the table concerned and of the partitioner used by the cluster.

The type of the arguments of the token depend on the type of the partition key columns. The return type depend on the partitioner in use:

• For Murmur3Partitioner, the return type is bigint.
• For RandomPartitioner, the return type is varint.
• For ByteOrderedPartitioner, the return type is blob.

For instance, in a cluster using the default Murmur3Partitioner, if a table is defined by:

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

then the token function will take a single argument of type text (in that case, the partition key is userid (there is no clustering columns so the partition key is the same than the primary key)), and the return type will be bigint.

Uuid¶

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

Timeuuid functions¶

SELECT * FROM myTable WHERE t = now()

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

Datetime functions¶

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

Blob conversion functions¶

A number of functions are provided to “convert” the native types into binary data (blob). For every <native-type> type supported by CQL (a notable exceptions is blob, for obvious reasons), the function typeAsBlob takes a argument of type type and return it as a blob. Conversely, the function blobAsType takes a 64-bit blob argument and convert it to a bigint value. And so for instance, bigintAsBlob(3) is 0x0000000000000003 and blobAsBigint(0x0000000000000003) is 3.

CREATE FUNCTION¶

Creating a new user-defined function uses the 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 instance:

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 either creates 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 if another function with the same signature does not exist.

OR REPLACE and IF NOT EXISTS cannot be used together.

Behavior on invocation with null values must be defined for each function. There are two options:

1. RETURNS NULL ON NULL INPUT declares that the function will always return null if any of the input arguments is null.
2. CALLED ON NULL INPUT declares that the function will always be executed.

DROP FUNCTION¶

Dropping a function uses the DROP FUNCTION statement:

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

For instance:

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

You must specify the argument types (arguments_signature) of the function to drop if there are multiple functions with the same name but a different signature (overloaded functions).

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

Count¶

The count function can be used to count the rows returned by a query. Example:

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

It also can be used to count the non null value of a given column:

SELECT COUNT (scores) FROM plays;

Max and Min¶

The max and min functions can be used to compute the maximum and the minimum value returned by a query for a given column. For instance:

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

Sum¶

The sum function can be used to sum up all the values returned by a query for a given column. For instance:

SELECT SUM (players) FROM plays;

Avg¶

The avg function can be used to compute the average of all the values returned by a query for a given column. For instance:

SELECT AVG (players) FROM plays;

CREATE AGGREGATE¶

Creating (or replacing) a user-defined aggregate function uses the 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 with the optional OR REPLACE keywords either creates 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.

CREATE AGGREGATE with the optional IF NOT EXISTS keywords either creates an aggregate if it does not already exist.

OR REPLACE and IF NOT EXISTS cannot be used together.

STYPE defines the type of the state value and must be specified.

The optional INITCOND defines the initial state value for the aggregate. It defaults to null. A non-null INITCOND must be specified for state functions that are declared with RETURNS NULL ON NULL INPUT.

SFUNC references an existing function to be used as the state modifying function. The type of first argument of the state function must match STYPE. The remaining argument types of the state function must match the argument types of the aggregate function. State is not updated for state functions declared with RETURNS NULL ON NULL INPUT and called with null.

The optional FINALFUNC is called just before the aggregate result is returned. It must take only one argument with type STYPE. 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.

If no 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¶

Dropping an user-defined aggregate function uses the 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 );

The 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 aggregates with the same name but a different signature (overloaded aggregates).

DROP AGGREGATE with the optional IF EXISTS keywords drops an aggregate if it exists, and does nothing if a function with the signature does not exist.