hive-order-sort-distribute-cluster

Hive Order by, Sort by, Cluster by and Distribute by
Hive Order by, Sort by, Cluster by and Distribute by
 



"Order by" clause ensures the global sorting on the column(s) specified.
Very costly operation as all the data has to pass to single reducer irrespective of mappers count.
Syntax: select <col list> from <table name> ORDER BY <col_list> <asc or desc> limit <no. of rows> offset <offset value>


DATA
10
2
7
4
5
6
3
8
9
1
  
MAPPER-1
10
2
7
4
MAPPER-2
5
6
3
MAPPER-3
8
9
1
  
REDUCER-1
1
2
3
4
5
6
7
8
9
10
  
OUTPUT
1
2
3
4
5
6
7
8
9
10
   


"Sort by" clause used to sort on the column(s) specified on the local/reducer level.
If multiple reducers are in place, mutliple groups of sorted rows will be returned.
Syntax: select <col list> from <table name> SORT BY <col_list> <asc or desc> limit <no. of rows> offset <offset value>


DATA
10
2
7
4
5
6
3
8
9
1
  
MAPPER-1
10
2
7
4
MAPPER-2
5
6
3
MAPPER-3
8
9
1
  
REDUCER-1
2
4
7
10
REDUCER-2
3
5
6
REDUCER-3
1
8
9
  
OUTPUT
2
4
7
10
3
5
6
1
8
9
   


"Distribute by" clause controls the allocation of the map output among the reducers.
The Data will be grouped by the distributed column and each group will be sent a single and same sreducer.
But there is no gurantee of allocation, but normally partial allocations of same set of data to two different reducers is prevented.
Sorting is not possible on the data on the reducer level.
Syntax: select <col list> from <table name> DISTRIBUTE BY by <col_list> <asc or desc> limit <no. of rows> offset <offset value>


DATA
X,10
Y,2
X,7
Y,4
Z,5
X,1
Y,3
Z,8
Z,9
X,6
  
MAPPER-1
X,10
Y,2
X,7
Y,4
MAPPER-2
Z,5
X,1
Y,3
MAPPER-3
Z,8
Z,9
X,6
  
REDUCER-1
X,10
X,7
X,1
X,6
REDUCER-2
Y,2
Y,4
Y,3
REDUCER-3
Z,5
Z,8
Z,9
  
OUTPUT
X,10
X,7
X,1
X,6
Y,2
Y,4
Y,3
Z,5
Z,8
Z,9
   


"Cluster by" combines both "Distribute by" and "Sort by".
It ensures that grouping of the data by the specified column and each group is sorted and sent to same and single reducer.
A limitation that it distributes and sorts on same column. If we want to sort by different column then we need to use distribute and sort clauses.
Compressed outputs can be splittable.
Syntax: select <col list> from <table name> CLUSTER BY <col_list> <asc or desc> limit <no. of rows> offset <offset value>


DATA
X,10
Y,2
X,7
Y,4
Z,5
X,1
Y,3
Z,8
Z,9
X,6
  
MAPPER-1
X,10
Y,2
X,7
Y,4
MAPPER-2
Z,5
X,1
Y,3
MAPPER-3
Z,8
Z,9
X,6
  
REDUCER-1
X,1
X,6
X,7
X,10
REDUCER-2
Y,2
Y,3
Y,4
REDUCER-3
Z,5
Z,8
Z,9
  
OUTPUT
X,1
X,6
X,7
X,10
Y,2
Y,3
Y,4
Z,5
Z,8
Z,9
  
Compiled on SATURDAY, 27-JULY-2024, 10:01:08 AM IST

Comments

Post a Comment

Popular posts from this blog

hadoop-installation-ubuntu

Hadoop Setup Hadoop Setup Hadoop installation & configuration on Ubuntu Prerequisites: 1. Java open-jdk 8 2. SSH Server - open ssh server & client 3. Hadoop dist - 2.9.1 1. Installing JDK8 on Ubuntu $ sudo apt-get update $ sudo apt-get install openjdk-8-jdk $ whereis java or whereis jvm //To get the path of Java General installation directory - /usr/lib/jvm/java-8-openjdk-amd64 Adding Java JDK8 path to environment variables $ sudo gedit /etc/profile or $ gedit .bashrc export JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64 export PATH=$PATH:$HOME/bin:$JAVA_HOME/bin Verify the Java installation $ java -version $ javac -version 2. Extract hadoop-2.9.1.tar.gz Search and Download Apache Hadoop 2.9.1 from Apache Hadoop site And extract the file to a directory /home/mm/softwares/ 3. Configuring Hadoop directories on path $ sudo gedit /etc/profile or $ sudo gedit .bashrc export HADOOP_HOME=/h...
Read more

jenv-tool

Jenv Setup Jenv Setup Jenv tool is used to install, manage and switch between different Java versions easily. Automates the manual editing of changing paths and system properties. Homepage: Jenv Setup Jenv 1      1. Jenv project homepage 2      Homepage: https://github.com/jenv/jenv 3      4      2. Installation via scoop: easy way to install on Windows. For Other Operating systems Check here 5      cmd> scoop bucket add extras 6      cmd> scoop install extras/jenv 7      8      Prerequisite: Scoop should be installed and configured. See Config Scoop for more info. 9      10      3. Check the scoop ...
Read more

hive-installation-in-ubuntu

Hive Setup Hive Setup Download Hive Download and extract hive to a directory. Add below values to the .bashrc file export HIVE_HOME=/home/mkm/mm/softwares/hive-3.1.3 export PATH=$PATH:$HIVE_HOME/bin How to Setup Hive on Ubuntu in 3 different ways. Embedded Metastore(Derby) Pre-requisite -  Install Derby DB Local Metastore(Same Machine/JVM) Pre-requisite -  Install MySQL Server Remote Metastore(Remote Machine/JVM) Embedded Metastore(Derby) Will Update soon Local Metastore(Same Machine/JVM) 1. Check MySQL Server Instance is running or not $ ps -e | grep mysql or sudo service mysql status If not, Install MySQL Server by following page in Pre-requisite section 2. Login to the MySQL server $ mysql -u root -p Create Hive Metastore Database mysql> CREATE DATABASE metastore_db; Create new user login for Hive mysql> CREATE USER 'hiveadmin'@'localhost' IDENTIFIED BY ...
Read more