[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6_v5.patch Attaching updated patch with negative test for sequence usage in ON DUPLICATE KEY clause (disallowed), and test for RAND() and CURRENT_DATE() (both allowed). > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_4.x-HBase-0.98.patch, > PHOENIX-6_v2.patch, PHOENIX-6_v3.patch, PHOENIX-6_v4.patch, > PHOENIX-6_v5.patch, PHOENIX-6_wip1.patch, PHOENIX-6_wip2.patch, > PHOENIX-6_wip3.patch, PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > * The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > * This new clause cannot be used with > ** Immutable tables since the whole point is to atomically update a row in > place which isn't allowed for immutable tables. > ** Transactional tables because these use optimistic concurrency as their > mechanism for consistency and isolation. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6_v4.patch Updated patch with CASE statement test. > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_4.x-HBase-0.98.patch, > PHOENIX-6_v2.patch, PHOENIX-6_v3.patch, PHOENIX-6_v4.patch, > PHOENIX-6_wip1.patch, PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, > PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > * The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > * This new clause cannot be used with > ** Immutable tables since the whole point is to atomically update a row in > place which isn't allowed for immutable tables. > ** Transactional tables because these use optimistic concurrency as their > mechanism for consistency and isolation. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6_v3.patch Good catches, [~samarthjain]. I've flagged both usage of aggregation and setting of CURRENT_SCN as errors for ON DUPLICATE KEY clause. Here's the new patch. > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_4.x-HBase-0.98.patch, > PHOENIX-6_v2.patch, PHOENIX-6_v3.patch, PHOENIX-6_wip1.patch, > PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > * The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > * This new clause cannot be used with > ** Immutable tables since the whole point is to atomically update a row in > place which isn't allowed for immutable tables. > ** Transactional tables because these use optimistic concurrency as their > mechanism for consistency and isolation. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6_v2.patch Rebased patch on latest master. Ping, [~samarthjain]. > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_4.x-HBase-0.98.patch, > PHOENIX-6_v2.patch, PHOENIX-6_wip1.patch, PHOENIX-6_wip2.patch, > PHOENIX-6_wip3.patch, PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > * The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > * This new clause cannot be used with > ** Immutable tables since the whole point is to atomically update a row in > place which isn't allowed for immutable tables. > ** Transactional tables because these use optimistic concurrency as their > mechanism for consistency and isolation. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: (was: PHOENIX-6.patch) > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6_4.x-HBase-0.98.patch, PHOENIX-6_wip1.patch, > PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > * The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > * This new clause cannot be used with > ** Immutable tables since the whole point is to atomically update a row in > place which isn't allowed for immutable tables. > ** Transactional tables because these use optimistic concurrency as their > mechanism for consistency and isolation. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6.patch > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_4.x-HBase-0.98.patch, > PHOENIX-6_wip1.patch, PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, > PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > * The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > * This new clause cannot be used with > ** Immutable tables since the whole point is to atomically update a row in > place which isn't allowed for immutable tables. > ** Transactional tables because these use optimistic concurrency as their > mechanism for consistency and isolation. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: (was: PHOENIX-6.patch) > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6_4.x-HBase-0.98.patch, PHOENIX-6_wip1.patch, > PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > * The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > * This new clause cannot be used with > ** Immutable tables since the whole point is to atomically update a row in > place which isn't allowed for immutable tables. > ** Transactional tables because these use optimistic concurrency as their > mechanism for consistency and isolation. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6_4.x-HBase-0.98.patch > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_4.x-HBase-0.98.patch, > PHOENIX-6_wip1.patch, PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, > PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > * The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > * This new clause cannot be used with > ** Immutable tables since the whole point is to atomically update a row in > place which isn't allowed for immutable tables. > ** Transactional tables because these use optimistic concurrency as their > mechanism for consistency and isolation. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: (was: PHOENIX-6_4.x-HBase-0.98.patch) > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_4.x-HBase-0.98.patch, > PHOENIX-6_wip1.patch, PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, > PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > * The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > * This new clause cannot be used with > ** Immutable tables since the whole point is to atomically update a row in > place which isn't allowed for immutable tables. > ** Transactional tables because these use optimistic concurrency as their > mechanism for consistency and isolation. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6_4.x-HBase-0.98.patch Patch that applies to 4.x-HBase-0.98 branch, [~mujtabachohan]. > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_4.x-HBase-0.98.patch, > PHOENIX-6_wip1.patch, PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, > PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > * The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > * This new clause cannot be used with > ** Immutable tables since the whole point is to atomically update a row in > place which isn't allowed for immutable tables. > ** Transactional tables because these use optimistic concurrency as their > mechanism for consistency and isolation. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Description: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} So the general form is: {code} UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE =, ...] ] {code} The following restrictions will apply: * The may not be part of the primary key constraint - only KeyValue columns will be allowed. * This new clause cannot be used with ** Immutable tables since the whole point is to atomically update a row in place which isn't allowed for immutable tables. ** Transactional tables because these use optimistic concurrency as their mechanism for consistency and isolation. was: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} So the general form is: {code} UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE =, ...] ] {code} The following restrictions will apply: * The may not be part of the primary key constraint - only KeyValue columns will be allowed. * This new clause cannot be used with * Immutable tables since the whole point is to atomically update a row in place which isn't allowed for immutable tables. * Transactional tables because these use optimistic concurrency as their mechanism for consistency and isolation. > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_wip1.patch, > PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Description: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} So the general form is: {code} UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE =, ...] ] {code} The following restrictions will apply: * The may not be part of the primary key constraint - only KeyValue columns will be allowed. * This new clause cannot be used with * Immutable tables since the whole point is to atomically update a row in place which isn't allowed for immutable tables. * Transactional tables because these use optimistic concurrency as their mechanism for consistency and isolation. was: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} So the general form is: {code} UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE =, ...] ] {code} The following restrictions will apply: - The may not be part of the primary key constraint - only KeyValue columns will be allowed. To handle the maintenance of immutable indexes, we'll need to push the maintenance to the server side. This is because the mutations for indexes on immutable tables are calculated on the client-side, while this new syntax would potentially modify the value on the server-side. > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_wip1.patch, > PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6.patch Please review [~samarthjain]. See JIRA description for overview of syntax. The implementation uses an Increment to represent the UPSERT VALUES ... ON DUPLICATE KEY statement. This allows us to execute the statement while the row is write locked so that it can be atomic. As usual, any mix of statements can be included in a commit. Multiple ON DUPLICATE KEY statements can be committed together in which case they all execute sequentially. Basically, they should have the same behavior with and without auto commit being on. [~mujtabachohan] - can you do a perf run with this patch? Would be good to compare UPSERT VALUES to UPSERT VALUES with an ON DUPLICATE KEY clause. It will be slower because it needs to take a lock and do a read before writing. A nice to have (but maybe we'd need to do this manually), would be to compare transactional table UPSERT VALUES with UPSERT VALUES with an ON DUPLICATE KEY clause. We'd need to handle in the client the case where the rows conflict and resubmit the transaction, though, for a fair comparision. > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_wip1.patch, > PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > - The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > To handle the maintenance of immutable indexes, we'll need to push the > maintenance to the server side. > This is because the mutations for indexes on immutable tables are calculated > on the client-side, while this new syntax would potentially modify the value > on the server-side. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: (was: PHOENIX-6.patch) > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6_wip1.patch, PHOENIX-6_wip2.patch, > PHOENIX-6_wip3.patch, PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > - The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > To handle the maintenance of immutable indexes, we'll need to push the > maintenance to the server side. > This is because the mutations for indexes on immutable tables are calculated > on the client-side, while this new syntax would potentially modify the value > on the server-side. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6.patch > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6.patch, PHOENIX-6_wip1.patch, > PHOENIX-6_wip2.patch, PHOENIX-6_wip3.patch, PHOENIX-6_wip4.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > - The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > To handle the maintenance of immutable indexes, we'll need to push the > maintenance to the server side. > This is because the mutations for indexes on immutable tables are calculated > on the client-side, while this new syntax would potentially modify the value > on the server-side. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6_wip3.patch Working patch. Still needs more testing around indexes and multi-threading > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6_wip1.patch, PHOENIX-6_wip2.patch, > PHOENIX-6_wip3.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > - The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > To handle the maintenance of immutable indexes, we'll need to push the > maintenance to the server side. > This is because the mutations for indexes on immutable tables are calculated > on the client-side, while this new syntax would potentially modify the value > on the server-side. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6_wip2.patch Server-side for ON DUPLICATE KEY clause > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6_wip1.patch, PHOENIX-6_wip2.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > - The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > To handle the maintenance of immutable indexes, we'll need to push the > maintenance to the server side. > This is because the mutations for indexes on immutable tables are calculated > on the client-side, while this new syntax would potentially modify the value > on the server-side. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Description: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} So the general form is: {code} UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE =, ...] ] {code} The following restrictions will apply: - The may not be part of the primary key constraint - only KeyValue columns will be allowed. To handle the maintenance of immutable indexes, we'll need to push the maintenance to the server side. This is because the mutations for indexes on immutable tables are calculated on the client-side, while this new syntax would potentially modify the value on the server-side. was: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} So the general form is: {code} UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE =, ...] ] {code} The following restrictions will apply: - The may not be part of the primary key constraint - only KeyValue columns will be allowed. To handle the maintenance of immutable indexes, we'll need to push the maintenance to the server side. This is because the mutations for indexes on immutable tables are calculated on the client-side, while this new syntax would potentially modify the value on the server-side. > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6_wip1.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY UPDATE COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: >
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Attachment: PHOENIX-6_wip1.patch Patch with client-side changes. > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > Attachments: PHOENIX-6_wip1.patch > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > - The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > To handle the maintenance of immutable indexes, we'll need to push the > maintenance to the server side. > This is because the mutations for indexes on immutable tables are calculated > on the client-side, while this new syntax would potentially modify the value > on the server-side. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Description: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} So the general form is: {code} UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE =, ...] ] {code} The following restrictions will apply: - The may not be part of the primary key constraint - only KeyValue columns will be allowed. To handle the maintenance of immutable indexes, we'll need to push the maintenance to the server side. This is because the mutations for indexes on immutable tables are calculated on the client-side, while this new syntax would potentially modify the value on the server-side. was: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} So the general form is: {code} UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE =, ...] ] {code} The following restrictions will apply: - The may not be part of the primary key constraint - only KeyValue columns will be allowed. - If the table is immutable, the may not appear in a secondary index. This is because the mutations for indexes on immutable tables are calculated on the client-side, while this new syntax would potentially modify the value on the server-side. > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > - The may not be part of the primary key constraint - only KeyValue > columns will be
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Description: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} So the general form is: {code} UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE =, ...] ] {code} The following restrictions will apply: - The may not be part of the primary key constraint - only KeyValue columns will be allowed. - If the table is immutable, the may not appear in a secondary index. This is because the mutations for indexes on immutable tables are calculated on the client-side, while this new syntax would potentially modify the value on the server-side. was: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor >Assignee: James Taylor > Fix For: 4.9.0 > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} > So the general form is: > {code} > UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE > =, ...] ] > {code} > The following restrictions will apply: > - The may not be part of the primary key constraint - only KeyValue > columns will be allowed. > - If the table is immutable, the may not appear in a secondary > index. This is because the mutations for indexes on immutable tables are > calculated on the client-side, while this new syntax would potentially modify > the value on the server-side. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Description: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} was: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0) ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor > Fix For: 4.9.0 > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0) > ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Description: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key" construct for UPSERT. With this construct, the UPSERT VALUES statement would run atomically and would thus require a read before write which would obviously have a negative impact on performance. For an example of similar syntax , see MySQL documentation at http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. A related discussion is on PHOENIX-2909. Initially we'd support the following: # This would prevent the setting of VAL to 0 if the row already exists: {code} UPSERT INTO T (PK, VAL) VALUES ('a',0) ON DUPLICATE KEY IGNORE; {code} # This would increment the valueS of COUNTER1 and COUNTER2 if the row already exists and otherwise initialize them to 0: {code} UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0) ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; {code} was: To support inserting a new row only if it doesn't already exist, we should support the "on duplicate key ignore" construct (or it's SQL standard equivalent) for UPSERT. See this discussion for more detail: https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor > Fix For: 4.9.0 > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key" construct for UPSERT. With this construct, the > UPSERT VALUES statement would run atomically and would thus require a read > before write which would obviously have a negative impact on performance. For > an example of similar syntax , see MySQL documentation at > http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J. > A related discussion is on PHOENIX-2909. > Initially we'd support the following: > # This would prevent the setting of VAL to 0 if the row already exists: > {code} > UPSERT INTO T (PK, VAL) VALUES ('a',0) > ON DUPLICATE KEY IGNORE; > {code} > # This would increment the valueS of COUNTER1 and COUNTER2 if the row already > exists and otherwise initialize them to 0: > {code} > UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0) > ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1; > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-6) Support ON DUPLICATE KEY construct
[ https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-6: --- Summary: Support ON DUPLICATE KEY construct (was: Support on duplicate key ignore construct) > Support ON DUPLICATE KEY construct > -- > > Key: PHOENIX-6 > URL: https://issues.apache.org/jira/browse/PHOENIX-6 > Project: Phoenix > Issue Type: New Feature >Reporter: James Taylor > Fix For: 4.9.0 > > > To support inserting a new row only if it doesn't already exist, we should > support the "on duplicate key ignore" construct (or it's SQL standard > equivalent) for UPSERT. > See this discussion for more detail: > https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J -- This message was sent by Atlassian JIRA (v6.3.4#6332)