Yugabyte Cloud Query Language (YCQL)

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.

Quick Start

You can explore the basics of the YCQL API using the Quick start steps.

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.

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

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

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

• The following table lists all security statements that are not yet implemented.

Primitive Type
LIST ROLES
LIST PERMISSIONS

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.

• The following table lists all YCQL primitive types that are not yet implemented.

Primitive Type
TUPLE