How does impala provide faster query response compared to hive

Question

I have recently started looking into querying large sets of CSV data lying on HDFS using Hive and Impala. As I was expecting, I get better response time with Impala compared to Hive for the queries I have used so far.

I am wondering if there are some types of queries/use cases that still need Hive and where Impala is not a good fit.

How does Impala provide faster query response compared to Hive for the same data on HDFS?

Yapon

Answer 1

You should see Impala as "SQL on HDFS", while Hive is more "SQL on Hadoop".

In other words, Impala doesn't even use Hadoop at all. It simply has daemons running on all your nodes which cache some of the data that is in HDFS, so that these daemons can return data quickly without having to go through a whole Map/Reduce job.

The reason for this is that there is a certain overhead involved in running a Map/Reduce job, so by short-circuiting Map/Reduce altogether you can get some pretty big gain in runtime.

That being said, Impala does not replace Hive, it is good for very different use cases. Impala doesn't provide fault-tolerance compared to Hive, so if there is a problem during your query then it's gone. Definitely for ETL type of jobs where failure of one job would be costly I would recommend Hive, but Impala can be awesome for small ad-hoc queries, for example for data scientists or business analysts who just want to take a look and analyze some data without building robust jobs. Also from my personal experience, Impala is still not very mature, and I've seen some crashes sometimes when the amount of data is larger than available memory.

Streeto

Thanks Charles for this explanation. "Impala doesn't provide fault-tolerance compared to Hive", does it mean if a node goes while the query is processing then it fails. Did you have some other scenario(s) in mind. – Yapon

@Integrator From an interview in May 2013, one of the product managers at Cloudera confirmed that in its current implementation, if a node fails mid-query, that query would get aborted, and the user would need to reissue that query (datanami.com/datanami/2013-05-01/…) – Streeto

@CharlesMenguy, i have a question here. 1.) When you referred "It simply has daemons running on all your nodes which cache some of the data that is in HDFS" When the actual cache Happens? Is that when the data actually gets loaded to HDFS? or Impala has its own Configuration that Cache now and then. 2.) And when you mention that "Some of the Data". Does it means that it Cache only Part of the data Set in a Table? if that is the case will it miss remaining records. – Reminisce

Answer 2

IMHO, SQL on HDFS and SQL on Hadoop are the same. After all Hadoop is HDFS( and also MapReduce). So when we say SQL on HDFS, it is understood that it is SQL on Hadoop(could be with or without MapReduce).

Coming back to the actual question, Impala provides faster response as it uses MPP(massively parallel processing) unlike Hive which uses MapReduce under the hood, which involves some initial overheads (as Charles sir has specified). Massively parallel processing is a type of computing that uses many separate CPUs running in parallel to execute a single program where each CPU has it's own dedicated memory. The very fact that Impala, being MPP based, doesn't involve the overheads of a MapReduce jobs viz. job setup and creation, slot assignment, split creation, map generation etc., makes it blazingly fast.

But that doesn't mean that Impala is the solution to all your problems. Being highly memory intensive (MPP), it is not a good fit for tasks that require heavy data operations like joins etc., as you just can't fit everything into the memory. This is where Hive is a better fit.

So, if you need real time, ad-hoc queries over a subset of your data go for Impala. And if you have batch processing kinda needs over your Big Data go for Hive.

HTH

Syllabize

"SQL on HDFS and SQL on Hadoop are the same": well, not really, since (as you say) "SQL on hadoop" = "SQL on hdfs using m/r" i.e. "SQL on hdfs" bypasses m/r completely. – Marcum

Impala, Presto, and the other fast new query engines use data in HDFS, but are not based on MapReduce. They sidestep it completely. – Agatha

I never said that impala is SQL on HDFS using MR. It is clearly specified in my answer that it uses MPP. – Syllabize

Answer 3

There are some key features in impala that makes its fast.

1. It does not use map/reduce which are very expensive to fork in separate jvms. It runs separate Impala Daemon which splits the query and runs them in parallel and merge result set at the end.

2. It does most of its operation in-memory.

3. It uses hdfs for its storage which is fast for large files. It caches as much as possible from queries to results to data.

4. It supports new file format like parquet, which is columnar file format. So if you use this format it will be faster for queries where you are accessing only few columns most of the time.

Zohar

But how would parquet file format helps in querying RDBMS queries... I'm exploring Impala, so just curios. Do share if you have any clear documentation. Thanks – Dollfuss

parquet is columnar storage and using parquet you get all those advantages you can get in columnar database. Its alot faster when you are using few columns than all of them in tables in most of your queries. – Zohar

Hive now also supports parquet, so your 4th point is no longer a difference between Impala and Hive. – Alialia

Answer 4

Impala doesn't replace MapReduce or use MapReduce as a processing engine.Let's first understand key difference between Impala and Hive.

1. Impala performs in-memory query processing while Hive does not
2. Hive use MapReduce to process queries, while Impala uses its own processing engine.
3. Hive can be extended using User Defined Functions (UDF) or writing a custom Serializer/Deserializer (SerDes); however, Impala does not support extensibility as Hive does for now
4. Impala depends on Hive to function, while Hive does not depend on any other application and just needs the core Hadoop platform (HDFS and MapReduce)
5. Impala queries are subsets of HiveQL, which means that almost every Impala query (with a few limitation) can run in Hive. But vice-versa is not true because some of the HiveQL features supported in Hive are not supported in Impala.

Now why Impala is faster than Hive in Query processing? Below are the some key points.

1. While processing SQL-like queries, Impala does not write intermediate results on disk(like in Hive MapReduce); instead full SQL processing is done in memory, which makes it faster.

2. With Impala, the query starts its execution instantly compared to MapReduce, which may take significant time to start processing larger SQL queries and this adds more time in processing.

3. Impala Query Planner uses smart algorithms to execute queries in multiple stages in parallel nodes to provide results faster, avoiding sorting and shuffle steps, which may be unnecessary in most of the cases.

4. Impala has information about each data block in HDFS, so when processing the query, it takes advantage of this knowledge to distribute queries more evenly in all DataNodes.

5. There exists Impala daemon, which runs on each DataNode. These are responsible for processing queries.When query submitted, impalad(Impala daemon) reads and writes to data file and parallelizes the query by distributing the work to all other Impala nodes in the Impala cluster.

6. Another key reason for fast performance is that Impala first generates assembly-level code for each query. The assembly code executes faster than any other code framework because while Impala queries are running natively in memory, having a framework will add additional delay in the execution due to the framework overhead.

Impala processes all queries in memory, so memory limitation on nodes is definitely a factor. You must have enough memory to support the resultant dataset, which could grow multifold during complex JOIN operations.

If a query starts processing the data and the resultant dataset cannot fit in the available memory, the query will fail.

Waller

The statements about Impala only processing queries in memory are categorically incorrect and have been for five years at this point. Impala has supported spilling to disk in some form since the 2.0 release and it's been enhanced over time. It's true Impala defaults to running in memory but it is not limited to that. – Punch

Answer 5

I can think o the following reasons why Impala is faster, especially on complex SELECT statements.

• Cloudera Impala being a native query language, avoids startup overhead which is commonly seen in MapReduce/Tez based jobs (MapReduce programs take time before all nodes are running at full capacity). In Hive, every query has this problem of “cold start” whereas Impala daemon processes are started at boot time itself, always being ready to process a query.
• For tables with a large volume of data and/or many partitions, retrieving all the metadata for a table can be time-consuming, taking minutes in some cases. Thus, each Impala node caches all of this metadata to reuse for future queries against the same table.
• Apache Hive is fault tolerant whereas Impala does not support fault tolerance. When a hive query is run and if the DataNode goes down while the query is being executed, the output of the query will be produced as Hive is fault tolerant. However, that is not the case with Impala. If a query execution fails in Impala it has to be started all over again.

Ezmeralda