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

["Babar Tareen (JIRA)" <ji...@apache.org>]

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

Babar Tareen edited comment on PHOENIX-2271 at 9/21/15 8:17 PM:
----------------------------------------------------------------

Transactions might be too heavy for my use case because of the high write volume. The application does some computations and stores the results in a table. These results have a column(s) representing the accuracy of the computation. I only want to update the result if it is more accurate than the existing one, regardless of the order of the operation. There will always be a single version of a result. In this case transaction features like snapshot isolation are unnecessary. With results being written from many workers, checkAndPut like api will be more performant as comparison happens on the server side and the applications don't need to read the record. This change is only for the Upsert statement, it would be an extension to a non standard sql part of phoenix.


was (Author: babartareen):
Transactions might be too heavy for my use case because of the high write volume. The application does some computations and stores the results in a table. These results have a column(s) representing the accuracy of the computation. I only want to update the result if it is more accurate than the existing one, regardless of the order of the operation. There will always be a single version of a result. In this case transaction features like snapshot isolation are unnecessary. With results being written from many workers, checkAndPut like api will be more performant as comparison happens on the server side and the applications don't need to read the record.

> 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)