Cassandra: Selecting the data

'SELECT' statement is used to extract the information from the table.

Syntax

<select-stmt> ::= SELECT ( JSON )? <select-clause>
                  FROM <tablename>
                  ( WHERE <where-clause> )?
                  ( GROUP BY <group-by>)?
                  ( ORDER BY <order-by> )?
                  ( PER PARTITION LIMIT <integer> )?
                  ( LIMIT <integer> )?
                  ( ALLOW FILTERING )?

<select-clause> ::= DISTINCT? <selection-list>

<selection-list> ::= <selector> (AS <identifier>)? ( ',' <selector> (AS <identifier>)? )*
                   | '*'

<selector> ::= <identifier>
             | <term>
             | WRITETIME '(' <identifier> ')'
             | COUNT '(' '*' ')'
             | TTL '(' <identifier> ')'
             | CAST '(' <selector> AS <type> ')'
             | <function> '(' (<selector> (',' <selector>)*)? ')'

<where-clause> ::= <relation> ( AND <relation> )*

<relation> ::= <identifier> <op> <term>
             | '(' <identifier> (',' <identifier>)* ')' <op> <term-tuple>
             | <identifier> IN '(' ( <term> ( ',' <term>)* )? ')'
             | '(' <identifier> (',' <identifier>)* ')' IN '(' ( <term-tuple> ( ',' <term-tuple>)* )? ')'
             | TOKEN '(' <identifier> ( ',' <identifer>)* ')' <op> <term>

<op> ::= '=' | '<' | '>' | '<=' | '>=' | CONTAINS | CONTAINS KEY
<group-by> ::= <identifier> (',' <identifier>)*
<order-by> ::= <ordering> ( ',' <odering> )*
<ordering> ::= <identifer> ( ASC | DESC )?
<term-tuple> ::= '(' <term> (',' <term>)* ')'

Create a keyspace, table and insert some data to the table

Step 1: Create a keyspace cassandratutorial.

CREATE keyspace cassandratutorial WITH REPLICATION =

{

         'class' : 'SimpleStrategy',

         'replication_factor' : 1

};

Step 2: Create a table employee in the keyspace 'cassandratutorial'.

CREATE TABLE IF NOT EXISTS cassandratutorial.employee (

  id INT PRIMARY KEY,

  firstName VARCHAR,

  lastName VARCHAR,

  age int,

) WITH comment = 'Table to store Employee information';

Step 3: Execute below statements to insert 3 records to the table 'employee'.

INSERT INTO cassandratutorial.employee JSON '{"id" : 1, "firstName" : "Krishna", "lastName" : "Gurram", "age" : 30}';

INSERT INTO cassandratutorial.employee JSON '{"id" : 2, "firstName" : "Ram", "lastName" : "Gurram", "age" : 31}';

INSERT INTO cassandratutorial.employee JSON '{"id" : 3, "firstName" : "Vijay", "lastName" : "Ponnam", "age" : 45}';

Select all the records from the table ‘employee’

'SELECT * FROM cassandratutorial.employee;' selects all the records from employee table.

cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age | firstname | lastname
----+-----+-----------+----------
  1 |  30 |   Krishna |   Gurram
  2 |  31 |       Ram |   Gurram
  3 |  45 |     Vijay |   Ponnam

(3 rows)

Select specific columns from the table

‘SELECT id, firstname FROM cassandratutorial.employee;’ select id and firstname columns from the table employee.

cqlsh> SELECT id, firstname FROM cassandratutorial.employee;

 id | firstname
----+-----------
  1 |   Krishna
  2 |       Ram
  3 |     Vijay

(3 rows)

Count number of rows in a table

‘'SELECT COUNT(*) FROM cassandratutorial.employee;'’ count number of rows in the table.

cqlsh> SELECT COUNT(*) FROM cassandratutorial.employee;

 count
-------
     3

(1 rows)

Warnings :
Aggregation query used without partition key

Previous                                                    Next                                                    Home