Hello I am using HDP 2.5.3 and Phoenix 4.7 and it seems to me flashback / snapshot query would not work if the latest index / indexed data changes. Below is what I have done and experienced: -- creating the schema and table
CREATE SCHEMA IF NOT EXISTS TRADE;
DROP TABLE IF EXISTS TRADE.TRADE;
CREATE TABLE IF NOT EXISTS TRADE.TRADE (ID VARCHAR, BROKER VARCHAR CONSTRAINT pk PRIMARY KEY (ID));
CREATE LOCAL INDEX TRADE_BROKER_IDX on TRADE.TRADE (BROKER);
-- insert some date
0: jdbc:phoenix:> UPSERT INTO TRADE.TRADE VALUES ('123', 'BROKER123');
1 row affected (0.085 seconds)
0: jdbc:phoenix:> UPSERT INTO TRADE.TRADE VALUES ('456', 'BROKER456');
1 row affected (0.036 seconds)
0: jdbc:phoenix:> SELECT ID, BROKER FROM TRADE.TRADE WHERE BROKER = 'BROKER456';
+------+------------+
| ID | BROKER |
+------+------------+
| 456 | BROKER456 |
+------+------------+
1 row selected (0.107 seconds)
0: jdbc:phoenix:> EXPLAIN SELECT ID, BROKER FROM TRADE.TRADE WHERE BROKER = 'BROKER456';
+----------------------------------------------------------------------------------------+
| PLAN |
+----------------------------------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER TRADE:TRADE [1,'BROKER456'] |
| SERVER FILTER BY FIRST KEY ONLY |
+----------------------------------------------------------------------------------------+
2 rows selected (0.054 seconds)
-- logged current time
0: jdbc:phoenix:> select cast(CURRENT_TIME() as BIGINT) from TRADE.TRADE limit 1;
+--------------------------------------------+
| TO_BIGINT(TIME '2017-03-28 14:50:38.678') |
+--------------------------------------------+
| 1490706922000 |
+--------------------------------------------+
0: jdbc:phoenix:sandbox:2181/h> !quit
[root]$ sqlline.py "sandbox:2181/hbase-secure;currentSCN=1490706922000"
0: jdbc:phoenix:sandbox:2181/h> -- showing what the snapshot data should look like
0: jdbc:phoenix:sandbox:2181/h> SELECT ID, BROKER FROM TRADE.TRADE WHERE BROKER = 'BROKER456';
+------+------------+
| ID | BROKER |
+------+------------+
| 456 | BROKER456 |
+------+------------+
1 row selected (0.197 seconds)
0: jdbc:phoenix:sandbox:2181/h> -- showing what the select is using correct index
0: jdbc:phoenix:sandbox:2181/h> EXPLAIN SELECT ID, BROKER FROM TRADE.TRADE WHERE BROKER = 'BROKER456';
+----------------------------------------------------------------------------------------+
| PLAN |
+----------------------------------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER TRADE:TRADE [1,'BROKER456'] |
| SERVER FILTER BY FIRST KEY ONLY |
+----------------------------------------------------------------------------------------+
2 rows selected (0.044 seconds)
0: jdbc:phoenix:sandbox:2181/h> !quit
[root]$ sqlline.py
0: jdbc:phoenix:> -- now make changes to the index and indexed data
0: jdbc:phoenix:> DROP INDEX TRADE_BROKER_IDX ON TRADE.TRADE;
2 rows affected (0.748 seconds)
0: jdbc:phoenix:> UPSERT INTO TRADE.TRADE VALUES ('456', '456BROKER');
1 row affected (0.1 seconds)
0: jdbc:phoenix:> SELECT ID, BROKER FROM TRADE.TRADE WHERE BROKER = '456BROKER';
+------+------------+
| ID | BROKER |
+------+------------+
| 456 | 456BROKER |
+------+------------+
1 row selected (0.123 seconds)
0: jdbc:phoenix:master.sandbox.lbg.com:2181/h> CREATE LOCAL INDEX TRADE_BROKER_IDX on TRADE.TRADE (BROKER);
2 rows affected (5.212 seconds)
0: jdbc:phoenix:> EXPLAIN SELECT ID, BROKER FROM TRADE.TRADE WHERE BROKER = 'BROKER456';
+-----------------------------------------------------------------------+
| PLAN |
+-----------------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER TRADE:TRADE |
| SERVER FILTER BY BROKER = 'BROKER456' |
+-----------------------------------------------------------------------+
2 rows selected (0.042 seconds)
0: jdbc:phoenix:> !quit
[root]$ sqlline.py "sandbox:2181/hbase-secure;currentSCN=1490706922000"
0: jdbc:phoenix:sandbox:2181/h> -- I would expect snaphot to use index, but in this case it is using
0: jdbc:phoenix:sandbox:2181/h> -- latest index state - i.e. no index and full scan
0: jdbc:phoenix:sandbox:2181/h> SELECT ID, BROKER FROM TRADE.TRADE WHERE BROKER = 'BROKER456';
+------+------------+
| ID | BROKER |
+------+------------+
| 456 | BROKER456 |
+------+------------+
1 row selected (0.168 seconds)
0: jdbc:phoenix:sandbox:2181/h> EXPLAIN SELECT ID, BROKER FROM TRADE.TRADE WHERE BROKER = 'BROKER456';
+-----------------------------------------------------------------------+
| PLAN |
+-----------------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER TRADE:TRADE |
| SERVER FILTER BY BROKER = 'BROKER456' |
+-----------------------------------------------------------------------+
2 rows selected (0.035 seconds)
0: jdbc:phoenix:sandbox:2181/h> !quit
[root]$ sqlline.py "sandbox:2181/hbase-secure;currentSCN=1490706922000"
0: jdbc:phoenix:sandbox:2181/h> -- now recreate index and update indexed data yet again
0: jdbc:phoenix:> CREATE LOCAL INDEX TRADE_BROKER_IDX on TRADE.TRADE (BROKER);
2 rows affected (5.23 seconds)
0: jdbc:phoenix:> UPSERT INTO TRADE.TRADE VALUES ('456', 'BROKER-456');
1 row affected (0.021 seconds)
0: jdbc:phoenix:> -- at latest state correctly retrieved data and use index in explain
0: jdbc:phoenix:> SELECT * FROM TRADE.TRADE;
+------+-------------+
| ID | BROKER |
+------+-------------+
| 456 | BROKER-456 |
| 123 | BROKER123 |
+------+-------------+
2 rows selected (0.14 seconds)
0: jdbc:phoenix:> EXPLAIN SELECT * FROM TRADE.TRADE WHERE BROKER = 'BROKER-456';
+-----------------------------------------------------------------------------------------+
| PLAN |
+-----------------------------------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER TRADE:TRADE [2,'BROKER-456'] |
| SERVER FILTER BY FIRST KEY ONLY |
+-----------------------------------------------------------------------------------------+
2 rows selected (0.047 seconds)
0: jdbc:phoenix:> !quit
[root]$ sqlline.py "sandbox:2181/hbase-secure;currentSCN=1490706922000"
0: jdbc:phoenix:sandbox:2181/h> -- UNEXPECTEDLY NOTHING IS RETURNED!
0: jdbc:phoenix:sandbox:2181/h> SELECT * FROM TRADE.TRADE ;
+-----+---------+
| ID | BROKER |
+-----+---------+
+-----+---------+
No rows selected (0.066 seconds)
0: jdbc:phoenix:sandbox:2181/h> EXPLAIN SELECT * FROM TRADE.TRADE ;
+----------------------------------------------------------------------------+
| PLAN |
+----------------------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER TRADE:TRADE [1] |
| SERVER FILTER BY FIRST KEY ONLY |
+----------------------------------------------------------------------------+
2 rows selected (0.031 seconds)
0: jdbc:phoenix:sandbox:2181/h> SELECT * FROM TRADE.TRADE WHERE BROKER = 'BROKER456';
+-----+---------+
| ID | BROKER |
+-----+---------+
+-----+---------+
No rows selected (0.077 seconds)
0: jdbc:phoenix:sandbox:2181/h> -- AND IT WORKS IF FORCING PHOENIX TO DO FULL SCAN
0: jdbc:phoenix:sandbox:2181/h> SELECT /*+NO_INDEX*/ * FROM TRADE.TRADE ;
+------+------------+
| ID | BROKER |
+------+------------+
| 123 | BROKER123 |
| 456 | BROKER456 |
+------+------------+
2 rows selected (0.07 seconds)
Anyone had the same issue? How did you resolve it or is this a phoenix bug? If this is a bug is there plan to fix this in which HDP / Phoenix version? Thank you!
... View more
