DML

SELECT clause

A SELECT clause specifies which columns to return. It can be a wildcard (*) to return all columns, or a comma-separated list of selectors.

Supported selectors

WRITETIME function

WRITETIME(column_name) is available only in SELECT clauses. It returns the timestamp metadata stored with each column value. It accepts exactly one argument: a column name.

WHERE clause

The WHERE clause filters which rows to return. Relations can be defined on primary key columns or columns with a secondary index.

Key constraints:

• Non-equality relations (<, >, <=, >=) are not allowed on partition key columns. The IN operator is treated as an equality relation.

• For clustering columns, relations must select a contiguous set of rows.

Allowed vs. not allowed

The following query is allowed because all three primary key columns are constrained in order:

-- Allowed: userid (partition key) + blog_title + posted_at (clustering columns in order)
CREATE TABLE posts (
    userid text,
    blog_title text,
    posted_at timestamp,
    entry_title text,
    content text,
    category int,
    PRIMARY KEY (userid, blog_title, posted_at)
);

SELECT entry_title, content FROM posts
  WHERE userid = 'john doe'
    AND blog_title = 'John''s Blog'
    AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31';

The following query is not allowed because blog_title is skipped, which breaks the contiguous row requirement:

-- Not allowed: blog_title must be constrained before posted_at
SELECT entry_title, content FROM posts
  WHERE userid = 'john doe'
    AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31';

IN relation

The IN relation is allowed only on the last column of the partition key and the last column of the primary key.

Tuple notation for clustering columns

Tuple notation lets you combine multiple clustering columns in a single relation:

-- Rows where (blog_title, posted_at) is greater than ('John''s Blog', '2012-01-01')
SELECT * FROM posts
  WHERE userid = 'john doe'
    AND (blog_title, posted_at) > ('John''s Blog', '2012-01-01');

Tuple notation also works with IN:

SELECT * FROM posts
  WHERE userid = 'john doe'
    AND (blog_title, posted_at) IN (('John''s Blog', '2012-01-01'), ('Extreme Chess', '2014-06-01'));

GROUP BY clause

GROUP BY groups rows that share the same column values into a single row.

Constraints:

• Group only at the partition key or clustering column level.

• Column names in GROUP BY must follow the primary key column order.

• Equality-constrained primary key columns cannot appear in GROUP BY.

GROUP BY transfers data to a server node for aggregation. Avoid it on large datasets, as it may significantly increase query latency.

ORDER BY clause

ORDER BY controls the sort order of results. Specify column names followed by ASC (ascending, the default) or DESC (descending).

The valid orderings depend on the table's CLUSTERING ORDER definition:

• If no CLUSTERING ORDER is defined: you can sort by clustering column order or its reverse.

• If CLUSTERING ORDER is defined: you can sort by that order or its reverse.

LIMIT clause

LIMIT restricts the number of rows returned. Use PER PARTITION LIMIT to cap results per partition.

ALLOW FILTERING

By default, Lindorm CQL rejects queries that require server-side filtering — queries whose performance cannot be predicted from the number of returned results alone. You can use the ALLOW FILTERING clause to explicitly run queries that contain filters. After you specify ALLOW FILTERING, the performance of the query may be unpredictable.

Example

Given the following table and index:

CREATE TABLE users (
    username text PRIMARY KEY,
    firstname text,
    lastname text,
    birth_year int,
    country text
);

CREATE INDEX ON users(birth_year);

Both of these queries are allowed because Lindorm CQL can guarantee their performance is proportional to the result size:

SELECT * FROM users;
SELECT * FROM users WHERE birth_year = 1981;

This query is rejected by default because it may scan all indexed rows for birth_year = 1981 even if few users are from France:

-- Rejected without ALLOW FILTERING
SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR';

Add ALLOW FILTERING to run it explicitly:

SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR' ALLOW FILTERING;

HOTDATA

When a table is created with hot/cold data separation, use the HOTDATA keyword to query only the hot data tier:

SELECT HOTDATA * FROM persioninfo;

Without HOTDATA, the query scans both hot and cold data and returns the combined results.

Operations on search indexes

Lindorm CQL supports fuzzy queries, multi-dimensional queries, and sorting on columns that have a search index.

-- Exact match
SELECT * FROM persioninfo WHERE c2 LIKE 'v2';
-- Suffix match
SELECT * FROM kss.tb WHERE c2 LIKE '%v2';
-- Prefix match
SELECT * FROM kss.tb WHERE c2 LIKE 'v2%';
-- Contains match
SELECT * FROM kss.tb WHERE c2 LIKE '%v2%';

SELECT selectors
  FROM table
  WHERE (indexed_column_expression | search_query = 'search_expression')
  [ LIMIT n ]
  [ ORDER BY column_name ];

CREATE TABLE persioninfo (
    name text PRIMARY KEY,
    city text,
    id int,
    friend nested
) WITH extensions = {'CONSISTENCY_TYPE': 'strong', 'MUTABILITY': 'IMMUTABLE'};

CREATE SEARCH INDEX sidx ON persioninfo WITH COLUMNS (id, city);

INSERT INTO persioninfo (name, city, id, friend)
  VALUES ('MY', 'hangzhou', 18, '[{"lili":18}, {"gg":20}]');

-- Query using a Solr expression
SELECT name, id FROM persioninfo
  WHERE search_query = '+city:hangzhou +age:[1 TO 18]';