Working with queries in Amazon Keyspaces
This section gives an introduction into working with queries in Amazon Keyspaces (for Apache Cassandra). The CQL
statements available to query, transform, and manage data are SELECT,
INSERT, UPDATE, and DELETE. The following topics
outline some of the more complex options available when working with queries. For the
complete language syntax with examples, see DML statements (data manipulation language) in Amazon Keyspaces.
Ordering results in Amazon Keyspaces
The ORDER BY clause specifies the sort order of the results returned
in a SELECT statement. The statement takes a list of column names as arguments and for each column you can
specify the sort order for the data. You can only specify clustering columns in ordering clauses, non-clustering columns are not allowed.
The two available sort order options for the returned results are ASC for ascending and DESC
for descending sort order. If you don't specify the sort order in the query statement, the default ordering of the
clustering column is used.
The possible sort orders you can use in an ordering clause depend on the sort order assigned to each clustering column at table creation. Query results can only be sorted in the order defined for all clustering columns at table creation or the inverse of the defined sort order. Other possible combinations are not allowed.
For example, if the table's CLUSTERING ORDER is (col1 ASC, col2 DESC, col3 ASC), then the valid
parameters for ORDER BY are either (col1 ASC, col2 DESC, col3 ASC) or (col1 DESC,
col2 ASC, col3 DESC). For more information on CLUSTERING ORDER, see table_options under CREATE TABLE.
Paginating results in Amazon Keyspaces
Amazon Keyspaces automatically paginates the results from SELECT statements when the data read to process the SELECT
statement exceeds 1 MB. With pagination, the SELECT statement results are divided into "pages" of data that
are 1 MB in size (or less). An application can process the first page of
results, then the second page, and so on. Clients should always check for pagination tokens when executing SELECT queries that return multiple rows.
If a client supplies a PAGE SIZE that requires reading more than 1 MB of data, Amazon Keyspaces breaks up the results automatically into multiple pages based on the 1 MB data-read increments.
For example, if the average size of a row is 100 KB and you specify a PAGE SIZE of 20, Amazon Keyspaces paginates data automatically after it reads 10 rows (1000 KB of data read).
Because Amazon Keyspaces paginates results based on the number of rows read to process a request and not the number of rows returned in the result set, some pages may not contain any rows if you are running filtered queries.
For example, if you set PAGE SIZE to 10 and Keyspaces evaluates 30 rows
to process your SELECT query, Amazon Keyspaces will return three pages. If only a subset of the rows matched your query, some pages may have less than 10 rows.
