You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Drew Kutcharian <dr...@venarc.com> on 2014/01/20 23:14:48 UTC
Data modeling users table with CQL
Hey Guys,
I’m new to CQL (but have been using C* for a while now). What would be the best way to model a users table using CQL/Cassandra 2.0 Lightweight Transactions where we would like to have:
- A unique TimeUUID as the primary key of the user
- A unique email address used for logging in
In the past I would use Zookeeper and/or Astyanax’s "Uniqueness Constraint” but I want to see how can this be handled natively.
Cheers,
Drew
Re: Data modeling users table with CQL
Posted by Tupshin Harper <tu...@tupshin.com>.
It's a broad topic, but I mean all of the best practices alluded to by
writeups like this.
http://www.technicalinfo.net/papers/WebBasedSessionManagement.html
-Tupshin
On Jan 21, 2014 11:37 AM, "Drew Kutcharian" <dr...@venarc.com> wrote:
> Cool. BTW, what do you mean by have additional session tracking ids?
> What’d that be for?
>
> - Drew
>
> On Jan 21, 2014, at 10:48 AM, Tupshin Harper <tu...@tupshin.com> wrote:
>
> It does sound right.
>
> You might want to have additional session tracking id's, separate from
> the user id, but that is an additional implementation detail, and could be
> external to Cassandra. But the approach you describe accurately describes
> what I would do as a first pass, at least.
>
> -Tupshin
> On Jan 21, 2014 10:41 AM, "Drew Kutcharian" <dr...@venarc.com> wrote:
>
>> Thanks, I was actually thinking of doing that. Something along the lines
>> of
>>
>> CREATE TABLE user (
>> id timeuuid PRIMARY KEY,
>> email text,
>> name text,
>> ...
>> );
>>
>> CREATE TABLE user_email_index (
>> email text,
>> id timeuuid,
>> PRIMARY KEY (email, id)
>> );
>>
>> And during registration, I would just use LWT on the user_email_index
>> table first and insert the record and then insert the actual user record
>> into user table w/o LWT. Does that sound right to you?
>>
>> - Drew
>>
>>
>>
>> On Jan 21, 2014, at 10:01 AM, Tupshin Harper <tu...@tupshin.com> wrote:
>>
>> One CQL row per user, keyed off of the UUID.
>>
>> Another table keyed off of email, with another column containing the UUID
>> for lookups in the first table. Only registration will require a
>> lightweight transaction, and only for the purpose of avoiding duplicate
>> email registration race conditions.
>>
>> -Tupshin
>> On Jan 21, 2014 9:17 AM, "Drew Kutcharian" <dr...@venarc.com> wrote:
>>
>>> A shameful bump ;)
>>>
>>> > On Jan 20, 2014, at 2:14 PM, Drew Kutcharian <dr...@venarc.com> wrote:
>>> >
>>> > Hey Guys,
>>> >
>>> > I’m new to CQL (but have been using C* for a while now). What would be
>>> the best way to model a users table using CQL/Cassandra 2.0 Lightweight
>>> Transactions where we would like to have:
>>> > - A unique TimeUUID as the primary key of the user
>>> > - A unique email address used for logging in
>>> >
>>> > In the past I would use Zookeeper and/or Astyanax’s "Uniqueness
>>> Constraint” but I want to see how can this be handled natively.
>>> >
>>> > Cheers,
>>> >
>>> > Drew
>>> >
>>>
>>
>>
>
Re: Data modeling users table with CQL
Posted by Drew Kutcharian <dr...@venarc.com>.
Cool. BTW, what do you mean by have additional session tracking ids? What’d that be for?
- Drew
On Jan 21, 2014, at 10:48 AM, Tupshin Harper <tu...@tupshin.com> wrote:
> It does sound right.
>
> You might want to have additional session tracking id's, separate from the user id, but that is an additional implementation detail, and could be external to Cassandra. But the approach you describe accurately describes what I would do as a first pass, at least.
>
> -Tupshin
>
> On Jan 21, 2014 10:41 AM, "Drew Kutcharian" <dr...@venarc.com> wrote:
> Thanks, I was actually thinking of doing that. Something along the lines of
>
> CREATE TABLE user (
> id timeuuid PRIMARY KEY,
> email text,
> name text,
> ...
> );
>
> CREATE TABLE user_email_index (
> email text,
> id timeuuid,
> PRIMARY KEY (email, id)
> );
>
> And during registration, I would just use LWT on the user_email_index table first and insert the record and then insert the actual user record into user table w/o LWT. Does that sound right to you?
>
> - Drew
>
>
>
> On Jan 21, 2014, at 10:01 AM, Tupshin Harper <tu...@tupshin.com> wrote:
>
>> One CQL row per user, keyed off of the UUID.
>>
>> Another table keyed off of email, with another column containing the UUID for lookups in the first table. Only registration will require a lightweight transaction, and only for the purpose of avoiding duplicate email registration race conditions.
>>
>> -Tupshin
>>
>> On Jan 21, 2014 9:17 AM, "Drew Kutcharian" <dr...@venarc.com> wrote:
>> A shameful bump ;)
>>
>> > On Jan 20, 2014, at 2:14 PM, Drew Kutcharian <dr...@venarc.com> wrote:
>> >
>> > Hey Guys,
>> >
>> > I’m new to CQL (but have been using C* for a while now). What would be the best way to model a users table using CQL/Cassandra 2.0 Lightweight Transactions where we would like to have:
>> > - A unique TimeUUID as the primary key of the user
>> > - A unique email address used for logging in
>> >
>> > In the past I would use Zookeeper and/or Astyanax’s "Uniqueness Constraint” but I want to see how can this be handled natively.
>> >
>> > Cheers,
>> >
>> > Drew
>> >
>
Re: Data modeling users table with CQL
Posted by Tupshin Harper <tu...@tupshin.com>.
It does sound right.
You might want to have additional session tracking id's, separate from the
user id, but that is an additional implementation detail, and could be
external to Cassandra. But the approach you describe accurately describes
what I would do as a first pass, at least.
-Tupshin
On Jan 21, 2014 10:41 AM, "Drew Kutcharian" <dr...@venarc.com> wrote:
> Thanks, I was actually thinking of doing that. Something along the lines
> of
>
> CREATE TABLE user (
> id timeuuid PRIMARY KEY,
> email text,
> name text,
> ...
> );
>
> CREATE TABLE user_email_index (
> email text,
> id timeuuid,
> PRIMARY KEY (email, id)
> );
>
> And during registration, I would just use LWT on the user_email_index
> table first and insert the record and then insert the actual user record
> into user table w/o LWT. Does that sound right to you?
>
> - Drew
>
>
>
> On Jan 21, 2014, at 10:01 AM, Tupshin Harper <tu...@tupshin.com> wrote:
>
> One CQL row per user, keyed off of the UUID.
>
> Another table keyed off of email, with another column containing the UUID
> for lookups in the first table. Only registration will require a
> lightweight transaction, and only for the purpose of avoiding duplicate
> email registration race conditions.
>
> -Tupshin
> On Jan 21, 2014 9:17 AM, "Drew Kutcharian" <dr...@venarc.com> wrote:
>
>> A shameful bump ;)
>>
>> > On Jan 20, 2014, at 2:14 PM, Drew Kutcharian <dr...@venarc.com> wrote:
>> >
>> > Hey Guys,
>> >
>> > I’m new to CQL (but have been using C* for a while now). What would be
>> the best way to model a users table using CQL/Cassandra 2.0 Lightweight
>> Transactions where we would like to have:
>> > - A unique TimeUUID as the primary key of the user
>> > - A unique email address used for logging in
>> >
>> > In the past I would use Zookeeper and/or Astyanax’s "Uniqueness
>> Constraint” but I want to see how can this be handled natively.
>> >
>> > Cheers,
>> >
>> > Drew
>> >
>>
>
>
Re: Data modeling users table with CQL
Posted by Drew Kutcharian <dr...@venarc.com>.
You’re right. I didn’t catch that. No need to have email in the PRIMARY KEY.
On Jan 21, 2014, at 5:11 PM, Jon Ribbens <jo...@unequivocal.co.uk> wrote:
> On Tue, Jan 21, 2014 at 10:40:39AM -0800, Drew Kutcharian wrote:
>> Thanks, I was actually thinking of doing that. Something along the lines
>> of
>> CREATE TABLE user (
>> id timeuuid PRIMARY KEY,
>> email text,
>> name text,
>> ...
>> );
>> CREATE TABLE user_email_index (
>> email text,
>> id timeuuid,
>> PRIMARY KEY (email, id)
>> );
>> And during registration, I would just use LWT on the user_email_index
>> table first and insert the record and then insert the actual user record
>> into user table w/o LWT. Does that sound right to you?
>
> Yes, although unless I'm confused you don't need "id" in the
> primary key on "user_email_index", just "PRIMARY KEY (email)".
Re: Data modeling users table with CQL
Posted by Jon Ribbens <jo...@unequivocal.co.uk>.
On Tue, Jan 21, 2014 at 10:40:39AM -0800, Drew Kutcharian wrote:
> Thanks, I was actually thinking of doing that. Something along the lines
> of
> CREATE TABLE user (
> id timeuuid PRIMARY KEY,
> email text,
> name text,
> ...
> );
> CREATE TABLE user_email_index (
> email text,
> id timeuuid,
> PRIMARY KEY (email, id)
> );
> And during registration, I would just use LWT on the user_email_index
> table first and insert the record and then insert the actual user record
> into user table w/o LWT. Does that sound right to you?
Yes, although unless I'm confused you don't need "id" in the
primary key on "user_email_index", just "PRIMARY KEY (email)".
Re: Data modeling users table with CQL
Posted by Drew Kutcharian <dr...@venarc.com>.
Thanks, I was actually thinking of doing that. Something along the lines of
CREATE TABLE user (
id timeuuid PRIMARY KEY,
email text,
name text,
...
);
CREATE TABLE user_email_index (
email text,
id timeuuid,
PRIMARY KEY (email, id)
);
And during registration, I would just use LWT on the user_email_index table first and insert the record and then insert the actual user record into user table w/o LWT. Does that sound right to you?
- Drew
On Jan 21, 2014, at 10:01 AM, Tupshin Harper <tu...@tupshin.com> wrote:
> One CQL row per user, keyed off of the UUID.
>
> Another table keyed off of email, with another column containing the UUID for lookups in the first table. Only registration will require a lightweight transaction, and only for the purpose of avoiding duplicate email registration race conditions.
>
> -Tupshin
>
> On Jan 21, 2014 9:17 AM, "Drew Kutcharian" <dr...@venarc.com> wrote:
> A shameful bump ;)
>
> > On Jan 20, 2014, at 2:14 PM, Drew Kutcharian <dr...@venarc.com> wrote:
> >
> > Hey Guys,
> >
> > I’m new to CQL (but have been using C* for a while now). What would be the best way to model a users table using CQL/Cassandra 2.0 Lightweight Transactions where we would like to have:
> > - A unique TimeUUID as the primary key of the user
> > - A unique email address used for logging in
> >
> > In the past I would use Zookeeper and/or Astyanax’s "Uniqueness Constraint” but I want to see how can this be handled natively.
> >
> > Cheers,
> >
> > Drew
> >
Re: Data modeling users table with CQL
Posted by Tupshin Harper <tu...@tupshin.com>.
One CQL row per user, keyed off of the UUID.
Another table keyed off of email, with another column containing the UUID
for lookups in the first table. Only registration will require a
lightweight transaction, and only for the purpose of avoiding duplicate
email registration race conditions.
-Tupshin
On Jan 21, 2014 9:17 AM, "Drew Kutcharian" <dr...@venarc.com> wrote:
> A shameful bump ;)
>
> > On Jan 20, 2014, at 2:14 PM, Drew Kutcharian <dr...@venarc.com> wrote:
> >
> > Hey Guys,
> >
> > I’m new to CQL (but have been using C* for a while now). What would be
> the best way to model a users table using CQL/Cassandra 2.0 Lightweight
> Transactions where we would like to have:
> > - A unique TimeUUID as the primary key of the user
> > - A unique email address used for logging in
> >
> > In the past I would use Zookeeper and/or Astyanax’s "Uniqueness
> Constraint” but I want to see how can this be handled natively.
> >
> > Cheers,
> >
> > Drew
> >
>
Re: Data modeling users table with CQL
Posted by Drew Kutcharian <dr...@venarc.com>.
A shameful bump ;)
> On Jan 20, 2014, at 2:14 PM, Drew Kutcharian <dr...@venarc.com> wrote:
>
> Hey Guys,
>
> I’m new to CQL (but have been using C* for a while now). What would be the best way to model a users table using CQL/Cassandra 2.0 Lightweight Transactions where we would like to have:
> - A unique TimeUUID as the primary key of the user
> - A unique email address used for logging in
>
> In the past I would use Zookeeper and/or Astyanax’s "Uniqueness Constraint” but I want to see how can this be handled natively.
>
> Cheers,
>
> Drew
>