DSE Version: 6.0



In this unit you will be learning about UDTs, or User Defined Types. These are useful if you need to store a more complex data structure wihin a single cell in your table.


Alright, let’s say you need to store a more complex data structure within a single cell in your table, you might not be able to do that with a collection and you might move onto to using a user defined type, affectionately known here as a UDT.  UDTs allow you to group related fields of data together and are named and type. UDTs can be include any supported data types included collections and other UDTs Allows embedding of more complex data within a single column, adding flexibility to your table and data model.

Let’s take a look at some examples here.  First I create the types. Wow, this looks eerily similar to create a table.  Huh. Anyway. Here I am creating an address type which has street, city, zip and a set of phone numbers.  Now all of these fields I created make up a single address. Let’s imagine that several of our users have the same address.  Well in the relational world, you would have a users table and an address table and you’d have to join these two together. Well, cassandra’s denormalization technique would be to embed the address in a single cell of the users table.  There’s a second small UDT example here where we have “full name” which is made up a user’s first name and their last name.

Okay, so now what?  We created our types, how do we use those in our tables?  Well, next you see our create table definition here on this slide.  Notice we used our full_name UDT in our “name” column. Next line down we created a SET collection type with our full_name UDT to create a direct_reports column.  And finally, we used our address UDT in a MAP collection.

UDTs might take a little practice, so take some time now to go do the exercise!

No write up.

Exercise 3.1: User Defined Types (UDTs)

In this exercise, you will:

  • Create a User Defined Type
  • Alter an existing table and add additional columns


After reviewing your design of the tables that support tag and year queries, your manager happened to remember that there isn't a tags column in the original table that stores video metadata. Now, your manager is asking you to include that in the videos table schema and also to add another column to store video encoding information. The revised videos table schema:

Column NameData Type
tagsset < text >

The encoding data structure:

Field NameData Type
bit_ratesset < text >


1. Use a text editor to open and review the videos.csv file in the ~/labwork/udts directory. Notice the addition of the tags column.

2. Also, open and review the videos_encoding.csv file (also in the ~/labwork/udts directory).

3. At the prompt, navigate to /home/ubuntu/labwork/udts. Launch cqlsh and switch to the killrvideo keyspace.

4. Run the TRUNCATE command to erase the data from the videos table.

5. Alter the videos table to add a tags column.

6. Load the data from the videos.csv file using the COPY command.

COPY videos FROM 'videos.csv' WITH HEADER=true;

Remember, we do not need to create the User Defined Type called video_encoding because we did so in the previous exercise. However, take a look at the code below as a refresher.

CREATE TYPE IF NOT EXISTS killrvideo.video_encoding (
     bit_rates SET<text>,
     encoding TEXT,
     height INT,
     width INT

7. Alter your table to add an encoding column of the video_encoding type.

8. Load the data from the videos_encoding.csv file using the COPY command.

COPY videos (video_id, encoding) FROM 'videos_encoding.csv' WITH HEADER=true;

9. Run a query to retrieve the first 10 rows of the videos table.

Notice the altered table contains data for the new tags and encoding column.

10. Exit cqlsh.

No FAQs.
No resources.
Comments are closed.