phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From alex kamil <alex.ka...@gmail.com>
Subject Re: How to implement conditional updates of existing rows via Phoenix
Date Fri, 23 May 2014 02:19:23 GMT
yw,

when correlated
sub-query<https://issues.apache.org/jira/browse/PHOENIX-945>is
implemented you could also get fancy with it and use dynamic statuses,
something along the lines:
UPSERT INTO queue(name, status) SELECT name, (select  status from
other_table where some_condition='true') FROM queue WHERE status = '4' and AND
environment='QA'

Alex



On Thu, May 22, 2014 at 10:04 PM, Dmitry Goldenberg
<dgoldenberg@kmwllc.com>wrote:

> Alex,
>
> This worked *perfectly*! Thank you so much.
>
> - Dmitry
>
>
> On Thursday, May 22, 2014 9:57:32 PM UTC-4, AK wrote:
>
>> as I think about it, why can't you do
>> UPSERT INTO queue(name, status) SELECT name, '1' FROM queue WHERE status
>> = '4' and AND environment='QA'
>>
>>
>> On Thu, May 22, 2014 at 9:48 PM, Dmitry Goldenberg <dgold...@kmwllc.com>wrote:
>>
>>> Alex,
>>>
>>> Thanks for the reference, indeed that's what we'd want. I think it may
>>> take me some time to come up to speed on the code enough to contribute a
>>> patch, I'll see what I can do. In the meantime, if someone who's already
>>> been in this code a lot could implement it perhaps that's the fastest way
>>> to go. Is this slated for any upcoming release?
>>>
>>> - Dmitry
>>>
>>>
>>> On Thu, May 22, 2014 at 9:37 PM, alex kamil <alex....@gmail.com> wrote:
>>>
>>>> Dmitry,
>>>>
>>>> looks like UPSERT SET<https://issues.apache.org/jira/browse/PHOENIX-792>
might
>>>> help,but it's not implemented yet, pls consider contributing a patch.  also
>>>> including the main mailing list
>>>>
>>>> Alex
>>>>
>>>>
>>>> On Thu, May 22, 2014 at 8:39 PM, Dmitry Goldenberg <dgold...@kmwllc.com
>>>> > wrote:
>>>>
>>>>> Correction "A pure UPSERT doesn't handle a WHERE clause."
>>>>>
>>>>>
>>>>> On Thursday, May 22, 2014 8:37:56 PM UTC-4, Dmitry Goldenberg wrote:
>>>>>
>>>>>>  Hi Alex,
>>>>>>
>>>>>> Yes, I've thought of that.
>>>>>>
>>>>>> Examples:
>>>>>> UPSERT INTO test.targetTable(col1, col2) SELECT col3, col4 FROM
>>>>>> test.sourceTable WHERE col5 < 100
>>>>>> UPSERT INTO foo SELECT * FROM bar;
>>>>>>
>>>>>> This doesn't seem to have a way of providing a VALUES clause which
is
>>>>>> exactly what I need. I want to be able to select items in the queue
where
>>>>>> status=4, but reset the value to 1 and persist it.
>>>>>>
>>>>>> A pure UPSERT doesn't handle a VALUES clause either:
>>>>>> UPSERT INTO TEST VALUES('foo','bar',3);
>>>>>> UPSERT INTO TEST(NAME,ID) VALUES('foo',123);
>>>>>>
>>>>>> Ideally one would want a combo of these two. Perhaps a direct
>>>>>> implementation of a SQL update?
>>>>>>
>>>>>> - Dmitry
>>>>>>
>>>>>>
>>>>>> On Thu, May 22, 2014 at 8:19 PM, alex kamil <alex....@gmail.com>wrote:
>>>>>>
>>>>>>> Dmitry, how about upsert select<http://phoenix.incubator.apache.org/language/index.html#upsert_select>
>>>>>>> ?
>>>>>>>
>>>>>>>
>>>>>>> thanks
>>>>>>> Alex
>>>>>>>
>>>>>>>
>>>>>>> On Thu, May 22, 2014 at 6:13 PM, Dmitry Goldenberg <
>>>>>>> dgold...@kmwllc.com> wrote:
>>>>>>>
>>>>>>>> Hi all,
>>>>>>>>
>>>>>>>> I'm trying to implement the type of logic with Phoenix which
can be
>>>>>>>> summarized as the below SQL statement:
>>>>>>>>
>>>>>>>> *UPDATE queue SET status = 1 WHERE status = 4 AND environment='QA'*
>>>>>>>>
>>>>>>>> I've noticed that UPSERT's don't support WHERE clauses. It
seems to
>>>>>>>> me then that I have to first create a view which would allow
me to work
>>>>>>>> with all the rows WHERE status=4 AND environment='QA'.
>>>>>>>>
>>>>>>>> So then, create view myview1 as select * from queue where
status=4
>>>>>>>> and environment='QA';    I can tell that selection of rows
works for this
>>>>>>>> view.  Next then, is to implement the UPDATE logic:
>>>>>>>>
>>>>>>>> *UPSERT INTO myview (status) VALUES (1);*
>>>>>>>>
>>>>>>>> which produces the error: *org.apache.phoenix.schema.ConstraintViolationException:
>>>>>>>> MYVIEW.ENTRYID* may not be null.  Of note, my 'queue' table
has a
>>>>>>>> primary key called ENTRYID which I populate via a sequence.
>>>>>>>>
>>>>>>>> Any ideas as to how I could get around this constraint violation?
>>>>>>>>  Or perhaps another way of implementing conditional updates
(with a WHERE)
>>>>>>>> of existing rows via Phoenix?
>>>>>>>>
>>>>>>>> I have looked at the IMMUTABLE_ROWS property as well. I wasn't
>>>>>>>> clear on whether this is available on both tables and views
and whether it
>>>>>>>> defaults to mutable or immutable. I have tried setting this
on the view,
>>>>>>>> which wasn't allowed (*ERROR 1012 (42M03): Table undefined*).
>>>>>>>>  I've also tried setting IMMUTABLE_ROWS=false on the 'queue'
table itself
>>>>>>>> and that didn't make a difference.
>>>>>>>>
>>>>>>>> Any suggestions would be appreciated.
>>>>>>>>
>>>>>>>> --
>>>>>>>> You received this message because you are subscribed to the
Google
>>>>>>>> Groups "Phoenix HBase User" group.
>>>>>>>> To unsubscribe from this group and stop receiving emails
from it,
>>>>>>>> send an email to phoenix-hbase-user+unsubscribe@googlegroups.com.
>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>
>>>
>>  --
> You received this message because you are subscribed to the Google Groups
> "Phoenix HBase User" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to phoenix-hbase-user+unsubscribe@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>

Mime
View raw message