Difficulty: Beginner
Estimated Time: 10-15 minutes

So, you want to create a Cassandra schema? Cassandra's schema development methodology is different from the relational world's approach.

In this scenario, we'll learn how to create a Cassandra schema that deals with:

  • How to create tables
  • How to handle table joins
  • How to handle queries on non-primary key columns

If you are coming from a relational world, you create a schema by thinking about your data, creating a normalized model and then figuring out how to use the model in your app. Cassandra reverses this process by having you focus on queries within the app and using those queries to drive table design. We'll show you how!

ver 003

Help us make this better! Please give this scenario a ranking (click on the stars above).

tshirt

In this scenario, we learned how to create a Cassandra schema that deals with:

  • How to create tables
  • How to handle table joins
  • How to handle queries on non-primary key columns

You have the skills to create rudimentary Cassandra data models!

Try It Out: Cassandra Data Modeling

Step 1 of 3

How to Create Tables

Let's review how to create a table by looking at a user management example. Our user management system keeps track of users' profiles and supports authentication. For authentication, when a user logs-in, we want to find their password based on their email address. Once they login, we also want a unique identifier for the user. So, for each user, we need this data.

Column Name Column Type
email text
password text
user_id UUID

Note: We would never store passwords in plain-text in a production system, but we will for this example to keep things simple.


We'll store this table in a keyspace named user_management. Here are the commands to create the keyspace, set the default keyspace and create the table.

// Create the user_management keyspace CREATE KEYSPACE user_management WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 }; // Set user_management as the default keyspace USE user_management; // Create the user_credentials_by_email table CREATE TABLE credentials_by_email ( email text, password text, user_id UUID, PRIMARY KEY(email) );


ProTip: You can see we named the table credentials_by_email. It's a good practice in Cassandra to name tables based on what they contain as well as their partition key.


The user_credentials_by_email table supports the user login use-case, but we need another table that keeps track of the user profile data. Here's the data we need for this table.

Column Name Column Type
user_id UUID
last_name text
first_name text

When we create tables in Cassandra, we want to think about how we will use the table so that the table supports specific queries. When we use this table, we want to retrieve the user profile data based on the user_id, so we make the user_id column the partition key. Since this table contains profile data and its partition key is user_id, we'll name this table profiles_by_id. Write and execute the CQL to create this table:

Solution CREATE TABLE profiles_by_id ( user_id UUID, first_name text, last_name text, PRIMARY KEY(user_id) );

ProTip: The take away from this step is, in Cassandra, we create each table to handle a specific query. So, when you want to create a Cassandra schema, start by thinking about the use-cases of your app. Then use the use-cases to help you identify the queries your app needs. Finally, use the queries to help you define the tables.



Great! Now, you not only know how to create tables, you also know why to create tables in Cassandra!