Cassandra User-defined types(UDTs)

Table of contents

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,
"name":"Akhil",
"address":{"employee_id":1234,"residence_address":"65A Block","office_address":"75D Block","city":"Goa"},
"salary":"12000"}';

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:

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters

Show hidden characters

	class EmployeeRepository {

	val cluster = Cluster.builder.addContactPoint("127.0.0.1").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);"
	session.execute(KEYSPACE_TABLE)
	session.execute(ADDRESS_TABLE)
	session.execute(EMPLOYEE_TABLE)

	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)
	}
	}