“Cassandra uses tables to store various types of data. A table contains schema definition, which holds the multiple columns in the table, their data type, and various supported constraints. It’s good to keep in mind that tables within a Cassandra database do not exist as standalone entities. Therefore, a table is stored within a given keyspace whose definition is inherited by all the tables.
This post will teach you how to create a table using CQL definitions and supported data types within a given keyspace.”
Cassandra Create Table Statement
We use the CREATE TABLE keyword to create a table within a given Cassandra keyspace. The statement syntax is as shown:
column_definition [, ...]
PRIMARY KEY (column_name [, column_name ...])
[WITH table_options
| CLUSTERING ORDER BY (clustering_column_name order])
| ID = 'table_hash_tag'
| COMPACT STORAGE]
The create table statement default creates a table in the selected keyspace. However, you can specify the target keyspace using the dot notation expressed in the syntax above.
We can also use the IF NOT EXISTS keywords to prevent Cassandra from returning an error if a table with a similar name exists in the target keyspace. If we do not use the IF NOT EXISTS keyword will return an error and exit the query. This can be a drawback, especially on batch-processed queries.
The column_definition section allows you to specify the columns within that table. We define columns inside the pair of parentheses as comma-separated values.
By default, Cassandra requires at least one column in the table to hold a PRIMARY KEY constraint.
The PRIMARY KEY constraint definition is as shown:
NOTE: Cassandra does not allow a static, counter, or non-frozen column to be set as a primary key column.
There are two types of primary keys in Cassandra:
- Single Primary Key
- Compound Primary Key
A single primary column consists of a single primary column. A single primary key also acts as a partition key, allowing the data to be divided and stored by their unique values.
Compound primary keys, on the other hand, comprise more than one column. The first column in this type of primary key is defined as the partition keys, and the rest are defined as clustering keys.
In the table_options of the table creation command, you specify various options that modify I/O operations, compression, etc. Feel free to explore the documentation to learn more.
Examples
The following shows practical examples of creating various types of tables and multiple parameters.
Example 1 – Create a Simple Table
The following example shows how to create a simple table with a single primary key.
id uuid
username text,
email text,
status bool,
primary key(id)
);
The query above shows how to use the create table statement to create a Cassandra table that uses a simple primary key. It is good to note that although this format defines the primary key at the end of the schema definition, you can add it next to a column as shown:
id uuid primary key
username text,
email text,
status bool
);
Example 2 – Create Table With Composite Partition Key
The query below illustrates creating a Cassandra table using a compound key.
id int,
username text,
email text,
status bool
primary key((id, username), status)
);
If a table contains a composite partition key, Cassandra will use the specified columns as the partition keys, which are used to define the logical order inside the partition for read operations.
Example 3 – Create Table With Compound Primary Key
A table with a compound primary key uses a primary key, either a simple primary key or a composite partition key and the other as a clustering key.
An example is as shown:
id uuid,
product_name text
price int,
category text,
manufacturer text,
primary key(category, price)
) wit clustering order by (price ASC);
In this case, the category and the price columns are used to uniquely identify a given record from the specified table.
Conclusion
In this post, we explored various methods and techniques for creating various Cassandra table types. We discussed creating a table with a primary key, a table using a composite partition key, and a table with a compound primary key.
Thanks for reading!!
