[
https://issues.apache.org/jira/browse/PHOENIX-2271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14903074#comment-14903074
]
Cameron Hatfield commented on PHOENIX-2271:
-------------------------------------------
It seems like it would be nice to expose an underlying primitive for compare
and set, allowing you to get to features of HBase that aren't currently
exposed, for when you need lighter weight features, especially since compare
and set can open up more then a couple possibilities for different types of
distributed computations (such as the one we are trying to support)
For the upsert workaround, it seems a bit hacky to try to use udfs and a select
upsert, instead of exposing a real primitive for it. That would also require a
UDF call for each changed column, to compare the two versions, correct? (Don't
have enough background with UDFs, as I haven't taken a good look at the
underlying code yet).
For the merge statement, you would also add the RPC and implementation overhead
of defining both a when / when not pair, vs just the values + the compare
statement, not including complicating the optimization detection for the case
of simple compare and set.
Of the two, the merge statement seems the most promising, but only if it:
Supports inserting multiple values, instead of a single value (so that you get
the advantage of doing compare and set in one statement, instead of one upsert
per compare and set)
Supports a way to ensure you're using the compare and set, vs relying on the
optimizer to choose the option that does a compare and set under the hood.
This is because it won't do the correct thing, *unless* you happened to start a
transaction before running the statement.
The main issue I see is the second one is that requires you to basically expose
some non-sql standard way of doing things, in a sql standard statement, vs
exposing it in the already non-sql standard statement.
Aside from that, what is the timeline for calcite integration?
> 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)
