CQLSH Commands
Connecting to CQLSH

Connect to cqlsh using a username. If authenticator in cassandra.yaml is set to PasswordAuthenticator, the user also will require a valid password. -u option is used to specify the user and -p specifies the password. The user can also specify the keyspace to log into using -k option.

Make sure the Python version should not be later than 2.7.9

bigdata@orcl1 ~]$ /opt/apache/apache-cassandra-3.7/bin/cqlsh -u prajeeth -p hello -k testks
Connected to cassandra1 at orcl1:9042.
[cqlsh 5.0.1 | Cassandra 3.7 | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
prajeeth@cqlsh:testks>
CREATE KEYSPACE Command
prajeeth@cqlsh:testks> CREATE KEYSPACE testks WITH REPLICATION = {'class' : 'SimpleStrategy', 'replication_factor' : 1};
Creating internal user accounts
prajeeth@cqlsh:testks> CREATE USER prajeeth WITH PASSWORD 'mypassword' NOSUPERUSER
DESCRIBE KEYSPACES Statement
prajeeth@cqlsh:testks> DESC KEYSPACES

testks  system_schema  system_auth  system  system_distributed  system_traces
DESCRIBE <keyspace> Statement
prajeeth@cqlsh:testks> desc testks

CREATE KEYSPACE testks WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'} 
 AND DURABLE_WRITES = true;
USE Statement
prajeeth@cqlsh:testks> USE testks
   ... ;
prajeeth@cqlsh:testks>
CREATE TABLE Statement
CREATE TABLE testks.employee (
   empid varchar,
   fname varchar,
   lname varchar,
   age int,
   doj timestamp,
   PRIMARY KEY (empid)
)
WITH COMMENT = 'My first table'
AND READ_REPAIR_CHANCE = 1.0
AND COMPACTION =
 {'class' : 'SizeTieredCompactionStrategy', 'min_threshold' : 16};
INSERT Statement
insert into testks.employee (empid, fname, lname, age, doj) values ('6395183992585970', 'Irene', 'Powell', 62, '2011-02-17');
insert into testks.employee (empid, fname, lname, age, doj) values ('30380484498761', 'James', 'Bryant', 37, '2011-04-25');
insert into testks.employee (empid, fname, lname, age, doj) values ('3557625050756142', 'Anne', 'Perkins', 41, '2010-03-23');
insert into testks.employee (empid, fname, lname, age, doj) values ('3560108995720304', 'Mildred', 'Austin', 52, '2013-11-04');
insert into testks.employee (empid, fname, lname, age, doj) values ('5100177095916932', 'Kelly', 'Perkins', 30, '2013-03-09');
insert into testks.employee (empid, fname, lname, age, doj) values ('4508934184893500', 'Jacqueline', 'Adams', 34, '2008-07-01');
insert into testks.employee (empid, fname, lname, age, doj) values ('3568148633644484', 'Eric', 'Gibson', 56, '2013-04-02');
insert into testks.employee (empid, fname, lname, age, doj) values ('3553935257296311', 'Carolyn', 'Stewart', 58, '2014-07-16');
insert into testks.employee (empid, fname, lname, age, doj) values ('5602210364534889', 'Ann', 'Ramos', 48, '2011-06-27');
insert into testks.employee (empid, fname, lname, age, doj) values ('3582988371004405', 'Jeffrey', 'Hudson', 32, '2011-11-05');
SELECT Statement
prajeeth@cqlsh:testks> SELECT * FROM employee;

 empid               | age | doj                             | fname      | lname
---------------------+-----+---------------------------------+------------+------------
    6759918096880284 |  38 | 2013-01-03 18:30:00.000000+0000 |       Alan |      Wells
    3564018710459193 |  36 | 2013-03-02 18:30:00.000000+0000 |    Kenneth |      Mills
 6334561257928428923 |  40 | 2009-02-15 18:30:00.000000+0000 |     Willie |        Fox
     201730441233490 |  51 | 2006-06-28 18:30:00.000000+0000 |    Dorothy |     Hughes
   50382531428615733 |  58 | 2016-03-30 18:30:00.000000+0000 |      Jason |     Garcia
   56022448597800728 |  54 | 2007-05-29 18:30:00.000000+0000 |       Sean |     Jordan
    5100174512108581 |  30 | 2010-06-19 18:30:00.000000+0000 |     Cheryl |    Watkins
    3550656007365189 |  56 | 2006-08-19 18:30:00.000000+0000 |     Donald |   Martinez

(result truncated)
User-defined datatype for table column

Cassandra tables can have user-defined datatype for columns. To use user-defined columns, first define the type. Then, use the type in the column

Let us first create a type called fullname in keyspace testks.

CREATE TYPE testks.fullname (
  firstname text,
  lastname text
);

After the type is successfully created, use it in the table. When using user-defined datatype, keyword frozen has to be used and the datatype should be used within angel brackets as seen below.

CREATE TABLE testks.users (
  id uuid PRIMARY KEY,
  name frozen <fullname>,
  direct_reports frozen <fullname>
);

The CREATE TYPE and CREATE TABLE command will fail if the authorizer in cassandra.yaml is set to CassandraAuthorizer. To grant CREATE permission, see here

Determining SUPERUSER

To determin the superuser, use the table system_auth.roles. The column is_superuser displays True for superuser

cassandra@cqlsh:system_auth> SELECT * FROM system_auth.roles;

 role      | can_login | is_superuser | member_of | salted_hash
-----------+-----------+--------------+-----------+--------------------------------------------------------------
 cassandra |      True |         True |      null | $2a$10$iiGPsK57gJYgbt5kiFxH1.xptR/LVhlSD2EZNk1fDCLH8gffgOKoa
  prajeeth |      True |        False |      null | $2a$10$INb0.cHK052bSlOtN8WdSeECG07256wQp99X8XmaGiziLbcBJdu1K

(2 rows)
Comments