You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Techy Teck <co...@gmail.com> on 2013/11/08 03:53:12 UTC
CQL Tables in Cassandra with an Index
I am using the below table in our use case -
create table test_new (
employee_id text,
employee_name text,
value text,
last_modified_date timeuuid,
primary key (employee_id, last_modified_date)
);
create index employee_name_idx on test_new (employee_name);
In my above table employee_id will be unique always starting from 1 till
32767. So our query pattern is like this -
1. Give me everything for any of the employee_id?
2. Give me everything for what has changed in last 5 minutes?
3. Give me everything for any of the employee_name?
I will be inserting below data into my above table -
insert into test_new (employee_id, employee_name, value,
last_modified_date) values ('1', 'e27', 'some_value', now());
insert into test_new (employee_id, employee_name, value,
last_modified_date) values ('2', 'e27', 'some_new_value', now());
insert into test_new (employee_id, employee_name, value,
last_modified_date) values ('3', 'e28', 'some_new_again_value', now());
So now is there any way to avoid this particular scenario in my above table
for the below query.. Somehow somebody by mistake is trying to execute the
below query then it will create another row with employee_id as 1 and with
other fields? I don't want anyone to insert the same employee_id again if
it is already there in the cassandra database.. Any thoughts?
insert into test_new (employee_id, employee_name, value,
last_modified_date) values ('1', 'e29', 'some_new_value', now());
Re: CQL Tables in Cassandra with an Index
Posted by Alex Popescu <al...@datastax.com>.
Conditional inserts/updates (lightweight transactions) are available only
in C* 2.0+.
Also most of the time you should try to think about alternative ways to
solve the problem and rely on these only if you cannot find a different
solution (the reason for this is that they come with performance penalties
and you'd be better off with a scalable & performant design, rather than
taking the easy way out ;-)
On Fri, Nov 8, 2013 at 11:40 AM, Techy Teck <co...@gmail.com> wrote:
> If I execute the above query from CQL shell, it doesn't work for me at
> all... This is what I get -
>
> cqlsh:pp> insert into test_new (employee_id, employee_name, value,
> last_modified_date) values ('1', 'e29', 'some_new_value', now()) if not
> exists
> ;
> Bad Request: line 1:123 missing EOF at 'if'
>
> Is there anything I am missing here? I am running Cassandra 1.2.3
>
>
>
>
> On Fri, Nov 8, 2013 at 5:33 AM, DuyHai Doan <do...@gmail.com> wrote:
>
>> Consider using the new lightweight transaction
>>
>> insert into test_new (employee_id, employee_name, value,
>> last_modified_date) values ('1', 'e29', 'some_new_value', now()) *if
>> not exists*;
>>
>>
>>
>> Le vendredi 8 novembre 2013 03:53:12 UTC+1, Techy Teck a écrit :
>>
>>> I am using the below table in our use case -
>>>
>>> create table test_new (
>>> employee_id text,
>>> employee_name text,
>>> value text,
>>> last_modified_date timeuuid,
>>> primary key (employee_id, last_modified_date)
>>> );
>>>
>>> create index employee_name_idx on test_new (employee_name);
>>>
>>> In my above table employee_id will be unique always starting from 1 till
>>> 32767. So our query pattern is like this -
>>>
>>> 1. Give me everything for any of the employee_id?
>>> 2. Give me everything for what has changed in last 5 minutes?
>>> 3. Give me everything for any of the employee_name?
>>>
>>>
>>> I will be inserting below data into my above table -
>>>
>>> insert into test_new (employee_id, employee_name, value,
>>> last_modified_date) values ('1', 'e27', 'some_value', now());
>>> insert into test_new (employee_id, employee_name, value,
>>> last_modified_date) values ('2', 'e27', 'some_new_value', now());
>>> insert into test_new (employee_id, employee_name, value,
>>> last_modified_date) values ('3', 'e28', 'some_new_again_value', now());
>>>
>>> So now is there any way to avoid this particular scenario in my above
>>> table for the below query.. Somehow somebody by mistake is trying to
>>> execute the below query then it will create another row with employee_id as
>>> 1 and with other fields? I don't want anyone to insert the same employee_id
>>> again if it is already there in the cassandra database.. Any thoughts?
>>>
>>> insert into test_new (employee_id, employee_name, value,
>>> last_modified_date) values ('1', 'e29', 'some_new_value', now());
>>>
>>>
>>> To unsubscribe from this group and stop receiving emails from it, send
>> an email to java-driver-user+unsubscribe@lists.datastax.com.
>>
>
> To unsubscribe from this group and stop receiving emails from it, send an
> email to java-driver-user+unsubscribe@lists.datastax.com.
>
--
:- a)
Alex Popescu
Sen. Product Manager @ DataStax
@al3xandru
Re: CQL Tables in Cassandra with an Index
Posted by Techy Teck <co...@gmail.com>.
If I execute the above query from CQL shell, it doesn't work for me at
all... This is what I get -
cqlsh:pp> insert into test_new (employee_id, employee_name, value,
last_modified_date) values ('1', 'e29', 'some_new_value', now()) if not
exists
;
Bad Request: line 1:123 missing EOF at 'if'
Is there anything I am missing here? I am running Cassandra 1.2.3
On Fri, Nov 8, 2013 at 5:33 AM, DuyHai Doan <do...@gmail.com> wrote:
> Consider using the new lightweight transaction
>
> insert into test_new (employee_id, employee_name, value,
> last_modified_date) values ('1', 'e29', 'some_new_value', now()) *if not
> exists*;
>
>
>
> Le vendredi 8 novembre 2013 03:53:12 UTC+1, Techy Teck a écrit :
>
>> I am using the below table in our use case -
>>
>> create table test_new (
>> employee_id text,
>> employee_name text,
>> value text,
>> last_modified_date timeuuid,
>> primary key (employee_id, last_modified_date)
>> );
>>
>> create index employee_name_idx on test_new (employee_name);
>>
>> In my above table employee_id will be unique always starting from 1 till
>> 32767. So our query pattern is like this -
>>
>> 1. Give me everything for any of the employee_id?
>> 2. Give me everything for what has changed in last 5 minutes?
>> 3. Give me everything for any of the employee_name?
>>
>>
>> I will be inserting below data into my above table -
>>
>> insert into test_new (employee_id, employee_name, value,
>> last_modified_date) values ('1', 'e27', 'some_value', now());
>> insert into test_new (employee_id, employee_name, value,
>> last_modified_date) values ('2', 'e27', 'some_new_value', now());
>> insert into test_new (employee_id, employee_name, value,
>> last_modified_date) values ('3', 'e28', 'some_new_again_value', now());
>>
>> So now is there any way to avoid this particular scenario in my above
>> table for the below query.. Somehow somebody by mistake is trying to
>> execute the below query then it will create another row with employee_id as
>> 1 and with other fields? I don't want anyone to insert the same employee_id
>> again if it is already there in the cassandra database.. Any thoughts?
>>
>> insert into test_new (employee_id, employee_name, value,
>> last_modified_date) values ('1', 'e29', 'some_new_value', now());
>>
>>
>> To unsubscribe from this group and stop receiving emails from it, send
> an email to java-driver-user+unsubscribe@lists.datastax.com.
>