Tutorial: Read from an Amazon Keyspaces table

In the Tutorial: Inserting and loading data into an Amazon Keyspaces table section, you used the SELECT statement to verify that you had successfully added data to your table. In this section, you refine your use of SELECT to display specific columns, and only rows that meet specific criteria.

The general form of the SELECT statement is as follows.

SELECT column_list FROM table_name [WHERE condition [ALLOW FILTERING]] ;

Selecting all the data in your table

The simplest form of the SELECT statement returns all the data in your table.

Important

In a production environment, it is typically not a best practice to run this command, which returns all the data in your table.

To select all your table's data

• Run the following query.

  SELECT * FROM "myGSGKeyspace".employees_tbl ;

  Using the wild-card character ( * ) for the column_list selects all columns.

Selecting a subset of columns

To query for a subset of columns

• To retrieve only the id, name, and manager_id columns, run the following query.

  SELECT name, id, manager_id FROM "myGSGKeyspace".employees_tbl ;

  The output will contain only the specified columns in the order listed in the SELECT statement.

Selecting a subset of rows

When querying a large dataset, you might only want records that meet certain criteria. To do this, you can append a WHERE clause to the end of our SELECT statement.

To query for a subset of rows

• To retrieve only the record for the employee with the id '234-56-7890', run the following query.

  SELECT * FROM "myGSGKeyspace".employees_tbl WHERE id='234-56-7890' ;

  The preceding SELECT statement returns only the rows where the id is 234-56-7890.

Understanding the WHERE clause

The WHERE clause is used to filter the data and return only the data that meets the specified criteria. The specified criteria can be a simple condition or a compound condition.

How to use conditions in a WHERE clause

• A simple condition – A single column.

  WHERE column_name=value

  You can use a simple condition in a WHERE clause if any of the following conditions are met:

  • The column is the only column in the table's primary key.

  • You add ALLOW FILTERING after the condition in the WHERE clause.

    Be aware that using ALLOW FILTERING can result in inconsistent performance, especially with large, and multi-partitioned tables.

• A compound condition – Multiple simple conditions connected by AND.

  WHERE column_name1=value1 AND column_name2=value2 AND column_name3=value3...

  You can use compound conditions in a WHERE clause if any of the following conditions are met:

  • The columns in the WHERE clause exactly match the columns in the table's primary key, no more and no fewer.

  • You add ALLOW FILTERING after the compound condition in the WHERE clause, as in the following example.

    SELECT * FROM my_table WHERE col1=5 AND col2='Bob' ALLOW FILTERING ;

    Be aware that using ALLOW FILTERING can result in inconsistent performance, especially with large, and multi-partitioned tables.

Try it

Create your own CQL queries to find the following from your employees_tbl table:

• Find the name, project, and id of all employees.

• Find what project Bob the intern is working on (include at least his name, project, and role in the output).

• Advanced: Create an application to find all the employees who have the same manager as Bob the intern. HINT: This might take more than one query.

• Advanced: Create an application to find selected columns of all employees working on the project NightFlight. HINT: Solving this might require multiple statements.