hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mich Talebzadeh" <m...@peridale.co.uk>
Subject RE: optimize joins in hive 1.2.1
Date Mon, 18 Jan 2016 09:56:14 GMT
Hi,

 

 

Your point on 

 

“Basically modern technologies, such as Hive, but also relational database,
suggest to prejoin tables and working on big flat tables. The reason is that
they are distributed systems and you should avoid transferring for each
query a lot of data between nodes.”

 

Can you please clarify whether you are suggesting to merge fact table with
dimension tables in Hive as an example, In other words further demoralise
it?

 

Thanks

 

 

Dr Mich Talebzadeh

 

LinkedIn
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUr
V8Pw

 

Sybase ASE 15 Gold Medal Award 2008

A Winning Strategy: Running the most Critical Financial Data on ASE 15

http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.
pdf

Author of the books "A Practitioner’s Guide to Upgrading to Sybase ASE 15",
ISBN 978-0-9563693-0-7. 

co-author "Sybase Transact SQL Guidelines Best Practices", ISBN
978-0-9759693-0-4

Publications due shortly:

Complex Event Processing in Heterogeneous Environments, ISBN:
978-0-9563693-3-8

Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume
one out shortly

 

http://talebzadehmich.wordpress.com

 

NOTE: The information in this email is proprietary and confidential. This
message is for the designated recipient only, if you are not the intended
recipient, you should destroy it immediately. Any information in this
message shall not be understood as given or endorsed by Peridale Technology
Ltd, its subsidiaries or their employees, unless expressly so stated. It is
the responsibility of the recipient to ensure that this email is virus free,
therefore neither Peridale Technology Ltd, its subsidiaries nor their
employees accept any responsibility.

 

-----Original Message-----
From: Jörn Franke [mailto:jornfranke@gmail.com] 
Sent: 18 January 2016 08:37
To: user@hive.apache.org
Subject: Re: optimize joins in hive 1.2.1

 

Do you have some data model?

 

Basically modern technologies, such as Hive, but also relational database,
suggest to prejoin tables and working on big flat tables. The reason is that
they are distributed systems and you should avoid transferring for each
query a lot of data between nodes. 

Hence, Hive works with the ORC format (or Parquet) to support storage
indexes and bloom filters. The idea here is that these techniques allow you
to skip reading a lot of data blocks. So the first optimization is using the
right format. The second optimization is to use compression. For example,
snappy allows fast decompression so it is more suitable for current data.
The third optimizations are partitions and buckets. The fourth optimization
is evaluating the different type of joins. The fifths optimizations can be
in-memory technologies, such as ignite HDFS cache or llap. The six
optimization is the execution engine. Tez supports currently in Hive the
most optimizations. The seventh optimization is data model: use for join
keys int or similar numeric values. Try to use the right data type for your
needs. The eight optimization can be hardware improvements.  The ninth
optimization can be use the right data structure. If your queries are more
graph type queries then consider using a graph database, such as TitanDB.
The tenth type of optimizations are os/network level type of optimizations.
For example using Jumbo frames for your network.

Not all necessary in this order and there is much more to optimize.

 

But as always this depends on your data structure. Sometimes joins can make
sense. Most of the time you have to experiment.

 

> On 18 Jan 2016, at 09:07, Divya Gehlot < <mailto:divya.htconex@gmail.com>
divya.htconex@gmail.com> wrote:

> 

> Hi,

> Need tips/guidance to optimize(increase perfomance) billion data rows
joins in hive .

> 

> Any help would be appreciated.

> 

> 

> Thanks,

> Divya 


Mime
View raw message