[
https://issues.apache.org/jira/browse/PHOENIX-2271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14901512#comment-14901512
]
Babar Tareen commented on PHOENIX-2271:
---------------------------------------
With transactions the application code will be like
1. Begin transaction
2. Get the currently stored result in the table
3. if the new result is more accurate (compare the accuracy column value in
currently stored and the new results) then
3.1 Do an Upsert
4. Commit transaction
This involves multiple remote calls from the phoenix client to the server. The
application needs to read the record at step (2) just for a comparison. Passing
in the comparison expression with Upsert will require just one Upsert call. The
overhead of these additional calls with transactions will add up when
processing hundreds of thousands of results. Hopefully this clarifies the use
case described above.
> 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)
