1 . Cursors in Apache Phoenix HBaseCon West 2017 June 12, 2017 Anirudha Jadhav ajadhav2@bloomberg.net Biju Nair bnair10@bloomberg.net © 2017 Bloomberg Finance L.P. All rights reserved. 

2 . Bloomberg Leading data and analytics provider for the financial industry © 2017 Bloomberg Finance L.P. All rights reserved. 

3 .Bloomberg is a data company 

4 . Reality of working with data • The data model changes over time • Users querying the data model don’t necessarily change • Alternate query patterns for the same dataset • Data infrastructure usage needs to be simple © 2017 Bloomberg Finance L.P. All rights reserved. 

5 . Apache Phoenix • Recipes of best practices for using HBase over a familiar SQL’ish grammar • It is so much more than SQL o User defined functions for push-down o Secondary indices o Statistics collections, optimizations based on heuristics o ORM libraries o JDBC, ODBC support with Query servers o Integrations: Spark, Kafka, MR and others © 2017 Bloomberg Finance L.P. All rights reserved. 

6 . Extending Apache Phoenix • A very active and helpful community • Our ongoing work o Apache Calcite o Distributed tests and nightly performance build o Multi-DC replication o Deep paging with cursor implementation © 2017 Bloomberg Finance L.P. All rights reserved. 

7 . HBase Application ZooKeeper HBase HBase Client Quorum Master RegionServer RegionServer RegionServer HDFS HDFS HDFS DataNode DataNode DataNode © 2017 Bloomberg Finance L.P. All rights reserved. 

8 . Phoenix Application Phoenix Client ZooKeeper HBase HBase Client Quorum Master RegionServer RegionServer RegionServer SYSTEM.CATALOG SYSTEM.STATS Phoenix RPC Phoenix RPC Phoenix endpoint endpoint Coprocessors HDFS HDFS HDFS DataNode DataNode DataNode https://www.slideshare.net/enissoz/apache-phoenix-past-present-and-future-of-sql-over-hbase http://phoenix.apache.org/presentations/OC-HUG-2014-10-4x3.pdf © 2017 Bloomberg Finance L.P. All rights reserved. 

9 . Phoenix Client HBase Connection Management Client HBase Authentication Client SQL Parsing ANTLR4 Phoenix Client Query rewrite/ Hints/Rules Optimization Query Plan Generation Rules Transaction Management Tephra © 2017 Bloomberg Finance L.P. All rights reserved. 

10 . Phoenix query execution Connection con = DriverManager.getConnection("jdbc:phoenix:zkquorum:2181:/hbase:principal:keytabfile") ; … PreparedStatement statement = con.prepareStatement("select * from TBL"); … ResultSet rset = statement.executeQuery(); … while (rset.next() != null) … rset.close() … © 2017 Bloomberg Finance L.P. All rights reserved. 

11 . Phoenix query execution Connect to HBase getConnection Parse SQL Statement prepareStatement Read/Cache Metadata Validate SQL statement Create query plan executeQuery Optimize query plan Create Result Iterator Create Phoenix Result Set Close ResultSet close() © 2017 Bloomberg Finance L.P. All rights reserved. 

12 . Phoenix Server Application Phoenix Client HBase Client Meta Data Write Read Request Request Request RegionServer RegionServer RegionServer SYSTEM.CATALOG USER_TABLE USER_TABLE MetaDataEndPointImpl UngroupedAggregateRO UngroupedAggregateRO GroupedAggregateRO GroupedAggregateRO MetaDataRegionObserver ScanRegionObserver ScanRegionObserver Indexer Index ServerCachingEndpointImpl © 2017 Bloomberg Finance L.P. All rights reserved. 

13 . Cursors • To support row pagination o Should support forward and backward traversal • Support required for select queries only • Data needs to be consistent during traversal © 2017 Bloomberg Finance L.P. All rights reserved. 

14 . Cursors • DECLARE tCursor CURSOR FOR SELECT * FROM TBL • OPEN tCursor • FETCH NEXT 10 ROWS FROM tCursor • FETCH PRIOR 5 ROWS FROM tCursor • CLOSE tCursor © 2017 Bloomberg Finance L.P. All rights reserved. 

15 . Implementation options • PHOENIX-2606 • Use row value constructors o Query rewrite and complex • Wrapper over available query Resultsets o Can leverage Resultsets and so relatively simple © 2017 Bloomberg Finance L.P. All rights reserved. 

16 . Cursor Lifecycle PreparedStatement statement = con.prepareStatement("DECLARE tCursor CURSOR FOR SELECT * FROM TBL"); statement.execute(); … statement = con.prepareStatement("OPEN tCursor"); statement = con.prepareStatement("FETCH NEXT FROM tCursor"); ResultSet rset = statement.execute(); while (rset.next != null) … statement = con.prepareStatement(“CLOSE tCursor"); statement.execute(); © 2017 Bloomberg Finance L.P. All rights reserved. 

17 . Cursor lifecycle Parse SQL Statement Create/Optimize QueryPlan DECLARE CURSOR Create CursorWrapper Set Cursor Status to Open OPEN CURSOR Execute CursorFetchPlan Create CursorResultIterator FETCH Create Phoenix ResultSet Close Cursor CLOSE © 2017 Bloomberg Finance L.P. All rights reserved. 

18 . Cursor Challenges • Data Consistency o Query start timestamp provides snapshot consistency • Optimization o Use Scan object for non aggregate queries • Cache sizing o Dynamic sizing © 2017 Bloomberg Finance L.P. All rights reserved. 

19 .Contributors • Gabriel Jimenez (MIT) • Anirudha Jadhav (Bloomberg) • Biju Nair (Bloomberg) • Ankit Singhal (Hortonworks) 

20 .Q&A Thank You Hardening Apache Phoenix @PhoenixCon tomorrow 2 PM PT © 2017 Bloomberg Finance L.P. All rights reserved.