The BusinessIntelligence for
Hadoop Benchmark
Q4 2016

Table of Contents
Hadoop as an Analytics Platform

1

Executive Summary: Key Findings

2

The Business Intelligence Evaluation Framework

3

Benchmark Data Set

4

Business Intelligence Query Types

6

Overall Engine Improvement

7

Large Data Set: Key Findings and Observations

9

Small Data Set: Characteristics and Results

11

Concurrent Query Results

14

Cluster Environment and Engine Configurations

16

Business Intelligence Benchmarks Summary18

Hadoop as an Analytics Platform
The past decade has brought significant change to the way organizations store and process data.
Much of this change is due to the innovation, adoption, and commercialization of Hadoop. The
limitations of traditional data infrastructures have led many organizations to move to Hadoop
for not only new data use cases, but for day-to-day operational workloads as well.
As Hadoop matures, enterprises are starting to use this powerful platform to serve more diverse
workloads. Hadoop is no longer just a batch-processing platform for data science and machinelearning use cases – it has evolved into a multi-purpose data platform for operational reporting,
exploratory analysis, and real-time decision support.
With the ongoing innovation of the SQL-on-Hadoop and in-memory data processing engines,
Hadoop is now able to serve business-critical workloads in production. Hadoop is now ready tobe the data source for business intelligence (BI) and online analytical processing (OLAP)
workloads. According to the 2015 Hadoop Maturity Survey conducted by AtScale, Cloudera,
Hortonworks, MapR, and Tableau, Business Intelligence (BI) is the top use case enterprises plan
to migrate to Hadoop.
As we learned from the First Edition of our BI on Hadoop Benchmarks, the performance andmaturity of the top SQL-on-Hadoop engines - Impala, Hive, and SparkSQL - make them all
suitable candidates to support business intelligence workloads. When combined with thecontinued emergence of BI as the top workload for Hadoop adopters, it becomes clear that the
adoption use of SQL-on-Hadoop engines to enable BI is a trend that will only continue to grow.

Forrester predicts that 100% of enterprises will adopt
Hadoop in the next 24 months.

1

In fact, according to the 2015 Hadoop Maturity Survey conducted by AtScale, Cloudera,
Hortonworks, Mapr, and Tableau, the top Hadoop use case for enterprises is BusinessIntelligence.
Based on this trend, coupled with the positive community reception to our first round ofbenchmarks, we conducted a second round of benchmark testing with the purpose of evaluating
the progress of existing engines as well as the evaluation of new entrants in the SQL-on-Hadoop
space. This document provides a deep look into both the methodology and results of the Second
Edition of the BI on Hadoop Performance Benchmarks.

Executive Summary: Key Findings
The goal of this benchmark is to help technology evaluators select the best SQL-on-Hadoop
technology for their use cases. Key findings include:
•

•

•

•

•

SQL-on-Hadoop engines are well suited for Business Intelligence (BI): All tested engines- Hive, Impala, Presto, and Spark SQL - successfully executed all of the queries in ourbenchmark suite and are stable enough to support business intelligence workloads.
There is no single “best engine”: We continue to see different engines shine in different
areas. Depending on raw data size, query complexity, and the target number of end-users
enterprises will find that each engine has its own ‘sweet spot’.
Version-to-version improvements are significant: The open source community continues
to drive significant and rapid improvements across the board. All engines tested showed
between 2x and 4x performance gains in the six months between the first and second
edition of the benchmarks. This is great news for those enterprises deploying BI
workloads to Hadoop.
Small vs. Big Data: Impala and Spark SQL continue to shine for small data queries (queries
against small data sets). New in this edition, the latest release of Hive LLAP (Live Long andProcess) shows suitable “small data” query response times. Presto also shows promise on
small, interactive queries.
Few vs. Many Users: Impala continues to shine in terms of concurrent query performance,
and at the same time Hive and SparkSQL show significant improvements in thiscategory. Presto, new to this edition of the benchmarks, shows the best results inconcurrency testing.

The majority of this document describes the details of the testing methodology, datasets, andqueries used to complete this benchmark. A more technical summary of the benchmark results
as well configuration details can be found at the end of this paper be found at the end of thispaper.

2

The BI Benchmark Evaluation Framework
The AtScale BI on Hadoop Benchmarks consider the following core elements when evaluating
how well SQL-on-Hadoop engines satisfy Business Intelligence workloads:•••

Performs on Big Data: the SQL-on-Hadoop engine must be able to consistently analyze
billions or trillions of rows of data without generating errors and with response times on
the order of 10s or 100s of seconds.
Fast on Small Data: the engine needs to deliver interactive performance on known query
patterns return results in no greater than several seconds on small data sets (on the orderof thousands or millions of rows).
Stable for many Users: Enterprise BI user bases consist of hundreds or thousands of data
workers, and as a result the underlying SQL-on-Hadoop engine must perform reliably
under highly concurrent analysis workloads.

We believe that the above three criteria are representative of the primary requirements thatenterprises across industries including financial services, healthcare, retail, telecommunicationand healthcare will have to meet in order to successfully execute BI workloads on Hadoop.
To be clear, there are other aspects of SQL-on-Hadoop engines that can and have been
evaluated in other studies. Breadth of SQL syntax support and engine performance for Data
Science queries remain relevant evaluation criteria for other SQL-on-Hadoop workloads. Thatsaid, the AtScale Business Intelligence benchmark focuses on traditional OLAP-style (On-Line
Analytical Processing) queries that make extensive use of aggregation functions, GROUP BYs
and WHERE clauses.

3

Benchmark Data Set
AtScale used the Star Schema Benchmark (SSB) data set to perform tests in this benchmark
study. This data set is described in greater detail here:
http://www.cs.umb.edu/~poneil/StarSchemaB.PDF.
This benchmark data set is based on the widely-used TPCH data set and has been modified tomore accurately represent a data layout (in the form of a star schema) that is common forbusiness intelligence workloads.
We used a large scale version of the SSB data set, with a focus on testing queries across large
tables: the LINEORDER table contains close to 6 billion rows. Additionally, because big data sets
on Hadoop often include dimension tables with very high cardinality (an architecturally weak
spot for traditional Multidimensional-OLAP - or MOLAP - solutions) we also expanded the sizeof the CUSTOMER table to over 1 billion rows. The row counts for each of the tables used in the
BI benchmarks are shown in Table 1 below.
Table 1: Star Schema Benchmark (SSB) Table Details
T ab le N a me

Nu m ber of Ro ws

No tesCUSTOMER

1,050,000,000

Expanded customer dimension to test large joins

LINEORDER

5,999,989,709

SUPPLIER

2,000,000

PART2,000,000

DWDATE

16,799The data layout for the SSB schema and the relationships between the key tables from thebenchmark are shown in Figure 1 on the following page.

4

Benchmark Data Set (continued)
Figure 1: Star Schema Benchmark (SSB) Table Details

5Business Intelligence Query Types
In order to truly simulate a Business Intelligence enterprise environment, 13 queries were
tested. The queries used for this benchmark can be summarized into a several higher level query
patterns, spanning from small dataset complexity to high data volume and sophistication:•••

Q1.1 - Q1.3 - “Quick Metric” queries, which compute a particular metric value for a periodof time. These queries have a small number of JOIN and minimal/no GROUP BY operations.
Q2.1 - Q2.3 - “Product Insight” queries, which compute a metric (or several metrics)
aggregated against a set of product and date dimension attributes. These queries include
medium-sized JOIN operations and a small number of GROUP BY operations.
Q3.1 - Q4.3 - “Customer Insight” queries, which compute a metric (or several metrics)
aggregated against a set of product, customer, and date based dimensions. These queries
include medium and large sized JOIN operations as well as many GROUP BY operations.

During benchmark testing all 13 queries were executed twice; once each in two modes. Mode 1
was against the large data set and Mode 2 against the tables comprising AtScale’s Adaptive
Cache™ (aggregated data generated by the AtScale Engine based on the set of 13 queries
issued). This benchmark refers to the AtScale’s Adaptive Cache tables as the aggregated data. All
queries used in the benchmark were produced by the AtScale Engine based on Tableau
Queries. The only differences between the queries between engines were semantic. To find out
more about the AtScale semantic layer and query engine, go here.
Table 2: Benchmark Query Characteristics
Query
ID

Numb ero f Joins

L argest Joi n
T abl eNumb er o f
Group By s

Numb er o fFilt ers

C omme nts

Q1.1

1

16,7990

3

1 range condition, 1 comparative filter condition directlyon LINEORDER table

Q1.2

1

16,7990

3

2 range filter conditions directly on LINEORDER table

Q1.3

1

16,7990

4

2 range filter conditions directly on LINEORDER table, 2conditions on joined table

Q2.1

3

2,000,000

22filter on p_category (less selective)

Q2.2

3

2,000,000

22filter on p_brand, 2 values (more selective)

Q2.3

3

2,000,000

22filter on p_brand, 1 value (most selective)

Q3.1

3

1,050,000,000

33filter on region (less selective)

Q3.2

3

1,050,000,000

33filter on nation (more selective)

Q3.3

3

1,050,000,000

33filter on city (most selective)

Q3.4

3

1,050,000,000

33filter on city (most selective) and month (vs. year)

Q4.1

4

1,050,000,000

2

2

Q4.2

4

1,050,000,000

33includes filter on year (more selective)

Q4.3

4

1,050,000,000

33includes filter on year and nation (most selective)

6

Overall Engine Improvement
The AtScale architecture is rooted in a belief that the relational query engines that have been
under development for many years - including Impala, Hive, Spark SQL, and Presto - willcontinue to make improvements at a pace that will outperform what an individual proprietary
approach could achieve. We are pleased to find validation of this philosophy in this Second
Edition of our BI on Hadoop Benchmarks.
As shown in Figure 2, all three SQL engines that were evaluated in both the First and Second
Editions of the BI on Hadoop Benchmarks, within a period of 6 months, showed significant
performance gains.
Figure 2: Improvements Between SQL-on-Hadoop Engine Versions

From Hive 1.2 to Hive 2.1
•

•

•

7

Performance on large queries improved, on average, more than 340%. Of the engines
tested, the version-to-version improvements we saw with Hive 2.1 with LLAP were themost significant.
Small query performance improved over 2X, making Hive with LLAP a much more
attractive engine for both large and small query patterns.
The LLAP persistent daemon was the cause of the improvement for Hive. The ability for
Hive to offload some of the query processing, cache tables and metadata and to off-load
specific I/O tasks to a persistent daemon all contributed to the performance
improvements.

Overall Engine Improvement (continued)
From Spark 1.6 to Spark 2.0•

Large query performance improved, on average, by a factor of 2.4X. These gains were dueto the whole stage codegen and parquet reader improvements implemented in Spark 2.0.

•

Small query performance remained relatively the same, consistent with previously tested
excellent performance on small tables.

•

Concurrency also was helped by the whole stage codegen and parquet reader
improvements.

From Impala 2.3 to Impala 2.6

8•

Large query performance improved, on average, by a factor of 2.8X. These gains were due
to implementation of run time filtering and improvements in the parquet reader.

•

Small query performance remained relatively the same, consistent with previously tested
excellent performance on small tables.

•

Concurrency continued to be a strong point for Impala, consistent with previous
evaluations.

Large Data Set Results and Observations
The table and chart below show the relative performance of Impala, Spark SQL, and Hive for our
13 benchmark queries against the 6 Billion row LINEORDERS table.
Table 3: Benchmark Query Results for Large Tables
Query Execution Time (in seconds)
Query

Impala 2.6

Spark 2.0

Hive 2.1 (LLAP) Presto 0.152

Q1.1

5.6

4.8

10.5

10.3

Q1.2

5.0

3.8

9.1

8.7

Q1.3

5.6

3.3

9.3

7.9

Q2.1

8.0

11.8

10.0

12.4

Q2.2

6.2

11.0

9.3

10.4

Q2.3

6.0

10.6

9.3

9.8

Q3.1

12.6

15.5

37.5

35.5

Q3.2

15.5

15.6

38.1

39.9

Q3.3

8.0

9.2

28.1

24.9

Q3.4

7.4

6.8

28.7

15.7

Q4.1

97.3

64.9

137.6

134.4

Q4.2

49.1

30.3

131.9

123.8

Q4.3

26.8

13.2

137.9

135.5

*Note: fastest query time for each row is highlighted in green

Based on the results of the Large Table Benchmarks shown in Figure 3, there are several key
observations. Figure 3 represents the results graphically for visual analysis.

9Large Data Set Results and Observations (continued)
•

•

•

•

•

No single SQL-on-Hadoop engine is best for ALL queries. Consistent with the findings in
the First Edition of the BI on Hadoop Benchmarks, Spark SQL and Impala tend to be fasterthan Hive. For many queries, the performance difference between Impala and Spark SQL
is relatively small. Presto 0.152, a newcomer to the Second Edition of the BI on Hadoop
Benchmarks, shows a performance profile that is very similar to that of Hive 2.1.
Increasing the number of joins generally increases query processing time. Going from
“Quick Metric” to “Product Insight” queries (where the number of joins goes from 1 to 3)
had the largest query time difference on Hive and Presto. For very large joins Spark and
Impala also showed increases in query response time.
Increased query selectivity resulted in reduced query processing time (which may
reduce the total amount of data that needs to be included in the query processing steps).
Both Impala and Spark show performance improvements as query selectivity
increases. The sensitivity to selectivity is consistent with earlier versions of Impala, while
Spark shows improvements vs. earlier versions. Hive and Presto are much less sensitive
to query selectivity.
JOIN operations between very large tables increased query processing time for all
engines. As expected, joining two large tables was an expensive operation for all engines.
This was particularly true when involving the 1 Billion-row CUSTOMERS table.
As the number of joins increases, Impala and Spark SQL are more likely to perform
best. Consistent with our earlier benchmarks, Impala and Spark SQL tend to outperform
Presto and Hive for queries involving larger joins.

Figure 3: Benchmark Query Results for Large Tables

10Small Data Set: Characteristics & Results
Business Intelligence architectures rely on caching mechanisms in the form of in-memory stores,
pre-materialized data structures, and aggregate tables. As such, a complete set of BusinessIntelligence benchmarks needs to evaluate the performance of the execution engines against
such acceleration mechanisms.
In order to evaluate each of the engines for the benchmarks the team re-executed the Large
Table Queries after the tables comprising the AtScale Adaptive Cache were generated. Thesetables contain aggregated measures for the attributes included in the original raw data queries,
but (due to these aggregations) contain fewer rows of data. Note - AtScale Adaptive Cache tables
are stored on the Hadoop cluster in the form of Parquet or ORC tables, and can be queried by any SQL
on Hadoop engine.
AtScale Adaptive Cache™ technology provides two key functions:••

Cache creation: after the first queries have been executed, the AtScale engine uses
heuristic functions to determine if it is optimal to store related query result sets toaccelerate the processing of future queries. If so, one more aggregate tables may becreated in the Adaptive Cache.
Cache management: after the first caching results have been stored, the AtScale engine
manages the Adaptive Cache result sets to optimize for new queries, incorporate newdata entering the Hadoop cluster and to remove low-value aggregate tables.

The Table 4 shows the performance of these engines against such aggregate tables.

11

Small Data Set: Characteristics & Results (continued)
Table 4: Benchmark Query Results for Cache Tables
Query Execution Time (in seconds)
Query

Impala 2.6

Spark 2.0

Hive 2.0 LLAP

Presto 0.152

Q1.1

0.26740.47952.07520.3595Q1.2

0.29830.47982.07720.3841Q1.3

0.64240.69782.10790.4856

Q2.1

3.137

3.33714.30563.9627Q2.2

2.20932.279

4.2811

3.2376Q2.3

2.35412.25194.27562.7475Q3.1

1.63811.1865

3.27812.3797Q3.2

2.56931.642

8.0862

1.9072Q3.3

2.12851.28192.27551.9804Q3.4

2.40622.57855.471

3.7276Q4.1

3.4521

5.577

6.93835.9396Q4.2

4.04945.37637.6193

6.501

Q4.3

4.3873

3.582

4.52643.4225*Note: fastest query time for each row is highlighted in green

AtScale Adaptive Cache: Summary of Results
Our benchmark results indicate that both Impala, Spark SQL and Presto perform best on the
AtScale Adaptive Cache tables, effectively returning query results on our 6 Billion row data set
with query response times ranging from from under 300 milliseconds to several seconds.
With Hive 2.1 and the inclusion of LLAP, we also see a significant improvement in Hive query
performance against the smaller tables, with an average speedup of ~2X. With this latest roundof benchmark testing we feel that all four SQL-on-Hadoop engines can be legitimate options forboth the large and small table query engines.
Figure 4 showcases the roughly similar query performance profiles of all 4 engines whenbenchmarked against the aggregate cache tables.

12Small Data Set: Characteristics & Results (continued)
Figure 4 showcases the roughly similar query performance profiles of all 4 engines whenbenchmarked against the aggregate cache tables.
Figure 4: Benchmark Query Results for Cache Tables

Our benchmark results also illustrate significant performance gains that can be realized fromAtScale’s Adaptive Cache™ technology, with query performance for the same query improving
by as much as 50X. In real-life deployments on larger data sets (on the order of 100s of Billions ofrows) AtScale customers have seen performance gains over 100-200X as a result of thistechnology.

13

Concurrent Query Results
Business Intelligence (BI) workloads often involve many data workers issuing queries at the sametime. Concurrency is therefore a key factor when evaluating performance of a BI platform. And
with an increasing number of enterprises deploying more and more production BI workloads
against Hadoop platforms, there is an increasing focus on how SQL-on-Hadoop engines support
concurrent query workloads.
To properly assess engine performance against concurrent queries, AtScale tested eachbenchmark query with a range concurrent users. A test was executed for each of the 13 queries
with each test simultaneously submitting 1, 5, 10, or 25 queries simultaneously. Since
production BI workloads consistently execute against aggregate tables, the results publishedbelow are for queries against the AtScale Adaptive Cache aggregate tables (the “small data set”
results discussed above).
Figure 5: Concurrency Profile for Quick Metric Queries

The chart above shows how the 4 tested engines perform for the “Quick Metric” queries (Q1.x
quereis from Table 1) as simultaneous concurrent query volumes increase. As in the First
Edition of the benchmarks,

14

Concurrent Query Results (continued)
While all engines were able to scale to support up to 25+ concurrent users, we did observe
different behaviors from the various engines tested. Impala’s performance scaled better than
Spark SQL or Hive as the number of concurrent users increased these results are consistent with
AtScale’s experience using Impala in real-world customer deployments. Presto’s MPP
architecture also delivers excellent concurrent query scaling results, showing virtually no
degradation with up to 25 concurrent Quick Metric queries. It is worth noting that both Prestoand Impala were able to satisfy the 25 concurrent user workload and maintain an average query
response time of under 1 second.
In addition to looking at dashboard style queries represented by the Quick Metric benchmarks,
we also looked at the concurrency profile for more complex analytic queries (represented byqueries Q2.x through Q4.x in Table 1). The results are shown in Figure 6.
Figure 6: Concurrency Profile for Quick Metric Queries

The results are as follows:•

All engines demonstrate consistent query performance degradation under concurrent
workload.

•

There were no query failures for any of the engines tested, up to 25 concurrent queries.

•

15

While all engines can effectively handle a large number of concurrent analytic queries,
Spark, Impala, and Presto maintain a better average performance profile vs. Hive asconcurrent query workload increases.

Cluster Environment & Engine Configurations
In order to evaluate the base performance of the SQL-on-Hadoop engines we compared theperformance of Cloudera Impala, Hive-on-Tez, and Spark SQL, and Presto running against aretail data set as defined in the initial section of this document.
The test environment was an isolated 12 node cluster with 1 master node, 1 gateway node, and10 data nodes.
Benchmark Cluster Configuration Details
RAM per node

128G

CPU specs for data (worker) nodes

32 CPU cores

Storage specs for data (worker) nodes

2x 512mb SSD

The SQL-on-Hadoop engine benchmarks were performed by running each engine individually
against the same based Hadoop cluster configuration and data set. Details about engine-specificconfigurations and settings are below:

Impala Configuration
Impala Version

2.6

File Format

ParquetWorkers

10

Memory per worker

110G--num_hdfs_worker_threads

32--max_cached_file_handles

3000

16

Cluster Environment & Engine Configurations (continued)
SparkSQL Configuration
Spark Version

2.0.1-SNAPSHOT

File Format

ParquetWorkers

70

Memory per worker

16G

Cores per worker

4

Spark.kryoserializer.buffer.max

512

Hive LLAP Configuration
Tez Version

0.8.5

Hive Version

2.1

File Format

ORC

LLAP Settings--instances 10 --cache 50000m --xmx
55000m --size 110000m --executors 32

hive.execution.mode

llap

hive.llap.execution.mod

allhive.llap.io.enabledtrue

Presto Configuration
Presto Version

0.152

File Format

ORC

Nodes

1 coordinator
10 worker

Memory per node

110G

sink.max-buffer-size

10GB

hive.force-local-schedulingtrue

distributed-joins-enabled

false

NOTE: AtScale used default out-of-the box configuration settings and virtual machine instances to execute this benchmark.
These results should be considered an assessment of relative engine performance and not a rigorous or complete
performance evaluation of any single engine. While we did attempt to properly tune and configure each engine for optimal
performance using publicly available best-practices and documentation, AtScale received no assistance from any othervendors.

17

Business Intelligence Benchmarks Summary18