Apache Phoenix

Created: Feb 20, 2018 by Pradeep Gowda . Updated: Feb 20, 2018

Notes

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 blog
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

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.

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

SQL
reduces the amount of code users need to write.
Performance optimisations transparent to user
- aggregation
- skip scanning
- secondary indexing
- query optimisation
Leverage existing tooling around SQL
- SQL Client ( bin/sqlline.py )
- OLAP engine. eg: Pentaho. Kylin ?

Phoenix (key filter) >> phoenix (full table) > >> Hive over HBase
Hive and Impala have similar perf. Does not understand the structure of row-keys

Data archival
- from oracle into HBase while maintaining salesforce developer API
Platform monitoring

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

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

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.,

Left/Right/Full outer Join; cross join
additional: semi join; anti-join
Algorithms: hash-joni; sort-merge-join
Optimizations:
- 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'

usage:

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 string: Connection conn = DriverManager.getConnection("jdbc:phoenix:thin:url=http://localhost:8765");
See http://phonenix.apache.org/server.html
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 – WEEK , DAYOFMONTH etc.,
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.
Tephra Architecture

Download and install Docker beta for Mac

We will setup a three node hadoop cluster using this recipe as a template.

git clone git@github.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