incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sylvain Lebresne <sylv...@datastax.com>
Subject Re: Update multiple rows in a CQL lightweight transaction
Date Thu, 27 Feb 2014 08:48:43 GMT
On Thu, Feb 27, 2014 at 12:53 AM, Clint Kelly <clint.kelly@gmail.com> wrote:

> Thanks for your help everyone.
>
> Sylvain, as I understand it, the scenario I described above is not
> resolved by CASSANDRA-6561, correct?
>

Well, no, my point is that it kind of is resolved. At least if we're still
talking about:
  "If there is a row with (x,y,t,z) = (a,1,2,10), then update/insert a row
with (x,y,t,z) = (a,3,4,5) and update/insert a row with (x,y,t,z) =
(a,4,5,6)."
and assuming in you example schema that the the primary key is (x,y), then
you can do:
  BEGIN BATCH
    UPDATE foo SET t = 2 WHERE t = 2 AND z = 10 IF x = 'a' AND y = 1;
    INSERT INTO foo (x, y, t, z) VALUES ('a', 3, 4, 5);
    INSERT INTO foo (x, y, t, z) VALUES ('a', 4, 5, 6)
  APPLY BATCH

This do what you described above. Now, it is true that it will also
overwrite the column 't' of the first row with it's current value because
(you do need syntax wise to at least set one value in the first UPDATE),
which is not part of what you described, but since this is done
atomically, overwriting a column by its own value probably doesn't matter.
And it almost surely doesn't matter performance wise since
it all end up in one internal mutation and so updating one more column
won't make a measurable difference, especially since you're using
a lightweight transaction and those are not the most performance oriented
operation in the first place.

--
Sylvain


>
> (This scenario may not matter to most folks, which is totally fine, I just
> want to make sure that I understand.)
>
> Should I instead look into using the Thrift API to address this?
>
> Best regards,
> Clint
>
>
>
> On Tue, Feb 25, 2014 at 11:30 PM, Sylvain Lebresne <sylvain@datastax.com>wrote:
>
>> Sorry to interfere again here but CASSANDRA-5633 will not be picked up
>> because pretty much everything it was set to fix is fixed by
>> CASSANDRA-6561, this is *not* a syntax problem anymore.
>>
>>
>> On Wed, Feb 26, 2014 at 3:18 AM, Tupshin Harper <tupshin@tupshin.com>wrote:
>>
>>> Unfortunately there is no option to vote for a "resolved" ticket, but if
>>> you can propose a better syntax that people agree on, you could probably
>>> get some fresh traction on it.
>>>
>>> -Tupshin
>>>  On Feb 25, 2014 7:20 PM, "Clint Kelly" <clint.kelly@gmail.com> wrote:
>>>
>>>> Hi Tupshin,
>>>>
>>>> Thanks for your help!  Unfortunately in my case, I will need to do a
>>>> compare and set in which the compare is against a value in a dynamic column.
>>>>
>>>> In general, I need to be able to do the following:
>>>>
>>>>    - Check whether a given value exists in a dynamic column
>>>>    - If so, perform some number of insertions / deletions for dynamic
>>>>    columns in the same row (i.e., with the same partition key as the dynamic
>>>>    column used for the compare)
>>>>
>>>> I think you are correct that I need
>>>> https://issues.apache.org/jira/browse/CASSANDRA-5633 to be
>>>> implemented.  Is there any way to vote for that to get picked up again? 
:)
>>>>
>>>> Best regards,
>>>> Clint
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On Mon, Feb 24, 2014 at 2:32 PM, Tupshin Harper <tupshin@tupshin.com>wrote:
>>>>
>>>>> Hi Clint,
>>>>>
>>>>> That does appear to be an omission in CQL3. It would be possible to
>>>>> simulate it by doing
>>>>> BEGIN BATCH
>>>>>     UPDATE foo SET z = 10 WHERE x = 'a' AND y = 1 IF t= 2 AND z=10;
>>>>>     UPDATE foo SET t = 5,z=6 where x = 'a' AND y = 4
>>>>> APPLY BATCH;
>>>>>
>>>>> However, this does a redundant write to the first row if the condition
>>>>> holds, and I certainly wouldn't recommend doing that routinely.
>>>>>
>>>>> Alternatively, depending on your needs, you might be able to use a
>>>>> static column (coming with 2.0.6) as your conditional flag, as that column
>>>>> is shared by all rows in the partition.
>>>>>
>>>>> -Tupshin
>>>>>
>>>>>
>>>>>
>>>>> On Mon, Feb 24, 2014 at 3:57 PM, Clint Kelly <clint.kelly@gmail.com>wrote:
>>>>>
>>>>>> Hi Tupshin,
>>>>>>
>>>>>> Thanks for your help; I appreciate it.
>>>>>>
>>>>>> Could I do something like the following?
>>>>>>
>>>>>> Given the same table you started with:
>>>>>>
>>>>>> x | y | t | z
>>>>>> ---+---+---+----
>>>>>>  a | 1 | 2 | 10
>>>>>>  a | 2 | 2 | 20
>>>>>>
>>>>>> I'd like to write a compare-and-set that does something like:
>>>>>>
>>>>>> "If there is a row with (x,y,t,z) = (a,1,2,10), then update/insert
a
>>>>>> row with (x,y,t,z) = (a,3,4,5) and update/insert a row with (x,y,t,z)
>>>>>> = (a,4,5,6)."
>>>>>>
>>>>>>
>>>>>> I don't see how I could do this with what you outlined above---just
>>>>>> curious.  It seems like what I describe above under the hood would
be
>>>>>> a compare-and-(batch)-set on a single wide row, so it maybe is
>>>>>> possible with the Thrift API (I have to check).
>>>>>>
>>>>>> Thanks again!
>>>>>>
>>>>>> Best regards,
>>>>>> Clint
>>>>>>
>>>>>> On Sat, Feb 22, 2014 at 11:38 AM, Tupshin Harper <tupshin@tupshin.com>
>>>>>> wrote:
>>>>>> > #5633 was actually closed  because the static columns feature
>>>>>> > (https://issues.apache.org/jira/browse/CASSANDRA-6561) which
has
>>>>>> been
>>>>>> > checked in to the 2.0 branch but is not yet part of a release
(it
>>>>>> will be in
>>>>>> > 2.0.6).
>>>>>> >
>>>>>> > That feature will let you update multiple rows within a single
>>>>>> partition by
>>>>>> > doing a CAS write based on a static column shared by all rows
>>>>>> within the
>>>>>> > partition.
>>>>>> >
>>>>>> > Example extracted from the ticket:
>>>>>> > CREATE TABLE foo (
>>>>>> >     x text,
>>>>>> >     y bigint,
>>>>>> >     t bigint static,
>>>>>> >     z bigint,
>>>>>> > PRIMARY KEY (x, y) );
>>>>>> >
>>>>>> > insert into foo (x,y,t, z) values ('a', 1, 1, 10);
>>>>>> > insert into foo (x,y,t, z) values ('a', 2, 2, 20);
>>>>>> >
>>>>>> > select * from foo;
>>>>>> >
>>>>>> > x | y | t | z
>>>>>> > ---+---+---+----
>>>>>> >  a | 1 | 2 | 10
>>>>>> >  a | 2 | 2 | 20
>>>>>> > (Note that both values of "t" are 2 because it is static)
>>>>>> >
>>>>>> >
>>>>>> >  begin batch update foo set z = 1 where x = 'a' and y = 1; update
>>>>>> foo set z
>>>>>> > = 2 where x = 'a' and y = 2 if t = 4; apply batch;
>>>>>> >
>>>>>> >  [applied] | x | y    | t
>>>>>> > -----------+---+------+---
>>>>>> >      False | a | null | 2
>>>>>> >
>>>>>> > (Both updates failed to apply because there was an unmet
>>>>>> conditional on one
>>>>>> > of them)
>>>>>> >
>>>>>> > select * from foo;
>>>>>> >
>>>>>> >  x | y | t | z
>>>>>> > ---+---+---+----
>>>>>> >  a | 1 | 2 | 10
>>>>>> >  a | 2 | 2 | 20
>>>>>> >
>>>>>> >
>>>>>> > begin batch update foo set z = 1 where x = 'a' and y = 1; update
>>>>>> foo set z =
>>>>>> > 2 where x = 'a' and y = 2 if t = 2; apply batch;
>>>>>> >
>>>>>> >  [applied]
>>>>>> > -----------
>>>>>> >       True
>>>>>> >
>>>>>> > (both updates succeeded because the check on t succeeded)
>>>>>> >
>>>>>> > select * from foo;
>>>>>> > x | y | t | z
>>>>>> > ---+---+---+---
>>>>>> >  a | 1 | 2 | 1
>>>>>> >  a | 2 | 2 | 2
>>>>>> >
>>>>>> > Hope this helps.
>>>>>> >
>>>>>> > -Tupshin
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> > On Fri, Feb 21, 2014 at 6:05 PM, DuyHai Doan <doanduyhai@gmail.com>
>>>>>> wrote:
>>>>>> >>
>>>>>> >> Hello Clint
>>>>>> >>
>>>>>> >>  The Resolution status of the JIRA is set to "Later", probably
the
>>>>>> >> implementation is not done yet. The JIRA was opened to discuss
>>>>>> about impl
>>>>>> >> strategy but nothing has been coded so far I guess.
>>>>>> >>
>>>>>> >>
>>>>>> >>
>>>>>> >> On Sat, Feb 22, 2014 at 12:02 AM, Clint Kelly <
>>>>>> clint.kelly@gmail.com>
>>>>>> >> wrote:
>>>>>> >>>
>>>>>> >>> Folks,
>>>>>> >>>
>>>>>> >>> Does anyone know how I can modify multiple rows at once
in a
>>>>>> >>> lightweight transaction in CQL3?
>>>>>> >>>
>>>>>> >>> I saw the following ticket:
>>>>>> >>>
>>>>>> >>>     https://issues.apache.org/jira/browse/CASSANDRA-5633
>>>>>> >>>
>>>>>> >>> but it was not obvious to me from the comments how (or
whether)
>>>>>> this
>>>>>> >>> got resolved.  I also couldn't find anything in the
DataStax
>>>>>> >>> documentation about how to perform these operations.
>>>>>> >>>
>>>>>> >>> I'm in particular interested in how to perform a compare-and-set
>>>>>> >>> operation that modifies multiple rows (with the same
partition
>>>>>> key)
>>>>>> >>> using the DataStax Java driver.
>>>>>> >>>
>>>>>> >>> Thanks!
>>>>>> >>>
>>>>>> >>> Best regards,
>>>>>> >>> Clint
>>>>>> >>
>>>>>> >>
>>>>>> >
>>>>>>
>>>>>
>>>>>
>>>>
>>
>

Mime
View raw message