My data model:-
tid | codes | raw | type
-------------------------------------+--------------+--------------+------
a64fdd60-1bc4-11e5-9b30-3dca08b6a366 | {12, 34, 53} | {sdafb=safd} | cmd
CREATE TABLE MyTable (
tid TIMEUUID,
type TEXT,
codes SET<INT>,
raw TEXT,
PRIMARY KEY (tid)
);
CREATE INDEX ON myTable (codes);
How to query the table to return rows based on multiple set values.
This works:-
select * from logData where codes contains 34;
But i want to get row based on multiple set values and none of this works:-
select * from logData where codes contains 34, 12; or
select * from logData where codes contains 34 and 12; or
select * from logData where codes contains {34, 12};
Kindly assit.
The data model you are using is highly inefficient. Sets are meant to be used to get a set of data for a given primary key and not the other way round. If that is what is needed, you will have to rethink the model itself.
I would suggest creating different columns for each value you are using in a set and then using those columns as a composite primary key.
Are you really looking to get ALL log entries based on just codes? That could be quite a large dataset. Realistically, wouldn't you be looking at specific dates / date ranges? I'd key on that, and then use codes for filtering, or even filter on codes entirely on the client side.
If you have many codes, and you index on the sets, it might result in very high cardinality of the index, which would cause you issues. Whether you have your own lookup table, or use an index, remember that you essentially have a "table" where the pk is the value, and there are rows for that value for every "row" that matches the value. If that looks unacceptably large, then that's exactly what it is.
I'd recommend revisiting the requirement - again...do you really need all log entries EVER that match a certain code combination?
If you really do need to analyse the whole lot, then I'd recommend using Spark to run the job. You could then run a Spark job, and each node would deal with data on the same node; this will significantly reduce the impact compared to doing full table processing entirely in the application.
I know it's late. IMO the model with few minor changes would be sufficient to achieve what is expected. What one can do is to have as many rows as members of the power set of the set being queried.
Then the following queries are sufficient and do not need any filtering.
OR
If I create your table structure and insert a similar row to yours above, I can check for multiple values in the
codes
collection like this:Now as others have mentioned, let me also tell you why this is a terrible idea...
With a secondary index on the collection (and with the cardinality appearing to be fairly high) every node will have to be checked for each query. The idea with Cassandra, is to query by partition key as often as possible, that way you only have to hit one node per query. Apple's Richard Low wrote a great article called The sweet spot for Cassandra secondary indexes. It should make you re-think the way you use secondary indexes.
Secondly, the only way I could get Cassandra to accept this query, was to use ALLOW FILTERING. What this means, is that the only way Cassandra can apply all of your fitlering criteria (WHERE clause) is to pull back every row and individually filter-out the rows that do not meet your criteria. Horribly inefficient. To be clear, the ALLOW FILTERING directive is something that you should never use.
In any case, if
codes
are something that you will need to query by, then you should design an additional query table withcodes
as a part of the PRIMARY KEY.