Select Data from Table in Cassandra - Cassandra / CQL Tutorial

Select Data from Table in Cassandra

To Select the data from Cassandra , Select statement is used. If you need to select all columns , you can use "*" or provide list of column names. I am not using Where clause and other options in this post, Let's discuss them in next posts.

Syntax:

cqlsh>SELECT * FROM   tablename; 

 Or

cqlsh>SELECT column1,  column2,  column3, ....FROM tablename;

ALias:

You can also Alias the column names, you will be using "AS AliasName". if you want to use space in Alias Column name, you need to use double quotes around it e.g "My Alias Column Name"

cqlsh>SELECT column1 AS ColumnOne,        column2 AS ColumnTwo,        column3 AS "Column Three" FROM   tablename;

 

Example:

Let's create employee table by using CQL create statement and insert  sample data and then we will use Select statement to query the data.

CQLSH:techbrotherstutorials>CREATE TABLE employee 
             ( 
                          employeeid INT , 
                          fname TEXT, 
                          lname TEXT, 
                          address TEXT, 
                          age TINYINT, 
                          PRIMARY KEY((employeeid,fname),lname) 
             ) 
             WITH clustering 
ORDER BY     ( 
                          lname DESC);

 

Insert data into table by using CQL Insert command

INSERT INTO employee 
            (employeeid,fname,lname,address,age) 
            VALUES (1,'John','Doe','ABC Address',23);
INSERT INTO employee 
            (employeeid,fname,lname,address,age) 
            VALUES (2,'Robert','Ladson',' Address',40);
INSERT INTO employee 
            (employeeid,fname,lname,address,age) 
            VALUES (3,'M','Raza','New Address',38); 

 

Let's select all the records from employee table. Employee table in my case is in TechBrothersTutorials keyspace, I am already in TechBrothersTutorial keyspace. If you need to be in keyspace where your table exists, you can use "USE KEYSPACENAME".

CQLSH:techbrotherstutorials>SELECT * FROM   employee; 

Output

  employeeid | fname  | lname  | address      | age

------------+--------+--------+--------------+-----
         
3 |      M |   Raza |  New Address |  26
          1 |   John |    Doe |  ABC Address |  23
          2 | Robert | Ladson |  Xyz Address |  40

(3 rows)
 

 

Let's use column names in our Select query and alias , I am going to fname to FirstName, lname to LastName and address to "Home Address".

CQLSH:techbrotherstutorials>SELECT employeeid, 
       fname   AS FirstName, 
       lname   AS LastName, 
       address AS "Home Address", 
       age 
FROM   employee; 

Output 

 

  employeeid | firstname  | lastname  | Home Address      | age

------------+--------+--------+--------------+-----
         
3 |      M |   Raza |  New Address |  26
          1 |   John |    Doe |  ABC Address |  23
          2 | Robert | Ladson |  Xyz Address |  40

(3 rows)