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 01:57:32 GMT
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
<dgoldenberg@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.kamil@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 <
>> dgoldenberg@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.kamil@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 <
>>>>> dgoldenberg@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.
>>>>>>
>>>>>
>>>>>
>>>>
>>
>

Mime
View raw message