Built-in function call
Synopsis
Use a function call expression to apply the specified function to to given arguments between parentheses and return the result of the computation.
Syntax
function_call ::= function_name '(' [ arguments ... ] ')'
Builtin 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
Cast function
cast_call ::= CAST '(' column AS type ')'
CAST function converts the value returned from a table column to the specified data type.
| 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 |
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