Return-Path: Delivered-To: apmail-cassandra-user-archive@www.apache.org Received: (qmail 80240 invoked from network); 6 Oct 2010 20:01:06 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 6 Oct 2010 20:01:06 -0000 Received: (qmail 32188 invoked by uid 500); 6 Oct 2010 20:01:05 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 32073 invoked by uid 500); 6 Oct 2010 20:01:05 -0000 Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cassandra.apache.org Delivered-To: mailing list user@cassandra.apache.org Received: (qmail 32065 invoked by uid 99); 6 Oct 2010 20:01:04 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 06 Oct 2010 20:01:04 +0000 X-ASF-Spam-Status: No, hits=2.9 required=10.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_NONE,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (nike.apache.org: local policy) Received: from [209.85.215.44] (HELO mail-ew0-f44.google.com) (209.85.215.44) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 06 Oct 2010 20:00:57 +0000 Received: by ewy26 with SMTP id 26so4015763ewy.31 for ; Wed, 06 Oct 2010 13:00:36 -0700 (PDT) MIME-Version: 1.0 Received: by 10.213.35.129 with SMTP id p1mr9900896ebd.44.1286395235876; Wed, 06 Oct 2010 13:00:35 -0700 (PDT) Received: by 10.14.29.66 with HTTP; Wed, 6 Oct 2010 13:00:35 -0700 (PDT) X-Originating-IP: [108.117.1.170] In-Reply-To: <659AF4B5-9C92-4FBA-B20C-00DCDDB82E21@grnoc.iu.edu> References: <649A15D5-25BF-47A0-B6D0-007EA1C93947@grnoc.iu.edu> <659AF4B5-9C92-4FBA-B20C-00DCDDB82E21@grnoc.iu.edu> Date: Wed, 6 Oct 2010 15:00:35 -0500 Message-ID: Subject: Re: get keys based on values?? From: Matthew Dennis To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=0015174c1c121340620491f83b36 X-Virus-Checked: Checked by ClamAV on apache.org --0015174c1c121340620491f83b36 Content-Type: text/plain; charset=ISO-8859-1 As jbellis mentioned, the secondary indexes with > will work for this but in the mean time you can still index this manually in .6 (which will continue to work in .7 if need be). There are several ways to attack this now. If you don't have too many users you can have a row with "age" as the row key and then each column name will be the age of the user and the column value would be the row key for the user. C* will order the columns in that row by the column name so you could slice on them to get all the ids for the users in question. Keep in mind that this will create one row with an entry for every user so if you have lots of users that could be a big row with all the associated problems. If you have too much data for the above, you can create rows with the age as the row key and column names as the row id with that age. Then when you want to query for all the users with ages > 33 you would start at 34 then issue slice calls to each of those rows with a count of say 1000 to get the user ids, then multiget with those ids to get the users. If you have two much data for that and/or want better distribution of your indexes across the cluster, you can add a second level of indexes. You have one row with a row key of 33 that contains UUID column names representing the row keys of other rows that all contain user ids that have age 33. Then when you want to look up users with age 33, you query that top level row, get all the UUIDs for the other rows, query those to give you the ids of users with that age and then retrieve them. When you add a new user, query that first row to get all the row keys for the rows containing the users age and pick one randomly to write to. This has the obvious problem of reading before writing so keep that in mind. An alternative to having a second level of indexing but still splitting up your index to multiple rows you could have multiple hash functions (as many hash functions as rows you want to split up). Then when given 33, you pass it through all your different hash functions to return the rows that contain ids for users with age 33. On Wed, Oct 6, 2010 at 1:49 PM, Brayton Thompson wrote: > Ok, let me tweak the scenario a tiny bit. What if I wanted something > extremely arbitrary, for instance... simple comparisons like a WHERE clause > in SQL.... > > get Users.someuser['uuid'] where Users.someuser['age'] > 33 > > From what i've read this functionality defeats the point of Cassandra > because instead of indexing directly to a value C* would have to got to a > value and run a check for every entry. Am I correct here? > > So would my best bet be to simply get ALL of my users uuids and ages, then > throw away all of those that do not meet the required test? > > Thank you. > > On Oct 6, 2010, at 2:09 PM, Matthew Dennis wrote: > > As Norman said, secondary indexes are only in .7 but you can create > standard indexes in both .6 and .7 > > Basically have a email_domain_idx CF where the row key is the domain and > the column names have the row id of the user (the column value is unused in > this scenario). This sounds basically like what you described in your > original post. That's a very common way to do it in Cassandra (C*). This > is not all that different to what MySQL, PGSQl, etc do for you > automatically, just in C* you have to do it manually and remember to write > to that index column family whenever you write to the users CF. > > On Wed, Oct 6, 2010 at 12:56 PM, Brayton Thompson wrote: > >> Are secondary index's available in .6.5? or are they only in .7? >> >> On Oct 6, 2010, at 1:15 PM, Tyler Hobbs wrote: >> >> If you're interested in only checking part of a column's value, you can >> generally >> just store that part of the value in a different column. So, have an >> "email_addr" column >> and a "email_domain" column, which stores "aol.com", for example. >> >> Then you can just use a secondary index on the "email_domain" column. >> >> - Tyler >> >> On Wed, Oct 6, 2010 at 10:33 AM, Brayton Thompson wrote: >> >>> -----BEGIN PGP SIGNED MESSAGE----- >>> Hash: SHA1 >>> >>> Ok, I am VERY new to Cassandra and trying to get my head around its core >>> ideas. >>> >>> So lets say I have a CF of Users that contains all the info I would ever >>> want to know about them. One day I decide(for some reason) that I want to >>> send a mass email to only the users with AOL email addresses. Is there a >>> mechanism for getting only keys whose email attribute contains the string @ >>> aol.com ? Or is this frowned upon? I could also envision separate CF's >>> for each email type; that stored values to use as keys into my Users CF. Say >>> the AOL CF contains the usernames of everyone that has an aol account. So I >>> would pull all of the keys from that CF and then use them to index into the >>> Users CF to pull their email addresses. It seems to me that this is >>> redundant. So I would like your thoughts on my example. >>> >>> Thank you, >>> Brayton Thompson >>> thompsbp@grnoc.iu.edu >>> Global Research Network Operation Center >>> Indiana University >>> -----BEGIN PGP SIGNATURE----- >>> Version: GnuPG/MacGPG2 v2.0.14 (Darwin) >>> >>> iQIcBAEBAgAGBQJMrJa1AAoJENisXTckM+p9ffcP/1UmNDyWxDnOu41ZRcVwmJiE >>> +47QxqNc57WmdXX86FUvcauhPFFNZfbrbGwA61sof1sktSOL83osOXQuOfGr5GvT >>> tulU3+rQ1B+ea0x+aBESbKZwXHxckLGdst2Hro1eCVXEna+VvqkxNJ2rvYzE3hNM >>> FTNBWDIv3JbOChTYBnycBqg1iG5yMDkc2xEHlaiw9S/VsOPU18pPYrf42eoSqgnk >>> /rZDCxxiThznuaLI70QnU3O7ZTiyXpavN8BUW6KoeDZNAypgg1AayhEL2d67zZWu >>> qtnGEpoIeieinjccWMpkUrv2f14CZQ5gbJSLwPdoNLItYLnFvGHg0Ca/hXhrkIDr >>> BqnA0R5w2YHB+5p84gvj1NTRE0O2kXcUHkLDDBvnlLKUOUkoDyqr5tGAIwHhIwA7 >>> hpko76CyGN84bS8Kma+1D6e8wg9zqfiS9mvvErJCUOwyU5e+XeoiCdyhwgDHJKlW >>> T5UjMXdAHwyZly48J5l6jEJastHsL1wKAHeV/NlQ1gEx2CmnnJ0lBPDPqlT5Lxdb >>> uQFzS/YhFzxWL2gApHKF8EdCz4jFbPUggYYPsVgfYkNNBISgcIiQaEIIPkri96vb >>> V/xhnxLrFCO20NnGQ5PCTzCnZptyc3V+9WI542fnRGcS8SbF+N5BdLzoJBjtidrI >>> a/Nps/KUhJ5kVzJ0o8H3 >>> =oBhH >>> -----END PGP SIGNATURE----- >>> >> >> >> > > > -- > Riptano > Software and Support for Apache Cassandra > http://www.riptano.com/ > mdennis@riptano.com > m: 512.587.0900 f: 866.583.2068 > > > -- Riptano Software and Support for Apache Cassandra http://www.riptano.com/ mdennis@riptano.com m: 512.587.0900 f: 866.583.2068 --0015174c1c121340620491f83b36 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable As jbellis mentioned, the secondary indexes with > will work for this bu= t in the mean time you can still index this manually in .6 (which will cont= inue to work in .7 if need be).

There are several ways to attack thi= s now.=A0 If you don't have too many users you can have a row with &quo= t;age" as the row key and then each column name will be the age of the= user and the column value would be the row key for the user.=A0 C* will or= der the columns in that row by the column name so you could slice on them t= o get all the ids for the users in question.=A0 Keep in mind that this will= create one row with an entry for every user so if you have lots of users t= hat could be a big row with all the associated problems.

If you have too much data for the above, you can create rows with the a= ge as the row key and column names as the row id with that age.=A0 Then whe= n you want to query for all the users with ages > 33 you would start at = 34 then issue slice calls to each of those rows with a count of say 1000 to= get the user ids, then multiget with those ids to get the users.

If you have two much data for that and/or want better distribution of y= our indexes across the cluster, you can add a second level of indexes.=A0 Y= ou have one row with a row key of 33 that contains UUID column names repres= enting the row keys of other rows that all contain user ids that have age 3= 3.=A0 Then when you want to look up users with age 33, you query that top l= evel row, get all the UUIDs for the other rows, query those to give you the= ids of users with that age and then retrieve them.=A0 When you add a new u= ser, query that first row to get all the row keys for the rows containing t= he users age and pick one randomly to write to.=A0 This has the obvious pro= blem of reading before writing so keep that in mind.

An alternative to having a second level of indexing but still splitting= up your index to multiple rows you could have multiple hash functions (as = many hash functions as rows you want to split up).=A0 Then when given 33, y= ou pass it through all your different hash functions to return the rows tha= t contain ids for users with age 33.

On Wed, Oct 6, 2010 at 1:49 PM, Brayton Thom= pson <thompsb= p@grnoc.iu.edu> wrote:
Ok, let me tweak the scenario a tiny = bit. What if I wanted something extremely arbitrary, for instance... simple= comparisons like a WHERE clause in SQL.... =A0=A0

get U= sers.someuser['uuid'] where Users.someuser['age'] =A0> = =A033

From what i've read this functionality defeats= the point of Cassandra because instead of indexing directly to a value C* = would have to got to a value and run a check for every entry. =A0Am I corre= ct here?

So would my best bet be to simply get ALL of my users u= uids and ages, then throw away all of those that do not meet the required t= est?

Thank you.

On Oct 6, 2010, at 2:09 PM, Matthew Dennis wrote:
=
As Norman said, secondary indexes are only in= .7 but you can create standard indexes in both .6 and .7

Basically = have a email_domain_idx CF where the row key is the domain and the column n= ames have the row id of the user (the column value is unused in this scenar= io).=A0 This sounds basically like what you described in your original post= .=A0 That's a very common way to do it in Cassandra (C*).=A0 This is no= t all that different to what MySQL, PGSQl, etc do for you automatically, ju= st in C* you have to do it manually and remember to write to that index col= umn family whenever you write to the users CF.

On Wed, Oct 6, 2010 at 12:56 PM, Brayton Tho= mpson <thompsbp@grnoc.iu.edu> wrote:
Are secondary index's available i= n .6.5? or are they only in .7?

On O= ct 6, 2010, at 1:15 PM, Tyler Hobbs wrote:

If you're interested in only checking part of a column's value, you= can generally
just store that part of the value in a different column.= =A0 So, have an "email_addr" column
and a "email_domain&q= uot; column, which stores "aol.com", for example.

Then you can just use a secondary index on the "email_domain"= column.

- Tyler

On Wed, Oct 6, 20= 10 at 10:33 AM, Brayton Thompson <thompsbp@grnoc.iu.edu>= wrote:
-----BEGIN PGP SI= GNED MESSAGE-----
Hash: SHA1

Ok, I am VERY new to Cassandra and trying to get my head around its core id= eas.

So lets say I have a CF of Users that contains all the info I would ever wa= nt to know about them. One day I decide(for some reason) that I want to sen= d a mass email to only the users with AOL email addresses. Is there a mecha= nism for getting only keys whose email attribute contains the string @aol.com ? Or is this frowned u= pon? I could also envision separate CF's for each email type; that stor= ed values to use as keys into my Users CF. Say the AOL CF contains the user= names of everyone that has an aol account. So I would pull all of the keys = from that CF and then use them to index into the Users CF to pull their ema= il addresses. =A0It seems to me that this is redundant. So I would like you= r thoughts on my example.

Thank you,
Brayton Thompson
thompsbp@grnoc.i= u.edu
Global Research Network Operation Center
Indiana University
-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.14 (Darwin)

iQIcBAEBAgAGBQJMrJa1AAoJENisXTckM+p9ffcP/1UmNDyWxDnOu41ZRcVwmJiE
+47QxqNc57WmdXX86FUvcauhPFFNZfbrbGwA61sof1sktSOL83osOXQuOfGr5GvT
tulU3+rQ1B+ea0x+aBESbKZwXHxckLGdst2Hro1eCVXEna+VvqkxNJ2rvYzE3hNM
FTNBWDIv3JbOChTYBnycBqg1iG5yMDkc2xEHlaiw9S/VsOPU18pPYrf42eoSqgnk
/rZDCxxiThznuaLI70QnU3O7ZTiyXpavN8BUW6KoeDZNAypgg1AayhEL2d67zZWu
qtnGEpoIeieinjccWMpkUrv2f14CZQ5gbJSLwPdoNLItYLnFvGHg0Ca/hXhrkIDr
BqnA0R5w2YHB+5p84gvj1NTRE0O2kXcUHkLDDBvnlLKUOUkoDyqr5tGAIwHhIwA7
hpko76CyGN84bS8Kma+1D6e8wg9zqfiS9mvvErJCUOwyU5e+XeoiCdyhwgDHJKlW
T5UjMXdAHwyZly48J5l6jEJastHsL1wKAHeV/NlQ1gEx2CmnnJ0lBPDPqlT5Lxdb
uQFzS/YhFzxWL2gApHKF8EdCz4jFbPUggYYPsVgfYkNNBISgcIiQaEIIPkri96vb
V/xhnxLrFCO20NnGQ5PCTzCnZptyc3V+9WI542fnRGcS8SbF+N5BdLzoJBjtidrI
a/Nps/KUhJ5kVzJ0o8H3
=3DoBhH
-----END PGP SIGNATURE-----





--
Riptano
Software and Support for Apache Cassandr= a
http://www.ripta= no.com/
mdennis@riptano.co= m
m: 512.587.0900 f: 866.583.2068




--
Riptano
Software and Support for Apache Cassandr= a
http://www.ripta= no.com/
mdennis@riptano.co= m
m: 512.587.0900 f: 866.583.2068
--0015174c1c121340620491f83b36--