Cassandra User-defined types(UDTs)

Reading Time: 2 minutes

Cassandra supports a rich set of data types that we can use to create our tables. You can refer to all the supported data types from here. Other than that Cassandra also provides support to user-defined data types.

User-defined types (UDTs) can attach multiple data fields, each named and typed, to a single column. The fields used to create a UDT may be any valid data type, including collections and other existing UDTs. Once created, UDTs may be used to define a column in a table.

Let’s take an example to understand the need for UDT.

CREATE TABLE employee (
employee_id int PRIMARY KEY,
name text,
residence_address text,
office_address text,
city text,
salary text

We have a long list of fields in our table. If we can categories few fields together then the structure will look simple. UDT helps to combine field together like this:

CREATE TYPE address (
employee_id int,
residence_address text,
office_address text,
city text
CREATE TABLE employee (
employee_id int PRIMARY KEY,
name text,
address frozen<address>,
salary text

Now to insert the value into  the table you can query using the following command:

INSERT INTO employee JSON '{"employee_id":1234,
"address":{"employee_id":1234,"residence_address":"65A Block","office_address":"75D Block","city":"Goa"},

The output looks like this:


Note that the UDT field(i.e address) contains multiple pieces of information related to address.

Now let we show you how to create and use UDT in scala code.

Assume we have 2 case classes Employee and Address which looks like this:

case class Employee(employee_id: Int, name: String, address: Address, salary: String)
case class Address(employee_id: Int, residence_address: String, office_address: String, city: String)

Employee table contains a field named address which has type Address. So we can create the UDT for the same. so the code looks like this:

class EmployeeRepository {
val cluster = Cluster.builder.addContactPoint("").build()
val session = cluster.connect()
val KEYSPACE_TABLE = "CREATE KEYSPACE IF NOT EXISTS udt WITH REPLICATION = {'class' : 'SimpleStrategy', 'replication_factor' : 1 };"
val ADDRESS_TABLE = "CREATE TYPE IF NOT EXISTS udt.address (employee_id int, residence_address text, office_address text,city text);"
val EMPLOYEE_TABLE = "CREATE TABLE IF NOT EXISTS udt.employee (employee_id int PRIMARY KEY,name text,address frozen<address>,salary text);"
def insertEmployeeDetails(employee: Employee): ResultSet = {
val employeeJsonString = Json.toJson(employee).toString()
session.execute(s"INSERT INTO udt.employee JSON '$employeeJsonString';")
def getEmployeeDetails(employeeId: Int): ResultSet = {
session.execute("SELECT JSON * FROM udt.employee WHERE employee_id=" + employeeId)
def deleteEmployeeDetails(employeeId: Int): ResultSet = {
session.execute("DELETE * from udt.employee WHERE employee_id =" + employeeId)

We have ADDRESS_TABLE and EMPLOYEE_TABLE variables which contain the query to create address type UDT and employee table.

That’s all about it…!!

You can get the sample project for the above code from here.

Thanks for reading…..!!



Written by 

I am a Software Consultant at Knoldus Inc. I am a Scala Enthusiast. I am familiar with Object Oriented Programming Paradigms, and has also worked upon .NET based technologies. Aside from being a programmer, I am familiar with NoSQL database technologies such like Cassandra. I also worked on Lagom microservice architecture.