distribute by in hive

DISTRIBUTE BY
=============
1. DISTRIBUTE BY controls how map output will be divided among Reducers.
2. In the below example DISTRIBUTE BY ensures that records of ename will go to the same reducer and then to SORT the same
   data the way we want (ascending order of empid).
3. DISTRIBUTE BY works similar to GROUP BY in the sence that how it controls the reducers to receives the rows for  processing.

NOTE: Hive requires DISTRIBUTE BY clause comes BEFORE the SORT BY if we are using both in a query.
-----------------------------------
hive> select * from disttab;
OK
108   Sandeep     22000
109   Sandeep     22500
110   Sandeep     23500
111   Sandeep     24340
112   Karan 45000
113   Karan 45600
101   Ravi  46000
102   Prakash     34000
103   Murali      23000
104   Madan 45555
105   Kanth 56000
106   Varma 33333
Time taken: 0.409 seconds
hive>
-------------------------------------
hive> select empid , ename , esal from disttab DISTRIBUTE BY ename SORT BY ename asc , esal asc;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapred.reduce.tasks=<number>
Starting Job = job_201304160610_0008, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201304160610_0008
Kill Command = /usr/lib/hadoop/bin/hadoop job  -Dmapred.job.tracker=localhost:8021 -kill job_201304160610_0008
2013-04-16 07:05:36,346 Stage-1 map = 0%,  reduce = 0%
2013-04-16 07:05:40,480 Stage-1 map = 100%,  reduce = 0%
2013-04-16 07:05:49,639 Stage-1 map = 100%,  reduce = 33%
2013-04-16 07:05:50,765 Stage-1 map = 100%,  reduce = 100%
Ended Job = job_201304160610_0008
OK
108   Sandeep     22000
109   Sandeep     22500
110   Sandeep     23500
111   Sandeep     24340
105   Kanth 56000
112   Karan 45000
113   Karan 45600
104   Madan 45555
103   Murali      23000
102   Prakash     34000
101   Ravi  46000
106   Varma 33333
Time taken: 24.721 seconds
hive>

 

hadoop hive cluster by

CLUSTER BY
==========
1.CLUSTER BY clause is a short-hand way of expressing the DISTRIBUTE BY with SORT BY

PFB example how we have changed the way of DISTRIBUTE BY with SORT BY
---------------------------------------------------------------------


hive> select empid , ename , esal from disttab CLUSTER BY ename;                                
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapred.reduce.tasks=<number>
Starting Job = job_201304160610_0009, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201304160610_0009
Kill Command = /usr/lib/hadoop/bin/hadoop job  -Dmapred.job.tracker=localhost:8021 -kill job_201304160610_0009
2013-04-16 07:07:57,918 Stage-1 map = 0%,  reduce = 0%
2013-04-16 07:08:05,362 Stage-1 map = 100%,  reduce = 0%
2013-04-16 07:08:14,526 Stage-1 map = 100%,  reduce = 33%
2013-04-16 07:08:15,535 Stage-1 map = 100%,  reduce = 100%
Ended Job = job_201304160610_0009
OK
NULL  NULL  NULL
108   Gopal 22000
109   Gopal 22500
110   Gopal 23500
111   Gopal 24340
105   Kanth 56000
113   Karan 45600
112   Karan 45000
104   Madan 45555
103   Murali      23000
102   Prakash     34000
101   Ravi  46000
106   Varma 33333
Time taken: 32.426 seconds
hive>

 

Hive VS Impala

What is Impala?

Cloudera Impala is an open source Massively Parallel Processing (MPP) query engine that runs natively on Apache Hadoop. The Apache-licensed Impala project brings scalable parallel database technology to Hadoop, enabling users to issue low-latency SQL queries to data stored in HDFS and Apache HBase without requiring data movement or transformation

With Impala, analysts and data scientists now have the ability to perform real-time, “speed of thought” analytics on data stored in Hadoop via SQL or through Business Intelligence (BI) tools. The result is that large-scale data processing (via MapReduce) and interactive queries can be done on the same system using the same data and metadata – removing the need to migrate data sets into specialized systems and/or proprietary formats simply to perform analysis

How does impala provide faster query response compared to hive for the same data on hdfs ?

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.

Impala Use Cases

What are good use cases for Impala as opposed to Hive or MapReduce?

Impala is well-suited to executing SQL queries for interactive exploratory analytics on large data sets. Hive and MapReduce are appropriate for very long running, batch-oriented tasks such as ETL.

Is MapReduce required for Impala? Will Impala continue to work as expected if MapReduce is stopped?

Impala does not use MapReduce at all.

Can Impala be used for complex event processing?

For example, in an industrial environment, many agents may generate large amounts of data. Can Impala be

used to analyze this data, checking for notable changes in the environment?

Complex Event Processing (CEP) is usually performed by dedicated stream-processing systems. Impala is not

a stream-processing system, as it most closely resembles a relational database.

Is Impala intended to handle real time queries in low-latency applications or is it for ad hoc queries for the purpose of data exploration?

Ad-hoc queries are the primary use case for Impala. We anticipate it being used in many other situations where

low-latency is required. Whether Impala is appropriate for any particular use-case depends on the workload,

data size and query volume.

Apache Hive : ORDER BY VS SORT BY

ORDER BY VS SORT BY

===================

ORDER BY

--------

1. The ORDER BY clause is familar from other SQL dialects.

2. It performs the 'total ordering of the query result set' i.e. ' all the data will be passed to a single reducer' which

   will take longer time if we have larger datasets.

3. Hive requires the LIMIT clause for ORDER BY as it will take longer time (if hives property

   hive.mapred.mode is set to 'strict' which is by default 'nonstrict'

NOTE: In /usr/lib/hive/conf/hive-default.xml--> below property

<property>

  <name>hive.mapred.mode</name>

  <value>nonstrict</value>

  <description>The mode in which the hive operations are being performed. In strict mode, some risky queries are not allowed to run</description>

</property>

SORT BY

-------

3. Where as SORT BY (Hive's alternative to ORDER BY) , will orders the data only within each reducer and performs      the  'local ordering of the data' where each REDUCER'S OUTPUT will be SORTTED.

4. Better perfomance is traded for total ordering.

hive> select * from testudf;

OK

100       Karan         24000

101   Gopal             25000

102          RaviKanth  26000

103   Ravi Prakash      270000

104   Gopi Prakash      28000

105   Ravi Prakash      29000

106   Vishnu Prakash    31000

107   Kasi Prakash      34000

Time taken: 0.419 seconds

hive> desc testudf;

OK

empid int  

ename string     

esal  int  

Time taken: 0.197 seconds

hive> select empid , ename , esal from testudf order by empid asc , esal desc;

Total MapReduce jobs = 1

Launching Job 1 out of 1

Number of reduce tasks determined at compile time: 1

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapred.reduce.tasks=<number>

Starting Job = job_201304160610_0001, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201304160610_0001

Kill Command = /usr/lib/hadoop/bin/hadoop job  -Dmapred.job.tracker=localhost:8021 -kill job_201304160610_0001

2013-04-16 06:15:57,723 Stage-1 map = 0%,  reduce = 0%

2013-04-16 06:16:08,764 Stage-1 map = 100%,  reduce = 0%

2013-04-16 06:16:18,977 Stage-1 map = 100%,  reduce = 100%

Ended Job = job_201304160610_0001

OK

100       Karan         24000

101   Gopal             25000

102          RaviKanth  26000

103   Ravi Prakash      270000

104   Gopi Prakash      28000

105   Ravi Prakash      29000

106   Vishnu Prakash    31000

107   Kasi Prakash      34000

Time taken: 36.859 seconds

hive> select empid , ename , esal from testudf sort  by empid asc , esal desc;

Total MapReduce jobs = 1

Launching Job 1 out of 1

Number of reduce tasks not specified. Estimated from input data size: 1

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapred.reduce.tasks=<number>

Starting Job = job_201304160610_0002, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201304160610_0002

Kill Command = /usr/lib/hadoop/bin/hadoop job  -Dmapred.job.tracker=localhost:8021 -kill job_201304160610_0002

2013-04-16 06:18:23,766 Stage-1 map = 0%,  reduce = 0%

2013-04-16 06:18:31,316 Stage-1 map = 100%,  reduce = 0%

2013-04-16 06:18:40,440 Stage-1 map = 100%,  reduce = 33%

2013-04-16 06:18:41,451 Stage-1 map = 100%,  reduce = 100%

Ended Job = job_201304160610_0002

OK

100       Karan         24000

101   Gopal             25000

102          RaviKanth  26000

103   Ravi Prakash      270000

104   Gopi Prakash      28000

105   Ravi Prakash      29000

106   Vishnu Prakash    31000

107   Kasi Prakash      34000

Time taken: 28.06 seconds

hive>

failed with exception null hive

LFS

When We are loading data from LFS to table then it will load on second  try to load same data now data exist so it will create a copy  new newfile suffixed by *_copy_1.

/user/hive/warehouse/databasename/tablename


HDFS

When We are loading data from HDFS to table then it will try -mv  on second  try to load same data now data exist so it will not  perform -mv DUE to Write Once read many HDFS Feature and rename the source file.