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

Function Return Type Argument Type Description
BlobAs<Type> <Type> (BLOB) Converts a value from BLOB
<Type>AsBlob BLOB (<Type>) Converts a value to BLOB
DateOf TIMESTAMP (TIMEUUID) Conversion
MaxTimeUuid TIMEUUID (TIMESTAMP) Returns the associated max time UUID
MinTimeUuid TIMEUUID (TIMESTAMP) Returns the associated min time UUID
CurrentDate DATE () Return the system current date
CurrentTime TIME () Return the system current time of day
CurrentTimestamp TIMESTAMP () Return the system current timestamp
Now TIMEUUID () Returns the UUID of the current timestamp
TTL BIGINT (<AnyType>) Get time-to-live of a column
ToDate DATE (TIMESTAMP) Conversion
ToDate DATE (TIMEUUID) Conversion
ToTime TIME (TIMESTAMP) Conversion
ToTime TIME (TIMEUUID Conversion
ToTimestamp (TIMESTAMP) (DATE) Conversion
ToTimestamp (TIMESTAMP) (TIMEUUID) Conversion
ToUnixTimestamp BIGINT (DATE) Conversion
ToUnixTimestamp BIGINT (TIMESTAMP) Conversion
ToUnixTimestamp BIGINT (TIMEUUID) Conversion
UnixTimestampOf BIGINT (TIMEUUID) Conversion
UUID UUID () Returns a version 4 UUID
WriteTime BIGINT (<AnyType>) Returns the timestamp when the column was written
partition_hash BIGINT () Computes the partition hash value (uint16) for the partition key columns of a row

Aggregate Functions

Function Description
COUNT Returns number of selected rows
SUM Returns sums of column values
AVG Returns the average of column values
MIN Returns the minimum value of column values
MAX Returns the maximum value of column values

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.

Source column type Target data type
BIGINT SMALLINT, INT, TEXT
BOOLEAN TEXT
DATE TEXT, TIMESTAMP
DOUBLE BIGINT, INT, SMALLINT, TEXT
FLOAT BIGINT, INT, SMALLINT, TEXT
INT BIGINT, SMALLINT, TEXT
SMALLINT BIGINT, INT, TEXT
TIME TEXT
TIMESTAMP DATE, TEXT
TIMEUUID DATE, TIMESTAMP

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