Different Queries Used in Cassandra

Creating a database in Cassandra:

Note: In Cassandra, a Database is named Keyspace.

Syntax)

• Create KEYSPACE demo1

with replication={‘class’: ‘SimpleStrategy’, ‘replication_factor ‘:4};

• Create keyspace demo1

with replication = {‘class’:’NetworkTopologyStrategy’ ‘dc1′:3,’dc2’:2};

• Listing all the database:

Syntax: DESRIBE KEYSPACES;

• Using a Database:

Syntax)

Use KeySpacename;

Ex_ Use Demo1;

• List the tables present in the database

Syntax) DESRIBE TABLES;

• Creating a table:

Syntax) Create table tablename(col1 datatype, col2 datatype, primary key(col1));

Ex) Create table dummy(id int, name text, date_of_birth timestamp, primary key(id));

• How to get the schema of created table?

Syntax) DESCRIBE TABLENAME;

• Inserting Data into table:

Syntax)

• Insert into:

Ex)

Insert into dummy(id,name, date_of_birth) values(1, “abc”, “22-02-2002 02:20:20”)

• Copy from: Used for bulk loading the data from dataset.

Ex)

copy dummy( home_id,datatime,event, code_used) from ‘/home/destop/event.csv’ with header=true and delimiter = ‘,’;

• Retrieve data from the database:

In Cassandra the retrieval operation is performed using partition key.

Partition key is nothing but using which Cassandra stores a record at particular location.

By default partitioned key is created using columns in primary key.

Ex) select * from dummy where id=1;

But if we try to retrieve records using columns other than primary key columns in the where clause, then it will display an error.

In order to retrieve records using columns other than primary key we need to set the Secondary Index values for the respected columns using which we need to perform the retrieving operations with where clause.

Syntax) create INDEX  indexname ON tablename(columnname);

Ex) create INDEX name_index ON dummy(name);

You don’t need to remember the Index name, it’s only for the purpose of allocating the database with permissions to retrieve records as per a particular column in a table by indexing it.

Now you can perform select query with where clause over columns other than the primary key.

Ex) select * from dummy where name=”xyz”;

• Updating the record in table
  • Single Column update:

Syntax) Update tablename set colname=””where id= ‘’;

Ex) Update dummy set name= “abx” where id=2;

• Multiple column Update:

Syntax) Update tablename set col1=””, col21=”” where id= ‘’;

Ex) Update tablename set name=”axc”,date=”2012-02-22 02:02:02″ where id= 6;

• Deleting records:

Deleting according to particular column:

Syntax) delete col1 from tablename where id= ;

Ex) delete name from dummy where id= 1;

Delete the whole row from the table

Syntax) delete from tablename where id= ;

Ex) delete from dummy where id= 2;

Truncate: Used to the empty the entire table without deleting the schema of the table.

Syntax) truncate tablename;

Ex) truncate dummy;

Drop: Used to delete the entire table along with schema

Syntax) drop table tablename;

Ex) Drop table dummy;

• Drop Entire Database:

Syntax) drop keyspace databasename;

Ex) drop keyspace demo;

• Oreder By Clause

Syntax: SELECT column name FROM table name ORDER BY identifier;

Ex) SELECT * FROM dummy ORDER BY name;

• Help: It is used to helps the user to get a description of the cqlsh commands.

Syntax: HELP;

• Version: To get the version of cassandra installed

#Syntax) version

• Color: To perform the operation ‘COLOR’ keyword can be used.

Syntax) COLOR (‘<colour name>’);

• Debug: Used to Detect and eliminating the errors

#Syntax) DEBUG;

• File: This command helps users to connect cqlsh with file.

Syntax) FILE (‘<file name>’);

• U: The default name in Cassandra is ‘Cassandra’. Using ‘U’ keyword is used to assign a user.

#Syntax) U<“user name”>;

• P: Cassandra sets a default password to be ‘Çassandra’. Using the ‘P’ keyword user can set the password.

Syntax) P<“password”>;