Functions

CQL supports two main categories of functions:

  • The scalar functions, which simply take a number of values and produce an output with it.

  • The aggregate functions, which are used to aggregate multiple rows of results from a SELECT statement.

Scalar functions

Native functions

Cast

Supported starting from Scylla version 2.1

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

The following table describes the conversions supported by the cast function. Scylla will silently ignore any cast converting a cast datatype into its own datatype.

From

To

ascii

text, varchar

bigint

tinyint, smallint, int, float, double, decimal, varint, text, varchar

boolean

text, varchar

counter

tinyint, smallint, int, bigint, float, double, decimal, varint, text, varchar

date

timestamp

decimal

tinyint, smallint, int, bigint, float, double, varint, text, varchar

double

tinyint, smallint, int, bigint, float, decimal, varint, text, varchar

float

tinyint, smallint, int, bigint, double, decimal, varint, text, varchar

inet

text, varchar

int

tinyint, smallint, bigint, float, double, decimal, varint, text, varchar

smallint

tinyint, int, bigint, float, double, decimal, varint, text, varchar

time

text, varchar

timestamp

date, text, varchar

timeuuid

timestamp, date, text, varchar

tinyint

tinyint, smallint, int, bigint, float, double, decimal, varint, text, varchar

uuid

text, varchar

varint

tinyint, smallint, int, bigint, float, double, decimal, text, varchar

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 computes a token for a given partition key. The exact signature of the token function depends on the table concerned and on the partitioner used by the cluster.

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

  • For Murmur3Partitioner, the return type is bigint.

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

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

The token function accepts single argument of type text (in that case, the partition key is userid (there are no clustering columns, so the partition key is the same as 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.

Note

We called the values generated by minTimeuuid and maxTimeuuid fake UUID because they do no respect the Time-Based UUID generation process specified by the RFC 4122. In particular, the value returned by these two methods will not be unique. This means you should only use those methods for querying (as in the example above). Inserting the result of those methods is almost certainly a bad idea.

Datetime functions

New in version 2.3.

Retrieving the current date/time

The following functions can be used to retrieve the date/time at the time where the function is invoked:

Function name

Output type

currentTimestamp

timestamp

currentDate

date

currentTime

time

currentTimeUUID

timeUUID

For example, to retrieve the last two days of data, run the following query:

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

A number of functions are provided to “convert” a timeuuid, a timestamp, or a date into another native type.

Function name

Input type

Description

toDate

timeuuid

Converts the timeuuid argument into a date type

toDate

timestamp

Converts the timestamp argument into a date type

toTimestamp

timeuuid

Converts the timeuuid argument into a timestamp type

toTimestamp

date

Converts the date argument into a timestamp type

toUnixTimestamp

timeuuid

Converts the timeuuid argument into a bigInt raw value

toUnixTimestamp

timestamp

Converts the timestamp argument into a bigInt raw value

toUnixTimestamp

date

Converts the date argument into a bigInt raw value

dateOf

timeuuid

Similar to toTimestamp(timeuuid) (DEPRECATED)

unixTimestampOf

timeuuid

Similar to toUnixTimestamp(timeuuid) (DEPRECATED)

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 exception is a blob, for obvious reasons), the function typeAsBlob takes an argument of type type and returns it as a blob. Conversely, the function blobAsType takes a 64-bit blob argument and converts it to a bigint value. For example, bigintAsBlob(3) is 0x0000000000000003 and blobAsBigint(0x0000000000000003) is 3.

Aggregate functions

Aggregate functions work on a set of rows. They receive values for each row and return one value for the whole set.

If 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

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;

Apache Cassandra Query Language

Copyright

© 2016, The Apache Software Foundation.

Apache®, Apache Cassandra®, Cassandra®, the Apache feather logo and the Apache Cassandra® Eye logo are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.