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 at Cloud Expo, taking place Nov 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 19th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time to wait for long dev...
Amazon has gradually rolled out parts of its IoT offerings in the last year, but these are just the tip of the iceberg. In addition to optimizing their back-end AWS offerings, Amazon is laying the ground work to be a major force in IoT – especially in the connected home and office. Amazon is extending its reach by building on its dominant Cloud IoT platform, its Dash Button strategy, recently announced Replenishment Services, the Echo/Alexa voice recognition control platform, the 6-7 strategic...
19th Cloud Expo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Meanwhile, 94% of enterpri...
Using new techniques of information modeling, indexing, and processing, new cloud-based systems can support cloud-based workloads previously not possible for high-throughput insurance, banking, and case-based applications. In his session at 18th Cloud Expo, John Newton, CTO, Founder and Chairman of Alfresco, described how to scale cloud-based content management repositories to store, manage, and retrieve billions of documents and related information with fast and linear scalability. He addres...
Akana has announced the availability of version 8 of its API Management solution. The Akana Platform provides an end-to-end API Management solution for designing, implementing, securing, managing, monitoring, and publishing APIs. It is available as a SaaS platform, on-premises, and as a hybrid deployment. Version 8 introduces a lot of new functionality, all aimed at offering customers the richest API Management capabilities in a way that is easier than ever for API and app developers to use.
Kubernetes, Docker and containers are changing the world, and how companies are deploying their software and running their infrastructure. With the shift in how applications are built and deployed, new challenges must be solved. In his session at @DevOpsSummit at19th Cloud Expo, Sebastian Scheele, co-founder of Loodse, will discuss the implications of containerized applications/infrastructures and their impact on the enterprise. In a real world example based on Kubernetes, he will show how to ...
Personalization has long been the holy grail of marketing. Simply stated, communicate the most relevant offer to the right person and you will increase sales. To achieve this, you must understand the individual. Consequently, digital marketers developed many ways to gather and leverage customer information to deliver targeted experiences. In his session at @ThingsExpo, Lou Casal, Founder and Principal Consultant at Practicala, discussed how the Internet of Things (IoT) has accelerated our abil...
With so much going on in this space you could be forgiven for thinking you were always working with yesterday’s technologies. So much change, so quickly. What do you do if you have to build a solution from the ground up that is expected to live in the field for at least 5-10 years? This is the challenge we faced when we looked to refresh our existing 10-year-old custom hardware stack to measure the fullness of trash cans and compactors.
SYS-CON Events announced today that Isomorphic Software will exhibit at DevOps Summit at 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Isomorphic Software provides the SmartClient HTML5/AJAX platform, the most advanced technology for building rich, cutting-edge enterprise web applications for desktop and mobile. SmartClient combines the productivity and performance of traditional desktop software with the simp...
The emerging Internet of Everything creates tremendous new opportunities for customer engagement and business model innovation. However, enterprises must overcome a number of critical challenges to bring these new solutions to market. In his session at @ThingsExpo, Michael Martin, CTO/CIO at nfrastructure, outlined these key challenges and recommended approaches for overcoming them to achieve speed and agility in the design, development and implementation of Internet of Everything solutions wi...
Cloud computing is being adopted in one form or another by 94% of enterprises today. Tens of billions of new devices are being connected to The Internet of Things. And Big Data is driving this bus. An exponential increase is expected in the amount of information being processed, managed, analyzed, and acted upon by enterprise IT. This amazing is not part of some distant future - it is happening today. One report shows a 650% increase in enterprise data by 2020. Other estimates are even higher....
I wanted to gather all of my Internet of Things (IOT) blogs into a single blog (that I could later use with my University of San Francisco (USF) Big Data “MBA” course). However as I started to pull these blogs together, I realized that my IOT discussion lacked a vision; it lacked an end point towards which an organization could drive their IOT envisioning, proof of value, app dev, data engineering and data science efforts. And I think that the IOT end point is really quite simple…
Internet of @ThingsExpo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 19th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago. All major researchers estimate there will be tens of billions devices - comp...
Qosmos has announced new milestones in the detection of encrypted traffic and in protocol signature coverage. Qosmos latest software can accurately classify traffic encrypted with SSL/TLS (e.g., Google, Facebook, WhatsApp), P2P traffic (e.g., BitTorrent, MuTorrent, Vuze), and Skype, while preserving the privacy of communication content. These new classification techniques mean that traffic optimization, policy enforcement, and user experience are largely unaffected by encryption. In respect wit...
"My role is working with customers, helping them go through this digital transformation. I spend a lot of time talking to banks, big industries, manufacturers working through how they are integrating and transforming their IT platforms and moving them forward," explained William Morrish, General Manager Product Sales at Interoute, in this SYS-CON.tv interview at 18th Cloud Expo, held June 7-9, 2016, at the Javits Center in New York City, NY.