Built-in function call

Synopsis

Use a function call expression to apply the specified function to given arguments between parentheses and return the result of the computation.

Syntax

function_call ::= function_name '(' [ arguments ... ] ')'

Builtin Functions

Aggregate Functions

Semantics

Cast function

cast_call ::= CAST '(' column AS type ')'

CAST function converts the value returned from a table column to the specified data type.

partition_hash function

partition_hash is a function that takes as arguments the partition key columns of the primary key of a row and returns a uint16 hash value representing the hash value for the row used for partitioning the table. The hash values used for partitioning fall in the 0-65535 (uint16) range. Tables are partitioned into tablets, with each tablet being responsible for a range of partition values. The partition_hash of the row is used to decide which tablet the row will reside in.

partition_hash can be handy for querying a subset of the data to get approximate row counts or to breakdown full-table operations into smaller sub-tasks that can be run in parallel.

Querying a subset of the data

One use of partition_hash is to query a subset of the data and get approximate count of rows in the table. For example, suppose you have a table t with partitioning columns (h1,h2):

create table t (h1 int, h2 int, r1 int, r2 int, v int,
                         primary key ((h1, h2), r1, r2));

We can use this function to query a subset of the data (in this case, 1/128 of the data):

select count(*) from t where partition_hash(h1, h2) >= 0 and
                                      partition_hash(h1, h2) < 512;

The value 512 comes from dividing the full hash partition range by the number of subsets that you want to query (65536/128=512).

Parallel full table scans

To do a distributed scan, you can issue, in this case, 128 queries each using a different hash range:

.. where partition_hash(h1, h2) >= 0 and partition_hash(h1, h2) < 512;

.. where partition_hash(h1, h2) >= 512 and partition_hash(h1, h2) <1024 ;

and so on, till the last segment/range of 512 in the partition space:

.. where partition_hash(h1, h2) >= 65024;

Here is a full implementation of a parallel table scan using partition_hash in Python 3 and Go.

WriteTime function

The WriteTime function returns the timestamp in microseconds when a column was written. For example, suppose you have a table page_views with a column named views:

 SELECT writetime(views) FROM page_views;

 writetime(views)
------------------
 1572882871160113

(1 rows)

TTL function

The TTL function returns the number of seconds until a column or row expires. Assuming you have a table page_views and a column named views:

SELECT TTL(views) FROM page_views;

 ttl(views)
------------
      86367

(1 rows)

Examples

ycqlsh:example> CREATE TABLE test_cast (k INT PRIMARY KEY, ts TIMESTAMP);

ycqlsh:example> INSERT INTO test_cast (k, ts) VALUES (1, '2018-10-09 12:00:00');

ycqlsh:example> SELECT CAST(ts AS DATE) FROM test_cast;

 cast(ts as date)
------------------
       2018-10-09

See also

• All Expressions