Cassandra Query Language (CQL) elements in Amazon Keyspaces

Learn about the Cassandra Query Language (CQL) elements that are supported by Amazon Keyspaces, including identifiers, constants, terms, and data types.

Identifiers

Identifiers (or names) are used to identify tables, columns, and other objects. An identifier can be quoted or not quoted. The following applies.

identifier          ::=  unquoted_identifier | quoted_identifier
unquoted_identifier ::=  re('[a-zA-Z][a-zA-Z0-9_]*')
quoted_identifier   ::=  '"' (any character where " can appear if doubled)+ '"'

Constants

The following constants are defined.

constant ::=  string | integer | float | boolean | uuid | blob | NULL
string   ::=  '\'' (any character where ' can appear if doubled)+ '\''
              '$$' (any character other than '$$') '$$'
integer  ::=  re('-?[0-9]+')
float    ::=  re('-?[0-9]+(\.[0-9]*)?([eE][+-]?[0-9+])?') | NAN | INFINITY
boolean  ::=  TRUE | FALSE
uuid     ::=  hex{8}-hex{4}-hex{4}-hex{4}-hex{12}
hex      ::=  re("[0-9a-fA-F]")
blob     ::=  '0' ('x' | 'X') hex+

Terms

A term denotes the kind of values that are supported. Terms are defined by the following.

term                 ::=  constant | literal | function_call | arithmetic_operation | type_hint | bind_marker
literal              ::=  collection_literal | tuple_literal
function_call        ::=  identifier '(' [ term (',' term)* ] ')'
arithmetic_operation ::=  '-' term | term ('+' | '-' | '*' | '/' | '%') term

Data types

Amazon Keyspaces supports the following data types:

String types

Numeric types

Counters

A counter column contains a 64-bit signed integer. The counter value is incremented or decremented using the UPDATE statement, and it cannot be set directly. This makes counter columns useful for tracking counts. For example, you can use counters to track the number of entries in a log file or the number of times a post has been viewed on a social network. The following restrictions apply to counter columns:

In cases where a counter update fails (for example, because of timeouts or loss of connection with Amazon Keyspaces), the client doesn't know whether the counter value was updated. If the update is retried, the update to the counter value might get applied a second time.

Blob type

Boolean type

Time-related types

Collection types

You declare a collection column by using the collection type followed by another data type (for example, TEXT or INT) in angled brackets. You can create a column with a SET of TEXT, or you can create a MAP of TEXT and INT key-value pairs, as shown in the following example.

SET <TEXT>
MAP <TEXT, INT>

A non-frozen collection allows you to make updates to each individual collection element. Client-side timestamps and Time to Live (TTL) settings are stored for individual elements.

When you use the FROZEN keyword on a collection type, the values of the collection are serialized into a single immutable value, and Amazon Keyspaces treats them like a BLOB. This is a frozen collection. An INSERT or UPDATE statement overwrites the entire frozen collection. You can't make updates to individual elements inside a frozen collection.

Client-side timestamps and Time to Live (TTL) settings apply to the entire frozen collection, not to individual elements. Frozen collection columns can be part of the PRIMARY KEY of a table.

You can nest frozen collections. For example, you can define a MAP within a SET if the MAP is using the FROZEN keyword, as shown in the following example.

SET <FROZEN> <MAP <TEXT, INT>>>

Amazon Keyspaces supports nesting of up to 8 levels of frozen collections by default. For more information, see Amazon Keyspaces service quotas. For more information about functional differences with Apache Cassandra, see FROZEN collections. For more information about CQL syntax, see CREATE TABLE and ALTER TABLE.

Tuple type

The tuple data type represents a bounded group of literal elements. You can use a tuple as an alternative to a user defined type. You don't need to use the FROZEN keyword for tuples. This is because a tuple is always frozen and you can't update elements individually.

Other types

Static

In an Amazon Keyspaces table with clustering columns, you can use the STATIC keyword to create a static column of any type.

The following statement is an example of this.

my_column INT STATIC

For more information about working with static columns, see Estimate capacity consumption for static columns in Amazon Keyspaces.

User-defined types (UDTs)

Amazon Keyspaces supports user-defined types (UDTs). You can use any valid Amazon Keyspaces data type to create a UDT, including collections and other existing UDTs. You create UDTs in a keyspace and can use them to define columns in any table in the keyspace.

For more information about CQL syntax, see User-defined types (UDTs). For more information about working with UDTs, see User-defined types (UDTs) in Amazon Keyspaces.

To review how many UDTs are supported per keyspace, supported levels of nesting, and other default values and quotas related to UDTs, see Quotas and default values for user-defined types (UDTs) in Amazon Keyspaces.

JSON encoding of Amazon Keyspaces data types

Amazon Keyspaces offers the same JSON data type mappings as Apache Cassandra. The following table describes the data types Amazon Keyspaces accepts in INSERT JSON statements and the data types Amazon Keyspaces uses when returning data with the SELECT JSON statement.

For single-field data types such as float, int, UUID, and date, you also can insert data as a string. For compound data types and collections, such as tuple, map, and list, you can also insert data as JSON or as an encoded JSON string.