CREATE TYPE

A user-defined type (UDT) is a custom type that can store multiple values in one column, similar to a map collection. UDTs store multiple data fields, each with a name and type, on one column. Typically, the fields store related information like the parts of a mailing address. The fields in a UDT can be any valid data type, including collections and other UDTs.

UDTs cannot contain counter fields.

You must define a UDT as a custom type on a keyspace before using it in a table definition or query.

In table schemas and queries, UDTs behave like other data types:

UDT columns are created and dropped with CREATE TABLE and ALTER TABLE. To add a UDT column to a table, the UDT must be defined in the same keyspace as the table.
UDTs can be referenced in SELECT, INSERT, UPDATE, and DELETE statements. There is no keyspace scope restriction for these commands.
Collection types and other nested types can use UDTs, as shown in Write a UDT.

UDT warnings

Like collection types, UDTs are a useful way to reduce the number of columns and tables required for your data model, but you must use them judiciously to avoid performance issues and suboptimal data models:

Prefer regular columns with native data types whenever possible. Only use UDTs if absolutely necessary.
Collection usage guidelines also apply to UDTs, particularly with respect to FROZEN columns:
- Use frozen UDTs whenever possible. When frozen, a UDT is stored as a single value, and it must be overwritten in full; just like a frozen collection.
- For non-frozen UDTs, don’t specify too many fields. When non-frozen, the UDT is stored as a collection of fields, and the individual field values can be updated independently. However, this can lead to performance issues if there are too many fields or the UDT is deeply nested within other UDTs or collections.
You can add fields to a UDT, but you cannot remove them.
Serialization and deserialization is limited with UDTs. Instead, perform serialization and desensitization of UDT data inside the application, and then store the data as a BLOB.
Avoid deeply-nested UDTs and collections of UDTs.

You can nest UDTs inside other UDTs, and you can use UDTs as elements in collections, but these patterns aren’t recommended. Because UDTs contain their own sub-elements, it is much easier to overload collections with too many elements. This can degrade read/write performance and cause failed writes due to maximum mutation size limits.

Syntax

CREATE TYPE [ IF NOT EXISTS ] [<keyspace_name>].<type_name>
  (<field_name> <cql_datatype> [ , <field_name> <cql_datatype> ... ]) ;

Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`< >`	Variable value. Replace with a user-defined value.
`[]`	Optional. Square brackets (`[]`) surround optional command arguments. Do not type the square brackets.
`( )`	Group. Parentheses ( `( )` ) identify a group to choose from. Do not type the parentheses.
`\|`	Or. A vertical bar (`\|`) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
`...`	Repeatable. An ellipsis ( `...` ) indicates that you can repeat the syntax element as often as required.
`'<Literal string>'`	Single quotation (`'`) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
`{ <key> : <value> }`	Map collection. Braces (`{ }`) enclose map collections or key value pairs. A colon separates the key and the value.
`<<datatype1>,<datatype2>>`	Set, list, map, or tuple. Angle brackets ( `< >` ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
`<cql_statement>;`	End CQL statement. A semicolon (`;`) terminates all CQL statements.
`[--]`	Separate the command line options from the command arguments with two hyphens ( `--` ). This syntax is useful when arguments might be mistaken for command line options.
`' <<schema\> ... </schema\>> '`	Search CQL only: Single quotation marks (`'`) surround an entire XML schema declaration.
`@<xml_entity>='<xml_entity_type>'`	Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.

Options

Options Description Default

Options	Description	Default
`keyspace_name`	Optional. Name of the keyspace that contains the table to index.	If no name is specified, the current keyspace is used.
`type_name`	Unique name for the type. CQL types are reserved for a list. See type names.
`field_name`	Define fields that are in the UDT in a comma-separated list with `cql_datatype`.
`cql_datatype`	Valid CQL data type.

keyspace_name

Optional. Name of the keyspace that contains the table to index.

If no name is specified, the current keyspace is used.

type_name

Unique name for the type. CQL types are reserved for a list. See type names.

field_name

Define fields that are in the UDT in a comma-separated list with cql_datatype.

cql_datatype

Valid CQL data type.

Output of single quotes

A string with a single quote in a UDT text field is returned with two quotes in query results and COPY commands. For example, if you store the string Single ' quote in a UDT text field, the string is returned as Single '' quote.

Examples

Example with scalar types

Creates a UDT named basic_info on the cycling keyspace that stores personal data for a cyclist:

CREATE TYPE IF NOT EXISTS cycling.basic_info (
  birthday timestamp,
  nationality text,
  height text,
  weight text
);

Create a table with a column using the basic_info UDT. The frozen keyword is not required for UDTs that contain only non-collection fields, but it is recommended for the same reasons that frozen collections are recommended.

CREATE TABLE IF NOT EXISTS cycling.cyclist_stats (
  id UUID PRIMARY KEY,
  lastname text,
  basics basic_info
);

Example with nesting

A UDT can be nested in another column type. The following example uses a UDT to provide the values for a list column:

Create a UDT named race that stores the name, date, and time of each cycling race:

CREATE TYPE IF NOT EXISTS cycling.race (
  race_title text,
  race_date timestamp,
  race_time text
);

Create a table with a collection column, using the UDT in the collection definition.

The following table has a frozen list column (named races) that uses the race UDT as the list element type:
```
CREATE TABLE IF NOT EXISTS cycling.cyclist_races (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text,
  races list<FROZEN <race>>
);
```

Example with TTL

Create a UDT named basic_info_TTL_expire on the cycling keyspace that stores personal data for a cyclist.

For this example, the next_race column will be set with a timestamp and time-to-live (TTL).
```
CREATE TYPE IF NOT EXISTS cycling.basic_info_TTL_expire (
  birthday timestamp,
  nationality text,
  height text,
  weight text,
  next_race text
);
```

Create a table using the basic_info_TTL_expire UDT.

Note the use of the frozen keyword.

CREATE TABLE IF NOT EXISTS cycling.basic_info_TTL_expire (
  id uuid PRIMARY KEY,
  lastname text,
  basics <frozen<basic_info_TTL_expire>>
);

Use INSERT to write an entire row with USING TIMESTAMP and TTL:

INSERT INTO cycling.basic_info_TTL_expire (
  id, lastname, basics
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  'FRAME',
  {
    birthday : '1993-06-18',
    nationality : 'New Zealand',
    weight : '175',
    height : '72',
    next_race : 'Amgen Tour of California'
  }
)
USING TIMESTAMP 100 AND TTL 10000;

Use UPDATE to write only the basics column with USING TTL.

Because basics is frozen, all UDT fields must be provided.

UPDATE cycling.basic_info_TTL_expire USING TTL 100
  SET basics =   {
    birthday : '1993-06-18',
    nationality : 'New Zealand',
    weight : '175',
    height : '72',
    next_race : 'Tour de France'
  }
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;

Use SELECT with the WRITETIME() and TTL() functions to check the write time and TTL values:
```
SELECT WRITETIME(basics), TTL(basics) FROM cycling.basic_info_TTL_expire
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;
```
```
 writetime(basics) | ttl(basics)
-------------------+-------------
  1717445094201831 |          98

(1 rows)
```
This works because basics is frozen. You cannot use the selection function WRITETIME() on a non-frozen UDT column because the UDT is not frozen, and the write time is not stored with the UDT.

CREATE TYPE

UDT warnings

Syntax

Options

Output of single quotes

Examples

See also

Was this helpful?

Give Feedback