Data Manipulation¶

Selection clause¶

The select_clause determines which columns need to be queried and returned in the result-set, as well as any transformation to apply to this result before returning. It consists of a comma-separated list of selectors or, alternatively, of the wildcard character (*) to select all the columns defined in the table.

// Without alias
SELECT intAsBlob(4) FROM t;

//  intAsBlob(4)
// --------------
//  0x00000004

// With alias
SELECT intAsBlob(4) AS four FROM t;

//  four
// ------------
//  0x00000004

The WHERE clause¶

The WHERE clause specifies which rows must be queried. It is composed of relations on the columns that are part of the PRIMARY KEY.

Not all relations are allowed in a query. For instance, non-equal relations (where IN is considered as an equal relation) on a partition key are not supported (see the use of the TOKEN method below to do non-equal queries on the partition key). Moreover, for a given partition key, the clustering columns induce an ordering of rows and relations on them restricted to the relations that let you select a contiguous (for the ordering) set of rows. For instance, given:

CREATE TABLE posts (
    userid text,
    blog_title text,
    posted_at timestamp,
    entry_title text,
    content text,
    category int,
    PRIMARY KEY (userid, blog_title, posted_at)
)

The following query is allowed:

SELECT entry_title, content FROM posts
 WHERE userid = 'john doe'
   AND blog_title='John''s Blog'
   AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31'

But the following query is not, as it does not select a contiguous set of rows (and we suppose no secondary indexes are set):

// Needs a blog_title to be set to select ranges of posted_at
SELECT entry_title, content FROM posts
 WHERE userid = 'john doe'
   AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31'

When specifying relations, the TOKEN function can be used on the PARTITION KEY column to query. In that case, rows will be selected based on the token of their PARTITION_KEY rather than on the value. Note that the token of a key depends on the partitioner in use and that, in particular, the RandomPartitioner won’t yield a meaningful order. Also note that ordering partitioners always order token values by bytes (so even if the partition key is of type int, token(-1) > token(0) in particular). For example:

SELECT * FROM posts
 WHERE token(userid) > token('tom') AND token(userid) < token('bob')

Moreover, the IN relation is only allowed on the last column of the partition key and on the last column of the full primary key.

It is also possible to “group” CLUSTERING COLUMNS together in a relation using the tuple notation. For instance:

SELECT * FROM posts
 WHERE userid = 'john doe'
   AND (blog_title, posted_at) > ('John''s Blog', '2012-01-01')

will request all rows that sort after the one having “John’s Blog” as blog_title and ‘2012-01-01’ for posted_at in the clustering order. In particular, rows having a post_at <= '2012-01-01' will be returned as long as their blog_title > 'John''s Blog', which would not be the case for:

SELECT * FROM posts
 WHERE userid = 'john doe'
   AND blog_title > 'John''s Blog'
   AND posted_at > '2012-01-01'

The tuple notation may also be used for IN clauses on clustering columns:

SELECT * FROM posts
 WHERE userid = 'john doe'
   AND (blog_title, posted_at) IN (('John''s Blog', '2012-01-01'), ('Extreme Chess', '2014-06-01'))

The CONTAINS operator may only be used on collection columns (lists, sets, and maps). In the case of maps, CONTAINS applies to the map values. The CONTAINS KEY operator may only be used on map columns and applies to the map keys.

Grouping results¶

The GROUP BY option lets you condense into a single row all selected rows that share the same values for a set of columns. Using the GROUP BY option, it is only possible to group rows at the partition key level or at a clustering column level. The GROUP BY arguments must form a prefix of the primary key.

For example, if the primary key is (p1, p2, c1, c2), then the following queries are valid:

GROUP BY p1
GROUP BY p1, p2
GROUP BY p1, p2, c1
GROUP BY p1, p2, c1, c2

The following should be considered when using the GROUP BY option:

• If a primary key column is restricted by an equality restriction, it is not required to be present in the GROUP BY clause.

• Aggregate functions will produce a separate value for each group.

• If no GROUP BY clause is specified, aggregate functions will produce a single value for all the rows.

• If a column is selected without an aggregate function, in a statement with a GROUP BY, the first value encounter in each group will be returned.

Ordering results¶

The default order for a SELECT statement depends on the default clustering order of a table, which is defined when the table is created - it is ASC (ascendant) by default, but can be changed using the WITH CLUSTERING ORDER BY option. See CREATE TABLE.

The ORDER BY clause allows you to configure a non-default order of the returned result. It takes a list of column names along with the order for the column as an argument (ASC for ascendant and DESC for descendant, omitting the default order).

Currently, the possible orderings are limited by the clustering order defined on the table:

• If the table has been defined without any specific CLUSTERING ORDER, then allowed orderings are the order induced by the clustering columns and the reverse of that one.

• Otherwise, the orderings allowed are the order of the CLUSTERING ORDER option and the reversed one.

Limiting results¶

The LIMIT option to a SELECT statement limits the number of rows returned by a query, while the PER PARTITION LIMIT option (introduced in Scylla 3.1) limits the number of rows returned for a given partition by the query. Note that both types of limit can be used in the same statement.

Examples:

The Partition Key in the following table is client_id, and the clustering key is when. The table has seven rows, split between four clients (partition keys)

cqlsh:ks1> SELECT client_id, when FROM test;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       1 | 2020-01-01 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       2 | 2020-02-11 22:00:00.000000+0000
       2 | 2020-02-12 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000
       3 | 2020-02-10 22:00:00.000000+0000

(7 rows)

You can ask the query to limit the number of rows returned from all partition with LIMIT, for example:

cqlsh:ks1> SELECT client_id, when FROM ks1.test LIMIT 3;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       1 | 2020-01-01 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000

(3 rows)

You can ask the query to limit the number of rows returned for each client_id. For example, with limit of 1 :

cqlsh:ks1> SELECT client_id, when FROM ks1.test PER PARTITION LIMIT 1;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000
       3 | 2020-02-10 22:00:00.000000+0000

(4 rows)

Increasing limit to 2, would yield:

cqlsh:ks1> SELECT client_id, when FROM ks1.test PER PARTITION LIMIT 2;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       1 | 2020-01-01 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       2 | 2020-02-11 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000
       3 | 2020-02-10 22:00:00.000000+0000

(6 rows)

You can also mix the two limits types:

cqlsh> SELECT client_id, when FROM ks1.test PER PARTITION LIMIT 1 LIMIT 3;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000

(3 rows)

Allowing filtering¶

By default, CQL only allows select queries that don’t involve “filtering” server-side, i.e. queries where we know that all (live) record read will be returned (maybe partly) in the result set. The reasoning is that those “non filtering” queries have predictable performance in the sense that they will execute in a time that is proportional to the amount of data returned by the query (which can be controlled through LIMIT).

The ALLOW FILTERING option lets you explicitly allow (some) queries that require filtering. Please note that a query using ALLOW FILTERING may thus have unpredictable performance (for the definition above), i.e. even a query that selects a handful of records may exhibit performance that depends on the total amount of data stored in the cluster.

For instance, consider the following table holding user profiles with their year of birth (with a secondary index on it) and country of residence:

CREATE TABLE users (
    username text PRIMARY KEY,
    firstname text,
    lastname text,
    birth_year int,
    country text
)

CREATE INDEX ON users(birth_year);

Then the following queries are valid:

SELECT * FROM users;
SELECT * FROM users WHERE birth_year = 1981;

because in both cases, Scylla guarantees that these queries’ performance will be proportional to the amount of data returned. In particular, if no users were born in 1981, then the second query performance will not depend on the number of user profiles stored in the database (not directly at least: due to secondary index implementation consideration, this query may still depend on the number of nodes in the cluster, which indirectly depends on the amount of data stored. Nevertheless, the number of nodes will always be multiple orders of magnitude lower than the number of user profiles stored). Of course, both queries may return very large result sets in practice, but the amount of data returned can always be controlled by adding a LIMIT.

However, the following query will be rejected:

SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR';

because Scylla cannot guarantee that it won’t have to scan a large amount of data even if the result of those queries is small. Typically, it will scan all the index entries for users born in 1981 even if only a handful are actually from France. However, if you “know what you are doing”, you can force the execution of this query by using ALLOW FILTERING and so the following query is valid:

SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR' ALLOW FILTERING;

Bypass Cache¶

The BYPASS CACHE clause on SELECT statements informs the database that the data being read is unlikely to be read again in the near future, and also was unlikely to have been read in the near past; therefore, no attempt should be made to read it from the cache or to populate the cache with the data. This is mostly useful for range scans; these typically process large amounts of data with no temporal locality and do not benefit from the cache. The clause is placed immediately after the optional ALLOW FILTERING clause.

BYPASS CACHE is a Scylla CQL extension and not part of Apache Cassandra CQL.

For example:

SELECT * FROM users BYPASS CACHE;
SELECT name, occupation FROM users WHERE userid IN (199, 200, 207) BYPASS CACHE;
SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR' ALLOW FILTERING BYPASS CACHE;

Using Timeout¶

The USING TIMEOUT clause allows specifying a timeout for a specific request.

For example:

SELECT * FROM users USING TIMEOUT 5s;
SELECT name, occupation FROM users WHERE userid IN (199, 200, 207) BYPASS CACHE USING TIMEOUT 200ms;

USING TIMEOUT is a Scylla CQL extension and not part of Apache Cassandra CQL.

LIKE Operator¶

The LIKE operation on SELECT statements informs Scylla that you are looking for a pattern match. The expression ‘column LIKE pattern’ yields true only if the entire column value matches the pattern.

The search pattern is a string of characters with two wildcards, as shown:

• _ matches any single character

• % matches any substring (including an empty string)

• \ escapes the next pattern character, so it matches verbatim

• any other pattern character matches itself

• an empty pattern matches empty text fields

Currently, the match is case sensitive. The entire column value must match the pattern. For example, consider the search pattern ‘M%n’ - this will match Martin, but will not match Moonbeam because the m at the end isn’t matched. In addition, moon is not matched because M is not the same as m. Both the pattern and the column value are assumed to be UTF-8 encoded.

A query can find all values containing some text fragment by matching to an appropriate LIKE pattern.

Differences Between Scylla and Cassandra LIKE Operators

• In Apache Cassandra, you must create a SASI index to use LIKE. Scylla supports LIKE as a regular filter.

• Consequently, Scylla LIKE will be less performant than Apache Cassandra LIKE for some workloads.

• Scylla treats underscore (_) as a wildcard; Cassandra doesn’t.

• Scylla treats percent (%) as a wildcard anywhere in the pattern; Cassandra only at the beginning/end

• Scylla interprets backslash (\) as an escape character; Cassandra doesn’t.

• Cassandra allows case-insensitive LIKE; Scylla doesn’t (see #4911).

• Scylla allows empty LIKE pattern; Cassandra doesn’t.

Example A

In this example, LIKE specifies that the match is looking for a word that starts with the letter S. The % after the letter S matches any text to the end of the field.

SELECT * FROM pet_owners WHERE firstname LIKE ‘S%’ ALLOW FILTERING;
╭──────────┬─────────────────────┬────────────────╮
│ID        │LastName             │FirstName       │
├──────────┼─────────────────────┼────────────────┤
│1         │Adams                │Steven          │
├──────────┼─────────────────────┼────────────────┤
│15        │Erg                  │Sylvia          │
├──────────┼─────────────────────┼────────────────┤
│20        │Goldberg             │Stephanie       │
├──────────┼─────────────────────┼────────────────┤
│25        │Harris               │Stephanie       │
├──────────┼─────────────────────┼────────────────┤
│88        │Rosenberg            │Samuel          │
├──────────┼─────────────────────┼────────────────┤
│98        │Smith                │Sara            │
├──────────┼─────────────────────┼────────────────┤
│115       │Williams             │Susan           │
├──────────┼─────────────────────┼────────────────┤
│130       │Young                │Stuart          │
╰──────────┴─────────────────────┴────────────────╯

Example B

In this example, you are searching for all pet owners whose last name contains the characters ‘erg’.

SELECT * FROM pet_owners WHERE lastname LIKE ‘%erg%’ ALLOW FILTERING;

╭──────────┬─────────────────────┬────────────────╮
│ID        │LastName             │FirstName       │
├──────────┼─────────────────────┼────────────────┤
│11        │Berger               │David           │
├──────────┼─────────────────────┼────────────────┤
│18        │Gerg                 │Lawrence        │
├──────────┼─────────────────────┼────────────────┤
│20        │Goldberg             │Stephanie       │
├──────────┼─────────────────────┼────────────────┤
│88        │Rosenberg            │Samuel          │
├──────────┼─────────────────────┼────────────────┤
│91        │Schulberg            │Barry           │
├──────────┼─────────────────────┼────────────────┤
│110       │Weinberg             │Stuart          │
╰──────────┴─────────────────────┴────────────────╯

Note that this query does not return:

╭──────────┬─────────────────────┬────────────────╮
│ID        │LastName             │FirstName       │
├──────────┼─────────────────────┼────────────────┤
│15        │Erg                  │Sylvia          │
╰──────────┴─────────────────────┴────────────────╯

As it is case sensitive.

Example C

This table contains some commonly used LIKE filters and the matches you can expect the filter to return.

IF condition¶

It is, however, possible to use the conditions on some columns through IF, in which case the row will not be updated unless the conditions are met. Each such UPDATE gets a globally unique timestamp. But, please note that using IF conditions will incur a non-negligible performance cost (internally, Paxos will be used), so this should be used sparingly.

Update parameters¶

The UPDATE, INSERT (and DELETE and BATCH for the TIMESTAMP) statements support the following parameters:

• TIMESTAMP: sets the timestamp for the operation. If not specified, the coordinator will use the current time, in microseconds since the Unix epoch (January 1st 1970 at 00:00:00 UTC), at the start of statement execution as the timestamp. This is usually a suitable default. INSERT, UPDATE, DELETE, or BATCH statements USING TIMESTAMP should provide a unique timestamp value, similar to the one implicitly set by the coordinator by default, when the USING TIMESTAMP update parameter is absent. Scylla ensures that query timestamps created by the same coordinator node are unique (even across different shards on the same node). However, timestamps assigned at different nodes are not guaranteed to be globally unique. Note that with a steadily high write rate, timestamp collision is not unlikely. If it happens, e.g. two INSERTS have the same timestamp, a conflict resolution algorithm determines which of the inserted cells prevails (see update ordering for more information):

• TTL: specifies an optional Time To Live (in seconds) for the inserted values. If set, the inserted values are automatically removed from the database after the specified time. Note that the TTL concerns the inserted values, not the columns themselves. This means that any subsequent update of the column will also reset the TTL (to whatever TTL is specified in that update). By default, values never expire. A TTL of 0 is equivalent to no TTL. If the table has a default_time_to_live, a TTL of 0 will remove the TTL for the inserted or updated values. A TTL of null is equivalent to inserting with a TTL of 0. You can read more about TTL in the documentation and also in this Scylla University lesson.

• TIMEOUT: specifies a timeout duration for the specific request. Please refer to the SELECT section for more information.

Update ordering¶

INSERT, UPDATE, and DELETE operations are ordered by their TIMESTAMP.

Ordering of such changes is done at the cell level, where each cell carries a write TIMESTAMP, other attributes related to its expiration when it has a non-zero time-to-live (TTL), and the cell value.

The fundamental rule for ordering cells that insert, update, or delete data in a given row and column is that the cell with the highest timestamp wins.

However, it is possible that multiple such cells will carry the same TIMESTAMP. There could be several reasons for TIMESTAMP collision:

• Benign collision can be caused by “replay” of a mutation, e.g., due to client retry, or due to internal processes. In such cases, the cells are equivalent, and any of them can be selected arbitrarily.

• TIMESTAMP collisions might be normally caused by parallel queries that are served by different coordinator nodes. The coordinators might calculate the same write TIMESTAMP based on their local time in microseconds.

• Collisions might also happen with user-provided timestamps if the application does not guarantee unique timestamps with the USING TIMESTAMP parameter (see Update parameters for more information).

As said above, in the replay case, ordering of cells should not matter, as they carry the same value and same expiration attributes, so picking any of them will reach the same result. However, other TIMESTAMP conflicts must be resolved in a consistent way by all nodes. Otherwise, if nodes would have picked an arbitrary cell in case of a conflict and they would reach different results, reading from different replicas would detect the inconsistency and trigger read-repair that will generate yet another cell that would still conflict with the existing cells, with no guarantee for convergence.

Therefore, Scylla implements an internal, consistent conflict-resolution algorithm that orders cells with conflicting TIMESTAMP values based on other properties, like:

• whether the cell is a tombstone or a live cell,

• whether the cell has an expiration time,

• the cell TTL,

• and finally, what value the cell carries.

The conflict-resolution algorithm is documented in Scylla’s internal documentation and it may be subject to change.

Reliable serialization can be achieved using unique write TIMESTAMP and by using Lightweight Transactions (LWT) to ensure atomicity of INSERT, UPDATE, and DELETE.

Range deletions¶

Range deletions allow you to delete rows from a single partition, given that the clustering key is within the given range. The delete request can be determined on both ends, or it can be open-ended.

Open range deletions¶

Open range deletions delete rows based on an open-ended request (>, <, >=, =<, etc.)

For example, suppose your data set for events at Madison Square Garden includes:

CREATE KEYSPACE mykeyspace WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor': '1'}  AND durable_writes = true;
use mykeyspace ;
CREATE TABLE events ( id text, created_at date, content text, PRIMARY KEY (id, created_at) );

INSERT into events (id, created_at, content) VALUES ('concert', '2019-11-19', 'SuperM');
INSERT into events (id, created_at, content) VALUES ('concert', '2019-11-15', 'Billy Joel');
INSERT into events (id, created_at, content) VALUES ('game', '2019-11-03', 'Knicks v Sacramento');
INSERT into events (id, created_at, content) VALUES ('concert', '2019-10-31', 'Dead & Company');
INSERT into events (id, created_at, content) VALUES ('game', '2019-10-28', 'Knicks v Chicago');
INSERT into events (id, created_at, content) VALUES ('concert', '2019-10-25', 'Billy Joel');


SELECT * from events;

 id      | created_at | content
---------+------------+---------------------
    game | 2019-10-28 |    Knicks v Chicago
    game | 2019-11-03 | Knicks v Sacramento
 concert | 2019-10-25 |          Billy Joel
 concert | 2019-10-31 |      Dead & Company
 concert | 2019-11-15 |          Billy Joel
 concert | 2019-11-19 |              SuperM

(6 rows)

And you wanted to delete all of the concerts from the month of October using an open-ended range delete. You would run:

DELETE FROM events WHERE id='concert' AND created_at <= '2019-10-31';

SELECT * from events;

 id      | created_at | content
---------+------------+---------------------
    game | 2019-10-28 |    Knicks v Chicago
    game | 2019-11-03 | Knicks v Sacramento
 concert | 2019-11-15 |          Billy Joel
 concert | 2019-11-19 |              SuperM

(4 rows)

UNLOGGED batches¶

By default, Scylla uses a batch log to ensure all operations in a batch eventually complete or none will (note, however, that operations are only isolated within a single partition).

There is a performance penalty for batch atomicity when a batch spans multiple partitions. If you do not want to incur this penalty, you can tell Scylla to skip the batchlog with the UNLOGGED option. If the UNLOGGED option is used, a failed batch might leave the batch only partly applied.

COUNTER batches¶

Use the COUNTER option for batched counter updates. Unlike other updates in Scylla, counter updates are not idempotent.

Apache Cassandra Query Language (CQL) Reference

Copyright

© 2016, The Apache Software Foundation.

Apache®, Apache Cassandra®, Cassandra®, the Apache feather logo and the Apache Cassandra® Eye logo are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.