[PHOENIX-914] Native HBase timestamp support to optimize date range queries in Phoenix

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0.0
• Fix Version/s: 4.6.0
• Assignee: Samarth Jain
• Reporter: Vladimir Rodionov
• Created: 06/Apr/14 18:56
• Updated: 21/Nov/15 02:17
• Resolved: 03/Oct/15 22:10

Description

For many applications one of the column of a table can be (and must be) naturally mapped

to HBase timestamp. What it gives us is the optimization on StoreScanner where HFiles with timestamps out of range of

a Scan operator will be omitted. Let us say that we have time-series type of data (EVENTS) and custom compaction, where we create
series of HFiles with continuous non-overlapping timestamp ranges.

CREATE TABLE IF NOT EXISTS ODS.EVENTS (
METRICID VARCHAR NOT NULL,
METRICNAME VARCHAR,
SERVICENAME VARCHAR NOT NULL,
ORIGIN VARCHAR NOT NULL,
APPID VARCHAR,
IPID VARCHAR,
NVALUE DOUBLE,
TIME TIMESTAMP NOT NULL /+ TIMESTAMP +/,
DATA VARCHAR,
SVALUE VARCHAR
CONSTRAINT PK PRIMARY KEY (METRICID, SERVICENAME, ORIGIN, APPID, IPID, TIME)
) SALT_BUCKETS=40, IMMUTABLE_ROWS=true,VERSIONS=1,DATA_BLOCK_ENCODING='NONE';

Make note on TIME TIMESTAMP NOT NULL /+ TIMESTAMP +/ - this is the Hint to Phoenix that the column

TIME must be mapped to HBase timestamp.

The Query:

Select all events of type 'X' for last 7 days

SELECT * from EVENTS WHERE METRICID = 'X' and TIME < NOW() and TIME > NOW() - 7*24*3600000; (this may be not correct SQL syntax of course)

These types of queries will be efficiently optimized if:

1. Phoenix maps TIME column to HBase timestamp

2. Phoenix smart enough to map WHERE clause on TIME attribute to Scan timerange

Although this :

Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
Connection conn = DriverManager.connect(myUrl, props);

conn.createStatement().execute("UPSERT INTO myTable VALUES ('a')");
conn.commit();

will work in my case- it may not be efficient from performance point of view because for every INSERT/UPSERT
new Connection object and new Statement is created, beside this we still need the optimization 2. (see above).

Issue Links

• relates to: PHOENIX-967 Consider (some of) SQL 2011 temporal support