Cassandra CQLEngine Allow Filtering

Question

I'm using Python Cassandra Cqlengine extension. I create many-to-many table but I receive error in user_applications model query filtering process. I'm readed different resource for this problem, but I did not fully understand this problem.

Sources: https://ohioedge.com/2017/07/05/cassandra-primary-key-partitioning-key-clustering-key-a-simple-explanation/

Cassandra Allow filtering

Is ALLOW FILTERING in Cassandra for following query efficient?

Database Model:

class UserApplications(BaseModel):
    __table_name__ = "user_applications"

    user_id = columns.UUID(required=True, primary_key=True, index=True)
    application_id = columns.UUID(required=True, primary_key=True, index=True)
    membership_id = columns.UUID(required=True, primary_key=True, index=True)

Error Message:

Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

Python CQLEngine Code:

q = UserApplications.filter(membership_id=r.membership_id,
                                    user_id=r.user_id,
                                    application_id=r.application_id)

CQLEngine SQL Statements:

SELECT "id", "status", "created_date", "update_date" FROM db.user_applications WHERE "membership_id" = %(0)s AND "user_id" = %(1)s AND "application_id" = %(2)s LIMIT 10000

Describe Table Result:

CREATE TABLE db.user_applications (
    id uuid,
    user_id uuid,
    application_id uuid,
    membership_id uuid,
    created_date timestamp,
    status int,
    update_date timestamp,
    PRIMARY KEY (id, user_id, application_id, membership_id)
) WITH CLUSTERING ORDER BY (user_id ASC, application_id ASC, membership_id ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';
CREATE INDEX user_applications_membership_id_idx ON db.user_applications (membership_id);

Waiting your helps.

@AlexOtt I'm sorry, sentence is wrong. I edited question now. — rootturk, Aug 06 '18 at 08:10
What is the query you trying to execute that gives you this error? — Mohamed Ibrahim Elsayed, Aug 06 '18 at 08:31
can you perform `describe table user_applications;` (add keyspace before table name) in `cqlsh` and paste the output here... I'm really not sure that the table schema was generated correctly... — Alex Ott, Aug 06 '18 at 09:14
@AlexOtt Please check question, I'am added "describe table user_applications" command print. — rootturk, Aug 06 '18 at 10:20

score 3 · Accepted Answer · answered Aug 06 '18 at 11:26

The reason you are getting this error is that you are not adding ALLOW FILTERING flag to your query, if you add ALLOW FILTERING to the end of your query it should work.

Using ALLOW FILTERING in Cassandra queries actually allows cassandra to filter out some rows after loading them (maybe after it loads all rows from a table). For example in the case of your query the only way Cassandra can execute this query is by retrieving all the rows from the table UserApplications and then by filtering out the ones which do not have the requested value for the each of the columns your are restricting.

Using ALLOW FILTERING can have unpredictable performance outcomes and the actual performance depends on data distribution inside your table. If your table contains for example a 1 million rows and 95% of them have the requested value for the columns your are specifying the query will still be relatively efficient and you should use ALLOW FILTERING. On the other hand, if your table contains 1 million rows and only 2 rows contain the requested values , your query is extremely inefficient. Cassandra will load 999, 998 rows for nothing. In general if your queries require adding ALLOW FILTERING then probably you should rethink about your schema or add secondary indexes for the columns you are querying often.

In your case I suggest making columns membership_id, user_id, application_id as a composite partition key. If you do so you will no longer need to filter out any rows after loading because all rows having the same values for the three column will reside on the same partition (in the same physical node), and you should provide the three values in the query (you are already doing so in the query you added in the question). Here is the way you can do so:

CREATE TABLE db.user_applications (
    user_id uuid,
    application_id uuid,
    membership_id uuid,
    created_date timestamp,
    status int,
    update_date timestamp,
    PRIMARY KEY ((user_id, application_id, membership_id))
);

Cassandra CQLEngine Allow Filtering

1 Answers1

Linked