Cassandra

Start Cassandra –

woir@woir-VirtualBox:~/apache-cassandra-3.11.2/bin$ cd /home/woir/apache-cassandra-3.11.2/bin

woir@woir-VirtualBox:~/apache-cassandra-3.11.2/bin$ ./cassandra
woir@woir-VirtualBox:~/apache-cassandra-3.11.2/bin$ CompilerOracle: dontinline org/apache/cassandra/db/Columns$Serializer.deserializeLargeSubset (Lorg/apache/cassandra/io/util/DataInputPlus;Lorg/apache/cassandra/db/Columns;I)Lorg/apache/cassandra/db/Columns;
CompilerOracle: dontinline org/apache/cassandra/db/Columns$Serializer.serializeLargeSubset (Ljava/util/Collection;ILorg/apache/cassandra/db/Columns;ILorg/apache/cassandra/io/util/DataOutputPlus;)V
CompilerOracle: dontinline org/apache/cassandra/db/Columns$Serializer.serializeLargeSubsetSize (Ljava/util/Collection;ILorg/apache/cassandra/db/Columns;I)I
CompilerOracle: dontinline

………………………………………………….

 

woir@woir-VirtualBox:~/apache-cassandra-3.11.2/bin$ cd /home/woir/apache-cassandra-3.11.2/bin
woir@woir-VirtualBox:~/apache-cassandra-3.11.2/bin$ ./cqlsh
Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.11.2 | CQL spec 3.4.4 | Native protocol v4]
Use HELP for help.
cqlsh>

Get Help —

woir@woir-VirtualBox:~/apache-cassandra-3.11.2/bin$ ./cqlsh –help

cqlsh> Describe keyspaces;

cqlsh> CREATE KEYSPACE woir WITH replication =
 {'class':'SimpleStrategy', 'replication_factor' : 3};

  • To delete or drop the keyspace command is –
cqlsh> Drop keyspace keyspace_name;
cqlsh> use woir;

  • To create a employee table in woir(keyspace) command is-
cqlsh:woir> CREATE TABLE employee(
  emp_id int PRIMARY KEY, emp_name text, emp_unit text, emp_dept 
text, emp_job_title text, emp_annual_rt text, emp_hrly_rate text, 
emp_full_part text, emp_reg_temp text, emp_fid text );

cqlsh:woir> COPY employee (emp_id,emp_name, emp_unit, emp_dept, 
emp_job_title, emp_annual_rt, emp_hrly_rate, emp_full_part, 
emp_reg_temp, emp_fid) FROM 
'/home/woir/Downloads/City_Employee_Salaries_November_2017.csv' WITH 
DELIMITER='|' AND HEADER=TRUE;

  • check whether data inserted or not using below command-
cqlsh:woir>select * from employee;
  • To delete or drop the table command is –
cqlsh:woir> drop table employee;

  • Create table again
  •  we can insert data from command line-
cqlsh:woir> INSERT INTO employee (emp_id,emp_name, emp_unit,
 emp_dept, emp_job_title, emp_annual_rt, emp_hrly_rate,
 emp_full_part, emp_reg_temp, emp_fid) 
VALUES(1,'Jones,YongE.','H&NS','Community 
Engagement','Community&Commerce 
Specialist','56954','27.381731','F','R','4001');

cqlsh:woir> INSERT INTO employee (emp_id,emp_name, emp_unit, 
emp_dept, emp_job_title, emp_annual_rt, emp_hrly_rate, emp_full_part,
 emp_reg_temp, emp_fid) VALUES(2,'Bashore,Wendy 
Catherine','H&NS','Housing Combined','Administrative Officer 
III','67942.585','32.664704','F','R','4002');

cqlsh:woir> INSERT INTO employee (emp_id,emp_name, emp_unit, 
emp_dept, emp_job_title, emp_annual_rt, emp_hrly_rate, emp_full_part, 
emp_reg_temp, emp_fid) VALUES(3,'Bennett,Linda M','H&NS','Housing 
Combined','Office Assistant 
IV','46055.828','22.142225','F','R','4003');

  • Using the following command check the data inserted or not in the table
cqlsh:woir> select * from employee;

 

cqlsh> CAPTURE '/home/woir/Documents/Outputfile'

cqlsh:woir> select * from employee;
  • After running the above query we have to check for output  in /home/woir/Documents/Outputfile this capture path

cqlsh:woir> capture off;

Want to know Consistency Level ?

cqlsh> CONSISTENCY
Current consistency level is ONE.
cqlsh> 


Describe Cluster –
cqlsh> describe cluster;

Cluster: Test Cluster
Partitioner: Murmur3Partitioner

Describe tables
cqlsh:woir> describe tables ;

cqlsh:woir> describe table_name;

Expand

 

cqlsh:woir> expand on;

cqlsh:woir> select * from employee;

cqlsh:woir> expand off;

cqlsh:woir> show host;

cqlsh> show version;

Exit

This command is used to terminate the cql shell.

cqlsh:woir> exit;

Source –

Create a file /home/woir/Documents/runcommand with these commands-
   select * from woir.employee;
   describe keyspaces;
after creating file go to cqlsh prompt and run the following command-

cqlsh> source '/home/woir/Documents/runcommand'; ( to run a commands from file )

 

Alter Table –
  • Adding emp_email column field into the employee table using alter command
cqlsh:woir> ALTER TABLE employee ADD emp_email text;

cqlsh:woir> ALTER TABLE employee DROP emp_email;


  • Using TRUNCATE command we can clear all data from the table

 

 

TRUNCATE employee;

Alter Keyspace –

Alter or modifying the existing keyspace in the cassandra database

Before altering kayspace image –

cqlsh> ALTER KEYSPACE woir WITH replication = 
{'class':'NetworkTopologyStrategy'};

  • Again alter the keyspace –
cqlsh> ALTER KEYSPACE woir WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 3};

Creating an Index using Cqlsh –

cqlsh:woir> CREATE INDEX name ON employee (emp_name);


cqlsh:woir> drop index name;

Batch Statements –

 

BEGIN BATCH
  Statement1
  Statement2
  Statement3
APPLY BATCH;
cqlsh:woir> BEGIN BATCH
                INSERT INTO employee (emp_id,emp_name, emp_unit, 
emp_dept, emp_job_title, emp_annual_rt, emp_hrly_rate, emp_full_part, 
emp_reg_temp, emp_fid) VALUES(4,'Pfeiffer,Rebecca H','H&NS','Housing 
Combined','N&BS Program 
Coordinator','64608.81','31.061928','F','R','4004');
                INSERT INTO employee (emp_id,emp_name, emp_unit, 
emp_dept, emp_job_title, emp_annual_rt, emp_hrly_rate, emp_full_part, 
emp_reg_temp, emp_fid) VALUES(5,'Young,Robin F','H&NS','Housing 
Combined','Neighborhood Devel Specialist','57921.984','27.847108','F','R','4005');
            APPLY BATCH;

Updating Data using Cqlsh

 

cqlsh:woir> UPDATE employee SET emp_name='student_name',
emp_hrly_rate='5000' WHERE emp_id=5;

cqlsh:woir> SELECT emp_name, emp_hrly_rate from employee;

cqlsh:woir> SELECT * FROM employee WHERE emp_id=5;

cqlsh:woir> DELETE from employee WHERE emp_id=5;

Built in Data Types –

Data Type Constants Description
ascii strings Represents ASCII character string
bigint bigint Represents 64-bit signed long

Collection Types

 

Collection Description
list A list is a collection of one or more ordered elements.
map A map is a collection of key-value pairs.
set A set is a collection of one or more elements.

List

  • the order of the elements is to be maintained, and
cqlsh:woir> CREATE TABLE student_data(name text PRIMARY KEY,
 email list<text>);

cqlsh:woir> INSERT INTO student_data(name, email) 
VALUES ('shamu', ['abcd1234@gmail.com','labellaa@yahoo.com']);

cqlsh:woir> select * from student_data;

Adding to the list –

cqlsh:woir>  UPDATE student_data SET email = email
 +['yeah@woir.in']  where name = 'shamu';

SET

cqlsh:woir> CREATE TABLE student_data2 
(name text PRIMARY KEY, phone set<varint>);

cqlsh:woir> INSERT INTO student_data2(name, phone)
VALUES ('hema', {9848014338,9848022123});
cqlsh:woir> select * from student_data2;

cqlsh:woir> UPDATE student_data2 SET phone = phone + {9848022330}
  where name = 'hema';

cqlsh:woir> select * from student_data2;

MAP

store a key-value pair of elements.

cqlsh:woir> CREATE TABLE student_data3 
(name text PRIMARY KEY, address map<text, text>);

cqlsh:woir> INSERT INTO student_data3 (name, address)  VALUES 
('mohan', {'home' : 'hyderabad' , 'office': 'Delhi' } );

cqlsh:woir> select * from student_data3;

cqlsh:woir> UPDATE student_data3 SET address = address+
{'country':'India'} WHERE name = 'mohan';

cqlsh:woir> select * from student_data3;

Custom Data Type –

cqlsh:woir> CREATE TYPE bank_details (
   roll_num int,
   account_no int,
   account_holder_name text,
   account_type text,
   ifsc_code text,
   phone set<int>
);

cqlsh:woir> describe types

cqlsh:woir> ALTER TYPE bank_details ADD email text;

cqlsh:woir> describe type bank_details ;

 

cqlsh:woir> ALTER TYPE bank_details RENAME email TO gmail;

cqlsh:woir> describe type bank_details ;

cqlsh:woir> drop type bank_details;

Cassandra-stress:-

  • cassandra-stress write command
cassandra-stress write n=1000 no-warmup -node 127.0.0.1 \
-port native=9042 -schema keyspace=4kapache310 \
-col n=FIXED\(10\) size=FIXED\(400\) -rate threads=1 \
-pop dist=seq\(1..100\)
  •  cassandra-stress read command
cassandra-stress read n=1000 no-warmup -node 127.0.0.1 \
-port native=9042 -schema keyspace=4kapache310 \
-col n=FIXED\(10\) size=FIXED\(400\) -rate threads=1 \
-pop dist=seq\(1..100\)
  •  After running the above commands go to cqlsh prompt check whether database is created or not database_name (4kapache310)
  • Stop cassandra command is
 /home/woir/stop_cassandra.sh
CCM :
ccm create test -v 3.11.2

ccm populate -n 3

ccm start

\ps -eaf | grep cassandra

cqlsh 127.0.0.1 9042

cqlsh 127.0.0.2 9042

cqlsh 127.0.0.3 9042

ccm status

ccm node2 stop

ccm status

ccm node3 stop

ccm status

ccm node2 start

ccm status

ccm stop

ccm status

ccm remove test