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 thecolumn_list
selects all columns.
Selecting a subset of columns
To query for a subset of columns
-
To retrieve only the
id
,name
, andmanager_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 theid
is234-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 theWHERE
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 theWHERE
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
, andid
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.