CQL data types
Describes CQL column types.
Data type is declared and enforced for each column in a table.
String types
INSERT or UPDATE string values in single quotes;
additional escaping is required for field values that contain single quotes, see Escaping characters. For example, lastname = 'Smith'
.
- ascii
- US-ASCII characters.`
- text
- UTF-8 encoded string.
- varchar
- UTF-8 encoded string.
Numeric types
INSERT or UPDATE numeric values without quotes. For
example, age = 31
.
Integers
- tinyint
- 8-bit signed integer.
- smallint
- 16-bit signed integer.
- int
- 32-bit signed integer.
- bigint
- 64-bit signed integer.
- varint
- Arbitrary-precision integer.
Decimals
The default decimal separator is a period (.); change the decimal separator in the driver settings or using cqlshrc DECIMALSEP option. Internally the decimal separator is stored as a period.
- decimal
- Variable-precision decimal, supports integers and floats. Note: When dealing with currency, it is a best practice to have a currency class that serializes to and from an int or use the decimal form.
- float
- 32-bit IEEE-754 floating point.
- double
- 64-bit IEEE-754 floating point.
Date and time types
INSERT or UPDATE date/time values using single quotes
around string format or no quotes for integers. For example, setting a date in string format
purchase_date = '2017-05-12'
vs specifying it as an integer in days since
epoch purchase_date = 17298
.
- date
- 32-bit unsigned integer representing the number of days since epoch (January 1, 1970) with
no corresponding time value.
INSERT or UPDATE values as an integer (days since epoch) or in string format
'yyyy-mm-dd'
, for example'2017-05-13'
.Note: When loading data from CSV use datetimeformat option in a cqlshrc file to change the cqlsh COPY TO date parsing format. - DateRangeType
- Stores a date range. Truncated timestamps represent the entire date span or use the range
syntax to create a custom range.
Specify a custom range between single quotes and, where necessary, square brackets. For example:'[beginning_date TO end_date]'
'2018-01'
– Beginning of the first day to the end of the last day in January 2018.'2018-01T15'
– All minutes in a specific hour. For example, 15 is 3:00 PM to 3:59 PM.'[2017-01-15 TO 2017-11-01]'
– The start of the fifteenth of January through the end of the first day of November.'[2017 TO 2017-11-01]'
– Start of 2017 till the end of the first day of November.'[* TO 2018-01-31]'
– From the earliest representable time thru till the end of the day on 2018-01-31.
The data type name is case sensitive. Use single quotes to specifyThis example shows an'DateRangeType'
in a CQLCREATE TABLE
statement. For example:CREATE TABLE test.example ( id int PRIMARY KEY, daterange 'DateRangeType' );
INSERT
statement with a date range value:INSERT INTO test.example ( id, daterange ) VALUES ( 1, '[2017-02-02T14:57:00 TO 2017-02-02T15:10:17]' );
- duration
- Time duration encoded as these signed integers of variable lengths:
- Months
- Days
- Nanoseconds
- time
- Encoded 64-bit signed integers representing the number of nanoseconds since midnight with
no corresponding date value.
INSERT or UPDATE string format is
'hh:mm:ss[.fff]'
, where milliseconds (f) are optional. - timestamp
- 64-bit signed integer representing the date and time since epoch (January 1 1970 at
00:00:00 GMT) in milliseconds.
INSERT or UPDATE string format is ISO-8601; the string must contain the date and optionally can include the time and time zone,
'yyyy-mm-dd [hh:MM:ss[.fff]][+/-NNNN]'
where NNNN is the RFC 822 4-digit time zone specification (+0000 refers to GMT and US PST is -0800). If no time zone is specified, the client timezone is assumed. For example'2015-05-03 13:30:54.234-0800'
,'2015-05-03 13:30:54+0400'
, or'2015-05-03'
.
Unique identifiers
Specialized types
- blob
- Arbitrary bytes (no validation), expressed as hexadecimal. See Blob conversion functions.
- boolean
- True or false. Stored internally as
true
or false; when using the COPY TO in cqlsh to import or export data, change the format using the BOOLSTYLE option, for example when importing survey results that have yes/no style answer column. - counter
- 64-bit signed integer. Only one counter column is allowed per table. All other columns in a
counter table must be PRIMARY KEYs. Increment and decrement the counter with an UPDATE statement using the
+
and-
operators. Null values are not supported in the counter column, the initial count equals 0. - inet
- IP address string in IPv4 or IPv6 format.
Collection types
CQL supports storing multiple values in a single column. Use collections to store or denormalize small amounts of data, such as phone numbers, tags, or addresses. Collections are not appropriate for data that is expected to grow unbounded, such as all events for a particular user; instead use a table with clustering columns.
- Because collections are not indexed or paged internally, the entire collection is read in order to access a single element.
- Some operations on lists incur a read-before-write. Also list operations are not idempotent by nature and can cause timeout issues in the case of retries. INSERT on sets and maps never incur a read-before-write internally, therefore DataStax recommends sets over lists whenever possible.
- frozen
- Use frozen on a set, map, or list to serialize multiple components into
a single value,
frozen<collection_definition>
. Non-frozen types allow updates to individual fields, but values in a frozen collection are treated like blobs, any upsert overwrites the entire value. - list
- Comma separated list of non-unique values of the same data type,
list<data_type>
. Elements are ordered by their position in the list; the first position is zero. Supports appending and prepending elements in INSERT and UPDATE statements using the + and - operators.Warning: Lists have limitations and performance impact, whenever possible use set or a frozen list, for examplefrozen<list<int>>
. The append and prepend operations are not idempotent. If either of these operations timeout, the retry operation may (or may not) result in appending/prepending the value twice. - map
- Set of key-value pairs, where keys are unique and the map is sorted by its keys,
map<data_type[, data_type,...]>
. - set
- Comma separated list of unique values sorted by position starting at zero.
- tuple
- Fixed length set of elements of different types. Unlike other collection types, a tuple is
always frozen (without the need of the frozen keyword). The entire field is overwritten when
using INSERT and UPDATE, therefore the expressions
must provide a value for each element; explicitly declare null for elements that have no
value. Tuples can contain tuples, for example
tuple<int,tuple<text,text>,boolean>
and also be specified as a data type of another collection type, for exampleset<tuple<text,inet>
. - user defined type (UDT)
- Customize collection type that belongs to a specific keyspace. The UDT is only available in
the keyspace where it is created. The
system_schema.types
contains a list of all UDT, thekeyspace_name
,type_name
,field_names
, andfield_types
.
Deprecated types
The following types are supported for backward compatibility only.
- custom type
- Deprecated supported for backward compatibility. Customized type added as a
sub-class to
AbstractType
, where the class name is fully qualified or relative to theorg.apache.cassandra.db.marshal
package.Note: Replaced by user defined type (UDT).