Using the SELECT command.
Querying tables to select data is the reason data is stored in databases. Similar to
SQL, CQL can SELECT
data using simple or complex qualifiers. At its
simplest, a query selects all data in a table. At its most complex, a query
delineates which data to retrieve and display.
Procedure
-
The example below illustrates how to create a query that uses
first_name and last_name as a
filter.
cqlsh> SELECT * FROM users_by_name WHERE first_name = 'jane';
Note that Apache Cassandra™ will reject this query if
first_nameand last_name are not
part of the primary key, either a partition key or clustering column.
Queries require a sequential retrieval across the entire
users table. In a distributed database like
Cassandra, this is a crucial concept to grasp; scanning all data across all
nodes is prohibitively slow and thus blocked from execution. The use of
partition key and clustering columns in a WHERE
clause must
result in the selection of a contiguous set of rows.
-
You can also pick the columns to display instead of choosing all data.
cqlsh> SELECT first_name, age FROM users_by_name WHERE first_name = 'jane';
-
For a large table, limit the number of rows retrieved using
LIMIT
. The default limit is 10,000 rows. To sample data,
pick a smaller number. To retrieve more than 10,000 rows set
LIMIT
to a large value.
cqlsh> SELECT * FROM users LIMIT 10;
-
You can fine-tune the display order using the
ORDER BY
clause,
either descending or ascending. The partition key must be defined in the
WHERE
clause and the ORDER BY
clause
defines the clustering column to use for ordering.
cqlsh> SELECT * FROM users WHERE userID IN (102,104) ORDER BY age ASC;