SYS-CON MEDIA Authors: Liz McMillan, Sean Houghton, Glenn Rossman, Ignacio M. Llorente, Xenia von Wedel

Article

Performance Comparison Testing of Hive, esProc, and Impala Part 1

Three data computing languages

Performance comparison within Hive, Impala and esProc in grouping

summarizing, and join computing.

Hardware environment

PC count: 4
CPU: Intel Core i5 2500 (4 cores)
RAM: 16G
HDD: 2T/7200 rpm
Ethernet adapter: 1000 M

Software environment

OS: CentOS6. 4
JDK: 1. 6
Hadoop/hdfs 2. 2. 0

Test Result

Hive  0. 11. 0
esProc 3. 1
Impala 1. 2. 0

Data sampling

1. Restart PC before every test
2. Print the start time in the log before executing task
3. Print the end time in the log after executing task
4. Subtract the starting time from the ending time as the reference result
5. Repeat the step 1-4 for three times, and get the average value of the reference result as the final result of the test of this round

Test scenario

In order to ensure the test data is typical and comparable, the three products must go through the same computing. The Hive or Impala is designed for the data warehouse, providing the SQL-like syntax as the only available syntax. By comparison, esProc is designed as the complex procedural computing script, but not the data warehouse. In other words, esProc does not provide the SQL -style syntax directly, and esProc script can achieve the result of SQL computing by simulating in a more convenient style. So, the test computation this time is the SQL-style grouping, summarizing, and join operations.

In this test report, we use the HDFS and Hive incorporated in CDH5.0beta, while not the Hadoop that issued separately. This is because the Hadoop deployment and setup is rather complex, and the testing environment can frequently go wrong. But it is comparatively easy for CDH. esProc is easy to setup with an installation package of dozens MBs.

esProc supports both HDFS and the much faster operations on local disks, while Hive or Impala only supports HDFS. In order to test the extreme performances of these three solutions, esProc use the local disk for test, and split the data into several files and distribute them on several machines in advance, while Hive or Impala uses HDFS.

Grouping and Summarizing Test for Narrow Table

Data sample:
Table name: p_narrow
Col. count: 11
Row count: 500 million rows
Space occupied if saving as text: 120. 6G.
Data structure: personid int,name string,sex int,cityid int,birthday int,degree int,col1 string,col2 int,col3 int,col4 int,col5 string
Test case:
1.1 col. to group & 1 col. to summarize
Hive: select personid%10000, sum(col3) from p_narrow group by personid%10000
esProc: The codes fall into 3 parts. They are respectively: Program of summary machine, main program for node machine, and subprogram for node machine.

 

 


Impala: select personid%10000, sum(col3) from p_narrow group by personid%10000

2. 1 col. to group & 4 col. to summarize

Hive: select personid%10, count(col1), max(col2), sum(col3), count(col5) from p_narrow group by personid%10
esProc: The program for summary machine in cell A4 is changed to:
=A3. groups(personid: personid;count(cul1count): cul1count,max(cul2count): cul2count,sum(cul3sum): cul3sum,count(cul5): cul5count)
The main program for node machine in cell A5 is:
=A4. groups@o(personid: groups@o(personid: cu1count,max(col2count): cul2count,sum(col3sum): cul3sum,count(col5): cul5count)
The main program for node machine in cell A1 is:
=cursor. groups(personid%10000: personid; count(col1count): co1count, max(col2count): col2count, sum(col3sum): col3sum,count(col5): col5count)
Impala: select personid%10, count(col1), max(col2), sum(col3), count(col5) from p_narrow group by personid%10

3. 4 col. to group & 1 col. to summarize

Hive: select personid%10, cityid%10, birthdayid%10, col4%10 from p_narrow group by personid%10,cityid%10,birthdayid%10,col4%10
esProc: The program for summary machine in cell A4 is changed to:
=A3. groups(personid: personid, cityid: cityid, birthdayid: birthdayid, col4: col4; sum(cul3sum): cul3sum)
The main program for node machine in cell A5 is changed to:
=A4. groups@o(personid: personid, cityid: cityid, birthdayid: birthdayid, col4: col4; sum(col3sum): cul3sum)
The main program for node machine in cell A1 is changed to:
=cursor. groups(personid%10: personid, cityid%10: cityid, birthdayid%10: birthdayid, col4%10: col4; sum(col3sum): col3sum)
Impala: select personid%10, cityid%10, birthdayid%10, col4%10 from p_narrow group by personid%10,cityid%10,birthdayid%10,col4%10

4.4 col. to group & 4 col. to summarize

Hive: select personid%10, cityid%10, birthdayid%10, col4%10, count(col1), max(col2), sum(col3), count(col5) from p_narrow group by personid%10,cityid%10,birthdayid%10,col4%10
esProc: The program for summary machine in cell A4 is changed to:
=A3. groups(personid: personid, cityid: cityid, birthdayid: birthdayid, col4: col4; count(cul1count): cul1count,max(cul2count): cul2count,sum(cul3sum): cul3sum,count(cul5): cul5count)
The main program for node machine in cell A5 is changed to:
=A4. groups@o(personid: personid, cityid: cityid, birthdayid: birthdayid, col4: col4; count(col1count): cu1count,max(col2count): cul2count,sum(col3sum): cul3sum,count(col5): cul5count)
The main program for node machine in cell A1 is changed to:
=cursor. groups(personid%10: personid, cityid%10: cityid, birthdayid%10: birthdayid, col4%10: col4; count(col1count): co1count, max(col2count): col2count, sum(col3sum): col3sum, count(col5): col5count)
Impala: select personid%10, cityid%10, birthdayid%10, col4%10, count(col1), max(col2), sum(col3), count(col5) from p_narrow group by personid%10,cityid%10,birthdayid%10,col4%10
Test results:

Test results:


Grouping and summarizing test for wide table

Data sample:
Table name: p
Col. count: 106
Row count: 60 million
Space occupied if saving as text: 127. 9G.
Data structure: personid int,name string,sex int,cityid int,birthday int,degree int,col1 int,col2 int,col3 int,col4 int,col5 int,col6 int,col7 int,col8 int,col9 int,col10 int,col11 int,col12 int,col13 int,col14 int,col15 int,col16 int,col17 int,col18 int,col19 int,col20 int,col21 int,col22 int,col23 int,col24 int,col25 int,col26 int,col27 int,col28 int,col29 int,col30 int,col31 int,col32 int,col33 int,col34 int,col35 int,col36 int,col37 int,col38 int,col39 int,col40 int,col41 int,col42 int,col43 int,col44 int,col45 int,col46 int,col47 int,col48 int,col49 int,col50 int,col51 int,col52 int,col53 int,col54 int,col55 int,col56 int,col57 int,col58 int,col59 int,col60 int,col61 int,col62 int,col63 int,col64 int,col65 int,col66 int,col67 int,col68 int,col69 int,col70 int,col71 int,col72 int,col73 int,col74 int,col75 int,col76 int,col77 int,col78 int,col79 int,col80 int,col81 int,col82 int,col83 int,col84 string,col85 string,col86 string,col87 string,col88 string,col89 string,col90 string,col91 string,col92 string,col93 string,col94 string,col95 string,col96 string,col97 string,col98 string,col99 string,col100 string

Test case:
1.1 col. to group & 1 col. to summarize
Hive: select personid%10000, sum(col3) from p group by personid%10000
esProc: The codes can be divided into 3 parts. They are respectively: Program for summary machine, main program for node machine, and subprogram for node machine.

 

 


Impala: select personid%10000, sum(col3) from p group by personid%10000

2.1 col. to group & 4 col. to summarize

Hive: select personid%10, count(col1), max(col2), sum(col3), count(col5) from p group by personid%10
esProc: The program for summary machine in cell A4 is changed to:
=A3. groups(personid: personid;count(cul1count): cul1count,max(cul2count): cul2count,sum(cul3sum): cul3sum,count(cul5): cul5count)
The main program for node machine in cell A5 is changed to:
=A4. groups@o(personid: personid;count(col1count): cu1count,max(col2count): cul2count,sum(col3sum): cul3sum,count(col5): cul5count)
The main program for node machine in cell A1 is changed to:
=cursor. groups(personid%10000: personid; count(col1count): co1count, max(col2count): col2count, sum(col3sum): col3sum,count(col5): col5count)
Impala: select personid%10, count(col1), max(col2), sum(col3), count(col5) from p group by personid%10

3.4 col. to group & 1 col. to summarize

Hive: select personid%10, cityid%10, birthdayid%10, col4%10 from p group by personid%10,cityid%10,birthdayid%10,col4%10
esProc: The program for summary machine in cell A4 is changed to:
=A3. groups(personid: personid, cityid: cityid, birthdayid: birthdayid, col4: col4; sum(cul3sum): cul3sum)
The main program for node machine in cell A5 is changed to:
=A4. groups@o(personid: personid, cityid: cityid, birthdayid: birthdayid, col4: col4; sum(col3sum): cul3sum)
The main program for node machine in cell A1 is changed to:
=cursor. groups(personid%10: personid, cityid%10: cityid, birthdayid%10: birthdayid, col4%10: col4; sum(col3sum): col3sum)
Impala: select personid%10, cityid%10, birthdayid%10, col4%10 from p group by personid%10,cityid%10,birthdayid%10,col4%10

4.4 col. to group & 4 col. to summarize

Hive: select personid%10, cityid%10, birthdayid%10, col4%10, count(col1), max(col2), sum(col3), count(col5) from p group by personid%10,cityid%10,birthdayid%10,col4%10
esProc: The program for summary machine in cell A4 is changed to:
=A3. groups(personid: personid, cityid: cityid, birthdayid: birthdayid, col4: col4; count(cul1count): cul1count,max(cul2count): cul2count,sum(cul3sum): cul3sum,count(cul5): cul5count)
The main program for node machine in cell A5 is changed to:
=A4. groups@o(personid: personid, cityid: cityid, birthdayid: birthdayid, col4: col4; count(col1count): cu1count,max(col2count): cul2count,sum(col3sum): cul3sum,count(col5): cul5count)
The main program for node machine in cell A1 is changed to:
=cursor. groups(personid%10: personid, cityid%10: cityid, birthdayid%10: birthdayid, col4%10: col4; count(col1count): co1count, max(col2count): col2count, sum(col3sum): col3sum, count(col5): col5count)
Impala: select personid%10, cityid%10, birthdayid%10, col4%10, count(col1), max(col2), sum(col3), count(col5) from p group by personid%10,cityid%10,birthdayid%10,col4%10
Test results:


The performance testing and result comparison regarding the join computing will be discussed in the next article: Performance Comparison Testing of Hive, esProc, and Impala Part 2.

Personal blog: http://www.datakeyword.blogspot.com/
Web: http://www.raqsoft.com/product-esproc

More Stories By Jessica Qiu

Jessica Qiu is the editor of Raqsoft. She provides press releases for data computation and data analytics.

Latest Stories
The BPM world is going through some evolution or changes where traditional business process management solutions really have nowhere to go in terms of development of the road map. In this demo at 15th Cloud Expo, Kyle Hansen, Director of Professional Services at AgilePoint, shows AgilePoint’s unique approach to dealing with this market circumstance by developing a rapid application composition or development framework.
SYS-CON Events announced today Isomorphic Software, the global leader in high-end, web-based business applications, will exhibit at SYS-CON's DevOps Summit 2015 New York, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Isomorphic Software is the global leader in high-end, web-based business applications. We develop, market, and support the SmartClient & Smart GWT HTML5/Ajax platform, combining the productivity and performance of traditional desktop software ...

ARMONK, N.Y., Nov. 20, 2014 /PRNewswire/ --  IBM (NYSE: IBM) today announced that it is bringing a greater level of control, security and flexibility to cloud-based application development and delivery with a single-tenant version of Bluemix, IBM's

"BSQUARE is in the business of selling software solutions for smart connected devices. It's obvious that IoT has moved from being a technology to being a fundamental part of business, and in the last 18 months people have said let's figure out how to do it and let's put some focus on it, " explained Dave Wagstaff, VP & Chief Architect, at BSQUARE Corporation, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4-6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
The major cloud platforms defy a simple, side-by-side analysis. Each of the major IaaS public-cloud platforms offers their own unique strengths and functionality. Options for on-site private cloud are diverse as well, and must be designed and deployed while taking existing legacy architecture and infrastructure into account. Then the reality is that most enterprises are embarking on a hybrid cloud strategy and programs. In this Power Panel at 15th Cloud Expo (http://www.CloudComputingExpo.com...
"Our premise is Docker is not enough. That's not a bad thing - we actually love Docker. At ActiveState all our products are based on open source technology and Docker is an up-and-coming piece of open source technology," explained Bart Copeland, President & CEO of ActiveState Software, in this SYS-CON.tv interview at DevOps Summit at Cloud Expo®, held Nov 4-6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
The Internet of Things is not new. Historically, smart businesses have used its basic concept of leveraging data to drive better decision making and have capitalized on those insights to realize additional revenue opportunities. So, what has changed to make the Internet of Things one of the hottest topics in tech? In his session at @ThingsExpo, Chris Gray, Director, Embedded and Internet of Things, discussed the underlying factors that are driving the economics of intelligent systems. Discover ...
SYS-CON Events announced today that Windstream, a leading provider of advanced network and cloud communications, has been named “Silver Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9–11, 2015, at the Javits Center in New York, NY. Windstream (Nasdaq: WIN), a FORTUNE 500 and S&P 500 company, is a leading provider of advanced network communications, including cloud computing and managed services, to businesses nationwide. The company also offers broadband, p...
SYS-CON Media announced today that Aruna Ravichandran, VP of Marketing, Application Performance Management and DevOps at CA Technologies, has joined DevOps Journal’s authors. DevOps Journal is focused on this critical enterprise IT topic in the world of cloud computing. DevOps Journal brings valuable information to DevOps professionals who are transforming the way enterprise IT is done. Aruna's inaugural article "Four Essential Cultural Hacks for DevOps Newbies" discusses how to demonstrate the...
The move in recent years to cloud computing services and architectures has added significant pace to the application development and deployment environment. When enterprise IT can spin up large computing instances in just minutes, developers can also design and deploy in small time frames that were unimaginable a few years ago. The consequent move toward lean, agile, and fast development leads to the need for the development and operations sides to work very closely together. Thus, DevOps become...
Verizon Enterprise Solutions is simplifying the cloud-purchasing experience for its clients, with the launch of Verizon Cloud Marketplace, a key foundational component of the company's robust ecosystem of enterprise-class technologies. The online storefront will initially feature pre-built cloud-based services from AppDynamics, Hitachi Data Systems, Juniper Networks, PfSense and Tervela. Available globally to enterprises using Verizon Cloud, Verizon Cloud Marketplace provides a one-stop shop fo...
AppZero has announced that its award-winning application migration software is now fully qualified within the Microsoft Azure Certified program. AppZero has undergone extensive technical evaluation with Microsoft Corp., earning its designation as Microsoft Azure Certified. As a result of AppZero's work with Microsoft, customers are able to easily find, purchase and deploy AppZero from the Azure Marketplace. With just a few clicks, users have an Azure-based solution for moving applications to the...
“In the past year we've seen a lot of stabilization of WebRTC. You can now use it in production with a far greater degree of certainty. A lot of the real developments in the past year have been in things like the data channel, which will enable a whole new type of application," explained Peter Dunkley, Technical Director at Acision, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
SYS-CON Events announced today that IDenticard will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. IDenticard™ is the security division of Brady Corp (NYSE: BRC), a $1.5 billion manufacturer of identification products. We have small-company values with the strength and stability of a major corporation. IDenticard offers local sales, support and service to our customers across the United States and Canada...
SYS-CON Events announced today that AIC, a leading provider of OEM/ODM server and storage solutions, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. AIC is a leading provider of both standard OTS, off-the-shelf, and OEM/ODM server and storage solutions. With expert in-house design capabilities, validation, manufacturing and production, AIC's broad selection of products are highly flexible and are conf...