DbSchema | Cassandra - How to Create a Table?

This tutorial will guide you through the process of creating a table in Cassandra using the cqlsh shell and DbSchema.

Table of Contents:

1. Introduction
2. Prerequisites
3. Understanding Cassandra’s Data Definitions
4. Understanding Static Column in Cassandra
5. Understanding Cassandra’s Table Options
6. Creating a Table in Cassandra using cqlsh
7. Creating a Table in Cassandra using DbSchema
8. Conclusion
9. References

Introduction

Cassandra is a highly scalable, high-performance distributed database system designed to handle large amounts of data across many commodity servers. It provides high availability with no single point of failure. In this tutorial, we’ll dive into how to create a table in Cassandra using two different methods: the Cassandra Query Language Shell (cqlsh) and a visual database tool called DbSchema.

Prerequisites

• Apache Cassandra installed and running.
• Knowledge of the Cassandra Query Language (CQL).
• DbSchema installed and connected to your Cassandra database (for the DbSchema section).

For installation and establishing connection refer to Cassandra-How to create a Keyspace?

Understanding Cassandra’s Data Definitions

Below is a table explaining common data definitions in Cassandra:

Term	Description
__keyspace_name`	A namespace that defines data replication on nodes. Equivalent to a schema in a relational database.
__table_name`	A defined structure in which data is stored, similar to a table in a relational database.
__name`: unquoted_name, quoted_name	A unquoted name is an identifier in CQL that is not surrounded by double quotes and is case-insensitive. A quoted name, surrounded by double quotes, is case-sensitive.

Understanding Static Column in Cassandra

A __static column` in Cassandra is a special type of column that is shared among all rows of a partition. This column is useful when you want to attach some data to a partition without repeating the data for each row.

Understanding Cassandra’s Table Options

Table options in Cassandra can be defined when creating or altering a table. Here is a table explaining common table options:

Option	Description
__comment`	Optional description of the table.
__speculative_retry`	Determines the retry policy for the table.
__cdc`	Indicates whether the table should be included in change data capture (requires cdc=true in cassandra.yaml).
__additional_write_policy`	Policy that determines where to write additional copies of data.
__default_time_to_live`	The default TTL (time to live) for entries in the table.
__compaction`	The compaction strategy for the table.
__compression`	The compression parameters for the table.
__caching`	How Cassandra caches the table’s data.
__read_repair`	The read repair option for the table.

Creating a Table in Cassandra using cqlsh

In order to create a table in Cassandra using the cqlsh shell, follow these steps:

1. Launch the cqlsh shell from your terminal by running __cqlsh`.
2. Once you’re in the cqlsh shell, you need to create a keyspace (if not already created) that will house your table. To create a keyspace refer to Cassandra-How to create a Keyspace?
3. Now, switch to your keyspace using the __USE` command. Example:

USE mykeyspace;

4. It’s time to create your table using the __CREATE TABLE` command. Here is an example command:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name TEXT,
    email TEXT,
    age INT
);

In this example, ‘employees’ is the table name, and ‘id’, ‘name’, ‘email’, and ‘age’ are the column names.

To learn about basic datatypes refer to Understanding Data Types

Creating a Table in Cassandra using DbSchema

DbSchema is a diagram-oriented database tool that allows you to create, document, and manage databases visually. To create a table in DbSchema, follow these steps:

1. Open DbSchema.
2. Connect to your Cassandra database by clicking on ‘Connect’ and choosing your database.
3. Once connected, right-click on the canvas (white blank space) and select ‘Create Table’.
4. A dialog box will open where you can enter the table name and column details.
5. Fill out the details and click ‘Save’ to create the table.

Create Tables and Visually Manage Cassandra using DbSchema

DbSchema is a Cassandra client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Create Table

Start the application and connect to the Cassandra database. Right-click the table folder to create a table.

---

Add Table Columns

Add the columns to the table.

---

Conclusion

This tutorial covered creating tables in Cassandra using both cqlsh and DbSchema, as well as a detailed explanation of common data definitions and table options. Understanding these elements is crucial to effectively managing your Cassandra databases.

References

1. Apache Cassandra official documentation.
2. DbSchema official documentation.
3. Cassandra’s CQL reference.
4. DbSchema’s Cassandra diagramming guide.