0

I have a big Cassandra table with around 15 fields, and I wanted to create many materialized views to support all of my queries. The Primary Key on the base table is (CompanyName, ctime) where ctime is a timeuuid. The table records every click on a certain company website at a certain timestamp.

For instance, one of my queries includes searching what are the most used browsers on a certain time range (max. one year).

So I did

CREATE MATERIALIZED VIEW clicks_by_browser 
AS SELECT CompanyName, ctime, browsername
FROM companyclicks
WHERE CompanyName is not null AND ctime is not null AND browsername is not null
PRIMARY KEY(CompanyName, ctime, browsername)

But this does not work. When I do the query:

SELECT browsername, count(*) from clicks_by_browser
WHERE CompanyName='example' and ctime>=minTimeuuid(...) 
GROUP BY browsername;

Cassandra rejects it because "Group by only support groups of columns following their declared order in the primary key". So the issue is I declared ctime before browsername in the primary key.

So I tried inverting their order as clustering columns,

CREATE MATERIALIZED VIEW clicks_by_browser 
AS SELECT CompanyName, ctime, browsername
FROM companyclicks
WHERE CompanyName is not null AND ctime is not null AND browsername is not null
PRIMARY KEY(CompanyName, browsername, ctime)

But now Cassandra rejects the same query because ctime can't be restricted in the WHERE clause as preceding column browsername is not restricted.

So is such a query just not possible in Cassandra currently? Or am I missing something obvious?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.