Notes from Phoenix - A High Performance Open Source SQL Layer over HBase Nov, 2013 and Apache Phoenix the evolution of a relational database layer over HBase Jun 30, 2015 slides. Both talks by James Taylor
- Phoenix is a High performance SQL Engine over HBase data
- Query Engine
- Metadata repository
- Compare to Impala
- Targets low latency application (eg: web applications)
- Open Source – top level Apache project
Regular JDBC driver
- metadatadata + regular jdbc API
- 10 years of Fortune 500 stocks
- 30M rows
- Single AWS node
- interactive visual query for stock prices …
What is Hbase
- Runs on HDFS
- K/V store – distributed, sorted, map
- low level APIs – get, put values given row key & scan over range given start and stop row keys.
Why use HBase?
- you have lots of data. scale linearly. shards automatically. data is split and redistributed
- no transactions out of the box.
- you need strict consistency.
- hbase handle incremental data changes by building log structure merge trees
Why use Phoenix?
- reduces the amount of code users need to write.
Performance optimisations transparent to user
- skip scanning
- secondary indexing
- query optimisation
Leverage existing tooling around SQL
SQL Client (
- OLAP engine. eg: Pentaho. Kylin?
Phoenix versus Hive performance
- Phoenix (key filter) >> phoenix (full table) > >> Hive over HBase
- Hive and Impala have similar perf. Does not understand the structure of row-keys
- from oracle into HBase while maintaining salesforce developer API
- Platform monitoring
Why is it important?
- scalable, low latency app development starts with Phoenix
- Phoenix worries about physical scale and fast performance so that app developers don’t have to
- Looks, tastes, feels like SOQL to force.com developer
Archival problem set
- Field history tracking grows unbounded. call centers call individual change. No data is deleted for 8.5 years.
- Enterprise customers require long term storage of ‘cold’ data.
- Data retention policies can require of data to be kept around
Archive pilot demonstration
- insret data from Oracle into Phoenix
Monitoring use case
- security, compliance and audit
- product support and “Limits analysis”
- product usage and management (roll up into aggregates)
- Identity fraud
Phoenix under the hood
- aggregate co-processor on severside per region, parallelised.
- returns only the distinct set of features
- preserves network bandwidth
- client does final merge-sort.
- client returns the result as a JDBC result
Roadmap (Nov, 2013)
- Apache incubator
- multi-tenant tables
- monitoring and manageent
- cost-based query optimizer
- sql OLAP extensions
(WINDOW, PARTITION OVER, RANK)
State of the union (Jun 2015)
- Runs TPC queries
- Supports JOIN, sub-queries, derived tables
Secondary indexing strategies
- Immutable for write-once/append only data (eg: Log data.. has time component). There is no incremental secondary index.
- Global for read-heavy mutable data (co-processors on the server side keep indexes in sync with data tables). A second HBase table is created that is ordered according to the columns you are indexing. Optimiser will use this table if it deems this table to be more efficient.
- Local for write-heavy mutable or immutable data. Custom load-balancers. All the writes are local. The writes can be done on the same region servers.
- statistics driven parallel execution – background process in hbase. allows the user to optimze queries. uses equidistant keys in region to collect stats.
- Tracing and metrics for monitoring and management – better visibility into what’s going on. Uses Apache HTrace. Collect data across scans into stats table. Then query for bottlenecks – index maint etc.,
Join and subquery support
- Left/Right/Full outer Join; cross join
- additional: semi join; anti-join
- Algorithms: hash-joni; sort-merge-join
- Predicate push-down
- FK-to-PK join optimisation
- Global index with missing data columns
- Correlated subquery rewrite
- TPC - benchmarking suite
- HBase 1.0 support
- Functional indexes – 4.3 release of Ph. Indexing on expression not on column.
SELECT AVG(response_time) FROM SERVER_METRICS WHERe DAYOFMONTH(create_time)=1
Adding the following index will turn it into a range scan:
CREATE INDEX day_of_month_idx ON SERVER_METRICS(DAYOFMONTH(create_time)) INCLUDE(response_time)
- User defined functions – extension points to phoenix. Define the metadata of the fun (input, output) params.
CREATE FUNCTION WOEID_DISTANCE(INTEGER, INTEGER) RETURN INTEGER AS 'org.apache.geo.woeidDistance' USING JAR '/lib/geo/geoloc.jar'
SELECT FROM woeid a JOIN woeid b on a.country = b.country WHERE woeid_distance(a.ID, b.ID) < 5;
This query executes on server side. This is tenant aware, which means.. there is no chance of conflict between tenants defining the same UDF.
Query Server + Thin Driver – offloads queryplanning and execution to different server(s). Minimises client dependencies
Connection conn = DriverManager.getConnection("jdbc:phoenix:thin:url=http://localhost:8765");
Union ALL support – query multiple tables to get single resultset.
- testing at scale with Pherf – define a scenario and deploy pherf to the cluster. It will generate the data and collect the stats. Testing with representattive data size. functional data sets.
- MR Index build
- Spark Integration .. RDD backed by a Phoe table.
Data built-in functions –
Tranactions – using http://tephra.io
- supports repeable_read isolation level
- allows reading your own uncommitted data
- Optional – enable on a table by table basis
- no performance penalty when not used
- Optimistic concurrency control
- no deadlocks or lock escalation
- cost of conflict detection and possible rollback is higher
- good id conflicts are rare: short transactions, disjoin partitioning of work
- conflict detection is not necessary if the data is immutable. ie., write-once/append-only data.
- next step for Phoenix
- Query parser, compiler and planner framework
- SQL-92 compliant
- Pluggable cost-based optimizer framework
- Interop with Calcite adaptors
- already used by Drill, Hive, Kylin, Samza
- Supports any JDBC source
- One Cost model to rule them all
Setting up a local phoenix cluster using Docker
Download and install Docker beta for Mac
We will setup a three node hadoop cluster using this recipe as a template.
git clone email@example.com:kiwenlau/hadoop-cluster-docker.git cd hadoop-cluster-docker docker build .
Lot of things fly by …
Successfully built ed4ece2b19f2
Create hadoop network
sudo docker network create --driver=bridge hadoop Password: f678bdfc5918e15a578b909692e6f04a4ef5f730a95ebb0f16da5a30c38354b1