[
https://issues.apache.org/jira/browse/PHOENIX-2271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14901726#comment-14901726
]
James Taylor commented on PHOENIX-2271:
---------------------------------------
Thanks for the explanation, [~babartareen]. How about combining (2), (3),and
(3a) into a single statement with an UPSERT SELECT call and one or more UDFs?
Something like this:
UPSERT INTO my_table(pk, result, accuracy) SELECT pk, calc_result(v1,v2),
calc_accuracy(v1,v2) from my_table;
You may be able to batch the UPSERT SELECT over multiple rows too, depending on
where/how you do your calculation. This would amortize the RPC further.
> 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)
