The Cassandra Table is used to store the data in Cassandra and it is similar to the SQL but the internal implementation of the Cassandra table is different from the SQL. To create a table in Cassandra we must define a Primary key along with the data columns. We can add an additional WITH clause to configure the other properties of the Table such as caching, compaction, and so on.
Cassandra Table Characteristics
The Cassandra Table can start with the alphanumeric characters and a combination of underscores can also be used but the table name should start with a letter only. To create the table in the keyspace, we can use the keyspacename.tablename. In case the keyspace is already in use then just create the table name in the current keyspace.
Primary Key
The Primary Key is used to identify the location of the data in Cassandra. It is created during the table creation time and later it can't be altered. If there is a requirement to change the primary key from the table then we can create a new table with a new definition and primary key and then write the existing data. There are two types of Primary keys, the first one is a Single Key which is created on a one-column, and the second one is called the Composite Key which is created on multiple columns.
Create Table
To Create a Table in Cassandra, we can use the Create table command.
The following is the syntax of a create table.
Syntax:
CREATE TABLE [ IF NOT EXISTS ] tablename '('columndefinition ( ',' columndefinition )*
[ ',' PRIMARY KEY '(' primary_key ')' ]
')' [ WITH table_options ]
columndefinition::= columnname cql_type [ STATIC ] [ PRIMARY KEY]
primary_key::= partition_key [ ',' clusteringcolumns ]
partition_key::= columnname | '(' columnname ( ',' columnname )* ')'
clusteringcolumns::= columnname ( ',' columnname )*
TABLE_OPTIONS:TABLE_OPTIONS:=: compact storage [ AND table_options ]
| clustering ORDER BY '(' clustering_order ')'
[ AND table_options ] | options
clustering_order::= columnname (ASC | DESC) ( ',' columnname (ASC | DESC) )*
Now let us see an example to create an employee table in the cloudduggu keyspace with the following column families, a single primary index, and a comment string. If the cloudduggu keyspace is not created then create it by following the create keyspace tutorial section.
Command:
cqlsh> USE cloudduggu;CQLSH:cloudduggu> CREATE TABLE cloudduggu.employee(
empid INT PRIMARY KEY,
empname TEXT,
empcity TEXT,
empstate TEXT,
empsal VARINT,
empphone VARINT
) with comment='Employee Records';
Command:
Verify The Table:
We can use the describe table command to verify the structure of the table.
CQLSH:cloudduggu> describe cloudduggu.employee;