Cassandra Commands Quick Reference

Source/Reference:
http://www.tutorialspoint.com/cassandra/cassandra_quick_guide.htm
http://docs.datastax.com/en/cql/3.0/cql/cql_reference/cql_data_types_c.html

ram@ram-pc:~cqlsh
Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 2.1.4 | CQL spec 3.2.0 | Native protocol v3]
Use HELP for help.
cqlsh>


Data types:

CQL Type	Constants	Description
ascii	strings	US-ASCII character string
bigint	integers	64-bit signed long
blob	blobs	Arbitrary bytes (no validation), expressed as hexadecimal
boolean	booleans	true or false
counter	integers	Distributed counter value (64-bit long)
decimal	integers, floats	Variable-precision decimal
double	integers	64-bit IEEE-754 floating point
float	integers, floats	32-bit IEEE-754 floating point
inet	strings	IP address string in IPv4 or IPv6 format*
int	integers	32-bit signed integer
list	n/a	A collection of one or more ordered elements
map	n/a	A JSON-style array of literals: { literal : literal, literal : literal ... }
set	n/a	A collection of one or more elements
text	strings	UTF-8 encoded string
timestamp	integers, strings	Date plus time, encoded as 8 bytes since epoch
uuid	uuids	A UUID in standard UUID format
timeuuid	uuids	Type 1 UUID only (CQL 3)
varchar	strings	UTF-8 encoded string
varint	integers	Arbitrary-precision integer

cqlsh> help

Documented shell commands:
===========================
CAPTURE      COPY  DESCRIBE  EXPAND  PAGING  SOURCE
CONSISTENCY  DESC  EXIT      HELP    SHOW    TRACING

CQL help topics:
================
ALTER                        CREATE_TABLE_OPTIONS  SELECT            
ALTER_ADD                    CREATE_TABLE_TYPES    SELECT_COLUMNFAMILY
ALTER_ALTER                  CREATE_USER           SELECT_EXPR       
ALTER_DROP                   DELETE                SELECT_LIMIT      
ALTER_RENAME                 DELETE_COLUMNS        SELECT_TABLE      
ALTER_USER                   DELETE_USING          SELECT_WHERE      
ALTER_WITH                   DELETE_WHERE          TEXT_OUTPUT       
APPLY                        DROP                  TIMESTAMP_INPUT   
ASCII_OUTPUT                 DROP_COLUMNFAMILY     TIMESTAMP_OUTPUT  
BEGIN                        DROP_INDEX            TRUNCATE          
BLOB_INPUT                   DROP_KEYSPACE         TYPES             
BOOLEAN_INPUT                DROP_TABLE            UPDATE            
COMPOUND_PRIMARY_KEYS        DROP_USER             UPDATE_COUNTERS   
CREATE                       GRANT                 UPDATE_SET        
CREATE_COLUMNFAMILY          INSERT                UPDATE_USING      
CREATE_COLUMNFAMILY_OPTIONS  LIST                  UPDATE_WHERE      
CREATE_COLUMNFAMILY_TYPES    LIST_PERMISSIONS      USE               
CREATE_INDEX                 LIST_USERS            UUID_INPUT        
CREATE_KEYSPACE              PERMISSIONS        
CREATE_TABLE                 REVOKE             


cqlsh> consistency
Current consistency level is ONE.

cqlsh> capture 'cassandra_out.txt'
Now capturing query output to 'cassandra_out.txt'.

cqlsh> describe cluster;

Cluster: Test Cluster
Partitioner: Murmur3Partitioner


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

system_traces  system  employee_personal

cqlsh> use employee_personal;
cqlsh:employee_personal>

cqlsh:employee_personal> ALTER KEYSPACE employee_personal WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 2};

cqlsh:employee_personal> describe keyspace employee_personal

CREATE KEYSPACE employee_personal WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '2'}  AND durable_writes = true;

cqlsh:employee_personal> CREATE TABLE employee(
                     ...    emp_rollno varint PRIMARY KEY,
                     ...    emp_fname text,
                     ...    emp_lname text,
                     ...    emp_deptid varint,
                     ...    emp_salary varint );

cqlsh:employee_personal> describe tables;

employee
cqlsh:employee_personal> describe table employee;

CREATE TABLE employee_personal.employee (
    emp_rollno varint PRIMARY KEY,
    emp_deptid varint,
    emp_fname text,
    emp_lname text,
    emp_salary varint
) WITH bloom_filter_fp_chance = 0.01
    AND caching = '{"keys":"ALL", "rows_per_partition":"NONE"}'
    AND comment = ''
    AND compaction = {'min_threshold': '4', 'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32'}
    AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99.0PERCENTILE';

cqlsh:employee_personal> alter table employee add emp_manager_id varint;
cqlsh:employee_personal> alter table employee drop emp_lname;
cqlsh:employee_personal> desc table employee;

CREATE TABLE employee_personal.employee (
    emp_rollno varint PRIMARY KEY,
    emp_deptid varint,
    emp_fname text,
    emp_manager_id varint,
    emp_salary varint
) WITH bloom_filter_fp_chance = 0.01
    AND caching = '{"keys":"ALL", "rows_per_partition":"NONE"}'
    AND comment = ''
    AND compaction = {'min_threshold': '4', 'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32'}
    AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99.0PERCENTILE';

cqlsh:employee_personal> create index ind_deptid on employee(emp_deptid);

cqlsh:employee_personal> insert into employee(emp_rollno, emp_deptid, emp_fname, emp_manager_id, emp_salary)
                     ... values (5001, 51, 'Ramkumar', 4731, 34000);

cqlsh:employee_personal> insert into employee(emp_rollno, emp_deptid, emp_fname, emp_manager_id, emp_salary)
                     ... values (5002, 52, 'Karthik', 4731, 43000);

cqlsh:employee_personal> insert into employee(emp_rollno, emp_deptid, emp_fname, emp_manager_id, emp_salary)
                     ... values (5003, 52, 'Saravanan', 4731, 57000);

cqlsh:employee_personal> update employee set emp_fname = 'RSaravanan', emp_manager_id = 4741 where emp_rollno = 5003;

cqlsh:employee_personal> capture off;
cqlsh:employee_personal> select * from employee;

 emp_rollno | emp_deptid | emp_fname  | emp_manager_id | emp_salary
------------+------------+------------+----------------+------------
       5003 |         52 | RSaravanan |           4741 |      57000
       5001 |         51 |   Ramkumar |           4731 |      34000
       5002 |         52 |    Karthik |           4731 |      43000
cqlsh:employee_personal> select emp_deptid, emp_fname from employee where emp_salary>5000 order by emp_rollno desc;
InvalidRequest: code=2200 [Invalid query] message="ORDER BY with 2ndary indexes is not supported."

cqlsh:employee_personal> select * from employee where emp_deptid = 52;

 emp_rollno | emp_deptid | emp_fname  | emp_manager_id | emp_salary
------------+------------+------------+----------------+------------
       5003 |         52 | RSaravanan |           4741 |      57000
       5002 |         52 |    Karthik |           4731 |      43000

cqlsh:employee_personal> drop index ind_deptid;
cqlsh:employee_personal> truncate  employee;
cqlsh:employee_personal> select * from employee;

 emp_rollno | emp_deptid | emp_fname | emp_manager_id | emp_salary
------------+------------+-----------+----------------+------------

(0 rows)
cqlsh:employee_personal> drop table employee;
cqlsh:employee_personal>

Batch
BEGIN BATCH
<insert-stmt>/ <update-stmt>/ <delete-stmt>
APPLY BATCH