Return-Path: X-Original-To: apmail-cassandra-user-archive@www.apache.org Delivered-To: apmail-cassandra-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id D9E661087F for ; Fri, 8 Nov 2013 19:52:42 +0000 (UTC) Received: (qmail 48879 invoked by uid 500); 8 Nov 2013 19:52:40 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 48820 invoked by uid 500); 8 Nov 2013 19:52:40 -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 48812 invoked by uid 99); 8 Nov 2013 19:52:40 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 08 Nov 2013 19:52:40 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of apopescu@datastax.com designates 209.85.223.175 as permitted sender) Received: from [209.85.223.175] (HELO mail-ie0-f175.google.com) (209.85.223.175) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 08 Nov 2013 19:52:36 +0000 Received: by mail-ie0-f175.google.com with SMTP id u16so755562iet.34 for ; Fri, 08 Nov 2013 11:52:15 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc:content-type; bh=Bmk4lFVkNrM0Sluq1lcDH1wx7w1I12c2E0HBEhl9oMA=; b=jRTF6qg3bi0vDD1TJk2uuTT9W46ODkjUM49x1fW90SG4O3lVnc9Z2wHvLyfvXUag70 8r3aPD01OinB8sroJoj2aX/G8Vtmw93k2yw1tgcxF4FfUjacvbNQB/izKp66vKYrFRjH lQY2mFNsZpcns/I4KYJDCIU6TZsJNUJUWKb3zeY43Zqs/bRf/cjO2ytek79iCnHaqDxx xTFilMM3W4fr1f0COvJJU90K495a5JEZv+kKWA+qoKN0ynviDlXI742IVaaTHCIK8a1/ eq4Yy+MLfHQtKP3korMgUvwxaWTCNwtokG/Su7BT1/in/sAZdfPK0qsO6W3Gan999Aej 4zJA== X-Gm-Message-State: ALoCoQnXVMezlfwKzJdM7empebPF+/QGQKYTvZD5ndLFVJDcciWE/hSpMe3toXdmfId34k1X8jNQ X-Received: by 10.50.21.6 with SMTP id r6mr3712042ige.44.1383940334945; Fri, 08 Nov 2013 11:52:14 -0800 (PST) MIME-Version: 1.0 Received: by 10.64.13.84 with HTTP; Fri, 8 Nov 2013 11:51:54 -0800 (PST) In-Reply-To: References: <9bad6164-9cd6-4134-8e76-1445c3c9cb71@lists.datastax.com> From: Alex Popescu Date: Fri, 8 Nov 2013 11:51:54 -0800 Message-ID: Subject: Re: CQL Tables in Cassandra with an Index To: "java-driver-user@lists.datastax.com" Cc: user Content-Type: multipart/alternative; boundary=047d7ba9724e0e655604eaafb896 X-Virus-Checked: Checked by ClamAV on apache.org --047d7ba9724e0e655604eaafb896 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 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 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 =C3=A9crit : >> >>> 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 til= l >>> 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_i= d as >>> 1 and with other fields? I don't want anyone to insert the same employe= e_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 a= n > email to java-driver-user+unsubscribe@lists.datastax.com. > --=20 :- a) Alex Popescu Sen. Product Manager @ DataStax @al3xandru --047d7ba9724e0e655604eaafb896 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Conditional inserts/updates (lightweight transactions) are= available only in C* 2.0+.

Also most of the time you sh= ould try to think about alternative ways to solve the problem and rely on t= hese only if you cannot find a different solution (the reason for this is t= hat they come with performance penalties and you'd be better off with a= scalable & performant design, rather than taking the easy way out ;-)<= /div>


On Fri,= Nov 8, 2013 at 11:40 AM, Techy Teck <comptechgeeky@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, l= ast_modified_date) values ('1', 'e29',=C2=A0 'some_new_= value', now()) if not exists
;
Bad Request: line 1:123 missing EOF at 'if'

Is th= ere anything I am missing here? I am running Cassandra 1.2.3



On Fri, Nov 8, 2013 at 5:33 AM, DuyHai Doan <doanduyhai@gmail.com= > wrote:
Consider using the new lightweight transaction

=C2=A0insert into test_new (employee_id, employee_name, value, last= _modified_date) values ('1', 'e29',=C2=A0 'some_new_val= ue', now()) if not exists;



Le vendredi 8 novembre 2013 03:53:12 UTC+1, Tec= hy Teck a =C3=A9crit=C2=A0:
I am using the below table in our use case -

=C2= =A0=C2=A0=C2=A0 create table test_new (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 employee_id text,
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 employee_name text,
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 value= text,
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 last_modified_date tim= euuid,
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 primary key (employee_id, last_m= odified_date)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 );
=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 create index employee_name_idx on test_new (employee_n= ame);
=C2=A0=C2=A0
In my above table employee_id will be unique alwa= ys starting from 1 till 32767. So our query pattern is like this -

=C2=A0=C2=A0=C2=A0 1. Give me everything for any of the employee_id?=C2=A0=C2=A0=C2=A0 2. Give me everything for what has changed in last 5 mi= nutes?
=C2=A0=C2=A0=C2=A0 3. Give me everything for any of the employee_= name?
=C2=A0

I will be inserting below data into my above table -=

=C2=A0=C2=A0=C2=A0 insert into test_new (employee_id, employee_name, va= lue, last_modified_date) values ('1', 'e27',=C2=A0 'som= e_value', now());
=C2=A0=C2=A0=C2=A0 insert into test_new (employee_= id, employee_name, value, last_modified_date) values ('2', 'e27= ',=C2=A0 'some_new_value', now());
=C2=A0=C2=A0=C2=A0 insert into test_new (employee_id, employee_name, value,= last_modified_date) values ('3', 'e28',=C2=A0 'some_ne= w_again_value', now());
=C2=A0
So now is there any way to avoid t= his particular scenario in my above table for the below query.. Somehow som= ebody by mistake is trying to execute the below query then it will create a= nother row with employee_id as 1 and with other fields? I don't want an= yone to insert the same employee_id again if it is already there in the cas= sandra database.. Any thoughts?

=C2=A0=C2=A0=C2=A0 insert into test_new (employee_id, employee_name, va= lue, last_modified_date) values ('1', 'e29',=C2=A0 'som= e_new_value', now());
=C2=A0=C2=A0=C2=A0

To unsubscribe from this group and stop receiving emails from it, send an e= mail to java-driver-user+unsubscribe@lists.datastax.com.

To unsubscribe from this group and stop receiving emails from it, send an e= mail to java-driver-user+unsubscribe@lists.datastax.com.



--
=

:- a)


Alex Popescu
Sen. Product Manager @ DataStax
@a= l3xandru
--047d7ba9724e0e655604eaafb896--