Cassandra modifies the table

Change the use of the Cqlsh table

You can use the command ALTER TABLE to change the table. The syntax for modifying the table is given below.

Syntactic

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

With the ALTER command, you can do the following:

• Add a column

• Delete the column

Add a column

With the ALTER command, you can add columns to a table. W hen adding columns, it is important to note that column names do not conflict with existing column names, and that tables are not defined with compact storage options. ow.

ALTER TABLE table name
ADD  new column datatype;

Example

Here's an example of adding columns to an existing table. Here we add a column of text data types called emp_email in a table called emp.

cqlsh:tutorialspoint> ALTER TABLE emp
   ... ADD emp_email text;

Verify

Use the SELECT statement to verify that the column has been added. Here you can observe the newly added column emp_email.

cqlsh:tutorialspoint> select * from emp;

 emp_id | emp_city | emp_email | emp_name | emp_phone | emp_sal
--------+----------+-----------+----------+-----------+---------

Delete the column

With the ALTER command, you can remove columns from a table. B efore you remove a column from a table, check that the table is not defined using the compact storage option. The syntax for removing columns from the table using the ALTER command is given below.

ALTER table name
DROP column name;

Example

An example of removing a column from a table is given below. Here we delete a column emp_email the list.

cqlsh:tutorialspoint> ALTER TABLE emp DROP emp_email;

Verify

Use the select statement to verify that the column has been deleted, as shown below.

cqlsh:tutorialspoint> select * from emp;

 emp_id | emp_city | emp_name | emp_phone | emp_sal
--------+----------+----------+-----------+---------
(0 rows)

Because emp_email column has been deleted, you can no longer find it.

Use the Java API to change the table

You can create tables using the Execute() method of the Session class. Follow these steps to change the table using the Java API.

Step 1: Create a cluster object

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

//Creating Cluster.Builder object
Cluster.Builder builder1 = Cluster.builder();

Use the addContactPoint() method of the Cluster.Builder object to add a contact point (the IP address of the node). This method returns Cluster.Builder.

//Adding contact point to the Cluster.Builder object
Cluster.Builder builder2 = build.addContactPoint( "127.0.0.1" );

Create a cluster object with the new builder object. T o do this, there is a method called build() in the Cluster.Builder class. The following code shows how to create cluster objects.

//Building a cluster
Cluster cluster = builder.build();

You can use a single line of code to build cluster objects, as shown below.

Cluster cluster = Cluster.builder().addContactPoint("127.0.0.1").build();

Step 2: Create a session object

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

Session session = cluster.connect( );

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

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

Here we use KeySpace to name tp. Therefore, the session object is created as follows.

Step 3: Execute the query

You can use the Execute() method of the Session class to perform CQL queries. P ass the query to the execute() method in string format or state class objects. Whether you pass it to this method in string format will execute on cqlsh.

In the following example, we add a column to a table named emp. To do this, you must 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);

The complete program for adding columns to an existing table is given below.

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");
   }
}

Use the class name and .java save the above program and browse to the save location. Compile and execute the program as shown in the following image.

$javac Add_Column.java
$java Add_Column

Under normal conditions, it should produce the following output:

Column added

Delete the column

The face gives the complete program for removing columns 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");
   }
 }

Use the class name and .java save the above program and browse to the save location. Compile and execute the program as shown in the following image.

$javac Delete_Column.java
$java Delete_Column

Under normal conditions, it should produce the following output:

Column deleted

---