Re: Limit 1
As far as I know, the answer is yes, however it is unlikely that the cursor will have to probe very far to find a valid row unless your data is highly bursty. The key cache (assuming you have it enabled) will allow the query to skip unrelated rows in its search. However I would caution against TTL'ing the world and generating a 1-to-1 ratio of writes to deletes. One approach you can try is to compound your primary key with the hour. Then the latest hour of events can be retrieved by PK lookup. If you delete older rows that are outside of the partition you're operating on, your cursor will not have to skip tombstones to find valid results. On Wed, Apr 20, 2016 at 11:37 AM, Jimmy Lin <y2k...@gmail.com> wrote: > I have a following table(using default sized tier compaction) that its > column get TTLed every hour(as we want to keep only the last 1 hour events) > > And I do > Select * from mytable where object_id = ‘’ LIMIT 1; > > And since query only interested in last/latest value, will cassandra need > to scan multiple sstables or potentially skipping tombstones data just to > get the top of the latest data? > > Or is it smart enough to know the beginning of the sstables and get the > result very efficiently? > > > CREATE TABLE mytable ( > object_id text, > created timeuuid, > my_data text > PRIMARY KEY (object_id, created) > ) WITH CLUSTERING ORDER BY (created DESC) >
Limit 1
I have a following table(using default sized tier compaction) that its column get TTLed every hour(as we want to keep only the last 1 hour events) And I do Select * from mytable where object_id = ‘’ LIMIT 1; And since query only interested in last/latest value, will cassandra need to scan multiple sstables or potentially skipping tombstones data just to get the top of the latest data? Or is it smart enough to know the beginning of the sstables and get the result very efficiently? CREATE TABLE mytable ( object_id text, created timeuuid, my_data text PRIMARY KEY (object_id, created) ) WITH CLUSTERING ORDER BY (created DESC)
Re: Return list by limit 1 which is NOT null
No, you will need to filter client side. Can you do it in a single row ? Cheers - Aaron Morton Freelance Developer @aaronmorton http://www.thelastpickle.com On 24/01/2012, at 10:00 AM, Eric Martell wrote: HI, I am trying to create a keys list which I will fetch the key and then delete the same key in the subsequent call. When I use the CLI list, It always returns first row due to tombstone. Is there a way I can specify to use the limit 1 and return NOT null value. Please let me now. Thanks and Regards. [default@test_keyspace] list test_kms_keys; Using default limit of 100 --- RowKey: 33 --- RowKey: 04 = (column=key_id, value=4, timestamp=132734935120) --- RowKey: 32 = (column=key_id, value=2, timestamp=1326231507383000) --- RowKey: 31 --- RowKey: 311313 5 Rows Returned. Elapsed time: 6 msec(s). [default@test_keyspace] list test_kms_keys limit 1; --- RowKey: 33 1 Row Returned. Elapsed time: 2 msec(s).
Re: Return list by limit 1 which is NOT null
Thanks Aaron. How do I implement that in the row? Does row will not have any tombstones? Please let me know. Thanks and Regards. --- On Tue, 1/24/12, aaron morton aa...@thelastpickle.com wrote: From: aaron morton aa...@thelastpickle.com Subject: Re: Return list by limit 1 which is NOT null To: user@cassandra.apache.org Date: Tuesday, January 24, 2012, 3:12 AM No, you will need to filter client side. Can you do it in a single row ? Cheers -Aaron MortonFreelance Developer@aaronmortonhttp://www.thelastpickle.com On 24/01/2012, at 10:00 AM, Eric Martell wrote: HI, I am trying to create a keys list which I will fetch the key and then delete the same key in the subsequent call. When I use the CLI list, It always returns first row due to tombstone. Is there a way I can specify to use the limit 1 and return NOT null value. Please let me now. Thanks and Regards. [default@test_keyspace] list test_kms_keys; Using default limit of 100 --- RowKey: 33 --- RowKey: 04 = (column=key_id, value=4, timestamp=132734935120) --- RowKey: 32 = (column=key_id, value=2, timestamp=1326231507383000) --- RowKey: 31 --- RowKey: 311313 5 Rows Returned. Elapsed time: 6 msec(s). [default@test_keyspace] list test_kms_keys limit 1; --- RowKey: 33 1 Row Returned. Elapsed time: 2 msec(s).
Re: Return list by limit 1 which is NOT null
Tombstoned rows are returned without any columns. Cheers - Aaron Morton Freelance Developer @aaronmorton http://www.thelastpickle.com On 25/01/2012, at 4:37 AM, Eric Martell wrote: Thanks Aaron. How do I implement that in the row? Does row will not have any tombstones? Please let me know. Thanks and Regards. --- On Tue, 1/24/12, aaron morton aa...@thelastpickle.com wrote: From: aaron morton aa...@thelastpickle.com Subject: Re: Return list by limit 1 which is NOT null To: user@cassandra.apache.org Date: Tuesday, January 24, 2012, 3:12 AM No, you will need to filter client side. Can you do it in a single row ? Cheers - Aaron Morton Freelance Developer @aaronmorton http://www.thelastpickle.com On 24/01/2012, at 10:00 AM, Eric Martell wrote: HI, I am trying to create a keys list which I will fetch the key and then delete the same key in the subsequent call. When I use the CLI list, It always returns first row due to tombstone. Is there a way I can specify to use the limit 1 and return NOT null value. Please let me now. Thanks and Regards. [default@test_keyspace] list test_kms_keys; Using default limit of 100 --- RowKey: 33 --- RowKey: 04 = (column=key_id, value=4, timestamp=132734935120) --- RowKey: 32 = (column=key_id, value=2, timestamp=1326231507383000) --- RowKey: 31 --- RowKey: 311313 5 Rows Returned. Elapsed time: 6 msec(s). [default@test_keyspace] list test_kms_keys limit 1; --- RowKey: 33 1 Row Returned. Elapsed time: 2 msec(s).
Return list by limit 1 which is NOT null
HI, I am trying to create a keys list which I will fetch the key and then delete the same key in the subsequent call. When I use the CLI list, It always returns first row due to tombstone. Is there a way I can specify to use the limit 1 and return NOT null value. Please let me now. Thanks and Regards. [default@test_keyspace] list test_kms_keys; Using default limit of 100 --- RowKey: 33 --- RowKey: 04 = (column=key_id, value=4, timestamp=132734935120) --- RowKey: 32 = (column=key_id, value=2, timestamp=1326231507383000) --- RowKey: 31 --- RowKey: 311313 5 Rows Returned. Elapsed time: 6 msec(s). [default@test_keyspace] list test_kms_keys limit 1; --- RowKey: 33 1 Row Returned. Elapsed time: 2 msec(s).
