YCQL
Attention
This page documents an earlier version. Go to the latest (v2.1) version.Introduction
YCQL is a SQL-based, flexible-schema API that is best fit for internet-scale OLTP apps needing a semi-relational API highly optimized for write-intensive applications as well as blazing-fast queries. It supports distributed transactions, strongly consistent secondary indexes, and a native JSON column type. It has its roots in the Cassandra Query Language (CQL) .
YCQL supports the following 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 |
|---|---|
| <code>ALTER TABLE</code> | Alter a table |
| <code>ALTER KEYSPACE</code> | Alter a keyspace |
| <code>CREATE INDEX</code> | Create a new index on a table |
| <code>CREATE KEYSPACE</code> | Create a new keyspace |
| <code>CREATE TABLE</code> | Create a new table |
| <code>CREATE TYPE</code> | Create a user-defined data type |
| <code>DROP INDEX</code> | Remove an index |
| <code>DROP KEYSPACE</code> | Remove a keyspace |
| <code>DROP TABLE</code> | Remove a table |
| <code>DROP TYPE</code> | Remove a user-defined data type |
| <code>USE</code> | 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 gflag <code>use_cassandra_authentication</code> to true.
- Create, grant, and revoke users and roles
- Grant, and revoke permissions on database objects
| Statement | Description |
|---|---|
| <code>ALTER ROLE</code> | Alter a role |
| <code>CREATE ROLE</code> | Create a new role |
| <code>DROP ROLE</code> | Remove a role |
| <code>GRANT PERMISSION</code> | Grant a permission on an object to a role |
| <code>REVOKE PERMISSION</code> | Revoke a permission on an object from a role |
| <code>GRANT ROLE</code> | Grant a role to another role |
| <code>REVOKE ROLE</code> | 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 |
|---|---|
| <code>INSERT</code> | Insert rows into a table |
| <code>SELECT</code> | Select rows from a table |
| <code>UPDATE</code> | Update rows in a table |
| <code>DELETE</code> | Delete specific rows from a table |
| <code>TRANSACTION</code> | Makes changes to multiple rows in one or more tables in a transaction |
| <code>TRUNCATE</code> | 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. Expression can be used in the following components.
- The select list of <code>SELECT</code> statement. For example,
SELECT id + 1 FROM sample_table;. - The WHERE clause in <code>SELECT</code> , <code>DELETE</code> , <code>INSERT</code> , or <code>UPDATE</code> .
- The IF clause in <code>DELETE</code> , <code>INSERT</code> , or <code>UPDATE</code> .
- The VALUES clause in <code>INSERT</code> .
- The SET clause in <code>UPDATE</code> .
Currently, the following expressions are supported.
| Expression | Description |
|---|---|
| Simple Value | Column, constant, or null. Column alias cannot be used in expression yet. |
| Subscript <code>[]</code> | 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 |
|---|---|---|---|
| <code>BIGINT</code> | Yes | - | 64-bit signed integer |
| <code>BLOB</code> | Yes | - | String of binary characters |
| <code>BOOLEAN</code> | Yes | - | Boolean |
| <code>COUNTER</code> | No | - | 64-bit signed integer |
| <code>DECIMAL</code> | Yes | - | Exact, fixed-point number |
| <code>DATE</code> | Yes | - | Date |
| <code>DOUBLE</code> | Yes | - | 64-bit, inexact, floating-point number |
| <code>FLOAT</code> | Yes | - | 64-bit, inexact, floating-point number |
| <code>FROZEN</code> | Yes | 1 | Collection in binary format |
| <code>INET</code> | Yes | - | String representation of IP address |
| <code>INT</code> | <code>INTEGER</code> | Yes | - | 32-bit signed integer |
| <code>LIST</code> | No | 1 | Collection of ordered elements |
| <code>MAP</code> | No | 2 | Collection of pairs of key-and-value elements |
| <code>SET</code> | No | 1 | Collection of unique elements |
| <code>SMALLINT</code> | Yes | - | 16-bit signed integer |
| <code>TEXT</code> | <code>VARCHAR</code> | Yes | - | String of Unicode characters |
| <code>TIME</code> | Yes | - | Time of day |
| <code>TIMESTAMP</code> | Yes | - | Date-and-time |
| <code>TIMEUUID</code> | Yes | - | Timed UUID |
| <code>TINYINT</code> | Yes | - | 8-bit signed integer |
| <code>UUID</code> | Yes | - | Standard UUID |
| <code>VARINT</code> | Yes | - | Arbitrary-precision integer |
| <code>JSONB</code> | No | - | JSON data type similar to PostgreSQL jsonb |
-
User-defined data types are also supported.
-
The following table lists all CQL primitive types that are not yet implemented.
| Primitive Type |
|---|
TUPLE |