Querying in cassandra cql

Question

I am new to cassandra. suppose i have a table named customer with fields id as primary key,age, firstname and lastname. Also take it as there is so many datas in that table. How can query in that table to get my senario like, "a search query. that is is a select statement to obtain below senarios."

datas with have both firstname="something" and last name="something",that is query for satisfying both
If firstname=null and lastname='something that we given'
if lasttname=null and firstname'something that we given'

How can we query with a single cql query statement which satisfies both 3 conditions.

Is there is any way,.....

Chaity Chaity · Accepted Answer · 2018-12-19T08:51:02

You cannot achieve the result with one query. It's lot more different from RDBMS such as MySQL. There are some primary rules.

1st basic rule is you cannot query with a field if it's not mentioned in primary key. To be able to query with non-primary fields you've to mention ALLOW FILTERING which is not recommended and defeats the purpose of using C* for faster read in the first place. You can use secondary index on non-primary columns but there are some trade-off (you've to know where to use it).
2nd rule if there are more than one primary key, you've to mention fields in WHERE clause sequentially. You cannot query by clustering key without mentioning partition key or cannot query by 2nd clustering key without mentioning partition and 1st clustering key in WHERE clause and so on. (I assume you have studied basic things and know the terms partition or clustering key).
Primary keys cannot be NULL.

I am giving you an example below.

create table users(
    id text,
    firstname text,
    lastename text,
    primary key(id)
);

if your schema is like above, then you can only query by 'id'.

select * from users where id = ?;

you cannot query by first name or last name (they are not mentioned as key).

create table users(
    id text,
    firstname text,
    lastename text,
    primary key(firstname, id)
);

Here you can query by firstname or firstname and id.

select * from users where firstname = ? ;
select * from users where firstname = ? and id = ?;

1st query serves your 2nd requirement of searching with firstname. (Same as this you'd need another table where lastname as partition key for your 3rd requirement.) You cannot query by only id escaping firstname. By using ALLOW FILTERING you can query by id.

select * from users where id = ? ALLOW FILTERING;

create table users(
    id text,
    firstname text,
    lastename text,
    primary key(firstname, lastename, id)
);

Here you can query by firstname and lastname and id.

select * from users where firstname = ?;
select * from users where firstname = ? and lastname = ?;
select * from users where firstname = ? and lastname = ? and id = ?;

2nd query serves your 1st requirement.

Summary: You'd need at least 3 tables and denormalize/copy all data to 3 tables to serve your query. You can use only one table and index those other fields where it fits but performance will degrade. Materialized view (table per query) is much more faster than secondary index. Most importantly, here you can only search for exact match. Partial matching (like or % keywords in MySQL) will not work here. It is important to know the distributed nature and its internal architecture which upholds the data model described above.

Querying in cassandra cql

1 Answers