From a logical perspective, Apache Cassandra™ tables contain rows and columns of data.
As a NoSQL columnar database, Cassandra has you specify the table's columns and associated types. You also specify the keyspace that contains the table. Here's an example:
CREATE TABLE user_management.user_credentials (
email text,
password text,
PRIMARY KEY(email)
);
This table, named user_credentials, has two columns (email and password), which both have a text data type, which is similar to VARCHAR in relational databases.
Also notice the PRIMARY KEY clause, which tells us that the email column is the primary key. You must specify a primary key, because Cassandra only allows queries on primary key columns, which we will explain in more detail in the next step.
Now, when you describe the user_management keyspace, you see the user_credentials table.
Solution
DESCRIBE KEYSPACE user_management;
You can also describe a single table:
DESCRIBE TABLE user_management.user_credentials;
Note: We would never store passwords in plain-text in a production system, but we will for this example to keep things simple.
You may have noticed that we need to prefix the name of the table with the keyspace name. This can get tedious, so cqlsh lets you specify a default keyspace name like this:
USE user_management;
You see the cqlsh prompt changes to indicate the default keyspace. So now, when we work with the table, we only need to specify the table name.
After thinking about this table a bit, we realize that it would be nice to have another column for the user ID, so we could use this ID to refer to the user across other tables. We'll name this column user_id and it will be a Universally Unique Identifier (UUID) type.
Note: Cassandra uses UUID for ID columns because they are unique and efficient to generate in a distributed manner - as opposed to incrementing an integer.
Here's the command to add the column to the table:
ALTER TABLE user_credentials
ADD user_id UUID;
Now, describe the table again and see what it looks like.
Solution
DESCRIBE TABLE user_credentials;
We can see the column we added.
If we want to delete the table, we use the DROP command - just like we did for the keyspace.
DROP TABLE user_credentials;
Look at the keyspace to see if the DROP command worked
Solution
DESCRIBE KEYSPACE user_management;
So, now you know how to work with Cassandra tables. Let's use this knowledge to create a users table that has the following columns:
| Column Name |
Data Type |
| user_id |
UUID |
| first_name |
text |
| last_name |
text |
The user_id is the primary key.
Solution
CREATE TABLE users (
user_id UUID,
first_name text,
last_name text,
PRIMARY KEY(user_id)
);
We can add rows to the table with the following INSERT statements:
INSERT INTO users
(user_id, first_name, last_name)
VALUES(uuid(), 'Jeff', 'Carpenter');
INSERT INTO users
(user_id, first_name, last_name)
VALUES(uuid(), 'Patrick', 'McFadin');
INSERT INTO users
(user_id, first_name, last_name)
VALUES(uuid(), 'Rebecca', 'Mills');
We can also query the table with the following:
SELECT * FROM users;
NOTE: A SELECT statement without a WHERE clause performs a full table scan and should be avoided for production tables. We use it here for educational illustrative purposes only.
Finally, drop the users table.
Solution
DROP TABLE users;
Congratulations! You know how to work with tables!
