Hi , The best way will go with per query per table plan.. and distribute the common column into both tables. This will help you to support queries as well as Read and Write will be fast. Only Drawback will be, you have to insert common data into both tables at the same time which can be easily handled by the Client side.
On Mon, Jul 24, 2017 at 6:10 AM, Jonathan Haddad <j...@jonhaddad.com> wrote: > Using a different table to answer each query is the correct answer here > assuming there's a significant amount of data. > > If you don't have that much data, maybe you should consider using a > database like Postgres which gives you query flexibility instead of > horizontal scalability. > On Sun, Jul 23, 2017 at 1:10 PM techpyaasa . <techpya...@gmail.com> wrote: > >> Hi vladyu/varunbarala >> >> Instead of creating second table as you said can I just have one(first) >> table below and get all rows with status=0. >> >> CREATE TABLE IF NOT EXISTS test.user ( account_id bigint, pid bigint, >> disp_name text, status int, PRIMARY KEY (account_id, pid) ) WITH CLUSTERING >> ORDER BY (pid ASC); >>> >> >> I mean get all rows within same partition(account_id) whose status=0(say >> some value) using *UDF/UDA* in c* ? >> >>> >>> select group_by_status from test.user; >> >> >> where group_by_status is UDA/UDF >> >> >> Thanks in advance >> TechPyaasa >> >> >> On Sun, Jul 23, 2017 at 10:42 PM, Vladimir Yudovin <vla...@winguzone.com> >> wrote: >> >>> Hi, >>> >>> unfortunately ORDER BY is supported for clustering columns only... >>> >>> *Winguzone <https://winguzone.com?from=list> - Cloud Cassandra Hosting* >>> >>> >>> ---- On Sun, 23 Jul 2017 12:49:36 -0400 *techpyaasa . >>> <techpya...@gmail.com <techpya...@gmail.com>>* wrote ---- >>> >>> Hi Varun, >>> >>> Thanks a lot for your reply. >>> >>> In this case if I want to update status(status can be updated for given >>> account_id, pid) , I need to delete existing row in 2nd table & add new >>> one... :( :( >>> >>> Its like hitting cassandra twice for 1 change.. :( >>> >>> >>> >>> On Sun, Jul 23, 2017 at 8:42 PM, Varun Barala <varunbaral...@gmail.com> >>> wrote: >>> >>> Hi, >>> You can create pseudo index table. >>> >>> IMO, structure can be:- >>> >>> >>> CREATE TABLE IF NOT EXISTS test.user ( account_id bigint, pid bigint, >>> disp_name text, status int, PRIMARY KEY (account_id, pid) ) WITH CLUSTERING >>> ORDER BY (pid ASC); >>> CREATE TABLE IF NOT EXISTS test.user_index ( account_id bigint, pid bigint, >>> disp_name text, status int, PRIMARY KEY ((account_id, status), disp_name) ) >>> WITH CLUSTERING ORDER BY (disp_name ASC); >>> >>> to support query *:- select * from site24x7.wm_current_status where >>> uid=1 order by dispName asc;* >>> You can use *in condition* on last partition key *status *in table >>> *test.user_index.* >>> >>> >>> *It depends on your use case and amount of data as well. It can be >>> optimized more...* >>> Thanks!! >>> >>> On Sun, Jul 23, 2017 at 2:48 AM, techpyaasa . <techpya...@gmail.com> >>> wrote: >>> >>> Hi , >>> >>> We have a table like below : >>> >>> CREATE TABLE ks.cf ( accountId bigint, pid bigint, dispName text, >>> status int, PRIMARY KEY (accountId, pid) ) WITH CLUSTERING ORDER BY (pid >>> ASC); >>> >>> >>> >>> We would like to have following queries possible on the above table: >>> >>> select * from site24x7.wm_current_status where uid=1 and mid=1; >>> select * from site24x7.wm_current_status where uid=1 order by dispName >>> asc; >>> select * from site24x7.wm_current_status where uid=1 and status=0 order >>> by dispName asc; >>> >>> I know first query is possible by default , but I want the last 2 >>> queries also to work. >>> >>> So can some one please let me know how can I achieve the same in >>> cassandra(c*-2.1.17). I'm ok with applying indexes etc, >>> >>> Thanks >>> TechPyaasa >>> >>> >>> >>
