As with other languages, Cassandra Query Language (CQL) supports a flexible set of data types that include primitive data types, collections, and user-defined data types (UDTs). This topic describes the data types that CQL supports.
Numeric data types
The numeric data types supported by CQL include integers and floating-point numbers. These types are similar to standard data types in Java. CQL supports the following numeric data types:
int: a 32-bit signed integer, as in Java.
bigint: a 64-bit long integer, which is equivalent to long in Java.
smallint: a 16-bit signed integer, which is equivalent to short in Java. This data type was introduced in Apache Cassandra 2.2.
tinyint: an 8-bit signed integer, as in Java. This data type was introduced in Apache Cassandra 2.2.
varint: a variable-precision signed integer, which is equivalent to java.math.BigInteger.
float: a 32-bit IEEE-754 floating point, as in Java.
double: a 64-bit IEEE-754 floating point, as in Java.
decimal: a variable-precision decimal, which is equivalent to java.math.BigDecimal.
Textual data types
CQL provides the following data types for representing text:
text or varchar: a UTF-8 encoded character string, which is commonly used in CQL.
ascii: an ASCII character string.
Time and identity data types
timestamp: The time can be encoded into a 64-bit signed integer, but typically timestamps that support ISO 8601 standards are used to improve readability. We recommend that you always provide time zones for timestamps rather than relying on the time zone configuration of the operating system.
date and time: Apache Cassandra 2.1 and earlier only had the timestamp type to represent a date and time. The 2.2 release introduced date and time types that allowed dates and time to be independently represented. As with timestamp, these types support ISO 8601 formats.
uuid: A universally unique identifier (UUID) is a 128-bit value in which the bits conform to one of several types, of which the most commonly used are known as Type 1 and Type 4. The CQL uuid type is a Type 4 UUID, which is entirely based on random numbers. UUIDs are typically represented as dash-separated sequences of hexadecimal digits, such as ab7c46ac-c194-4c71-bb03-0f64986f3daa. The uuid type is often used as a surrogate key. This type can be used by itself or in combination with other values. Because UUIDs are of a finite length, they are not absolutely guaranteed to be unique. You can use the uuid() function in CQL to obtain a Type 4 UUID value.
timeuuid: This is a Type 1 UUID that is based on the MAC address of the computer, the system time, and a sequence number used to prevent duplicates. CQL provides several convenience functions for interacting with the timeuuid type, such as now(), dateOf(), and unixTimestampOf(). The availability of these convenience functions is one reason why timeuuid tends to be more frequently used than uuid.
Collection data types
Collection data types can store a collection of data. The elements stored in the set data type are unordered, but CQL returns the elements in sorted order. Sets can contain the data types mentioned earlier, user-defined types, and other collections.
The following example demonstrates how to use the set data type to store email information:
cqlsh:test_keyspace> CREATE TABLE test_user (first_name text , last_name text,emails set<text>, PRIMARY KEY (first_name)) ;
cqlsh:test_keyspace> INSERT INTO test_user (first_name, last_name,emails) VALUES ('Wu', 'Shi',{'iteblog@iteblog.com'});
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name = 'Wu';
first_name | emails | last_name
------------+-------------------------+-----------
Wu | {'iteblog@iteblog.com'} | Shi
(1 rows)
In the preceding statements, an email address is added for the user whose first_name is Wu. If you want to add another email address, use the following syntax:
cqlsh:test_keyspace> UPDATE test_user SET emails = emails + {'cassandra@iteblog.com' } WHERE first_name = 'Wu';
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name = 'Wu';
first_name | emails | last_name
------------+--------------------------------------------------+-----------
Wu | {'cassandra@iteblog.com', 'iteblog@iteblog.com'} | Shi
(1 rows)
Two email addresses are added for the user whose first_name is Wu. If you want to delete an email address, use the following syntax:
cqlsh:test_keyspace> UPDATE test_user SET emails = emails - {'cassandra@iteblog.com'} WHERE first_name = 'Wu';
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name = 'Wu';
first_name | emails | last_name
------------+-------------------------+-----------
Wu | {'iteblog@iteblog.com'} | Shi
(1 rows)
cqlsh:test_keyspace> UPDATE test_user SET emails ={} WHERE first_name = 'Wu';
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name = 'Wu';
first_name | emails | last_name
------------+--------+-----------
Wu | null | Shi
(1 rows)
In the preceding statements, SET emails = emails - {'cassandra@iteblog.com'} is used to remove an email address from the email list and SET emails ={} is used to clear all email information of the user.
list
The list data type contains an ordered list of elements. By default, the values are stored in order of insertion. The following example demonstrates how to add information such as phone numbers to the test_user table:
cqlsh:test_keyspace> ALTER TABLE test_user ADD phone list<text>;
cqlsh:test_keyspace> UPDATE test_user SET phone = ['1311234****' ] WHERE first_name = 'Wu';
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name = 'Wu';
first_name | emails | last_name | phone
------------+--------+-----------+-----------------
Wu | null | Shi | ['1311234****']
(1 rows)
In the preceding statements, a phone number is added for the user whose first_name is Wu. If you want to add another phone number, use the following syntax similar to that of set:
cqlsh:test_keyspace> UPDATE test_user SET phone = phone + ['1551111****' ] WHERE first_name = 'Wu';
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name = 'Wu';
first_name | emails | last_name | phone
------------+--------+-----------+--------------------------------
Wu | null | Shi | ['1311234****', '1551111****']
(1 rows)
In the output, the new phone number appears at the end of the list. You can use the following statements to insert a phone number to the front of the list:
cqlsh:test_keyspace> UPDATE test_user SET phone = ['1334444****' ] + phone WHERE first_name = 'Wu';
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name = 'Wu';
first_name | emails | last_name | phone
------------+--------+-----------+-----------------------------------------------
Wu | null | Shi | ['1334444****', '1311234****', '1551111****']
(1 rows)
You can modify an individual item in the list when you reference it by its index:
cqlsh:test_keyspace> UPDATE test_user SET phone[1] = '1888888****' WHERE first_name = 'Wu';
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name = 'Wu';
first_name | emails | last_name | phone
------------+--------+-----------+-----------------------------------------------
Wu | null | Shi | ['1334444****', '1888888****', '1551111****']
(1 rows)
The element with an index of 1 is modified. You can also delete a specific item by using its index:
cqlsh:test_keyspace> DELETE phone[2] from test_user WHERE first_name = 'Wu';
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name = 'Wu';
first_name | emails | last_name | phone
------------+--------+-----------+--------------------------------
Wu | null | Shi | ['1334444****', '1888888****']
(1 rows)
You can also use SET phone_numbers = phone_numbers - [ '1334444****' ] to delete an element.
map
The map data type contains a collection of key-value pairs. The keys and values can be of types except counter. Example:
cqlsh:test_keyspace> ALTER TABLE test_user ADD login_sessions map<timeuuid, int>;
cqlsh:test_keyspace> UPDATE test_user SET login_sessions = {now(): 13, now(): 18} WHERE first_name = 'Wu';
cqlsh:test_keyspace> SELECT first_name, login_sessions FROM test_user WHERE first_name = 'Wu';
first_name | login_sessions
------------+--------------------------------------------------------------------------------------
Wu | {1cc61ff0-5f8b-11e9-ac3a-5336cd8118f6: 13, 1cc61ff1-5f8b-11e9-ac3a-5336cd8118f6: 18}
(1 rows)
Other simple data types
boolean: The value is true or false. CQL is case-insensitive for the input value, but returns values of True or False.
blob: A binary large object (blob) is a colloquial computing term for an arbitrary array of bytes. The CQL blob type is useful for storing media or other binary file types. Cassandra does not validate or examine the bytes in a blob. In Cassandra, blobs are represented as hexadecimal digits. If you want to encode arbitrary textual data into a blob, you can use the textAsBlob() function.
inet: This type represents IPv4 or IPv6 addresses. cqlsh accepts all valid formats for defining IPv4 addresses, which includes dotted or non-dotted representations containing decimal, octal, or hexadecimal values. CQL returns IP addresses in the 192.168.XX.XX format.
counter: The counter data type is a 64-bit signed integer, whose value cannot be directly set, but only incremented or decremented. The counter type has some special restrictions. It cannot be used as part of a primary key. If a counter is used, all of the columns other than primary keys must be counters.
UDTs
If the default data types in Cassandra do not meet your requirements, you can use UDTs. For example, if you want to use a column to store the address information about a user, you need to obtain information such as the zip code and street name. Using a text column to store these values may not meet your requirements. In this case, you can define a UDT. Example:
cqlsh:test_keyspace> CREATE TYPE address (
... street text,
... city text,
... state text,
... zip_code int);
The preceding statement defines the address data type. A UDT is scoped by the keyspace in which it is defined. This indicates that the address data type can be used only in test_keyspace. If you use DESCRIBE KEYSPACE test_keyspace, the output shows that the address data type is part of test_keyspace. The following example demonstrates how to use the defined address type:
cqlsh:test_keyspace> ALTER TABLE test_user ADD addresses map<text, frozen<address>>;
cqlsh:test_keyspace> UPDATE test_user SET addresses = addresses + {'home': { street: 'shangdi 9', city: 'Beijing', state: 'Beijing', zip_code: 100080} } WHERE first_name = 'Wu';
cqlsh:test_keyspace> SELECT first_name, addresses FROM test_user WHERE first_name = 'Wu';
first_name | addresses
------------+--------------------------------------------------------------------------------------
Wu | {'home': {street: 'shangdi 9', city: 'Beijing', state: 'Beijing', zip_code: 100080}}
(1 rows)