Partitioning in HIVE with example

 

HIVE Partitioning came to solve the normal queries performance as explained in the following example.

Simply partitioning is an operation where you group the similar data together which belong to one of the data parameters, in our example we can group based on the customer nationality.

Bucketing is almost similar but it group the data based on the customer ID which expected to be too much groups unlike the country which almost five only.

1- Create google dataproc stack 

Make sure that you enabled the 300$ credit then create new stack, accept the default for the manager node and make sure that the worker nodes has at least 2 CPUs 

 

 after the VMs get provisioned initiate couple of SSH console to the master node 

 

2- Download and unzip the dataset

pythonarabia@cluster-eff3-m:~$ wget https://github.com/futurexskill/bigdata/raw/master/retailstore_large.zip

pythonarabia@cluster-eff3-m:~$ ls -lrt
total 4300
-rw-r--r-- 1 pythonarabia pythonarabia 4401734 Dec  8 14:21 retailstore_large.zip
pythonarabia@cluster-eff3-m:~$ unzip retailstore_large.zip
Archive:  retailstore_large.zip
  inflating: retailstore_large.csv   
pythonarabia@cluster-eff3-m:~$ ls -lrt
total 43996
-rw-r--r-- 1 pythonarabia pythonarabia 40646169 Dec 14  2019 retailstore_large.csv
-rw-r--r-- 1 pythonarabia pythonarabia  4401734 Dec  8 14:21 retailstore_large.zip

3- Create hadoop default directory and retailcust directory 

The retailcust directory will host the csv customer data file

pythonarabia@cluster-eff3-m:~$ hadoop fs -mkdir /user/pythonarabia
mkdir: `/user/pythonarabia': File exists
pythonarabia@cluster-eff3-m:~$ hadoop fs -mkdir /user/pythonarabia/retailcust
pythonarabia@cluster-eff3-m:~$ hadoop fs -ls
Found 1 items
drwxr-xr-x   - pythonarabia hadoop          0 2023-12-08 14:27 retailcust

4- Copy the csv file to retailcust directory

pythonarabia@cluster-eff3-m:~$ hadoop fs -put retailstore_large.csv /user/pythonarabia/retailcust
pythonarabia@cluster-eff3-m:~$ hadoop fs -ls /user/pythonarabia/retailcust
Found 1 items
-rw-r--r--   2 pythonarabia hadoop   40646169 2023-12-08 14:29 /user/pythonarabia/retailcust/retailstore_large.csv

5- Create retailstore database 

hive> create database retailstore;
OK
Time taken: 0.213 seconds
hive> show databases;
OK
default
retailstore
Time taken: 0.026 seconds, Fetched: 2 row(s)
hive> use retailstore;
OK
Time taken: 0.06 seconds

6- Create logical table (retailcustext_large) points to the data exist in (retailcust) directory

The create table command defile the fields along with the type plus it refer that the original data is comma separated and also the data location.

hive> create external table retailcustext_large (customerid INT, age INT, salary FLOAT,gender String,country String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/pythonarabia/retailcust/' TBLPROPERTIES ("skip.header.line.count"="1") ;
OK
Time taken: 0.351 seconds
hive> show tables;
OK
retailcustext_large
Time taken: 0.047 seconds, Fetched: 1 row(s)

7- Some queries using HIVE 

hive> select * from retailcustext_large limit 5;
OK
1       18      20000.0 Male    Germany
2       19      22000.0 Female  France
3       20      24000.0 Female  England
4       21      2600.0  Male    England
5       22      50000.0 Male    France
Time taken: 0.19 seconds, Fetched: 5 row(s)

Get the total records count, as per the following query we have 1M record and as shown the query fired 1 map task and 1 reducer.

hive> select count(*) from retailcustext_large ;
Query ID = pythonarabia_20231208143723_a236f919-1b4f-454d-a270-7ceddc0f0333
Total jobs = 1
Launching Job 1 out of 1
Tez session was closed. Reopening...
Session re-established.
Session re-established.
Status: Running (Executing on YARN cluster with App id application_1702045229814_0002)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 10.54 s    
----------------------------------------------------------------------------------------------
OK
1048575
Time taken: 28.738 seconds, Fetched: 1 row(s)

Get the total count of German customers, as per below query we have 278528 customer.

hive> select count(*) from retailcustext_large where country = 'Germany';
Query ID = pythonarabia_20231208144047_3a989bc4-5da6-4c50-85d1-3d9b2d71fdf5
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_1702045229814_0002)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 10.37 s    
----------------------------------------------------------------------------------------------
OK
278528
Time taken: 10.978 seconds, Fetched: 1 row(s)

8- Partitioning

10.9 seconds just to get the count of German customers from 1M records which considered too slow, partitioning came to help in speed up the queries.

The /user/pythonarabia/retailcust directory is hosted in hadoop fs and any thing it get saved in this directory stored in distributed manner in the hadoop cluster one master and two worker nodes.

Every time we fire HIVE query the YARN initiate the MapReduce to scan the file and find the matching records.

To speed up the operation we should use the partitioning techniques, which simply we save the data in different directory each directory has only the data belong to specific group, for example different directory based on date, but in our sample we will need different directory per country.

You need to understand your data to plan properly for your queries!.

You have couple of options:-

First, manually create directories and manually too manipulate the data and split it in different CSV files then save it to the directories, this will work.

Second, using HIVE create new table with partition parameter value "country" then query the original table and insert to the new table as follow 

 

Enable the partitioning in the current session (session specific)

hive> set hive.exec.dynamic.partition=true;
hive> set hive.exec.dynamic.partition.mode=nonstrict;

Create the new table, partitioned by country, specified the required fields along with data type and also the directory which will be created automatically by the create table command

hive> create external table retailcustext_large_partitioned (customerid INT, age INT, salary FLOAT,gender String) partitioned by (country String) location  '/user/pythonarabia/retailcust-partitioned/';
OK
Time taken: 0.086 seconds
hive> show tables;
OK
retailcustext_large
retailcustext_large_partitioned
Time taken: 0.033 seconds, Fetched: 2 row(s)

Now the directory is empty, the table is created and structured and ready to receive data frouped by country 

pythonarabia@cluster-eff3-m:~$ hadoop fs -ls /user/pythonarabia/retailcust-partitioned/
pythonarabia@cluster-eff3-m:~$ hadoop fs -ls /user/pythonarabia/
Found 2 items
drwxr-xr-x   - pythonarabia hadoop          0 2023-12-08 14:29 /user/pythonarabia/retailcust
drwxr-xr-x   - pythonarabia hadoop          0 2023-12-08 15:04 /user/pythonarabia/retailcust-partitioned

Now will fire insert query to the partitioned table sourced from select * from the original table as follow 

hive> insert into table retailcustext_large_partitioned partition(country)
    > select * from retailcustext_large;
Query ID = pythonarabia_20231208151016_f9366fbc-0d8c-4443-87a5-3dd06d95571e
Total jobs = 1
Launching Job 1 out of 1
Tez session was closed. Reopening...
Session re-established.
Session re-established.
Status: Running (Executing on YARN cluster with App id application_1702045229814_0003)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED      2          2        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 16.25 s    
----------------------------------------------------------------------------------------------
Loading data to table retailstore.retailcustext_large_partitioned partition (country=null)

Loaded : 3/3 partitions.
         Time taken to load dynamic partitions: 0.415 seconds
         Time taken for adding to write entity : 0.002 seconds
OK
Time taken: 29.486 seconds

As you expected! yes in the new directory we have three directories since we have three countries at the original table

pythonarabia@cluster-eff3-m:~$ hadoop fs -ls /user/pythonarabia/retailcust-partitioned
Found 3 items
drwxr-xr-x   - pythonarabia hadoop          0 2023-12-08 15:10 /user/pythonarabia/retailcust-partitioned/country=England
drwxr-xr-x   - pythonarabia hadoop          0 2023-12-08 15:10 /user/pythonarabia/retailcust-partitioned/country=France
drwxr-xr-x   - pythonarabia hadoop          0 2023-12-08 15:10 /user/pythonarabia/retailcust-partitioned/country=Germany

Each directory country has one file 

pythonarabia@cluster-eff3-m:~$ hadoop fs -ls /user/pythonarabia/retailcust-partitioned/country=Germany
Found 1 items
-rw-r--r--   2 pythonarabia hadoop    6635199 2023-12-08 15:10 /user/pythonarabia/retailcust-partitioned/country=Germany/000000_0

Now fire the same query to get the count of german customers but using the new table, it get executed in 1 second only instead of 10 ... wooow!!

hive> select count(*) from retailcustext_large_partitioned where country = 'Germany';
Query ID = pythonarabia_20231208152455_14750967-20bb-48da-a744-5d9b93b6d17c
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_1702045229814_0004)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 0.60 s     
----------------------------------------------------------------------------------------------
OK
278528
Time taken: 1.004 seconds, Fetched: 1 row(s)

And when your select * from the new table you will get all the records normally but this time sorted by country 

hive> select * from retailcustext_large_partitioned limit 10;
OK
3       20      24000.0 Female  England
4       21      2600.0  Male    England
6       23      35000.0 Female  England
13      33      29000.0 Female  England
14      35      7600.0  Male    England
16      37      40000.0 Female  England
29      55      45000.0 Male    England
30      57      47000.0 Male    England
31      58      35000.0 Male    England
32      60      37000.0 Male    England
Time taken: 0.169 seconds, Fetched: 10 row(s)

Now try England will take 1 second too

hive> select count(*) from retailcustext_large_partitioned where country = 'England';
Query ID = pythonarabia_20231208153011_eaf87d32-2baf-435d-bbab-362890e1817d
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_1702045229814_0004)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 0.75 s     
----------------------------------------------------------------------------------------------
OK
475136
Time taken: 1.073 seconds, Fetched: 1 row(s)