Data TypesCopy

CountersCopy

The counter type is used to define counter columns. A counter column is a column with a 64-bit signed integer value and on which two operations are supported: incrementing and decrementing (see the UPDATE statement for syntax). Note that the value of a counter cannot be set: a counter does not exist until first incremented/decremented, and that first increment/decrement is made as if the prior value was 0.

Counters have a number of important limitations:

• They cannot be used for columns part of the PRIMARY KEY of a table.

• A table that contains a counter can only contain counters. In other words, either all the columns of a table outside the PRIMARY KEY have the counter type, or none of them have it.

• Counters do not support expiring data with Time to Live (TTL).

• The deletion of counters is supported but is only guaranteed to work the first time you delete a counter. In other words, you should not re-update a counter that you have deleted (if you do, proper behavior is not guaranteed).

• Counter updates are, by nature, not idempotent. An important consequence is that if a counter update fails unexpectedly (timeout or loss of connection to the coordinator node), the client has no way to know if the update has been applied or not. In particular, replaying the update may or may not lead to an overcount.

Noteworthy characteristicsCopy

Collections are meant for storing/denormalizing a relatively small amount of data. They work well for things like “the phone numbers of a given user”, “labels applied to an email”, etc. But when items are expected to grow unbounded (“all messages sent by a user”, “events registered by a sensor”…), then collections are not appropriate, and a specific table (with clustering columns) should be used. A collection can be frozen or non-frozen.

A non-frozen collection can be modified, i.e., have an element added or removed. A frozen collection is immutable, and can only be updated as a whole. Only frozen collections can be used as primary keys or nested collections.

By default, a collection is non-frozen. To declare a frozen collection, use FROZEN keyword:

frozen_collection_type: FROZEN '<' MAP '<' `cql_type` ',' `cql_type` '>' '>'
                       : | FROZEN '<' SET '<' `cql_type` '>' '>'
                       : | FROZEN '<' LIST '<' `cql_type` '>' '>'

Collections, frozen and non-frozen, have the following noteworthy characteristics and limitations:

• Individual collections are not indexed internally. This means that even to access a single element of a collection, the whole collection has to be read (and reading one is not paged internally).

• While insertion operations on sets and maps never incur a read-before-write internally, some operations on lists do. Further, some list operations are not idempotent by nature (see the section on lists below for details), making their retry in case of timeout problematic. It is thus advised to prefer sets over lists when possible.

Please note that while some of those limitations may or may not be removed/improved upon in the future, it is an anti-pattern to use a (single) collection to store large amounts of data.

MapsCopy

A map is a (sorted) set of key-value pairs, where keys are unique, and the map is sorted by its keys. You can define a map column with:

CREATE TABLE users (
    id text PRIMARY KEY,
    name text,
    favs map<text, text> // A map of text keys, and text values
);

A map column can be assigned new contents with either INSERT or UPDATE, as in the following examples. In both cases, the new contents replace the map’s old content, if any:

INSERT INTO users (id, name, favs)
           VALUES ('jsmith', 'John Smith', { 'fruit' : 'Apple', 'band' : 'Beatles' });

UPDATE users SET favs = { 'fruit' : 'Banana' } WHERE id = 'jsmith';

Note that ScyllaDB does not distinguish an empty map from a missing value, thus assigning an empty map ({}) to a map is the same as deleting it.

Further, maps support:

• Updating or inserting one or more elements:

  UPDATE users SET favs['author'] = 'Ed Poe' WHERE id = 'jsmith';
  UPDATE users SET favs = favs + { 'movie' : 'Cassablanca', 'band' : 'ZZ Top' } WHERE id = 'jsmith';
  

• Removing one or more element (if an element doesn’t exist, removing it is a no-op but no error is thrown):

  DELETE favs['author'] FROM users WHERE id = 'jsmith';
  UPDATE users SET favs = favs - { 'movie', 'band'} WHERE id = 'jsmith';
  

  Note that for removing multiple elements in a map, you remove from it a set of keys.

Lastly, TTLs are allowed for both INSERT and UPDATE, but in both cases, the TTL set only applies to the newly inserted/updated elements. In other words:

UPDATE users USING TTL 10 SET favs['color'] = 'green' WHERE id = 'jsmith';

will only apply the TTL to the { 'color' : 'green' } record, the rest of the map remaining unaffected.

SetsCopy

A set is a (sorted) collection of unique values. You can define a set column with:

CREATE TABLE images (
    name text PRIMARY KEY,
    owner text,
    tags set<text> // A set of text values
);

A set column can be assigned new contents with either INSERT or UPDATE, as in the following examples. In both cases, the new contents replace the set’s old content, if any:

INSERT INTO images (name, owner, tags)
            VALUES ('cat.jpg', 'jsmith', { 'pet', 'cute' });

UPDATE images SET tags = { 'kitten', 'cat', 'lol' } WHERE name = 'cat.jpg';

Note that ScyllaDB does not distinguish an empty set from a missing value, thus assigning an empty set ({}) to a set is the same as deleting it.

Further, sets support:

• Adding one or multiple elements (as this is a set, inserting an already existing element is a no-op):

  UPDATE images SET tags = tags + { 'gray', 'cuddly' } WHERE name = 'cat.jpg';
  

• Removing one or multiple elements (if an element doesn’t exist, removing it is a no-op but no error is thrown):

  UPDATE images SET tags = tags - { 'cat' } WHERE name = 'cat.jpg';
  

Lastly, as for maps, TTLs, if used, only apply to the newly inserted values.

ListsCopy

A list is an ordered list of values (not necessarily unique). You can define a list column with:

CREATE TABLE plays (
    id text PRIMARY KEY,
    game text,
    players int,
    scores list<int> // A list of integers
);

A list column can be assigned new contents with either INSERT or UPDATE, as in the following examples. In both cases, the new contents replace the list’s old content, if any:

INSERT INTO plays (id, game, players, scores)
           VALUES ('123-afde', 'quake', 3, [17, 4, 2]);

UPDATE plays SET scores = [ 3, 9, 4] WHERE id = '123-afde';

Note that ScyllaDB does not distinguish an empty list from a missing value, thus assigning an empty list ([]) to a list is the same as deleting it.

Further, lists support:

• Appending and prepending values to a list:

  UPDATE plays SET players = 5, scores = scores + [ 14, 21 ] WHERE id = '123-afde';
  UPDATE plays SET players = 6, scores = [ 3 ] + scores WHERE id = '123-afde';
  

• Setting the value at a particular position in the list. This implies that the list has a pre-existing element for that position or an error will be thrown that the list is too small:

  UPDATE plays SET scores[1] = 7 WHERE id = '123-afde';
  

• Removing an element by its position in the list. This implies that the list has a pre-existing element for that position, or an error will be thrown that the list is too small. Further, as the operation removes an element from the list, the list size will be diminished by 1, shifting the position of all the elements following the one deleted:

  DELETE scores[1] FROM plays WHERE id = '123-afde';
  

• Deleting all the occurrences of particular values in the list (if a particular element doesn’t occur at all in the list, it is simply ignored, and no error is thrown):

  UPDATE plays SET scores = scores - [ 12, 21 ] WHERE id = '123-afde';
  

Lastly, as for maps, TTLs, when used, only apply to the newly inserted values.

Creating a UDTCopy

Creating a new user-defined type is done using a CREATE TYPE statement defined by:

create_type_statement: CREATE TYPE [ IF NOT EXISTS ] `udt_name`
                     :     '(' `field_definition` ( ',' `field_definition` )* ')'
field_definition: `identifier` `cql_type`

A UDT has a name (udt_name), which is used to declare columns of that type and is a set of named and typed fields. The udt_name can be any type, including collections or other UDTs. UDTs and collections inside collections must always be frozen (no matter which version of ScyllaDB you are using).

For example:

CREATE TYPE full_name (
  first text,
  last text
);


CREATE TYPE phone (
  country_code int,
  number text,
);

CREATE TYPE address (
  street text,
  city text,
  zip text,
  phones map<text, frozen<phone>>
);


CREATE TABLE superheroes (
     name frozen<full_name> PRIMARY KEY,
     home frozen<address>
);

A non-frozen UDT example with ScyllaDB Open Source 3.2 and higher:

CREATE TYPE ut (a int, b int);
CREATE TABLE cf (a int primary key, b ut);

Same UDT in versions prior:

CREATE TYPE ut (a int, b int);
CREATE TABLE cf (a int primary key, b frozen<ut>);

UDT literalsCopy

Once a user-defined type has been created, value can be input using a UDT literal:

udt_literal: '{' `identifier` ':' `term` ( ',' `identifier` ':' `term` )* '}'

In other words, a UDT literal is like a map literal but its keys are the names of the fields of the type. For instance, one could insert into the table defined in the previous section using:

INSERT INTO superheroes (name, home)
     VALUES ({first: 'Buffy', last: 'Summers'},
             {street: '1630 Revello Drive',
              city: 'Sunnydale',
              phones: { 'land-line' : { country_code: 1, number: '1234567890'},
                        'fax' : { country_code: 1, number: '10000000'}}
             }
     );

INSERT INTO superheroes (name, home)
     VALUES ({first: 'Al', last: 'Bundy'},
             {street: '9764 Jeopardy Lane',
              city: 'Chicago'});

To be valid, a UDT literal should only include fields defined by the type it is a literal of, but it can omit some fields (in which case those will be null).

You can use UDT in a WHERE clause of a SELECT statement as follow:

SELECT * from superheroes WHERE name={first: 'Al', last: 'Bundy'};

result:

name                         | home
------------------------------+--------------------------------------------------------------------------
{first: 'Al', last: 'Bundy'} | {street: '9764 Jeopardy Lane', city: 'Chicago', zip: null, phones: null}

Note that if you provide a subset of the UDT, for example, just the first name in the example below, null will be used for the missing values. For example:

SELECT * from superheroes WHERE name={first: 'Al'};

result:

name | home
------+------

(0 rows)

Altering a UDTCopy

An existing user-defined type can be modified using an ALTER TYPE statement:

alter_type_statement: ALTER TYPE `udt_name` `alter_type_modification`
alter_type_modification: ADD `field_definition`
                       : | RENAME `identifier` TO `identifier` ( `identifier` TO `identifier` )*

You can:

• Add a new field to the type (ALTER TYPE address ADD country text). That new field will be null for any values of the type created before the addition.

• Rename the fields of the type (ALTER TYPE address RENAME zip TO zipcode).

Dropping a UDTCopy

You can drop an existing user-defined type using a DROP TYPE statement:

drop_type_statement: DROP TYPE [ IF EXISTS ] `udt_name`

Dropping a type results in the immediate, irreversible removal of that type. However, attempting to drop a type that is still in use by another type, table or function will result in an error.

If the type dropped does not exist, an error will be returned unless IF EXISTS is used, in which case the operation is a no-op.