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 );
- Download the .csv from here
- command to insert data directly from .csv file is –
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