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 ... ] ')'

Built-in Functions

Aggregate Functions

Semantics

• The argument data types must be convertible to the expected type for that argument that was specified by the function definition.
• Function execution will return a value of the specified type by the function definition.
• YugabyteDB allows function calls to be used any where that expression is allowed.

CAST function

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

Syntax

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

The following table lists the column data types and the target data types.

Example

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

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 beneficial for querying a subset of the data to get approximate row counts or to break down 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) as follows:

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

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

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 as follows:

.. 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;

Refer to partition_hash in Python 3 and Go for full implementation of a parallel table scan.

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)

See also

• All Expressions