Data Definition Command – Cassandra Query Language

1. Objective

In our last tutorial, we studied 10 Cassandra Shell Commands and in this Cassandra Tutorial, we will deal with the Data Definition Command in CQL. Moreover, we will discuss keyspace in Cassandra and its various commands like Create Keyspace, Use, Alter Keyspace, Drop Keyspace, Create Table, Alter Table, Truncate Table, Drop Table,  Create Index, and Drop Index. In addition, we will discuss each command with their syntax & example.

So, let’s start Data Definition command – CQL

Data Definition Command – Cassandra Query Language

2. CQL – Data Definition Command

Data definition command are used for defining the data in the database. They are another type of commands used in cqlsh prompt. In other words, they are used to create data and data storage units in the database. These commands basically are used to create and manipulate keyspace, table, and index.

3. Cassandra Keyspace

In Cassandra Data definition Command, Keyspace is basically a namespace that defines data replication on nodes. There are many clusters in a Cassandra database. Every Cassandra cluster contains one keyspace per node. This keyspace is created and altered using Data Definition Command.

a. Create Keyspace

This command is used to create a keyspace. Every system specifies a node. This system or node has a keyspace. This keyspace is a storage unit for the data. Data definition command help to create keyspace.
SYNTAX:

1. CREATE KEYSPACE <keyspace name> WITH <properties> AND <properties>

The statement has two main properties that are replication and durable writes. The replication property includes class and replication factor. For the class property, the user has to enter the strategy name. For the replication factor, the user has to enter the number of replicas that have to be made. The durable_writes property includes the boolean values. These properties are optional. A user may or may not use these properties.

FULL SYNTAX:

1. CREATE KEYSPACE 'keyspace name'
2. WITH replication{'class':'<strategy name>','replication factor':'<number of replicas>'}
3. AND durable_writes='<Boolean value>';

Example: Let us create a keyspace named ‘keyspace 1’ with simple strategy and 2 number of replicas. The durable writes for this keyspace is false.

1. cqlsh> CREATE KEYSPACE 'keyspace1'
2. ... WITH replication{'class':'simple strategy','replication factor':'2'}
3. ... AND durable_writes='false';

b. Use

This command in Data Definition Command, is used to use a keyspace stored in a database. Cassandra uses keyword ‘USE’ to perform the following function.
SYNTAX:

1. USE<keyspace name>

Example:
Let us use the keyspace ‘keyspace1’ that we made in the earlier example.

1. cqlsh>USE keyspace1;
2. cqlsh; keyspace1>

c. Alter Keyspace

This command is used to alter the properties of the keyspace. ALTER KEYSPACE command is used to do the operation. A user can alter replication and durable_writes properties. Data definition command help to alter keyspace. 

SYNTAX:

1. ALTER KEYSPACE <keyspace name> WITH <properties> AND <properties>

or

1. ALTER KEYSPACE <keyspace name>
2. WITH replication={'class':'strategy name','replication factor':'number of replicas'}
3. AND durable_writes=<'boolean type'>;\

Example: Let us alter the properties of keyspace1 from simple strategy to network topology strategy. The number of replicas, from 2 to 5 and boolean type, from false to true.

1. cqlsh> ALTER KEYSPACE keyspace1
2. ... WITH replication{'class':'NetworkTopologyStrategy','no. of replicas':'5'}
3. ... AND durable_writes= true;

d. Drop Keyspace

This command lets the user drop a keyspace. In other words, the user deletes the keyspace using this. To perform this operation DROP KEYSPACE keyword is used.
SYNTAX:

1. DROP KEYSPACE<keyspace name>;

Example: Let us delete the keyspace we developed in the previous example.

1. cqlsh> DROP KEYSPACE keyspace1;

e. Create Table

A table is one of the most important storage units in Cassandra Database. The user has the ability to create a table that can be deployed to the Cassandra database.

SYNTAX:

1. CREATE TABLE/COLUMN FAMILY<table name>
2. ('<column-definition>','<columan-definition>')
3. (WITH<option>AND<option>)

The statement allowed the user to set field name and their respective field type and also the primary. The primary key is basically a main column that is used for identification of a row.

FULL SYNTAX:

1. CREATE TABLE <table name>
2. <field name1> <field type1>
3. <field name2> <field type2>
4. <field name3> <field type3>
5. primary key(<field name>);

EXAMPLE: Let us create a table ‘student ‘ with fields, name, age, id, and branch. Primary key is id.

1. cqlsh;keyspace1> CREATE TABLE student
2. ... name text,
3. ... age int,
4. ... id int,
5. ... branch text,
6. ... primary key (id);

f. Alter Table

A User can change the properties of the table. ALTER TABLE command is used to perform the following operation. The user can perform two operations, add a column and drop a column.

SYNTAX:

1. ALTER TABLE/COLUMN FAMILY <table name><instruction>;

i. Add a column

To add a column the user can use the following syntax.

1. ALTER TABLE <table name>
2. ADD <new column> <data type>;

ii. Drop a column

to drop a column user can use the following syntax.

1. ALTER TABLE <table name>
2. DROP <column name>;

Example: Let us alter the table student. In the table, we will add a column of a city and drop the column age.

ALTER TABLE student

1. ADD city text;
2. ALTER TYPE student
3. DROP age;

g. Truncate Table

If a table is not required by the user, the user can truncate the whole table. The keyword used to truncate the table is TRUNCATE. When the user truncates the table, all the entry in the rows is deleted. The table stays but with zero rows.

SYNTAX:

1. TRUNCATE<table name>;

Example: Let’s truncate the table student.

1. TRUNCATE student;

h. Drop Table

If the user wants to completely delete the table from the database, the user can use this command. DROP TABLE keyword is used to perform the following operation.

SYNTAX:

1. DROP TABLE<table name>;

Example: In this example, we will delete the table student.

1. DROP TABLE student;

i. Create Index

The user can set an index on a field in a table. CREATE INDEX keyword is used perform the following operation.

SYNTAX:

1. CREATE INDEX <field name> ON <table name>

Example: In this example, we will create an index on ‘id’ at table ‘student’.

1. cqlsh;keyspcae1> CREATE INDEX id ON student;

j. Drop Index

A user can drop the set index on the field in a table. A user can use DROP INDEX keyword to perform the function. Data Definition Command plays main role in it. 

SYNTAX:

1. DROP INDEX <field name>;

Example: In this example, we will drop the index on ‘id’ at table ‘student’.

1. cqlsh;keyspcae1> DROP INDEX id;

So, this was all about CQL-Data Definition Command Tutorial. Hope you like our explanation.

4. Conclusion

Hence, in this CQL tutorial, we studied Data definition Command in Cassandra Query Langauge (CQL), Create keyspace, use, alter keyspace, drop keyspace, create table, alter table, truncate table, drop table, create index, and drop index with examples and syntax. These data definition commands help to create & alter Cassandra keyspace, table, and index. In next article, we will learn about Data Manipulation Commands. Furthermore, if you have any query regarding CQL Data definition Command, feel free to ask in the command box.