Stephen Petschulat created PHOENIX-4552:
-------------------------------------------
Summary: Add support for a ROW_TIMESTAMP that doesn't affect the
primary key
Key: PHOENIX-4552
URL: https://issues.apache.org/jira/browse/PHOENIX-4552
Project: Phoenix
Issue Type: New Feature
Reporter: Stephen Petschulat
By declaring a ROW_TIMESTAMP constraint on a Phoenix table, it does two things
1) expose the hbase native timestamp as this column and 2) prepend your primary
key with this timestamp as well.
It would be useful to have a similar feature that only exposes the hbase native
timestamp. This would allow explicit setting of the timestamp when upserting
data while allowing multiple hbase versions. It is possible to then query for
that specific key and version(s).
Potential approach:
{code:sql}
CREATE TABLE COMMENTS (
COMMENT_ID INT NOT NULL,
REVISION_NUM BIGINT NOT NULL ROW_TIMESTAMP, // NEW use of keyword
COMMENT_BODY TEXT
CONSTRAINT PK PRIMARY KEY(COMMENT_ID))
UPSERT INTO COMMENTS (123, 1, 'edit 1 comment')
UPSERT INTO COMMENTS (123, 2, 'edit 2 of comment')
UPSERT INTO COMMENTS (123, 3, 'edit 3 of comment')
{code}
Current behavior of ROW_TIMESTAMP would create a new primary for each upsert,
so querying by primary key is no longer straightforward when you don't know the
version number at query time.
{code:sql}
SELECT * FROM COMMENTS WHERE COMMENT_ID = 123 // => returns most recent
version 'edit 3 of comment'
SELECT * FROM COMMENTS WHERE COMMENT_ID = 123 AND REVISION_NUM = 1 // =>
returns explicit version 'edit 1 comment'
{code}
It can also be useful to return multiple versions (related: PHOENIX-590)
{code:sql}
SELECT * FROM COMMENTS WHERE COMMENT_ID = 123 AND REVISION_NUM < 3 // =>
returns 2 rows
{code}
Or just the highest version less than or equal to a particular version
(allowing snapshot queries):
{code:sql}
// set CurrentSCN=2 on connection
SELECT * FROM COMMENTS WHERE COMMENT_ID = 123 // => returns 'edit 2 of comment'
{code}
CurrentSCN already allows this type of snapshot query but not against an
explicitly set timestamp with multiple versions. The primary key injection
prevents this. The above query would behave similar to:
{code:java}
scan 'COMMENTS', {TIMERANGE => [0, <maxversionid+1>]}
{code}
This returns the highest versioned value for each key that is less than a
specified maximum version number.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
