Welcome!

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. [email protected](personid: [email protected](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. [email protected](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. [email protected](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. [email protected](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. [email protected](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. [email protected](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
“DevOps is really about the business. The business is under pressure today, competitively in the marketplace to respond to the expectations of the customer. The business is driving IT and the problem is that IT isn't responding fast enough," explained Mark Levy, Senior Product Marketing Manager at Serena Software, in this SYS-CON.tv interview at DevOps Summit, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
The speed of software changes in growing and large scale rapid-paced DevOps environments presents a challenge for continuous testing. Many organizations struggle to get this right. Practices that work for small scale continuous testing may not be sufficient as the requirements grow. In his session at DevOps Summit, Marc Hornbeek, Sr. Solutions Architect of DevOps continuous test solutions at Spirent Communications, explained the best practices of continuous testing at high scale, which is rele...
SYS-CON Events announced today that Catchpoint Systems, Inc., a provider of innovative web and infrastructure monitoring solutions, has been named “Silver Sponsor” of SYS-CON's DevOps Summit at 18th Cloud Expo New York, which will take place June 7-9, 2016, at the Javits Center in New York City, NY. Catchpoint is a leading Digital Performance Analytics company that provides unparalleled insight into customer-critical services to help consistently deliver an amazing customer experience. Designed ...
Internet of @ThingsExpo, taking place June 6-8, 2017 at the Javits Center in New York City, New York, is co-located with the 20th International Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. @ThingsExpo New York Call for Papers is now open.
Providing the needed data for application development and testing is a huge headache for most organizations. The problems are often the same across companies - speed, quality, cost, and control. Provisioning data can take days or weeks, every time a refresh is required. Using dummy data leads to quality problems. Creating physical copies of large data sets and sending them to distributed teams of developers eats up expensive storage and bandwidth resources. And, all of these copies proliferating...
Every successful software product evolves from an idea to an enterprise system. Notably, the same way is passed by the product owner's company. In his session at 20th Cloud Expo, Oleg Lola, CEO of MobiDev, will provide a generalized overview of the evolution of a software product, the product owner, the needs that arise at various stages of this process, and the value brought by a software development partner to the product owner as a response to these needs.
Containers have changed the mind of IT in DevOps. They enable developers to work with dev, test, stage and production environments identically. Containers provide the right abstraction for microservices and many cloud platforms have integrated them into deployment pipelines. DevOps and Containers together help companies to achieve their business goals faster and more effectively. In his session at DevOps Summit, Ruslan Synytsky, CEO and Co-founder of Jelastic, reviewed the current landscape of D...
Smart Cities are here to stay, but for their promise to be delivered, the data they produce must not be put in new siloes. In his session at @ThingsExpo, Mathias Herberts, Co-founder and CTO of Cityzen Data, discussed the best practices that will ensure a successful smart city journey.
"We provide DevOps solutions. We also partner with some key players in the DevOps space and we use the technology that we partner with to engineer custom solutions for different organizations," stated Himanshu Chhetri, CTO of Addteq, in this SYS-CON.tv interview at DevOps at 18th Cloud Expo, held June 7-9, 2016, at the Javits Center in New York City, NY.
Hardware virtualization and cloud computing allowed us to increase resource utilization and increase our flexibility to respond to business demand. Docker Containers are the next quantum leap - Are they?! Databases always represented an additional set of challenges unique to running workloads requiring a maximum of I/O, network, CPU resources combined with data locality.
@ThingsExpo has been named the ‘Top WebRTC Influencer' by iTrend. iTrend processes millions of conversations, tweets, interactions, news articles, press releases, blog posts - and extract meaning form them and analyzes mobile and desktop software platforms used to communicate, various metadata (such as geo location), and automation tools. In overall placement, @ThingsExpo ranked as the number one ‘WebRTC Influencer' followed by @DevOpsSummit at 55th.
In 2014, Amazon announced a new form of compute called Lambda. We didn't know it at the time, but this represented a fundamental shift in what we expect from cloud computing. Now, all of the major cloud computing vendors want to take part in this disruptive technology. In his session at 20th Cloud Expo, John Jelinek IV, a web developer at Linux Academy, will discuss why major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform are all trying to sidestep VMs and containers...
SYS-CON Events announced today that Enzu will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY, and the 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Enzu’s mission is to be the leading provider of enterprise cloud solutions worldwide. Enzu enables online businesses to use its IT infrastructure to their competitive ad...
SYS-CON Events announced today that MobiDev, a client-oriented software development company, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place June 6-8, 2017, at the Javits Center in New York City, NY, and the 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. MobiDev is a software company that develops and delivers turn-key mobile apps, websites, web services, and complex softw...
In his session at 19th Cloud Expo, Claude Remillard, Principal Program Manager in Developer Division at Microsoft, contrasted how his team used config as code and immutable patterns for continuous delivery of microservices and apps to the cloud. He showed how the immutable patterns helps developers do away with most of the complexity of config as code-enabling scenarios such as rollback, zero downtime upgrades with far greater simplicity. He also demoed building immutable pipelines in the cloud ...