You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Miles Spielberg <mi...@box.com> on 2018/06/02 00:39:34 UTC
Atomic UPSERT on indexed tables
From https://phoenix.apache.org/atomic_upsert.html:
Although global indexes on columns being atomically updated are supported,
> it’s not recommended as a potentially a separate RPC across the wire would
> be made while the row is under lock to maintain the secondary index.
But the parser on 4.13.1 doesn't seem to agree:
0: jdbc:phoenix:thin:url=http://192.168.99.10> CREATE TABLE T1 (A VARCHAR
PRIMARY KEY, B VARCHAR);
No rows affected (1.299 seconds)
0: jdbc:phoenix:thin:url=http://192.168.99.10> CREATE INDEX T1_B ON T1(B);
No rows affected (6.285 seconds)
0: jdbc:phoenix:thin:url=http://192.168.99.10> UPSERT INTO T1(A,B)
VALUES('hello', 'world') ON DUPLICATE KEY IGNORE;
Error: Error -1 (00000) : Error while executing SQL "UPSERT INTO T1(A,B)
VALUES('hello', 'world') ON DUPLICATE KEY IGNORE": Remote driver error:
RuntimeException: java.sql.SQLException: ERROR 1224 (42Z24): The ON
DUPLICATE KEY clause may not be used when a table has a global index.
tableName=T1 -> SQLException: ERROR 1224 (42Z24): The ON DUPLICATE KEY
clause may not be used when a table has a global index. tableName=T1
(state=00000,code=-1)
0: jdbc:phoenix:thin:url=http://192.168.99.10>
Am I doing something wrong here? Is the documentation inaccurate?
Miles Spielberg
Staff Software Engineer
O. 650.485.1102
900 Jefferson Ave
Redwood City, CA 94063
Re: Atomic UPSERT on indexed tables
Posted by James Taylor <ja...@apache.org>.
It's possible that local indexes could be allowed for atomic upserts, but
global indexes are problematic (in that under load your cluster would
probably die). The reason is that there'd be a cross RS call made for each
row being atomically upserted. If the call hangs due to the RS hosting the
data being down, it ties up the handler thread which can lead to a
cascading effect when writes fail.
Not sure what your timeframe is, but support for Omid transactions is
getting close to being committed. It's on the omid2 feature branch. They
play well with HBase replication, so perhaps that's an option.
On Fri, Jun 8, 2018 at 12:01 PM, Miles Spielberg <mi...@box.com> wrote:
> This represents a serious shortcoming for our use case. We require some
> level of same-row atomic update capability on tables we'd also like to have
> global indices on. Our understanding is that running under Tephra doesn't
> play well with HBase replication, which we've been intending to use for
> disaster recovery.
>
> Our workload is read-dominated, so we think we're willing to accept the
> row-locking and write time performance penalties. Is this something that
> could be put under an option? Are there other significant shortcomings to
> combining ON DUPLICATE KEY with global indices?
>
> PHOENIX-3925 mentions "service protection," but I'm clear on what that's
> referring to.
>
> Miles Spielberg
> Staff Software Engineer
>
>
> O. 650.485.1102
> 900 Jefferson Ave
> <https://maps.google.com/?q=900+Jefferson+Ave+%0D%0A%0D%0ARedwood+City+,+CA+94063&entry=gmail&source=g>
> Redwood City, CA 94063
> <https://maps.google.com/?q=900+Jefferson+Ave+%0D%0A%0D%0ARedwood+City+,+CA+94063&entry=gmail&source=g>
>
> On Mon, Jun 4, 2018 at 7:14 PM, Sergey Soldatov <se...@gmail.com>
> wrote:
>
>> Yes, the documentation doesn't reflect the recent changes. Please see
>> https://issues.apache.org/jira/browse/PHOENIX-3925
>>
>> Thanks,
>> Sergey
>>
>> On Fri, Jun 1, 2018 at 5:39 PM, Miles Spielberg <mi...@box.com> wrote:
>>
>>> From https://phoenix.apache.org/atomic_upsert.html:
>>>
>>> Although global indexes on columns being atomically updated are
>>>> supported, it’s not recommended as a potentially a separate RPC across the
>>>> wire would be made while the row is under lock to maintain the secondary
>>>> index.
>>>
>>>
>>> But the parser on 4.13.1 doesn't seem to agree:
>>>
>>> 0: jdbc:phoenix:thin:url=http://192.168.99.10> CREATE TABLE T1 (A
>>> VARCHAR PRIMARY KEY, B VARCHAR);
>>>
>>> No rows affected (1.299 seconds)
>>>
>>> 0: jdbc:phoenix:thin:url=http://192.168.99.10> CREATE INDEX T1_B ON
>>> T1(B);
>>>
>>> No rows affected (6.285 seconds)
>>>
>>> 0: jdbc:phoenix:thin:url=http://192.168.99.10> UPSERT INTO T1(A,B)
>>> VALUES('hello', 'world') ON DUPLICATE KEY IGNORE;
>>>
>>> Error: Error -1 (00000) : Error while executing SQL "UPSERT INTO T1(A,B)
>>> VALUES('hello', 'world') ON DUPLICATE KEY IGNORE": Remote driver error:
>>> RuntimeException: java.sql.SQLException: ERROR 1224 (42Z24): The ON
>>> DUPLICATE KEY clause may not be used when a table has a global index.
>>> tableName=T1 -> SQLException: ERROR 1224 (42Z24): The ON DUPLICATE KEY
>>> clause may not be used when a table has a global index. tableName=T1
>>> (state=00000,code=-1)
>>>
>>> 0: jdbc:phoenix:thin:url=http://192.168.99.10>
>>>
>>> Am I doing something wrong here? Is the documentation inaccurate?
>>>
>>>
>>> Miles Spielberg
>>> Staff Software Engineer
>>>
>>>
>>> O. 650.485.1102
>>> 900 Jefferson Ave
>>> <https://maps.google.com/?q=900+Jefferson+Ave+%0D%0A%0D%0ARedwood+City+,+CA+94063&entry=gmail&source=g>
>>> Redwood City, CA 94063
>>> <https://maps.google.com/?q=900+Jefferson+Ave+%0D%0A%0D%0ARedwood+City+,+CA+94063&entry=gmail&source=g>
>>>
>>
>>
>
Re: Atomic UPSERT on indexed tables
Posted by Miles Spielberg <mi...@box.com>.
This represents a serious shortcoming for our use case. We require some
level of same-row atomic update capability on tables we'd also like to have
global indices on. Our understanding is that running under Tephra doesn't
play well with HBase replication, which we've been intending to use for
disaster recovery.
Our workload is read-dominated, so we think we're willing to accept the
row-locking and write time performance penalties. Is this something that
could be put under an option? Are there other significant shortcomings to
combining ON DUPLICATE KEY with global indices?
PHOENIX-3925 mentions "service protection," but I'm clear on what that's
referring to.
Miles Spielberg
Staff Software Engineer
O. 650.485.1102
900 Jefferson Ave
Redwood City, CA 94063
On Mon, Jun 4, 2018 at 7:14 PM, Sergey Soldatov <se...@gmail.com>
wrote:
> Yes, the documentation doesn't reflect the recent changes. Please see
> https://issues.apache.org/jira/browse/PHOENIX-3925
>
> Thanks,
> Sergey
>
> On Fri, Jun 1, 2018 at 5:39 PM, Miles Spielberg <mi...@box.com> wrote:
>
>> From https://phoenix.apache.org/atomic_upsert.html:
>>
>> Although global indexes on columns being atomically updated are
>>> supported, it’s not recommended as a potentially a separate RPC across the
>>> wire would be made while the row is under lock to maintain the secondary
>>> index.
>>
>>
>> But the parser on 4.13.1 doesn't seem to agree:
>>
>> 0: jdbc:phoenix:thin:url=http://192.168.99.10> CREATE TABLE T1 (A
>> VARCHAR PRIMARY KEY, B VARCHAR);
>>
>> No rows affected (1.299 seconds)
>>
>> 0: jdbc:phoenix:thin:url=http://192.168.99.10> CREATE INDEX T1_B ON
>> T1(B);
>>
>> No rows affected (6.285 seconds)
>>
>> 0: jdbc:phoenix:thin:url=http://192.168.99.10> UPSERT INTO T1(A,B)
>> VALUES('hello', 'world') ON DUPLICATE KEY IGNORE;
>>
>> Error: Error -1 (00000) : Error while executing SQL "UPSERT INTO T1(A,B)
>> VALUES('hello', 'world') ON DUPLICATE KEY IGNORE": Remote driver error:
>> RuntimeException: java.sql.SQLException: ERROR 1224 (42Z24): The ON
>> DUPLICATE KEY clause may not be used when a table has a global index.
>> tableName=T1 -> SQLException: ERROR 1224 (42Z24): The ON DUPLICATE KEY
>> clause may not be used when a table has a global index. tableName=T1
>> (state=00000,code=-1)
>>
>> 0: jdbc:phoenix:thin:url=http://192.168.99.10>
>>
>> Am I doing something wrong here? Is the documentation inaccurate?
>>
>>
>> Miles Spielberg
>> Staff Software Engineer
>>
>>
>> O. 650.485.1102
>> 900 Jefferson Ave
>> <https://maps.google.com/?q=900+Jefferson+Ave+%0D%0A%0D%0ARedwood+City+,+CA+94063&entry=gmail&source=g>
>> Redwood City, CA 94063
>> <https://maps.google.com/?q=900+Jefferson+Ave+%0D%0A%0D%0ARedwood+City+,+CA+94063&entry=gmail&source=g>
>>
>
>
Re: Atomic UPSERT on indexed tables
Posted by Sergey Soldatov <se...@gmail.com>.
Yes, the documentation doesn't reflect the recent changes. Please see
https://issues.apache.org/jira/browse/PHOENIX-3925
Thanks,
Sergey
On Fri, Jun 1, 2018 at 5:39 PM, Miles Spielberg <mi...@box.com> wrote:
> From https://phoenix.apache.org/atomic_upsert.html:
>
> Although global indexes on columns being atomically updated are supported,
>> it’s not recommended as a potentially a separate RPC across the wire would
>> be made while the row is under lock to maintain the secondary index.
>
>
> But the parser on 4.13.1 doesn't seem to agree:
>
> 0: jdbc:phoenix:thin:url=http://192.168.99.10> CREATE TABLE T1 (A VARCHAR
> PRIMARY KEY, B VARCHAR);
>
> No rows affected (1.299 seconds)
>
> 0: jdbc:phoenix:thin:url=http://192.168.99.10> CREATE INDEX T1_B ON T1(B);
>
> No rows affected (6.285 seconds)
>
> 0: jdbc:phoenix:thin:url=http://192.168.99.10> UPSERT INTO T1(A,B)
> VALUES('hello', 'world') ON DUPLICATE KEY IGNORE;
>
> Error: Error -1 (00000) : Error while executing SQL "UPSERT INTO T1(A,B)
> VALUES('hello', 'world') ON DUPLICATE KEY IGNORE": Remote driver error:
> RuntimeException: java.sql.SQLException: ERROR 1224 (42Z24): The ON
> DUPLICATE KEY clause may not be used when a table has a global index.
> tableName=T1 -> SQLException: ERROR 1224 (42Z24): The ON DUPLICATE KEY
> clause may not be used when a table has a global index. tableName=T1
> (state=00000,code=-1)
>
> 0: jdbc:phoenix:thin:url=http://192.168.99.10>
>
> Am I doing something wrong here? Is the documentation inaccurate?
>
>
> Miles Spielberg
> Staff Software Engineer
>
>
> O. 650.485.1102
> 900 Jefferson Ave
> <https://maps.google.com/?q=900+Jefferson+Ave+%0D%0A%0D%0ARedwood+City+,+CA+94063&entry=gmail&source=g>
> Redwood City, CA 94063
> <https://maps.google.com/?q=900+Jefferson+Ave+%0D%0A%0D%0ARedwood+City+,+CA+94063&entry=gmail&source=g>
>