Analyze 1 Billion record using HIVE and Google Dataproc

 

 

 

 

 

We will create 1B record store it to HDFS and use HIVE to analyze

The dataset data headers
CustomerID    Age    Salary    Gender    Country
The file retailstore_5mn which contains five million records. The file size approximately 200 megabytes.
Will copy the file from GitHub to our linux
Decompress then move this file to the Hadoop cluster and create 200 copies of this file so that we can have billion records for our analysis.

In a nutshell, we will create a 1B dataset by copying one csv has 5M record 20 times, these is after creating the dataproc stack, after that will create HDFS directory and copy the CSVs to it, then create HIVE logical table points to that directory, using HIVE we will output the query data to another directory, at the end you can expose the output data by any analytics visualized application.
The main goal of this tutorial is to get the average salary per country saved in a table.

So the Map and Reduce operations will go through the 1B record and group the customers data in multiple style based on HIVE query.
Map tasks convert the data to key value pair and write it to the desk THEN Reduce tasks start to read the key value pair and do the aggregation.


Step by Step, here we Gooo!

1- create dataproc cluster with worker node of n1 2 CPU

Building hadoop stack may take quite good time, using google dataproc is hassle free, with create stack button it will deploy manager and couple of nodes.

You need to sign up in google cloud, enable the 300$ of Dataproc which you will find it easily at top bar of the GCP console, the 300$ credit is more than enough for such tutorial which maximum consume 3$.

You  suppose to have google cloud account, open it and go to dataproc console, create cluster, everything is default except the worker node make it N1 2 CPU or whatever you prefer.

2- connect two SSH sessions to two sessions

click on the cluster you just created then go to vm instances and launch two SSH by clicking on the SSH button 

3- copy the file from GitHub

wget https://github.com/futurexskill/bigdata/raw/master/retailstore_5mn.zip
pythonarabia@cluster-f6c8-m:~$ wget https://github.com/futurexskill/bigdata/raw/master/retailstore_5mn.zip
--2023-12-08 05:50:58--  https://github.com/futurexskill/bigdata/raw/master/retailstore_5mn.zip
Resolving github.com (github.com)... 140.82.112.3
Connecting to github.com (github.com)|140.82.112.3|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://raw.githubusercontent.com/futurexskill/bigdata/master/retailstore_5mn.zip [following]
--2023-12-08 05:50:59--  https://raw.githubusercontent.com/futurexskill/bigdata/master/retailstore_5mn.zip
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.110.133, 185.199.108.133, 185.199.111.133, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.110.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 21048025 (20M) [application/zip]
Saving to: ‘retailstore_5mn.zip’

retailstore_5mn.zip         100%[==========================================>]  20.07M   132MB/s    in 0.2s    

2023-12-08 05:50:59 (132 MB/s) - ‘retailstore_5mn.zip’ saved [21048025/21048025]

pythonarabia@cluster-f6c8-m:~$ ls -lrts
total 20556
20556 -rw-r--r-- 1 pythonarabia pythonarabia 21048025 Dec  8 05:50 retailstore_5mn.zip



4- unzip

unzip retailstore_5mn.zip
pythonarabia@cluster-f6c8-m:~$ ls -lrt
total 210368
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Apr 25  2021 retailstore_5mn.csv
-rw-r--r-- 1 pythonarabia pythonarabia  21048025 Dec  8 05:50 retailstore_5mn.zip
pythonarabia@cluster-f6c8-m:~$ head -10 retailstore_5mn.csv
CustomerID,Age,Salary,Gender,Country
1,18,20000,Male,Germany
2,19,22000,Female,France
3,20,24000,Female,England
4,21,2600,Male,England
5,22,50000,Male,France
6,23,35000,Female,England
7,24,4300,Male,Germany
8,25,32000,Female,France
9,35,35000,Male,Germany



5- create a 200 copies from the file using shell script which loop, copy with rename, the output is 5M record

for i in {1..200}; do cp retailstore_5mn.csv "retailstore_5mn_$i.csv" ; done
pythonarabia@cluster-f6c8-m:~$ for i in {1..200}; do cp retailstore_5mn.csv "retailstore_5mn_$i.csv" ; done
- This will take sometime and meanwhile you can monitor from the other SSH screen
pythonarabia@cluster-f6c8-m:~$ ls -lrt
total 3454500
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Apr 25  2021 retailstore_5mn.csv
-rw-r--r-- 1 pythonarabia pythonarabia  21048025 Dec  8 05:50 retailstore_5mn.zip
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Dec  8 05:54 retailstore_5mn_1.csv
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Dec  8 05:54 retailstore_5mn_2.csv
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Dec  8 05:54 retailstore_5mn_3.csv
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Dec  8 05:54 retailstore_5mn_4.csv
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Dec  8 05:54 retailstore_5mn_5.csv
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Dec  8 05:54 retailstore_5mn_6.csv
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Dec  8 05:54 retailstore_5mn_7.csv
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Dec  8 05:54 retailstore_5mn_8.csv
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Dec  8 05:54 retailstore_5mn_9.csv
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Dec  8 05:54 retailstore_5mn_10.csv
-rw-r--r-- 1 pythonarabia pythonarabia 194363934 Dec  8 05:54 retailstore_5mn_11.csv



6- Create the hadoop directory HDFS

We need to create hadoop directory then move the file to and after that create HIVE logical table points to that directory
In this step we will create the directory, the main directory then retail to store the data
pythonarabia@cluster-f6c8-m:~$ hadoop fs -mkdir /user/pythonarabia
pythonarabia@cluster-f6c8-m:~$ hadoop fs -mkdir /user/pythonarabia/retail
pythonarabia@cluster-f6c8-m:~$ hadoop fs -ls
Found 1 items
drwxr-xr-x   - pythonarabia hadoop          0 2023-12-08 06:01 retail

7- Copy the 200 files to hadoop retail directory
for i in {1..200}; do hadoop fs -put "retailstore_5mn_$i.csv" retail  ; done
This will take quite sometime and you can monitor the progress from another shell window
pythonarabia@cluster-f6c8-m:~$ hadoop fs -ls retail
Found 7 items
-rw-r--r--   2 pythonarabia hadoop  194363934 2023-12-08 06:03 retail/retailstore_5mn_1.csv
-rw-r--r--   2 pythonarabia hadoop  194363934 2023-12-08 06:03 retail/retailstore_5mn_2.csv
-rw-r--r--   2 pythonarabia hadoop  194363934 2023-12-08 06:03 retail/retailstore_5mn_3.csv
-rw-r--r--   2 pythonarabia hadoop  194363934 2023-12-08 06:04 retail/retailstore_5mn_4.csv
-rw-r--r--   2 pythonarabia hadoop  194363934 2023-12-08 06:04 retail/retailstore_5mn_5.csv
-rw-r--r--   2 pythonarabia hadoop  194363934 2023-12-08 06:04 retail/retailstore_5mn_6.csv
-rw-r--r--   2 pythonarabia hadoop          0 2023-12-08 06:04 retail/retailstore_5mn_7.csv._COPYING


8- HIVE: create new table points to the retail directory

Connect to hive console
pythonarabia@cluster-f6c8-m:~$ hive
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/lib/tez/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/lib/hadoop/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Reload4jLoggerFactory]
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/lib/tez/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/lib/hadoop/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Reload4jLoggerFactory]
Hive Session ID = 14ca2e59-2b8f-409e-8602-28657ec5a195

Logging initialized using configuration in file:/etc/hive/conf.dist/hive-log4j2.properties Async: true
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.apache.hadoop.hive.common.StringInternUtils (file:/usr/lib/hive/lib/hive-common-3.1.3.jar) to field java.net.URI.string
WARNING: Please consider reporting this to the maintainers of org.apache.hadoop.hive.common.StringInternUtils
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
Hive Session ID = 854e66d7-31c2-44a0-b5c7-5b7f3e628e16

Create database
hive> create database pythonarabiastore;
OK
Time taken: 0.916 seconds
hive> show databases;
OK
default
pythonarabiastore
Time taken: 0.187 seconds, Fetched: 2 row(s)
hive> use pythonarabiastore;
OK
Time taken: 0.084 seconds

Create retailcust table which has the mentioned columns along with fields, fields separated by comma “,” and skip the header line
hive> create table retailcust (custId INT, age INT, salary FLOAT,gender String,country String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/pythonarabia/retail/' TBLPROPERTIES ("skip.header.line.count"="1") ;
OK
Time taken: 0.56 seconds

To test you can query the table
hive> select * from retailcust limit 10;
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
6       23      35000.0 Female  England
7       24      4300.0  Male    Germany
8       25      32000.0 Female  France
9       35      35000.0 Male    Germany
10      27      37000.0 Female  France
Time taken: 2.49 seconds, Fetched: 10 row(s)




8- Aggregate the customers avg salaries to another table

Create new directory
pythonarabia@cluster-f6c8-m:~$ hadoop fs -mkdir country
pythonarabia@cluster-f6c8-m:~$ hadoop fs -ls
Found 2 items
drwxr-xr-x   - pythonarabia hadoop          0 2023-12-08 06:55 country
drwxr-xr-x   - pythonarabia hadoop          0 2023-12-08 06:30 retail

Create new table for the aggregated data
hive> create table countryavgsalary (country String, avg_salary double) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/pythonarabia/country/' ;
OK
Time taken: 0.327 seconds
hive> show tables;
OK
countryavgsalary
retailcust
Time taken: 0.071 seconds, Fetched: 2 row(s)
hive> select * from countryavgsalary;
OK
Time taken: 0.299 seconds

Query the retailcust table and output to countryavgsalary table
hive> insert into countryavgsalary select country,avg(salary) from retailcust group by country;
Query ID = pythonarabia_20231208065852_a4238a5e-5edc-4289-8d38-8bc82dbb7e33
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_1702014055753_0002)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED     40         40        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED   1009       1009        0        0       0       0  
Reducer 3 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 03/03  [==========================>>] 100%  ELAPSED TIME: 198.61 s   
----------------------------------------------------------------------------------------------
Loading data to table pythonarabiastore.countryavgsalary
OK
Time taken: 211.956 seconds

FYI
pythonarabia@cluster-f6c8-m:~$ hadoop fs -ls country
Found 4 items
-rw-r--r--   2 pythonarabia hadoop         26 2023-12-08 07:01 country/000124_0
-rw-r--r--   2 pythonarabia hadoop         26 2023-12-08 07:01 country/000243_0
-rw-r--r--   2 pythonarabia hadoop         26 2023-12-08 07:01 country/000751_0
-rw-r--r--   2 pythonarabia hadoop          6 2023-12-08 07:01 country/000945_0
pythonarabia@cluster-f6c8-m:~$ hadoop fs -cat country/000124_0
France,35388.101407060894
pythonarabia@cluster-f6c8-m:~$ hadoop fs -cat country/000243_0
Germany,35387.61389079244
pythonarabia@cluster-f6c8-m:~$ hadoop fs -cat country/000751_0
England,35387.86422141547
pythonarabia@cluster-f6c8-m:~$ hadoop fs -cat country/000945_0
\N,\N

Now you can get the output data
hive> select * from countryavgsalary;
OK
France  35388.101407060894
Germany 35387.61389079244
England 35387.86422141547
NULL    NULL
Time taken: 0.261 seconds, Fetched: 4 row(s)

You can also create sql file and execute it from the terminal instead of writing command in the shell
nano query.sql

pythonarabia@cluster-f6c8-m:~$ nano query.sql
pythonarabia@cluster-f6c8-m:~$ hive -f query.sql
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/lib/tez/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/lib/hadoop/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Reload4jLoggerFactory]
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/lib/tez/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/lib/hadoop/lib/slf4j-reload4j-1.7.36.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Reload4jLoggerFactory]
Hive Session ID = 7af5a5f6-c2ec-4176-b818-16946a1c52b5

Logging initialized using configuration in file:/etc/hive/conf.dist/hive-log4j2.properties Async: true
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.apache.hadoop.hive.common.StringInternUtils (file:/usr/lib/hive/lib/hive-common-3.1.3.jar) to field java.net.URI.string
WARNING: Please consider reporting this to the maintainers of org.apache.hadoop.hive.common.StringInternUtils
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
Hive Session ID = 7ec4da74-e632-406f-a571-2647ac9d75d4
OK
Time taken: 1.662 seconds
Query ID = pythonarabia_20231208071344_df76189c-c3af-4124-84e4-f994ce13afde
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_1702014055753_0004)

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED     40         40        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED   1009       1009        0        0       0       0  
Reducer 3 ...... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 03/03  [==========================>>] 100%  ELAPSED TIME: 199.16 s   
----------------------------------------------------------------------------------------------
Loading data to table pythonarabiastore.countryavgsalary
OK
Time taken: 216.283 seconds

In this tutorial we managed to get dataset from githubm duplicated to create large dataset, we copied the dataset to our HDFS then we created database and two tables plus we were able to query table and the output saved to another table.