phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Cameron Hatfield (JIRA)" <>
Subject [jira] [Commented] (PHOENIX-2271) Upsert - CheckAndPut like functionality
Date Wed, 23 Sep 2015 22:55:04 GMT


Cameron Hatfield commented on PHOENIX-2271:

Would you be thinking of extending the standard merge statement (which, from what I can tell
without access to the ISO doc, only supports working on direct table references) with non-standard
support for a value constructor, ala the syntax I stole from TSQL? Without that, it seems
relatively hard to use it for our use case (compare-and-set for a row).
The other minus I would see is the inability to mix and match transactions and compare-and-set
within the same table, forcing you to have to choose one or the other. Though I am unsure
how much sense that would make anyways.

The another issue, with either decision should, would be if the MERGE / UPSERT would need
to be extended to return information on success or not of the UPSERT, similar to POSTGRESQLs
RETURNING. Currently we don't have a requirement for this, though I know there are use cases
for doing so.

> Upsert - CheckAndPut like functionality
> ---------------------------------------
>                 Key: PHOENIX-2271
>                 URL:
>             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|].
> 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
> {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

View raw message