Data Definition¶

Static columns¶

Some columns can be declared as STATIC in a table definition. A column that is static will be “shared” by all the rows belonging to the same partition (having the same partition key). For instance:

CREATE TABLE t (
    pk int,
    t int,
    v text,
    s text static,
    PRIMARY KEY (pk, t)
);

INSERT INTO t (pk, t, v, s) VALUES (0, 0, 'val0', 'static0');
INSERT INTO t (pk, t, v, s) VALUES (0, 1, 'val1', 'static1');

SELECT * FROM t;
   pk | t | v      | s
  ----+---+--------+-----------
   0  | 0 | 'val0' | 'static1'
   0  | 1 | 'val1' | 'static1'

As can be seen, the s value is the same (static1) for both of the rows in the partition (the partition key in that example being pk, both rows are in that same partition): the 2nd insertion has overridden the value for s.

Static columns have the following restrictions:

• tables with the COMPACT STORAGE option (see below) cannot use them.

• a table without clustering columns cannot have static columns (in a table without clustering columns, every partition has only one row, and so every column is inherently static).

• only non PRIMARY KEY columns can be static.

The partition key¶

Within a table, CQL defines the notion of a partition. A partition is simply the set of rows that share the same value for their partition key. Note that if the partition key is composed of multiple columns, then rows belong to the same partition only when they have the same values for all those partition key columns. So, for instance, given the following table definition and content:

CREATE TABLE t (
    a int,
    b int,
    c int,
    d int,
    PRIMARY KEY ((a, b), c, d)
);

SELECT * FROM t;
   a | b | c | d
  ---+---+---+---
   0 | 0 | 0 | 0    // row 1
   0 | 0 | 1 | 1    // row 2
   0 | 1 | 2 | 2    // row 3
   0 | 1 | 3 | 3    // row 4
   1 | 1 | 4 | 4    // row 5

row 1 and row 2 are in the same partition, row 3 and row 4 are also in the same partition (but a different one) and row 5 is in yet another partition.

Note that a table always has a partition key and that if the table has no clustering columns, then every partition of that table is only comprised of a single row (since the primary key uniquely identifies rows and the primary key is equal to the partition key if there are no clustering columns).

The most important property of partition is that all the rows belonging to the same partition are guarantee to be stored on the same set of replica nodes. In other words, the partition key of a table defines which of the rows will be localized together in the cluster, and it is thus important to choose your partition key wisely so that rows that need to be fetched together are in the same partition (so that querying those rows together require contacting a minimum of nodes).

However, please note that there is a flip-side to this guarantee: as all rows sharing a partition key are guaranteed to be stored on the same set of replica nodes, a partition key that groups too much data can create a hotspot.

Another useful property of a partition is that when writing data, all the updates belonging to a single partition are done atomically and in isolation, which is not the case across partitions.

The proper choice of the partition key and clustering columns for a table is probably one of the most important aspects of data modeling in Scylla. It largely impacts which queries can be performed and how efficient they are.

The clustering columns¶

The clustering columns of a table define the clustering order for the partition of that table. For a given partition, all the rows are physically ordered inside Scylla by that clustering order. For instance, given:

CREATE TABLE t (
    a int,
    b int,
    c int,
    PRIMARY KEY (a, b, c)
);

SELECT * FROM t;
   a | b | c
  ---+---+---
   0 | 0 | 4     // row 1
   0 | 1 | 9     // row 2
   0 | 2 | 2     // row 3
   0 | 3 | 3     // row 4

then the rows (which all belong to the same partition) are all stored internally in the order of the values of their b column (the order they are displayed above). So, where the partition keys of the table let you group rows on the same replica set, the clustering columns control how those rows are stored on the replica. That sorting allows the retrieval of a range of rows within a partition (for instance, in the example above, SELECT * FROM t WHERE a = 0 AND b > 1 and b <= 3) is very efficient.

Compact tables¶

A compact table is one defined with the COMPACT STORAGE option. This option is only maintained for backward compatibility for definitions created before CQL version 3 and shouldn’t be used for new tables. Declaring a table with this option creates limitations for the table, which are largely arbitrary (and exists for historical reasons). Amongst these limitations:

• a compact table cannot use collections nor static columns.

• if a compact table has at least one clustering column, then it must have exactly one column outside of the primary key ones. This implies that you cannot add or remove columns in particular after creation.

• a compact table is limited as to the indexes it can create, and no materialized view can be created on it.

Reversing the clustering order¶

The clustering order of a table is defined by the clustering columns of that table. By default, that ordering is based on the natural order of the clustering order, but the CLUSTERING ORDER lets you change that clustering order to use the reverse natural order for some (potentially all) of the columns.

The CLUSTERING ORDER option takes the comma-separated list of the clustering column, each with an ASC (for ascendant, e.g. the natural order) or DESC (for descendant, e.g. the reverse natural order). Note in particular that the default (if the CLUSTERING ORDER option is not used) is strictly equivalent to using the option with all clustering columns using the ASC modifier.

Note that this option is basically a hint for the storage engine to change the order in which it stores the row, but it has three visible consequences:

• it limits which ORDER BY clause is allowed for selects on that table. You can only order results by the clustering order or the reverse clustering order. Meaning that if a table has two clustering columns a and b, and you define WITH CLUSTERING ORDER (a DESC, b ASC), then in queries, you will be allowed to use ORDER BY (a DESC, b ASC) and (reverse clustering order) ORDER BY (a ASC, b DESC) but not ORDER BY (a ASC, b ASC) (nor ORDER BY (a DESC, b DESC)).

• it also changes the default order of results when queried (if no ORDER BY is provided). Results are always returned in clustering order (within a partition).

• it has a small performance impact on some queries as queries in reverse clustering order are slower than the one in forward clustering order. In practice, this means that if you plan on querying mostly in the reverse natural order of your columns (which is common with time series, for instance, where you often want data from the newest to the oldest), it is an optimization to declare a descending clustering order.

Other table options¶

A table supports the following options:

CREATE TABLE id (id int PRIMARY KEY) WITH compression = {'sstable_compression': 'LZ4Compressor'};

CREATE TABLE id (id int PRIMARY KEY) WITH compression = {};

CREATE TABLE customer_data (
    cust_id uuid,
    cust_first-name text,
    cust_last-name text,
    cust_phone text,
    cust_get-sms text,
    PRIMARY KEY (customer_id)
) WITH cdc = { 'enabled' : 'true', 'preimage' : 'true' };

CREATE TABLE caching (
                 k int PRIMARY KEY,
                 v1 int,
                 v2 int,
             ) WITH caching = {'enabled': 'true'};