I am currently trying to paginate query results from DSE 4.6.1 (Cassandra 2.0.12.200) with Python and cqlengine 0.21.0.
My table being queried is:
CREATE TABLE tags_for_search (
village_id int,
tag_prefix text,
tag text,
time timeuuid,
author_id int,
tag_id uuid,
type text,
PRIMARY KEY ((village_id, tag_prefix), tag, time)
) WITH CLUSTERING ORDER BY (time DESC) AND
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='' AND
dclocal_read_repair_chance=0.100000 AND
gc_grace_seconds=864000 AND
index_interval=128 AND
read_repair_chance=0.000000 AND
populate_io_cache_on_flush='false' AND
default_time_to_live=0 AND
speculative_retry='99.0PERCENTILE' AND
memtable_flush_period_in_ms=0 AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'LZ4Compressor'};
Is there an alternative for paging/pagination (DataStax Enterprise / DSE 4.6.1) of results in Python (using cqlengine 0.21.0)? The documented solution (http://cqlengine.readthedocs.org/en/latest/topics/queryset.html#token-function) appears to be broken due to #7016.
My initial query for data is:
SELECT * FROM that_keyspace.tags_for_search WHERE "tag_prefix" = 'der' AND "tag_text" = '#derpy1' AND "village_id" = 1 LIMIT 10000;
Or in Python via cqlengine:
village_tags = VillageSearch.objects.filter(village_id=1, tag_prefix='der', tag_text='#derpy1')
tags_data = []
for village_tag in village_tags:
tags_data.append(village_tag.to_dict())
first_page = village_tags
last = first_page[-1]
next_page = list(village_tags.filter(pk__token__gt=cqlengine.Token(last.pk)))
It is throwing the error:
code=2200 [Invalid query] message="Column "village_id" cannot be restricted by both an equality and an inequality relation"
Is there an alternative that I can use to avoid this bug for immediate usage?
Thank you for any assistance you are able to provide!
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT
*
FROM
that_keyspace.tags_for_search
WHERE
'tag_prefix' = 'der'
AND 'tag_text' = '#derpy1'
AND 'village_id' = 1 LIMIT 10000