Alternative for OR condition after where clause in select statement Cassandra

cassandracqlselectwhere

Is it possible in Cassandra to use multiple conditions union ed together after the where clause in a select statement like in any of the RDBMS. Here is my code :

SELECT * from TABLE_NAME WHERE COND1= 'something' OR COND2 = 'something';

Best Answer

Assuming COND is the name of your table's primary key, you can do:

SELECT * from TABLE_NAME WHERE COND1 in ('something', 'something');

So, there is no fully general OR operation, but it looks like this may be equivalent to what you were trying to do.

Remember, as you use CQL, that query planning is not meant to be one of its strengths. Cassandra code typically makes the assumption that you have huge amounts of data, so it will try to avoid doing any queries that might end up being expensive. In the RMDBS world, you structure your data according to intrinsic relationships (3rd normal form, etc), whereas in Cassandra, you structure your data according to the queries you expect to need. Denormalization is (forgive the pun) the norm.

Point is, CQL is intended to be a more familiar, friendly syntax for making Cassandra queries than the older Thrift RPC interface. It's not intended to be completely expressive and flexible the way that SQL is.

Related Solutions

Mysql – Create a temporary table in a SELECT statement without a separate CREATE TABLE

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)

From the manual found at http://dev.mysql.com/doc/refman/5.7/en/create-table.html

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

Sql – ny query for Cassandra as same as SQL:LIKE Condition

Since Cassandra 3.4 (3.5 recommended), LIKE queries can be achieved using a SSTable Attached Secondary Index (SASI).

For example:

CREATE TABLE cycling.cyclist_name ( 
  id UUID PRIMARY KEY, 
  lastname text, 
  firstname text
);

Creating the SASI as follows:

CREATE CUSTOM INDEX  fn_prefix ON cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex';

Then a prefix LIKE query is working:

SELECT * FROM cyclist_name WHERE firstname LIKE 'M%';
SELECT * FROM cyclist_name WHERE firstname LIKE 'Mic%';

These examples and more configuration options, like suffix queries, can be found in the documentation

A more in depth explanation about how SASI works can be found here.

Best Answer

Related Solutions

Mysql – Create a temporary table in a SELECT statement without a separate CREATE TABLE

Sql – ny query for Cassandra as same as SQL:LIKE Condition

Related Topic