Apache Phoenix

Wednesday October 11, 2017

Announcing Phoenix 4.12 released

The Apache Phoenix team is pleased to announce the immediate availability of the 4.12.0 release. Apache Phoenix enables SQL-based OLTP and operational analytics for Apache Hadoop using Apache HBase as its backing store and providing integration with other projects in the Apache ecosystem such as Spark, Hive, Pig, Flume, and MapReduce. The 4.x releases are compatible with HBase 0.98/1.1/1.2/1.3.

Highlights of the release include:

Source and binary downloads are available here.

Friday July 07, 2017

Announcing Phoenix 4.11 released

The Apache Phoenix team is pleased to announce the immediate availability of the 4.11.0 release. Apache Phoenix enables SQL-based OLTP and operational analytics for Apache Hadoop using Apache HBase as its backing store and providing integration with other projects in the Apache ecosystem such as Spark, Hive, Pig, Flume, and MapReduce. The 4.x releases are compatible with HBase 0.98/1.1/1.2/1.3.

Highlights of the release include:

Source and binary downloads are available here.

Thursday March 23, 2017

Announcing Phoenix 4.10 released

The Apache Phoenix team is pleased to announce the immediate availability of the 4.10.0 release. Apache Phoenix enables SQL-based OLTP and operational analytics for Hadoop using Apache HBase as its backing store and providing integration with other projects in the ecosystem such as Spark, Hive, Pig, Flume, and MapReduce. The 4.x releases are compatible with HBase 0.98/1.1/1.2.

Highlights of the release include:

Source and binary downloads are available here.

Column Mapping and Immutable Data Encoding

With Phoenix 4.10, we are rolling out a new feature that introduces a layer of column mapping between Phoenix column names and HBase column qualifiers. We have also added the capability of packing all column values for a column family into a single HBase cell. These improvements have helped improve performance across the board for the majority of use cases. In this blog, I will be going providing a bit more detailed info on these performance improvements.

Column Mapping

The motivation behind column mapping came from PHOENIX-1598. The key idea being that we should be using number based hbase column qualifiers for non-pk Phoenix columns instead of directly using column names. This helps Phoenix replace the need of having to do a binary search when looking for a cell in the sorted list of cells returned by HBase. This helps improve performance of certain queries (like ORDER BY or GROUP BY on non-pk axis) as the number of non-pk columns go up.

The indirection also enables us to write fast DDL operations like column rename (PHOENIX-2341) and metadata level column drops (PHOENIX-3680). Further, because these number based qualifiers are generally smaller (1 to 4 bytes) than column names, the disk size of tables is smaller which improves performance across the board.

To compare performance and disk space usage, we loaded 600 million rows of TPC-H data for LINEITEM table (downloaded from here) on to our test cluster using 1-byte qualifiers. HDFS disk size with column mapping was 40% smaller (100 GB) than with non-column mapped tables (160GB). As a consequence, the queries in the TPC-H benchmark against LINEITEM table (obtained from here) were also found to be 30-40% faster.

Column mapping also enables us to write custom projection and comparison filters that improve query performance as the number of columns being projected or filtered on go up (PHOENIX-3667). We did a test run where we compared query performance against non-column mapped and column mapped tables as the number of columns go up. As the graph below shows, as the number of columns projected increased, the performance gain by using the new filter also went up. 

Using column mapping is generally recommended unless you expect number of columns in your table and views on it to exceed 2147483647 (which is a lot!). Keep in mind though that for mutable tables this limit applies across all column families. For immutable tables, when using SINGLE_CELL_ARRAY_WITH_OFFSETS encoding scheme, this limit applies to per column family. In general, we expect that using a 2-byte column mapping scheme, which gives you 65535 columns, is good enough. One can override these defaults by using various table properties and configs. For more details on how to use column mapping and immutable data encoding, go here.

Immutable Data Encoding

The immutable storage scheme called SINGLE_CELL_ARRAY_WITH_OFFSETS packs columns belonging to a column family in a single cell. This drastically reduces the size of immutable data resulting in impressive size reduction and faster performance across the board.

To compare performance of queries for immutable encoded and non-encoded tables, we created a table with 25 VARCHAR non-pk columns, with each column name being 10 characters long having 15 character wide values. The table was dense i.e. more than 50% of the columns had values. HBase FAST_DIFF encoding was enabled which is the default with phoenix tables. All the queries were run with NO_CACHE hint to negate the effect of query performance because of block cache. We also made sure to take into consideration the effect of data being present in the OS page cache by ignoring query results for the first few runs.

As the graphs below show, using SINGLE_CELL_ARRAY_WITH_OFFSETS encoding drastically improves the performance for most kinds of queries. Data load time for 1M records using UPSERT with a batch size of 1000 was 3x faster. So were aggregate queries and queries that filtered on key value column. There was no significant impact on point queries though, which is expected.

It is important to note that this encoding could only be used when one of the numbered column mapping schemes is used. This is because internally the encoding relies on these number based column qualifiers to look up values of columns.

Future work/Limitations

Using the SINGLE_CELL_ARRAY_WITH_OFFSETS encoding scheme is recommended when the data is not sparse. Our general recommendation is to use this encoding when data is sufficiently dense (around 50% of columns have values). With growing sparseness the overhead of encoding starts negatively affecting performance (PHOENIX-3559). Also, we have seen that with the default HBase block size of 64K, performance starts to degrade once the size of the packed cell starts exceeding 50 KB. By default, for immutable multi-tenant tables, we use the ONE_CELL_PER_COLUMN encoding. Because of the way we assign column qualifiers for columns in views, it tends to make the data sparse especially when columns are added to views (PHOENIX-3575). There is also work that needs to be done for cleaning up data when a column is dropped from an immutable table with SINGLE_CELL_ARRAY_WITH_OFFSETS encoding (PHOENIX-3605).

Thursday December 01, 2016

Announcing Phoenix 4.9 released

The Apache Phoenix team is pleased to announce the immediate availability of the 4.9.0 release. Apache Phoenix enables SQL-based OLTP and operational analytics for Hadoop using Apache HBase as its backing store and providing integration with other projects in the ecosystem such as Spark, Hive, Pig, Flume, and MapReduce. The 4.x releases are compatible with HBase 0.98/1.1/1.2.

Here are some of the highlights of the releases:

Source and binary downloads are available here.

Thursday August 18, 2016

Announcing Phoenix 4.8 released

The Apache Phoenix team is pleased to announce the immediate availability of the 4.8.0 release. Apache Phoenix enables SQL-based OLTP and operational analytics for Hadoop using Apache HBase as its backing store and providing integration with other projects in the ecosystem such as Spark, Hive, Pig, Flume, and MapReduce. The 4.x releases are compatible with HBase 0.98/1.0/1.1/1.2.

Here are some of the highlights of the releases:

Source and binary downloads are available here.

Friday March 11, 2016

Announcing Phoenix 4.7 release with ACID transaction support

The Apache Phoenix team is pleased to announce the immediate availability of the 4.7.0 release. Apache Phoenix enables OLTP and operational analytics for Hadoop through SQL support and integration with other projects in the ecosystem such as Spark, HBase, Pig, Flume, and MapReduce.

Highlights of the release include:

Source and binary downloads are available here.

Tuesday November 03, 2015

New optimization for time series data in Apache Phoenix 4.6

Today's blog is brought to you by Samarth Jain, PMC member of Apache Phoenix, and Lead Member of the Technical Staff at Salesforce.com.

Apache Phoenix 4.6 now provides the capability of mapping a Phoenix primary key column to the native row timestamp of Apache HBase. The mapping is denoted by the keyword ROW_TIMESTAMP in the create table statement. Such a mapping provides the following two advantages: 

  • Allows Phoenix to set the min time range on scans since this column directly maps to the HBase cell timestamp. Presence of these time ranges lets HBase figure out which store files it should be scanning and which ones to skip. This comes in handy especially for temporal data when the queries are focused towards the tail end of the data.
  • Enables Phoenix to leverage the existing optimizations in place when querying against primary key columns.

Lets look at an example with some performance numbers to understand when a ROW_TIMESTAMP column could help.

Sample schema:

For performance analysis, we created two identical tables, one with the new ROW_TIMESTAMP qualifier and one without. 

CREATE TABLE EVENTS_RTS (
    EVENT_ID CHAR(15) NOT NULL,
    EVENT_TYPE CHAR(3) NOT NULL,
    EVENT_DATE DATE NOT NULL,
    APPLICATION_TYPE VARCHAR,
    SOURCE_IP VARCHAR
    CONSTRAINT PK PRIMARY KEY (
        EVENT_ID,
        EVENT_TYPE,
        EVENT_DATE ROW_TIMESTAMP))

The initial data load of 500 million records created data with the event_date set to dates over the last seven days. During the load, tables went through region splits and major compactions. After the initial load, we ran a mixed read/write workload with writes (new records) happening @500K records per hour. Each new row was created with EVENT_DATE as the current date/time.

Three sets of queries were executed that filtered on the EVENT_DATE column:

  • Newer than last hour's event data
  • Newer than last two day's event data
  • Outside of the time range of event data

For example, the following query would return the number of rows for the last hours worth of data:

SELECT COUNT(*) FROM EVENTS_RTS
WHERE EVENT_DATE > CURRENT_DATE() - 1/24

Below is the graph that shows variation of query times over the tail end of data (not major compacted) for the two tables

Below is a tabular summary of the various time ranges that were tested over the non-major compacted event data

Time # Duration(ms)
Range Rows Returned With Optimization Without Optimization
CREATED IN LAST 1 MINUTE 16K 200 4000
CREATED IN LAST 15 MINUTES 125K 700 130000
CREATED IN LAST 1 HOUR 500K 2100 500000
CREATED BEFORE LAST 8 DAYS 0 100 340000

As you can see from the results, using a ROW_TIMESTAMP gives a huge perf boost when querying over data that hasn’t been major compacted. For already major compacted data, the two tables show the same performance (i.e. there is no degradation). The query returning 0 records is a special case in which the date range falls out of the data that was loaded to the tables. Such a query returns almost instantaneously for EVENTS_RTS (0.1 seconds). The same query on EVENTS_WITHOUT_RTS takes more than 300 seconds. This is because with the time range information available on scans, HBase was quickly able to figure out that no store files have data within the range yielding a near instant response.

Effect of HBase major compaction

The HBase store file (HFile) stores time range (min and max row timestamps) in its metadata. When a scan comes in, HBase is able to look at this metadata and figure out whether it should be scanning the store file for returning the records the query has requested. When writes are happening to an HBase table, after crossing a threshold size, contents of the memstore are flushed to an HFile. Now if the queries are against the newly created (tail-end of data) HFiles, one would see a huge perf boost when using the ROW_TIMESTAMP column. This is because, the scans issued by Phoenix would need to read only these newly created store files. On the other hand, queries not utilizing the row_timestamp column will have to potentially scan the entire table.

The perf benefits are negated however, when HBase runs a major compaction on the table. In the default compaction policy, when number of HFiles exceeds a certain threshold or when a pre-determined time period crosses, HBase performs a major compaction to consolidate the number of store files in a region to one. This effectively ends up setting the time range of the lone store file to all the data contained within that region. As a result, scans are no longer able to filter out what store files to skip since the lone store file happens to contain all the data. Do note that in such a condition, the performance of the query with the row_timestamp column is the same as the one without.

In conclusion, if your table has a date based primary key and your queries are geared towards the tail-end of the data, you should think about using a row_timestamp column as it could yield huge performance gains.

Potential Future Work

One question you may be asking yourself is Why does performance drop after a major compaction occurs? I thought performance was supposed to improve after compaction. Time series data is different than other data in that it's typically write-once, append only. There are ways that this property of the data can be exploited such that better performance is maintained. For some excellent ideas along these lines, see Vladimir Rodionov's presentation from a previous HBase Meetup here.

Thursday August 13, 2015

Spatial data queries in Phoenix

Take a look at this excellent write-up by Dan Meany on implementing spatial data queries on Apache Phoenix using UDFs and secondary indexinghttps://github.com/threedliteguy/General/wiki/Adding-spatial-data-queries-to-Phoenix-on-HBase

Thursday July 30, 2015

Announcing Phoenix 4.5 released

The Apache Phoenix team is pleased to announce the immediate availability of the 4.5.0 release. Phoenix is a relational database layer on top of Apache HBase accessed as a JDBC driver for querying, updating, and managing HBase tables using SQL. The 4.x releases are compatible with HBase 0.98/1.0/1.1.

Here are some of the highlights of the 4.4 and 4.5 releases:

Source and binary downloads are available here.

Monday June 29, 2015

Spark Integration in Apache Phoenix

Today's blog is brought to you by our latest committer and the developer behind the Spark integration in Apache Phoenix, Josh Mahonin, a Software Architect at Interset.

PageRank with Phoenix and Spark

The Phoenix SQL interface provides a lot of great analytics capabilities on top of structured HBase data. Some tasks however, such as machine learning or graph analysis, are more efficiently done using other tools like Apache Spark.

Since the Phoenix 4.4.0 release, the phoenix-spark module allows us to expose Phoenix tables as RDDs or DataFrames within Spark. From there, that same data can be used with other tools within Spark, such as the machine learning library MLlib, the graph engine GraphX, or Spark Streaming.

This example makes use of the Enron email test set from Stanford Network Analysis Project, and executes the GraphX implementation of PageRank on it to find interesting entities. It then saves the results back to Phoenix.

Note that runnable source code is also available on Github

Prerequisites

  • Phoenix 4.4.0+
  • Spark 1.3.0+ (ensure phoenix-client JAR is in the Spark driver classpath, see setup guide )

Load sample data

Login to a node with the Apache Phoenix binaries available. I will use localhost to refer to the Phoenix URL, but you may need to adjust to your local environment

cd /path/to/phoenix/bin
./sqlline.py localhost

Once in the SQLLine console, we'll create the tables to hold the input data, and the destination table for the pagerank results

CREATE TABLE EMAIL_ENRON(
    MAIL_FROM BIGINT NOT NULL, 
    MAIL_TO BIGINT NOT NULL 
    CONSTRAINT pk PRIMARY KEY(MAIL_FROM, MAIL_TO));
CREATE TABLE EMAIL_ENRON_PAGERANK(
    ID BIGINT NOT NULL, 
    RANK DOUBLE 
    CONSTRAINT pk PRIMARY KEY(ID));

Use 'ctrl+d' to exit SQLline

Download and extract the file enron.csv.gz to a local directory, such as /tmp. We'll use 'psql.py' to load the CSV data

gunzip /tmp/enron.csv.gz
./psql.py -t EMAIL_ENRON localhost /tmp/enron.csv

When finished, you should see the output:
CSV Upsert complete. 367662 rows upserted

Interactive analysis with spark-shell

Login to a node with Spark installed. Note that the phoenix-client JAR must be available in the Spark driver classpath

cd /path/to/spark/bin
./spark-shell

Once you're in the spark shell, you can type, or copy the code below into the interactive shell

import org.apache.spark.graphx._
import org.apache.phoenix.spark._

// Load the phoenix table
val rdd = sc.phoenixTableAsRDD("EMAIL_ENRON", Seq("MAIL_FROM", "MAIL_TO"), zkUrl=Some("localhost"))

// Convert to an RDD of VertexId tuples
val rawEdges = rdd.map{ e => (e("MAIL_FROM").asInstanceOf[VertexId], e("MAIL_TO").asInstanceOf[VertexId]) }

// Create a graph with default edge weights
val graph = Graph.fromEdgeTuples(rawEdges, 1.0)

// Run page rank
val pr = graph.pageRank(0.001)

// Save to Phoenix
pr.vertices.saveToPhoenix("EMAIL_ENRON_PAGERANK", Seq("ID", "RANK"), zkUrl = Some("localhost"))

Once finished, you can exit spark-shell with 'ctrl+d'

Results

On your Phoenix node, open sqlline again

cd /path/to/phoenix/bin ./sqlline.py localhost

Let's run a query that will give us the top-ranked entities from the PageRank results

SELECT * FROM EMAIL_ENRON_PAGERANK ORDER BY RANK DESC LIMIT 5;

+------------------------------------------+------------------------------------------+
|                    ID                    |                   RANK                   |
+------------------------------------------+------------------------------------------+
| 5038                                     | 497.2989872977676                        |
| 273                                      | 117.18141799210386                       |
| 140                                      | 108.63091596789913                       |
| 458                                      | 107.2728800448782                        |
| 588                                      | 106.11840798585399                       |
+------------------------------------------+------------------------------------------+

Although this data-set has the email addresses removed, if you're curious, you can find results of a similar analysis here. If you're familiar with the Enron case, some of those names will ring a bell.

Conclusion

Although this example is fairly trivial, it shows the capabilities, as well as succinctness, of using Phoenix and Spark together to run complex algorithms across arbitrarily large datasets. In my experience, the methods shown here extend quite well to other "big data" problems such as community detection and clustering, as well as anomaly detection. There are likely many other problem domains which are applicable as well

Thanks for reading!

Friday April 24, 2015

TPC in Apache Phoenix

I often get asked "Does Apache Phoenix support joins?" That's when I know that marketing is hard as Phoenix has supported joins for over a year! In fact, thanks to the excellent work of Maryann Xue, one of our project management committee (PMC) members, Phoenix can run many of the complex TPC queries. TPC benchmarks are used in evaluating the performance of computer systems. From wikipedia:

Transaction Processing Performance Council (TPC) is a non-profit organization founded in 1988 to define transaction processing and database benchmarks and to disseminate objective, verifiable TPC performance data to the industry.  

Here are some of the query constructs that Phoenix support that you may not know about:

Derived Tables to create on-the-fly views:

  SELECT LastName, FirstName
    FROM
      ( SELECT BusinessEntityID, LastName, FirstName FROM Employee
        WHERE State = 'NY' ) AS EmployeeDerivedTable
    WHERE LastName = "Smith"
    ORDER BY FirstName;

Correlated Sub-queries to correlate column values in inner and outer queries:

  SELECT DISTINCT e.LastName, e.FirstName, e.BusinessEntityID, sp.Bonus
  FROM Employee AS e
  JOIN SalesPerson AS sp ON c.BusinessEntityID = sp.BusinessEntityID
  WHERE c.Bonus >=
  ( SELECT average(sp2.Bonus)
    FROM SalesPerson sp2
    JOIN Employee AS e2 ON e2.BusinessEntityID = sp2.BusinessEntityID
    WHERE e.DepartmentID = e2.DepartmentID );

Semi/Anti Joins to test for the existence or nonexistence of a row:

  SELECT p.Name, p.Job, p.EmployerID
  FROM People AS p
  WHERE EXISTS ( SELECT 1 FROM Employers AS e WHERE e.ID = p.EmployerID );

Union All to concatenate rows from multiple tables (available in 4.4.0 release):

  SELECT * FROM Employee e
  WHERE e.BusinessEntityID IN
  ( SELECT BusinessEntityID FROM SalesPerson WHERE Ranking >= 5.0
    UNION ALL
    SELECT BusinessEntityID FROM CustomerReview WHERE Score >= 8.0 ) 

Phoenix has support for both a broadcast hash join strategy, when one side of the join is small enough to fit into memory, as well as a sort merge join strategy for cases when it won't. Put all this functionality together, and Phoenix is capable of running many TPC queries.

Here's an example query from one of the TPC queries:

  SELECT supp_nation, cust_nation, l_year, sum(volume) as revenue
   FROM ( SELECT
    n1.n_name as supp_nation, n2.n_name as cust_nation,
    year(l_shipdate) as l_year, 
    l_extendedprice * (1 - l_discount) as volume
    FROM Supplier
    JOIN LineItem ON s_suppkey = l_suppkey
    JOIN Orders ON o_orderkey = l_orderkey
    JOIN Customer ON c_custkey = o_custkey
    JOIN Nation n1 ON s_nationkey = n1.n_nationkey
    JOIN Nation n2 ON c_nationkey = n2.n_nationkey
    WHERE ( n1.n_name = '[NATION1]' and n2.n_name = '[NATION2]' )
       OR (n1.n_name = '[NATION2]' and n2.n_name = '[NATION1]') )
       AND l_shipdate between date '1995-01-01' and date '1996-12-31'
  ) AS Shipping
  GROUP BY supp_nation, cust_nation, l_year
  ORDER BY supp_nation, cust_nation, l_year;

So, please repeat after me: "YES, Phoenix supports joins" :-)

Interested in learning more? Come hear Maryann Xue talk at the upcoming Apache Phoenix meetup on May 5th at the Hortonworks HQ in Santa Clara, or hear our joint talk at HBaseCon 2015 on May 7th where we'll discuss our plan for getting the rest of the way to complete ANSI SQL 92 support.

Tuesday February 24, 2015

Announcing Phoenix 4.3 released

The Apache Phoenix team is pleased to announce the immediate availability of the 4.3 release. Phoenix is a relational database layer on top of Apache HBase accessed as a JDBC driver for querying, updating, and managing HBase tables using SQL. The 4.x releases are compatible with HBase 0.98.1 and above.

The release includes:

  • Functional indexes to allow arbitrary expressions to be indexed
  • Map reduce over Phoenix tables
  • Cross join support
  • Query hint to force index usage
  • Set HBase properties through ALTER TABLE statement
  • ISO-8601 date format support on input
  • New RAND built-in function to generate random numbers
  • ANSI SQL date/time literal support
  • Query timeout support in JDBC Statement
  • ~90 bug fixes

Source and binary downloads are available here.

Thursday December 11, 2014

Announcing Phoenix 4.2.2 and 3.2.2 released

The Apache Phoenix team is pleased to announce the immediate availability of the 4.2.2/3.2.2 release. Phoenix is a relational database layer on top of Apache HBase accessed as a JDBC driver for querying, updating, and managing HBase tables using SQL. The 4.x releases are compatible with HBase 0.98 while the 3.x releases are compatible with HBase 0.94.

The release includes:

Source and binary downloads are available here.

Friday September 19, 2014

Getting started with Phoenix just got easier

Just wanted to pass along a very nice blog from SequenceIQ on a pre-cooked Docker that they setup for Phoenix+HBase: http://blog.sequenceiq.com/blog/2014/09/04/sql-on-hbase-with-apache-phoenix/

Calendar

Search

Hot Blogs (today's hits)

Tag Cloud

Categories

Feeds

Links

Navigation