YCQL API reference

Cassandra-compatible API

Introduction

Yugabyte Cloud Query Language (YCQL) is a semi-relational SQL API that is best fit for internet-scale OLTP and HTAP applications needing massive data ingestion and blazing-fast queries. It supports strongly consistent secondary indexes, a native JSON column type, and distributed transactions. It has its roots in the Cassandra Query Language (CQL).

This page covers the following YCQL features.

Data definition language (DDL) statements.
Data manipulation language (DML) statements.
Builtin functions and Expression operators.
Primitive user-defined data types.

DDL statements

Data definition language (DDL) statements are instructions for the following database operations.

Create, alter, and drop database objects
Create, grant, and revoke users and roles

Statement	Description
`ALTER TABLE`	Alter a table
`ALTER KEYSPACE`	Alter a keyspace
`CREATE INDEX`	Create a new index on a table
`CREATE KEYSPACE`	Create a new keyspace
`CREATE TABLE`	Create a new table
`CREATE TYPE`	Create a user-defined data type
`DROP INDEX`	Remove an index
`DROP KEYSPACE`	Remove a keyspace
`DROP TABLE`	Remove a table
`DROP TYPE`	Remove a user-defined data type
`USE`	Use an existing keyspace for subsequent commands

DDL security statements

Security statements are instructions for managing and restricting operations on the database objects.

Create, grant, and revoke users and roles
Grant, and revoke permissions on database objects

This feature is enabled by setting the YB-TServer configuration flag --use_cassandra_authentication to true.

Statement	Description
`ALTER ROLE`	Alter a role
`CREATE ROLE`	Create a new role
`DROP ROLE`	Remove a role
`GRANT PERMISSION`	Grant a permission on an object to a role
`REVOKE PERMISSION`	Revoke a permission on an object from a role
`GRANT ROLE`	Grant a role to another role
`REVOKE ROLE`	Revoke a role from another role

DML statements

Data manipulation language (DML) statements are used to read from and write to the existing database objects. YugabyteDB implicitly commits any updates by DML statements (similar to how Apache Cassandra behaves).

Statement	Description
`INSERT`	Insert rows into a table
`SELECT`	Select rows from a table
`UPDATE`	Update rows in a table
`DELETE`	Delete specific rows from a table
`TRANSACTION`	Makes changes to multiple rows in one or more tables in a transaction
`TRUNCATE`	Remove all rows from a table

Expressions

An expression is a finite combination of one or more values, operators, functions, and expressions that specifies a computation. Expressions can be used in the following components.

The select list of SELECT statement. For example, SELECT id + 1 FROM sample_table;.
The WHERE clause in SELECT, DELETE, INSERT, or UPDATE.
The IF clause in DELETE, INSERT, or UPDATE.
The VALUES clause in INSERT.
The SET clause in UPDATE.

Currently, the following expressions are supported.

Expression	Description
Simple Value	Column, constant, or null. Column alias cannot be used in expression yet.
Subscript `[]`	Subscripting columns of collection data types
Operator Call	Builtin operators only
Function Call	Builtin function calls only

Data types

The following table lists all supported primitive types.

Primitive Type	Allowed in Key	Type Parameters	Description
`BIGINT`	Yes	-	64-bit signed integer
`BLOB`	Yes	-	String of binary characters
`BOOLEAN`	Yes	-	Boolean
`COUNTER`	No	-	64-bit signed integer
`DECIMAL`	Yes	-	Exact, arbitrary-precision number, no upper-bound on decimal precision
`DATE`	Yes	-	Date
`DOUBLE`	Yes	-	64-bit, inexact, floating-point number
`FLOAT`	Yes	-	64-bit, inexact, floating-point number
`FROZEN`	Yes	1	Collection in binary format
`INET`	Yes	-	String representation of IP address
`INT` \| `INTEGER`	Yes	-	32-bit signed integer
`LIST`	No	1	Collection of ordered elements
`MAP`	No	2	Collection of pairs of key-and-value elements
`SET`	No	1	Collection of unique elements
`SMALLINT`	Yes	-	16-bit signed integer
`TEXT` \| `VARCHAR`	Yes	-	String of Unicode characters
`TIME`	Yes	-	Time of day
`TIMESTAMP`	Yes	-	Date-and-time
`TIMEUUID`	Yes	-	Timed UUID
`TINYINT`	Yes	-	8-bit signed integer
`UUID`	Yes	-	Standard UUID
`VARINT`	Yes	-	Arbitrary-precision integer
`JSONB`	No	-	JSON data type similar to PostgreSQL jsonb

User-defined data types are also supported.