How to create table in Cassandra - Cassandra / CQL Tutorial

How to create table in Cassandra by using CQL

Creating table in Cassandra is pretty much same like other SQL Databases. Below are some points to remember before you create table.

Table Name:

In Cassandra we can use string of alphanumeric characters and underscores but name should always start with letter.  If you will try to create table name that starts with number, you will get below error.

CQLSH:techbrotherstutorials>CREATE TABLE 123tablename 
             ( 
                          id int PRIMARY KEY, 
                          NAME text 

SyntaxException: line 1:13 no viable alternative at input '123' (CREATE TABLE [123]...)
 

Table Name Length: 

Cassandra allows only 48 characters long name for table name. if you will try more than 48 characters , you will get below error.

InvalidRequest: Error from server: code=2200 [Invalid query] message="Table names shouldn't be more than 48 characters long (got "LongTimeNameMoreThan48characters...............................")"

Primary Key is Must:

Cassandra does not allow to have table without primary key. Primary key identifies the location and order or stored data. If you need to change the primary key, you have to create a new table with correct primary key and copy the data from existing table to new table. You can not alter primary key in Cassandra.

Choose Keyspace ( Schema):

 You can switch to keyspace by using "USE KeyspaceName" in which Keyspace you would like to create the table or you can provide fully qualified name(KeyspaceName.TableName) while creating the table from other keyspace. 

Columns:

• Column name can not start with Number.
• Column names can be string of alphanumeric and underscores.
• Column name can be more than 48 characters which is not allowed in table name. I tried column name with 1024 characters and was able to create the table successfully.

 Syntax:

Below is the syntax to create table in Cassandra by using CQL

CQLSH:techbrotherstutorials>CREATE TABLE tablename 
             ( 
                          column1 DATATYPE PRIMARY KEY, 
                          column2 DATATYPE, 
                          column3 DATATYPE, 
                          ........., 
                          ......... );

 

There are multiple ways to write the create statement with Primary Key. We will further explore in next post. Choosing correct data type for your data is very important. I will suggest to visit chapter 2 to learn about different data types available in Cassandra. 

Example:

Let's create tbs table which can store name, address, phone number, salary or employees.

CQLSH:techbrotherstutorials>CREATE TABLE tbs 
             ( 
                          id UUID PRIMARY KEY, 
                          NAME TEXT, 
                          address TEXT, 
                          phonenumber TEXT, 
                          salary BIGINT 
             );