[jira] [Commented] (PHOENIX-2271) Upsert - CheckAndPut like functionality

["James Taylor (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-2271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14877306#comment-14877306 ] 

James Taylor commented on PHOENIX-2271:
---------------------------------------

Thanks for the work in this area, [~babartareen]. It's important that we stick with the SQL-92+ standards, but this seems to deviate from that. Did you know we have support for transactions over in the txn branch (soon to be merged into master)? In this optimistic concurrency model, if two different clients attempt to UPSERT the same row on a transactional table, the last one to commit would get an exception. Would this solve your use case?

As far as PHOENIX-6, it would be convenient to have a mechanism to not take an action if a row already exists. Given our planned move to Apache Calcite, support for ON DUPLICATE KEY IGNORE as specified by that JIRA may not be the best option. Calcite has support for the MERGE statement which is even more flexible - that be great if you could add the runtime support necessary to support that. I've filed PHOENIX-2275 for that.

> Upsert - CheckAndPut like functionality
> ---------------------------------------
>
>                 Key: PHOENIX-2271
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-2271
>             Project: Phoenix
>          Issue Type: Improvement
>            Reporter: Babar Tareen
>         Attachments: patch.diff
>
>
> The Upsert statement does not support HBase's checkAndPut api, thus making it difficult to conditionally update a row. Based on the comments from PHOENIX-6, I have implemented such functionality. The Upsert statement is modified to support compare clause, which allows us to pass in an expression. The expression is evaluated against the current record and Upsert is only performed when the expression evaluates to true. More details [here|https://github.com/babartareen/phoenix].
> h4. Examples
> Given that the FirstName is always set for the users, create a user record if one doesn't already exist.
> {code:sql}
> UPSERT INTO User (UserId, FirstName, LastName, Phone, Address, PIN) VALUES (1, 'Alice', 'A', '123 456 7890', 'Some St. in a city', 1122) COMPARE FirstName IS NULL;
> {code}
> Update the phone number for UserId '1' if the FirstName is set. Given that the FirstName is always set for the users, this will only update the record if it already exists.
> {code:sql}
> UPSERT INTO User (UserId, Phone) VALUES (1, '987 654 3210') COMPARE FirstName IS NOT NULL;
> {code}
> Update the phone number if the first name for UserId '1' starts with 'Al' and last name is 'A'
> {code:sql}
> UPSERT INTO User (UserId, Phone) VALUES (1, '987 654 3210') COMPARE FirstName LIKE 'Al%' AND LastName = 'A';  
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)