Tutorial: Read data from a table using the SELECT
statement
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]] ;
Select 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's typically not a best practice to run this command, because it returns all the data in your table.
To select all your table's data
-
Open AWS CloudShell and connect to Amazon Keyspaces using the following command. Make sure to update
us-east-1
with your own Region.cqlsh-expansion cassandra.
us-east-1
.amazonaws.com 9142 --ssl -
Run the following query.
SELECT * FROM catalog.book_awards ;
Using the wild-card character (
*
) for thecolumn_list
selects all columns. The output of the statement looks like the following example.year | award | category | rank | author | book_title | publisher ------+------------------+-------------+------+--------------------+-----------------------+--------------- 2020 | Wolf | Non-Fiction | 1 | Wang Xiulan | History of Ideas | AnyPublisher 2020 | Wolf | Non-Fiction | 2 | Ana Carolina Silva | Science Today | SomePublisher 2020 | Wolf | Non-Fiction | 3 | Shirley Rodriguez | The Future of Sea Ice | AnyPublisher 2020 | Kwesi Manu Prize | Fiction | 1 | Akua Mansa | Where did you go? | SomePublisher 2020 | Kwesi Manu Prize | Fiction | 2 | John Stiles | Yesterday | Example Books 2020 | Kwesi Manu Prize | Fiction | 3 | Nikki Wolf | Moving to the Chateau | AnyPublisher 2020 | Richard Roe | Fiction | 1 | Alejandro Rosalez | Long Summer | SomePublisher 2020 | Richard Roe | Fiction | 2 | Arnav Desai | The Key | Example Books 2020 | Richard Roe | Fiction | 3 | Mateo Jackson | Inside the Whale | AnyPublisher
Select a subset of columns
To query for a subset of columns
-
Open AWS CloudShell and connect to Amazon Keyspaces using the following command. Make sure to update
us-east-1
with your own Region.cqlsh-expansion cassandra.
us-east-1
.amazonaws.com 9142 --ssl -
To retrieve only the
award
,category
, andyear
columns, run the following query.SELECT award, category, year FROM catalog.book_awards ;
The output contains only the specified columns in the order listed in the
SELECT
statement.award | category | year ------------------+-------------+------ Wolf | Non-Fiction | 2020 Wolf | Non-Fiction | 2020 Wolf | Non-Fiction | 2020 Kwesi Manu Prize | Fiction | 2020 Kwesi Manu Prize | Fiction | 2020 Kwesi Manu Prize | Fiction | 2020 Richard Roe | Fiction | 2020 Richard Roe | Fiction | 2020 Richard Roe | Fiction | 2020
Select 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
-
Open AWS CloudShell and connect to Amazon Keyspaces using the following command. Make sure to update
us-east-1
with your own Region.cqlsh-expansion cassandra.
us-east-1
.amazonaws.com 9142 --ssl -
To retrieve only the records for the awards of a given year, run the following query.
SELECT * FROM catalog.book_awards WHERE year=2020 AND award='Wolf' ;
The preceding
SELECT
statement returns the following output.year | award | category | rank | author | book_title | publisher ------+-------+-------------+------+--------------------+-----------------------+--------------- 2020 | Wolf | Non-Fiction | 1 | Wang Xiulan | History of Ideas | AnyPublisher 2020 | Wolf | Non-Fiction | 2 | Ana Carolina Silva | Science Today | SomePublisher 2020 | Wolf | Non-Fiction | 3 | Shirley Rodriguez | The Future of Sea Ice | AnyPublisher
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 partition key column of the table.
-
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 you can use in the
WHERE
clause need to include either all or a subset of the columns in the table's partition key. If you want to use only a subset of the columns in theWHERE
clause, you must include a contiguous set of partition key columns from left to right, beginning with the partition key's leading column. For example, if the partition key columns areyear
,month
, andaward
then you can use the following columns in theWHERE
clause:year
year
ANDmonth
year
ANDmonth
ANDaward
-
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
book_awards
table:
-
Find the winners of the 2020 Wolf awards and display the book titles and authors, ordered by rank.
-
Show the first prize winners for all awards in 2020 and display the book titles and award names.