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:37:36 GMT
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