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. You have now been asked 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

The encoding data structure:

Field NameData Type


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

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. Do not run it again or you will get an error!

CREATE TYPE video_encoding (
     bit_rates SET,
     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.