data modeling - Cassandra Clustering Key not able to order by -


i new cassandra , trying figure out why cannot order logs created_at date. following table description, select result , select statement trying create.

    cassandra@cqlsh:mytable> describe table mytable.log;      create table mytable.log (         id uuid,         created_at timestamp,         deleted boolean,         level text,         message text,         obj text,         obj_name text,         origin text,         user int,         primary key (id, created_at)     ) clustering order (created_at desc)         , bloom_filter_fp_chance = 0.01         , caching = '{"keys":"all", "rows_per_partition":"none"}'         , comment = ''         , compaction = {'min_threshold': '4', 'class': 'org.apache.cassandra.db.compaction.sizetieredcompactionstrategy', 'max_threshold': '32'}         , compression = {'sstable_compression': 'org.apache.cassandra.io.compress.lz4compressor'}         , dclocal_read_repair_chance = 0.1         , default_time_to_live = 0         , gc_grace_seconds = 864000         , max_index_interval = 2048         , memtable_flush_period_in_ms = 0         , min_index_interval = 128         , read_repair_chance = 0.0         , speculative_retry = '99.0percentile';     create index deleted_idx on mytable.log (deleted);     create index level_idx on mytable.log (level);     create index message_idx on mytable.log (message);     create index origin_idx on mytable.log (origin);     create index user_idx on mytable.log (user);      cassandra@cqlsh:mytable> select *  mytable.log  "created_at" <= '2015-04-29 00:00:00' , "user" = 20 limit 10;       id                                   | created_at               | deleted | level | message | obj                                                                                                                                                                                                                                                                                                                                                | obj_name | origin          | user     --------------------------------------+--------------------------+---------+-------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-----------------+------      a98a98d5-5710-431b-a23d-d78ece882763 | 2015-04-28 19:18:34-0400 |   false |  net | updated |  {'prio': none, 'type_id': u'a', 'auth': none, 'is_free': false, 'ttl': 300l, 'active': true, 'domain_id': 32l, 'ordername': none, 'name': u'myrecord.mytable.net', 'created': '2015-04-14 17:44:23+00:00', 'modified': '2015-04-29 03:18:34.159619+00:00', 'id': 143l, 'content': u'192.213.216.16', 'change_date': 1430277514, 'owner_id': 20l} |   record | update_a_record |   20      893e9600-3d57-4b82-bdfd-41586023a90f | 2015-04-28 19:21:01-0400 |   false |  net | updated | {'prio': none, 'type_id': u'a', 'auth': none, 'is_free': false, 'ttl': 300l, 'active': true, 'domain_id': 32l, 'ordername': none, 'name': u'myrecord.mytable.net', 'created': '2015-04-14 17:44:23+00:00', 'modified': '2015-04-29 03:21:01.414393+00:00', 'id': 143l, 'content': u'192.213.15.16', 'change_date': 1430277661, 'owner_id': 20l} |   record | update_a_record |   20      f951b3ec-092a-4e9e-95c5-a6dce3363c29 | 2015-04-28 19:18:35-0400 |   false |  net | updated | {'prio': none, 'type_id': u'a', 'auth': none, 'is_free': false, 'ttl': 300l, 'active': true, 'domain_id': 32l, 'ordername': none, 'name': u'myrecord.mytable.net', 'created': '2015-04-14 17:44:23+00:00', 'modified': '2015-04-29 03:18:35.199869+00:00', 'id': 143l, 'content': u'192.213.15.16', 'change_date': 1430277515, 'owner_id': 20l} |   record | update_a_record |   20      db60ac52-39e9-4b46-accb-28a34b10579c | 2015-04-28 19:18:37-0400 |   false |  net | updated | {'prio': none, 'type_id': u'a', 'auth': none, 'is_free': false, 'ttl': 300l, 'active': true, 'domain_id': 32l, 'ordername': none, 'name': u'myrecord.mytable.net', 'created': '2015-04-14 17:44:23+00:00', 'modified': '2015-04-29 03:18:37.650135+00:00', 'id': 143l, 'content': u'192.213.15.16', 'change_date': 1430277517, 'owner_id': 20l} |   record | update_a_record |   20      336acc47-6a93-4ff9-a6c5-d29d3b2c4e35 | 2015-04-28 19:23:24-0400 |   false |  net | updated | {'prio': none, 'type_id': u'a', 'auth': none, 'is_free': false, 'ttl': 300l, 'active': true, 'domain_id': 32l, 'ordername': none, 'name': u'myrecord.mytable.net', 'created': '2015-04-14 17:44:23+00:00', 'modified': '2015-04-29 03:23:24.146505+00:00', 'id': 143l, 'content': u'192.213.15.16', 'change_date': 1430277804, 'owner_id': 20l} |   record | update_a_record |   20      4ca66f70-36cb-47cc-9324-6a5747d6a592 | 2015-04-28 19:18:48-0400 |   false |  net | updated | {'prio': none, 'type_id': u'a', 'auth': none, 'is_free': false, 'ttl': 300l, 'active': true, 'domain_id': 32l, 'ordername': none, 'name': u'myrecord.mytable.net', 'created': '2015-04-14 17:44:23+00:00', 'modified': '2015-04-29 03:18:48.242689+00:00', 'id': 143l, 'content': u'192.213.15.16', 'change_date': 1430277528, 'owner_id': 20l} |   record | update_a_record |   20      dbfda8bc-f6f2-4b97-b3c1-ccaff21338bb | 2015-04-28 19:18:32-0400 |   false |  net | updated | {'prio': none, 'type_id': u'a', 'auth': none, 'is_free': false, 'ttl': 300l, 'active': true, 'domain_id': 32l, 'ordername': none, 'name': u'myrecord.mytable.net', 'created': '2015-04-14 17:44:23+00:00', 'modified': '2015-04-29 03:18:32.857508+00:00', 'id': 143l, 'content': u'192.213.15.16', 'change_date': 1430277512, 'owner_id': 20l} |   record | update_a_record |   20      6c05779a-d3b8-40ac-84ee-af91a3bf6b15 | 2015-04-28 19:18:47-0400 |   false |  net | updated |  {'prio': none, 'type_id': u'a', 'auth': none, 'is_free': false, 'ttl': 300l, 'active': true, 'domain_id': 32l, 'ordername': none, 'name': u'myrecord.mytable.net', 'created': '2015-04-14 17:44:23+00:00', 'modified': '2015-04-29 03:18:47.181657+00:00', 'id': 143l, 'content': u'192.213.216.16', 'change_date': 1430277527, 'owner_id': 20l} |   record | update_a_record |   20      a037fb9d-cb58-4994-baad-88c441429199 | 2015-04-28 19:18:31-0400 |   false |  net | updated |  {'prio': none, 'type_id': u'a', 'auth': none, 'is_free': false, 'ttl': 300l, 'active': true, 'domain_id': 32l, 'ordername': none, 'name': u'myrecord.mytable.net', 'created': '2015-04-14 17:44:23+00:00', 'modified': '2015-04-29 03:18:31.680786+00:00', 'id': 143l, 'content': u'192.213.216.16', 'change_date': 1430277511, 'owner_id': 20l} |   record | update_a_record |   20      66ee42af-6770-4ef8-a300-764246ccc8ff | 2015-04-28 19:20:33-0400 |   false |  net | updated | {'prio': none, 'type_id': u'a', 'auth': none, 'is_free': false, 'ttl': 300l, 'active': true, 'domain_id': 32l, 'ordername': none, 'name': u'myrecord.mytable.net', 'created': '2015-04-14 17:44:23+00:00', 'modified': '2015-04-29 03:20:33.336544+00:00', 'id': 143l, 'content': u'192.213.15.16', 'change_date': 1430277633, 'owner_id': 20l} |   record | update_a_record |   20

what don't understand doesn't order created_at column in descending order. end goal store logs of app in table , able show few of them in dashboard why limit of 10.

what doing wrong here? regards

what don't understand doesn't order created_at column in descending order.

because cassandra enforce clustering order within partition key. partition key id. looks has unique level of cardinality. unique, if partition on won't have data within make sorting worthwhile.

select *  mytable.log   "created_at" <= '2015-04-29 00:00:00' , "user" = 20 limit 10;

to satisfy query, should create separate query table partitioned user, such logbyuser. you'll want table have same columns, primary key definition this:

primary key (user, created_at, id)

this primary key definition allow following query function expect:

select *  mytable.logbyuser   "created_at" <= '2015-04-29 00:00:00' , "user" = 20 limit 10;

also, i'd point out 2 things:

  1. cassandra functions best when design data model fit query patterns. may mean creating table each query. crazy might sound, creating 5 or 6 tables suit each of potential queries perform much better adding 5 secondary indexes 1 table.

  2. secondary indexes meant convenience, not performance. use known cassandra anti-pattern. using them on low-cardinality columns (especially booleans) asking trouble. not intended "magic bullet" bridge shortcomings of data model.


Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more