The primary key is a column that is used to uniquely identify a row. Therefore,defining a primary key is mandatory while creating a table. A primary key is made of one or more columns of a table. You can define a primary key of a table as shown below.

CREATE TABLE tablename(

column1 name datatype PRIMARYKEY,

column2 name data type,

column3 name data type.

)

CREATE TABLE tablename(

column1 name datatype PRIMARYKEY,

column2 name data type,

column3 name data type,

PRIMARY KEY (column1)

)

Example

Given below is an example to create a table in Cassandra using cqlsh. Here we are −

Using the keyspace tecklearn
Creating a table named emp

It will have details such as employee name, id, city, salary, and phone number. Employee id is the primary key.

cqlsh> USE tecklearn;

cqlsh:tecklearn>; CREATE TABLE emp(

emp_id int PRIMARY KEY,

emp_name text,

emp_city text,

emp_sal varint,

emp_phone varint

);

Verification

The select statement will give you the schema. Verify the table using the select statement as shown below.

cqlsh:tecklearn> select * from emp;

emp_id | emp_city | emp_name | emp_phone | emp_sal

——–+———-+———-+———–+———

(0 rows)

Here you can observe the table created with the given columns. Since we have deleted the keyspace tecklearn, you will not find it in the keyspaces list.

Creating a Table using Java API

You can create a table using the execute() method of Session class. Follow the steps given below to create a table using Java API.

Step1: Create a Cluster Object

First of all, create an instance of the Cluster.builder class of com.datastax.driver.core package as shown below.

//Creating Cluster.Builder object

Cluster.Builder builder1 = Cluster.builder();

Add a contact point (IP address of the node) using the addContactPoint() method of Cluster.Builder object. This method returns Cluster.Builder.

//Adding contact point to the Cluster.Builder object

Cluster.Builder builder2 = build.addContactPoint( “127.0.0.1” );

Using the new builder object, create a cluster object. To do so, you have a method called build() in the Cluster.Builder class. The following code shows how to create a cluster object.

//Building a cluster

Cluster cluster = builder.build();

You can build a cluster object using a single line of code as shown below.

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

Step 2: Create a Session Object

Create an instance of Session object using the connect() method of Cluster class as shown below.

Session session = cluster.connect( );

This method creates a new session and initializes it. If you already have a keyspace, you can set it to the existing one by passing the keyspace name in string format to this method as shown below.

Session session = cluster.connect(“ Your keyspace name ” );

Here we are using the keyspace named tp. Therefore, create the session object as shown below.

Session session = cluster.connect(“ tp” );

Step 3: Execute Query

You can execute CQL queries using the execute() method of Session class. Pass the query either in string format or as a Statement class object to the execute() method. Whatever you pass to this method in string format will be executed on the cqlsh.

In the following example, we are creating a table named emp. You have to store the query in a string variable and pass it to the execute() method as shown below.

//Query

String query = “CREATE TABLE emp(emp_id int PRIMARY KEY, ”

+ “emp_name text, ”

+ “emp_city text, ”

+ “emp_sal varint, ”

+ “emp_phone varint );”;

session.execute(query);

Given below is the complete program to create and use a keyspace in Cassandra using Java API.

import com.datastax.driver.core.Cluster;

import com.datastax.driver.core.Session;

public class Create_Table {

public static void main(String args[]){

//Query

String query = “CREATE TABLE emp(emp_id int PRIMARY KEY, ”

+ “emp_name text, ”

+ “emp_city text, ”

+ “emp_sal varint, ”

+ “emp_phone varint );”;

//Creating Cluster object

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

//Creating Session object

Session session = cluster.connect(“tp”);

//Executing the query

session.execute(query);

System.out.println(“Table created”);

}

Save the above program with the class name followed by .java, browse to the location where it is saved. Compile and execute the program as shown below.

$javac Create_Table.java

$java Create_Table

Under normal conditions, it should produce the following output −

Table created

Cassandra – Alter Table

Altering a Table

You can alter a table using the command ALTER TABLE. Given below is the syntax for creating a table.

Syntax

ALTER (TABLE | COLUMNFAMILY) <tablename> <instruction>

Using ALTER command, you can perform the following operations −

Add a column
Drop a column

Adding a Column

Using ALTER command, you can add a column to a table. While adding columns, you have to take care that the column name is not conflicting with the existing column names and that the table is not defined with compact storage option. Given below is the syntax to add a column to a table.

ALTER TABLE table name

ADD new column datatype;

Example

Given below is an example to add a column to an existing table. Here we are adding a column called emp_email of text datatype to the table named emp.

cqlsh:tecklearn> ALTER TABLE emp

… ADD emp_email text;

Verification

Use the SELECT statement to verify whether the column is added or not. Here you can observe the newly added column emp_email.

cqlsh:tecklearn> select * from emp;

——–+———-+———–+———-+———–+———

Dropping a Column

Using ALTER command, you can delete a column from a table. Before dropping a column from a table, check that the table is not defined with compact storage option. Given below is the syntax to delete a column from a table using ALTER command.

ALTER table name

DROP column name;

Example

Given below is an example to drop a column from a table. Here we are deleting the column named emp_email.

cqlsh:tecklearn> ALTER TABLE emp DROP emp_email;

Verification

Verify whether the column is deleted using the select statement, as shown below.

cqlsh:tecklearn> select * from emp;

emp_id | emp_city | emp_name | emp_phone | emp_sal

——–+———-+———-+———–+———

(0 rows)

Since emp_email column has been deleted, you cannot find it anymore.

Altering a Table using Java API

You can create a table using the execute() method of Session class. Follow the steps given below to alter a table using Java API.

Step1: Create a Cluster Object

First of all, create an instance of Cluster.builder class of com.datastax.driver.core package as shown below.

//Creating Cluster.Builder object

Cluster.Builder builder1 = Cluster.builder();

Add a contact point (IP address of the node) using the addContactPoint() method of Cluster.Builder object. This method returns Cluster.Builder.

//Adding contact point to the Cluster.Builder object

Cluster.Builder builder2 = build.addContactPoint( “127.0.0.1” );

Using the new builder object, create a cluster object. To do so, you have a method called build() in the Cluster.Builder class. The following code shows how to create a cluster object.

//Building a cluster

Cluster cluster = builder.build();

You can build a cluster object using a single line of code as shown below.

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

Step 2: Create a Session Object

Create an instance of Session object using the connect() method of Cluster class as shown below.

Session session = cluster.connect( );

This method creates a new session and initializes it. If you already have a keyspace, you can set it to the existing one by passing the KeySpace name in string format to this method as shown below.

Session session = cluster.connect(“ Your keyspace name ” );

Session session = cluster.connect(“ tp” );

Here we are using the KeySpace named tp. Therefore, create the session object as shown below.

Step 3: Execute Query

In the following example, we are adding a column to a table named emp. To do so, you have to store the query in a string variable and pass it to the execute() method as shown below.

//Query

String query1 = “ALTER TABLE emp ADD emp_email text”;

session.execute(query);

Given below is the complete program to add a column to an existing table.

import com.datastax.driver.core.Cluster;

import com.datastax.driver.core.Session;

public class Add_column {

public static void main(String args[]){

//Query

String query = “ALTER TABLE emp ADD emp_email text”;

//Creating Cluster object

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

//Creating Session object

Session session = cluster.connect(“tp”);

//Executing the query

session.execute(query);

System.out.println(“Column added”);

}

Save the above program with the class name followed by .java, browse to the location where it is saved. Compile and execute the program as shown below.

$javac Add_Column.java

$java Add_Column

Under normal conditions, it should produce the following output −

Column added

Deleting a Column

Given below is the complete program to delete a column from an existing table.

import com.datastax.driver.core.Cluster;

import com.datastax.driver.core.Session;

public class Delete_Column {

public static void main(String args[]){

//Query

String query = “ALTER TABLE emp DROP emp_email;”;

//Creating Cluster object

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

//Creating Session object

Session session = cluster.connect(“tp”);

//executing the query

session.execute(query);

System.out.println(“Column deleted”);

}

Save the above program with the class name followed by .java, browse to the location where it is saved. Compile and execute the program as shown below.

$javac Delete_Column.java

$java Delete_Column

Under normal conditions, it should produce the following output −

Column deleted

So, this brings us to the end of blog. This Tecklearn ‘How to Create and Alter Tables in Apache Cassandra’ helps you with commonly asked questions if you are looking out for a job in Cassandra and No-SQL Database Domain.

If you wish to learn HBase and build a career in Cassandra or No-SQL Database domain, then check out our interactive, Apache Cassandra Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/apache-cassandra-training/

Apache Cassandra Training

About the Course

Take your career to the next level as a certified Apache Cassandra developer by acquiring all the skills through our hands-on training sessions. Tecklearn’s Apache Cassandra Certification Training is designed by professionals as per the industry requirements and demands. This Cassandra Certification Training helps you to master the concepts of Apache Cassandra including Cassandra Architecture, its features, Cassandra Data Model, and its Administration. Our Cassandra certification training course lets you master the high availability NoSQL distributed database.

Why Should you take Apache Cassandra Training?

The average salary of a Software Engineer with Apache Cassandra skill is $120,500 per year. – Payscale.com
Cassandra is in use at Constant Contact, CERN, Comcast, eBay, GitHub, GoDaddy, Hulu, Instagram, Intuit, Netflix, Reddit, The Weather Channel, and over 1500 more companies that have large, active data sets.
Apache Cassandra is one of the most widely used NoSQL database. It offers features such as Fault Tolerance, Scalability, Flexible Data Storage and its efficient writes, which makes it the perfect database for various purposes.

What you will Learn in this Course?

Introduction to Big Data, and Cassandra

What is Big Data
Limitations of RDBMS
NoSQL and it’s Characteristics
CAP Theorem
Basic concepts of Cassandra
Features of Cassandra

Cassandra Data model, Installation and setup

Installation of Cassandra
Key concepts and deployment of non-relational database, column-oriented database, Data Model – column, column family

Cassandra Architecture

Explain the Architecture of Cassandra
Different Layers of Cassandra Architecture
Partitioning and Snitches
Explain Vnodes and How Read and Write Path works
Understand Compaction, Anti-Entropy and Tombstone
Describe Repairs in Cassandra

Deep Dive into Cassandra Database

Describe Different Data Types Used in Cassandra
Explain Collection Types
Describe What are CRUD Operations
Implement Insert, Select, Update and D elete of various elements
Implement Various Functions Used in Cassandra
Describe Importance of Roles and Indexing

Backup & Restore and Performance Tuning

Learn backup and restore functionality and its importance
Create a snapshot using Nodetool utility
Restore a snapshot
Understand how to choose the right balance of the following resources: memory, CPU, disks, number of nodes, and network.
Understand all the logs created by Cassandra
Explain the purpose of different log files
Configure the log files
Learn about Performance Tuning
Integration with Spark and Kafka

Advance Modelling

Rules of Cassandra data modelling
Modelling data around queries
Creating table for data queries

Deploying the IDE for Cassandra applications

Learning key drivers
Deploying the IDE for Cassandra applications and cluster connection
Data query implementation

Cassandra Administration

Understanding Node Tool Utility
Cluster management using Command Line Interface
Management and Monitoring using DataStax Ops Center

Cassandra API and Summarization

Cassandra client connectivity
Connection pool internals
Cassandra API
Features and concepts of Hector client
Thrift, JAVA code and Summarization

Got a question for us? Please mention it in the comments section and we will get back to you.

621