You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by alex kamil <al...@gmail.com> on 2014/05/23 03:37:36 UTC
Re: How to implement conditional updates of existing rows via Phoenix
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
<dg...@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 <al...@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.
>>>>
>>>
>>>
>>
Re: How to implement conditional updates of existing rows via Phoenix
Posted by alex kamil <al...@gmail.com>.
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
<dg...@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 <dg...@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 <al...@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 <al...@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.
>
Re: How to implement conditional updates of existing rows via Phoenix
Posted by alex kamil <al...@gmail.com>.
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
<dg...@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 <al...@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 <al...@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.
>>>>>>
>>>>>
>>>>>
>>>>
>>
>