Yugabyte Cloud Query Language (YCQL)

Yugabyte Cloud Query Language (YCQL)

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.

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.

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

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.

Learn more