# User-defined types (UDTs) in Amazon Keyspaces
A user-defined type (UDT) is a grouping of fields and data types that you can use to define a single column in Amazon Keyspaces. Valid data types for UDTs are all supported Cassandra data types, including collections and other UDTs that you've already created in the same keyspace. For more information about supported Cassandra data types, see [Cassandra data type support](cassandra-apis.md#cassandra-data-type).
You can use user-defined types (UDTs) in Amazon Keyspaces to organize data in a more efficient way. For example, you can create UDTs with nested collections which allows you to implement more complex data modeling in your applications. You can also use the frozen keyword for defining UDTs.
UDTs are bound to a keyspace and available to all tables and UDTs in the same keyspace. You can create UDTs in single-Region and multi-Region keyspaces.
You can create new tables or alter existing tables and add new columns that use a UDT. To create a UDT with a nested UDT, the nested UDT has to be frozen.
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](quotas.md#quotas-udts).
For information about how to calculate the encoded size of UDTs, see [Estimate the encoded size of data values based on data type](calculating-row-size.md#calculating-row-size-data-types).
For more information about CQL syntax, see [User-defined types (UDTs)](cql.ddl.type.md).
To learn more about UDTs and point-in time restore, see [PITR restore of tables with user-defined types (UDTs)](PointInTimeRecovery_HowItWorks.md#howitworks_backup_udt).
**Topics**
+ [Configure permissions](configure-udt-permissions.md)
+ [Create a UDT](keyspaces-create-udt.md)
+ [View UDTs](keyspaces-view-udt.md)
+ [Delete a UDT](keyspaces-delete-udt.md)
# Configure permissions to work with user-defined types (UDTs) in Amazon Keyspaces
Like tables, UDTs are bound to a specific keyspace. But unlike tables, you can't define permissions directly for UDTs. UDTs are not considered resources in AWS and they have no unique identifiers in the format of an Amazon Resource Name (ARN). Instead, to give an IAM principal permissions to perform specific actions on a UDT, you have to define permissions for the keyspace that the UDT is bound to. To work with UDTs in multi-Region keyspaces, additional permissions are required.
To be able to create, view, or delete UDTs, the principal, for example an IAM user or role, needs the same permissions that are required to perform the same action on the keyspace that the UDT is bound to.
For more information about AWS Identity and Access Management, see [AWS Identity and Access Management for Amazon Keyspaces](security-iam.md).
## Permissions to create a UDT
To create a UDT in a single-Region keyspace, the principal needs `Create` permissions for the keyspace.
The following IAM policy is an example of this.
```
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "cassandra:Create",
"Resource": [
"arn:aws:cassandra:us-east-1:111122223333:/keyspace/my_keyspace/"
]
}
]
}
```
To create a UDT in a multi-Region keyspace, in addition to `Create` permissions the principal also needs permissions for the action `CreateMultiRegionResource` for the specified keyspace.
The following IAM policy is an example of this.
```
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [ "cassandra:Create", "cassandra:CreateMultiRegionResource" ],
"Resource": [
"arn:aws:cassandra:us-east-1:111122223333:/keyspace/my_keyspace/"
]
}
]
}
```
## Permissions to view a UDT
To view or list UDTs in a single-Region keyspace, the principal needs read permissions for the system keyspace. For more information, see [`system_schema_mcs`](working-with-keyspaces.md#keyspace_system_schema_mcs).
The following IAM policy is an example of this.
```
{
"Version":"2012-10-17",
"Statement":[
{
"Effect":"Allow",
"Action":"cassandra:Select",
"Resource":[
"arn:aws:cassandra:us-east-1:111122223333:/keyspace/system*"
]
}
]
}
```
To view or list UDTs for a multi-Region keyspace, the principal needs permissions for the actions `SELECT` and `SelectMultiRegionResource` for the system keyspace. For more information, see [`system_multiregion_info`](working-with-keyspaces.md#keyspace_system_multiregion_info).
The following IAM policy is an example of this.
```
{
"Version":"2012-10-17",
"Statement":[
{
"Effect":"Allow",
"Action": ["cassandra:Select", "cassandra:SelectMultiRegionResource"],
"Resource":[
"arn:aws:cassandra:us-east-1:111122223333:/keyspace/system*"
]
}
]
}
```
## Permissions to delete a UDT
To delete a UDT from a single-Region keyspace, the principal needs permissions for the `Drop` action for the specified keyspace.
The following IAM policy is an example of this.
```
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "cassandra:Drop",
"Resource": [
"arn:aws:cassandra:us-east-1:111122223333:/keyspace/my_keyspace/"
]
}
]
}
```
To delete a UDT from a multi-Region keyspace, the principal needs permissions for the `Drop` action and for the `DropMultiRegionResource` action for the specified keyspace.
The following IAM policy is an example of this.
```
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [ "cassandra:Drop", "cassandra:DropMultiRegionResource" ],
"Resource": [
"arn:aws:cassandra:us-east-1:111122223333:/keyspace/my_keyspace/"
]
}
]
}
```
# Create a user-defined type (UDT) in Amazon Keyspaces
To create a UDT in a single-Region keyspace, you can use the `CREATE TYPE` statement in CQL, the `create-type` command with the AWS CLI, or the console.
UDT names must contain 48 characters or less, must begin with an alphabetic character, and can only contain alpha-numeric characters and underscores. Amazon Keyspaces converts upper case characters automatically into lower case characters.
Alternatively, you can declare a UDT name in double quotes. When declaring a UDT name inside double quotes, Amazon Keyspaces preserves upper casing and allows special characters.
You can also use double quotes as part of the name when you create the UDT, but you must escape each double quote character with an additional double quote character.
The following table shows examples of allowed UDT names. The first columns shows how to enter the name when you create the type, the second column shows how Amazon Keyspaces formats the name internally. Amazon Keyspaces expects the formatted name for operations like `GetType`.
| Entered name | Formatted name | Note |
| --- | --- | --- |
| MY\$1UDT | my\$1udt | Without double-quotes, Amazon Keyspaces converts all upper-case characters to lower-case. |
| "MY\$1UDT" | MY\$1UDT | With double-quotes, Amazon Keyspaces respects the upper-case characters, and removes the double-quotes from the formatted name. |
| "1234" | 1234 | With double-quotes, the name can begin with a number, and Amazon Keyspaces removes the double-quotes from the formatted name. |
| "Special\$1Ch@r@cters<>\$1\$1" | Special\$1Ch@r@cters<>\$1\$1 | With double-quotes, the name can contain special characters, and Amazon Keyspaces removes the double-quotes from the formatted name. |
| "nested""""""quotes" | nested"""quotes | Amazon Keyspaces removes the outer double-quotes and the escape double-quotes from the formatted name. |
------
#### [ Console ]
**Create a user-defined type (UDT) with the Amazon Keyspaces console**
1. Sign in to the AWS Management Console, and open the Amazon Keyspaces console at [https://console.aws.amazon.com/keyspaces/home](https://console.aws.amazon.com/keyspaces/home).
1. In the navigation pane, choose **Keyspaces**, and then choose a keyspace from the list.
1. Choose the **UDTs** tab.
1. Choose **Create UDT**
1. Under **UDT details**, enter the name for the UDT. Under **UDT fields** you define the schema of the UDT.
1. To finish, choose **Create UDT**.
------
#### [ Cassandra Query Language (CQL) ]
**Create a user-defined type (UDT) with CQL**
In this example we create a new version of the book awards table used in [Create a table in Amazon Keyspaces](getting-started.tables.md). In this table, we store all awards an author receives for a given book. We create two UDTs that are nested and contain information about the book that received an award.
1. Create a keyspace with the name `catalog`.
```
CREATE KEYSPACE catalog WITH REPLICATION = {'class': 'SingleRegionStrategy'};
```
1. Create the first type. This type stores *BISAC* codes, which are used to define the genre of books. A BISAC code consists out of an alpha-numeric code and up to four subject matter areas.
```
CREATE TYPE catalog.bisac (
bisac_code text,
subject1 text,
subject2 text,
subject3 text,
subject4 text
);
```
1. Create a second type for book awards that uses the first UDT. The nested UDT has to be frozen.
```
CREATE TYPE catalog.book (
award_title text,
book_title text,
publication_date date,
page_count int,
ISBN text,
genre FROZEN
);
```
1. Create a table with a column for the author's name and uses a list type for the book awards. Note that the UDT used in the list has to be frozen.
```
CREATE TABLE catalog.authors (
author_name text PRIMARY KEY,
awards list >
);
```
1. In this step we insert one row of data into the new table.
```
CONSISTENCY LOCAL_QUORUM;
```
```
INSERT INTO catalog.authors (author_name, awards) VALUES (
'John Stiles' ,
[{
award_title: 'Wolf',
book_title: 'Yesterday',
publication_date: '2020-10-10',
page_count: 345,
ISBN: '026204630X',
genre: { bisac_code:'FIC014090', subject1: 'FICTION', subject2: 'Historical', subject3: '20th Century', subject4: 'Post-World War II'}
},
{award_title: 'Richard Roe',
book_title: 'Who ate the cake?',
publication_date: '2019-05-13',
page_count: 193,
ISBN: '9780262046305',
genre: { bisac_code:'FIC022130', subject1: 'FICTION', subject2: 'Mystery & Detective', subject3: 'Cozy', subject4: 'Culinary'}
}]
);
```
1. In the last step we read the data from the table.
```
SELECT * FROM catalog.authors;
```
The output of the command should look like this.
```
author_name | awards
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
John Stiles | [{award_title: 'Wolf', book_title: 'Yesterday', publication_date: 2020-10-10, page_count: 345, isbn: '026204630X', genre: {bisac_code: 'FIC014090', subject1: 'FICTION', subject2: 'Historical', subject3: '20th Century', subject4: 'Post-World War II'}}, {award_title: 'Richard Roe', book_title: 'Who ate the cake?', publication_date: 2019-05-13, page_count: 193, isbn: '9780262046305', genre: {bisac_code: 'FIC022130', subject1: 'FICTION', subject2: 'Mystery & Detective', subject3: 'Cozy', subject4: 'Culinary'}}]
(1 rows)
```
For more information about CQL syntax, see [CREATE TYPE](cql.ddl.type.md#cql.ddl.type.create).
------
#### [ CLI ]
**Create a user-defined type (UDT) with the AWS CLI**
1. To create a type you can use the following syntax.
```
aws keyspaces create-type
--keyspace-name 'my_keyspace'
--type-name 'my_udt'
--field-definitions
'[
{"name" : "field1", "type" : "int"},
{"name" : "field2", "type" : "text"}
]'
```
1. The output of that command looks similar to this example. Note that `typeName` returns the formatted name of the UDT.
```
{
"keyspaceArn": "arn:aws:cassandra:us-east-1:111122223333:/keyspace/my_keyspace/",
"typeName": "my_udt"
}
```
------
# View user-defined types (UDTs) in Amazon Keyspaces
To view or list all UDTs in a single-Region keyspace, you can query the table `system_schema_mcs.types` in the system keyspace using a statement in CQL, or use the `get-type` and `list-type` commands with the AWS CLI, or the console.
For either option, the IAM principal needs read permissions to the system keyspace. For more information, see [Configure permissions to work with user-defined types (UDTs) in Amazon Keyspaces](configure-udt-permissions.md).
------
#### [ Console ]
**View user-defined types (UDT) with the Amazon Keyspaces console**
1. Sign in to the AWS Management Console, and open the Amazon Keyspaces console at [https://console.aws.amazon.com/keyspaces/home](https://console.aws.amazon.com/keyspaces/home).
1. In the navigation pane, choose **Keyspaces**, and then choose a keyspace from the list.
1. Choose the **UDTs** tab to review the list of all UDTs in the keyspace.
1. To review one UDT in detail, choose a **UDT** from the list.
1. On the **Schema**tab you can review the schema. On the **Used in** tab you can see if this UDT is used in tables or other UDTs. Note that you can only delete UDTs that are not in use by either tables or other UDTs.
------
#### [ Cassandra Query Language (CQL) ]
**View the user-defined types (UDTs) of a single-Region keyspace with CQL**
1. To see the types that are available in a given keyspace, you can use the following statement.
```
SELECT type_name
FROM system_schema_mcs.types
WHERE keyspace_name = 'my_keyspace';
```
1. To view the details about a specific type, you can use the following statement.
```
SELECT
keyspace_name,
type_name,
field_names,
field_types,
max_nesting_depth,
last_modified_timestamp,
status,
direct_referring_tables,
direct_parent_types
FROM system_schema_mcs.types
WHERE keyspace_name = 'my_keyspace' AND type_name = 'my_udt';
```
1. You can list all UDTs that exist in the account using `DESC TYPE`.
```
DESC TYPES;
Keyspace my_keyspace
---------------------------
my_udt1 my_udt2
Keyspace my_keyspace2
---------------------------
my_udt1
```
1. You can list all UDTs in the current selected keyspace using `DESC TYPE`.
```
USE my_keyspace;
my_keyspace DESC TYPES;
my_udt1 my_udt2
```
1. To list all UDTs in a multi-Region keyspace, you can query the system table `types` in the `system_multiregion_info` keyspace. The following query is an example of this.
```
SELECT keyspace_name, type_name, region, status FROM system_multiregion_info.types WHERE keyspace_name = 'mykeyspace' AND table_name = 'mytable';
```
The output of this command looks similar to this.
```
keyspace_name | table_name | region | status
mykeyspace | mytable | us-east-1 | ACTIVE
mykeyspace | mytable | ap-southeast-1 | ACTIVE
mykeyspace | mytable | eu-west-1 | ACTIVE
```
------
#### [ CLI ]
**View user-defined types (UDTs) with the AWS CLI**
1. To list the types available in a keyspace, you can use the `list-types` command.
```
aws keyspaces list-types
--keyspace-name 'my_keyspace'
```
The output of that command looks similar to this example.
```
{
"types": [
"my_udt",
"parent_udt"
]
}
```
1. To view the details about a given type you can use the `get-type` command.
```
aws keyspaces get-type
--type-name 'my_udt'
--keyspace-name 'my_keyspace'
```
The output of this command looks similar to this example.
```
{
"keyspaceName": "my_keyspace",
"typeName": "my_udt",
"fieldDefinitions": [
{
"name": "a",
"type": "int"
},
{
"name": "b",
"type": "text"
}
],
"lastModifiedTimestamp": 1721328225776,
"maxNestingDepth": 3
"status": "ACTIVE",
"directReferringTables": [],
"directParentTypes": [
"parent_udt"
],
"keyspaceArn": "arn:aws:cassandra:us-east-1:111122223333:/keyspace/my_keyspace/"
}
```
------
# Delete a user-defined type (UDT) in Amazon Keyspaces
To delete a UDT in a keyspace, you can use the `DROP TYPE` statement in CQL, the `delete-type` command with the AWS CLI, or the console.
------
#### [ Console ]
**Delete a user-defined type (UDT) with the Amazon Keyspaces console**
1. Sign in to the AWS Management Console, and open the Amazon Keyspaces console at [https://console.aws.amazon.com/keyspaces/home](https://console.aws.amazon.com/keyspaces/home).
1. In the navigation pane, choose **Keyspaces**, and then choose a keyspace from the list.
1. Choose the **UDTs** tab.
1. Choose the UDT that you want to delete. On the **Used in** you can confirm that the type you want to delete isn't currently used by a table or other UDT.
1. Choose **Delete** above the **Summary**.
1. Type `Delete` in the dialog that appears, and choose **Delete UDT**.
------
#### [ Cassandra Query Language (CQL) ]
**Delete a user-defined type (UDT) with CQL**
+ To delete a type, you can use the following statement.
```
DROP TYPE my_keyspace.my_udt;
```
For more information about CQL syntax, see [DROP TYPE](cql.ddl.type.md#cql.ddl.type.drop).
------
#### [ CLI ]
**Delete a user-defined type (UDT) with the AWS CLI**
1. To delete a type, you can use the following command.
```
aws keyspaces delete-type
--keyspace-name 'my_keyspace'
--type-name 'my_udt'
```
1. The output of the command looks similar to this example.
```
{
"keyspaceArn": "arn:aws:cassandra:us-east-1:111122223333:/keyspace/my_keyspace/",
"typeName": "my_udt"
}
```
------