Create Table in CQL

Category: Cassandra   Tags: Cassandra, Learning, Beginners, Basics, NoSQL Database, Cassandra CQL

Use CREATE TABLE command to create table in Cassandra using CQL:

CREATE TABLE IF NOT EXISTS keyspace_name.table_name
(
    column_definition1,
    column_definition2,
    ...
)
other property ...
                        

CREATE TABLE creates a new table under the current keyspace. If a table already exist then the CREATE TABLE command will return error. If IF NOT EXISTS option is used then using the create statement will create the table if it doesn't exist or will simply do nothing. Here is one example:

CREATE TABLE users (
  userid text PRIMARY KEY,
  roll_no int,
  first_name text,
  last_name text
);
                        

To view the table data use the SELECT command:

SELECT * from users;
SELECT * from users WHERE userid = "10";
                        

Ordering Results

You can do on-disk sorting of columns to order the result in ascending or descending order. The ascending order is the default order. If you need results in descending order, you can specify a clustering order to store columns on disk in the reverse order.

The following table definition changes the clustering order to descending by post time.

CREATE TABLE post (
  id text,
  posted_time timestamp,
  content text,
  PRIMARY KEY (id, posted_time)
)
WITH CLUSTERING ORDER BY (posted_time DESC);
                        

Inserting data into a table

To insert a user data for Amit, use the INSERT command:

INSERT INTO users (userid, roll_no, first_name, last_name)
  VALUES ("100", 10, 'Amit', 'Kumar');
                        

Delete data from a table

Delete a last_name from users table:

DELETE last_name FROM users where userid = '100';
                        

Delete a row from users table:

DELETE FROM users where userid = '100';