Sellkart is a primary e-commerce Indian website and retail store and like every other e-commerce website, it has to store user data and inventory of all the items it sells. It uses MySQL database which is one of the chief data storage choices. MySQL is safe, secure and reliable data storage. But as Sellkart grew larger, more and more customers started diving into this E-commerce portal. Now Sellkart, collects user data like their location, their behaviour, demographic logs, item purchased and items returned, their payment option such as COD, Credit card/Debit card. Analysts use this data to predict customer value, better payment options, and expand the business using better ad campaigns, easing up the processes.
To depict how the collected data can be strategically used, we will consider here a fake dataset of consumers which consists of the usernames, their city, their occupation, total no. of items purchased and total no. of items returned due to defects.
Here in the fake dataset (data.csv), we have the data of the customers which have bought more than 25 items.
Fields in the data.csv
- Username (String)
- City (String)
- Occupation (String)
- Purchased (Integer)
- Returned (Integer)
Sellkart uses this data, which has multiple columns and a considerably large dataset of over several gigabytes, to predict and understand the key insight of its system, such as:
- Customer’s lifetime value
- Product success
- Demographic purchase of a product
- Regions of concern
- Real time pricing of products
- Up sell engaging customers
Now we will show how a small range analysis takes place in Hadoop. Things we will find out using the data:
a) Finding the top consumer on the basis of amount of product they bought.
Uses:Give special privileges to best buyers, upselling average customers.
b) Finding the job sector which purchases most products.
Uses: keeping more products of bestselling range, recommendation to similar type of products.
c) Finding the cities where most products are purchased.
Uses:Region wise targeting buyers, focusing on areas where less customers are there, trying new products in good market.
1.Copying the data into HDFS.
a) Make the directory in hadoop HDFS
[training@localhost ~]$ hadoop fs -mkdir /user/training/case1
[training@localhost ~]$ hadoop fs -ls
Found 3 items
drwxr-xr-x - training supergroup 0 2015-07-21 16:48 /user/training/case1
-rw-r--r-- 1 training supergroup 44009445 2015-07-20 12:39 /user/training/crime
drwxr-xr-x - training supergroup 0 2015-07-20 14:21 /user/training/csvfile
b) Copying the file for further usage
[training@localhost ~]$ hadoop fs -copyFromLocal ~/Desktop/case1/data.csv /user/training/case1
[training@localhost ~]$ hadoop fs -ls /user/training/case1
Found 1 items
-rw-r--r-- 1 training supergroup 2489638 2015-07-21 16:49 /user/training/case1/data.csv
After this file is copied, it is processed using Hive, to obtain different results.
2. Using hive to process the data.
a) Creating table in hive, which will be used to store this data.
[training@localhost ~]$ hive
creatHive history file=/tmp/training/hive_job_log_training_201507211652_319213433.txt
hive> create table shopping (username string,city string,occupation string,purchaseditem,int,returneditem int) row format delimited fields
terminated by ',' stored as textfile
OK
Time taken: 2.892 seconds
hive> show tables;
OK
crimes
shopping
Time taken: 0.171 seconds
b) Load file into hive tables shopping
Hive>load data inpath '/user/training/case1/data.csv' overwrite into table shopping;
Loading data to table default.shopping
Deleted hdfs://localhost/user/hive/warehouse/shopping
OK
Time taken: 2.798 seconds
c) Knowing the insights of the table.
hive> describe formatted shopping;
OK
# col_name data_type comment
username string None
city string None
occupation string None
purchaseditem int None
returneditem int None
# Detailed Table Information
Database: default
Owner: training
CreateTime: Tue Jul 21 16:56:38 IST 2015
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://localhost/user/hive/warehouse/shopping
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1437478210
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim ,
serialization.format ,
Time taken: 0.302 seconds
3. Now we need to see how this data can be used.
a) Finding people who are best customer to offer them more discounts. First we will find what is the highest quantity ordered then we will match that quantity to all the records and find the top customers.
hive> select * from shopping limit 5;
OK
Seattle Podiatric doctor 187 5
Detroit Lakes Engineering and natural sciences manager 168 11
Jackson Map editor 187 17
Miami Clinical manager 193 5
Santa Fe Springs Radiographer 207 20
Time taken: 0.335 seconds
hive> select max(purchaseditem) from shopping;
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=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201507212155_0001, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201507212155_0001
Kill Command = /usr/lib/hadoop/bin/hadoop job -Dmapred.job.tracker=localhost:8021 -kill job_201507212155_0001
2015-07-21 23:16:01,887 Stage-1 map = 0%, reduce = 0%
2015-07-21 23:16:06,988 Stage-1 map = 100%, reduce = 0%
2015-07-21 23:16:16,075 Stage-1 map = 100%, reduce = 100%
Ended Job = job_201507212155_0001
OK
250
Time taken: 18.794 seconds
hive> select * from shopping where purchaseditem = 250;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201507212155_0002, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201507212155_0002
Kill Command = /usr/lib/hadoop/bin/hadoop job -Dmapred.job.tracker=localhost:8021 -kill job_201507212155_0002
2015-07-21 23:18:31,586 Stage-1 map = 0%, reduce = 0%
2015-07-21 23:18:33,598 Stage-1 map = 100%, reduce = 0%
2015-07-21 23:18:34,608 Stage-1 map = 100%, reduce = 100%
Ended Job = job_201507212155_0002
OK
Ablay1993 Seattle Ergonomist 250 7
Ablent71 Sugar Land Professional property manager 250 10
Affew1942 Alquippa Human resources consultant 250 13
Agar1976 Bell Oak Zookeeper 250 5
Akeem Manchester Marine 250 11
Alat1954 Columbus Assembler 250 7
Albertha Baton Rouge Mixing and blending machine operator 250 21
Amelia.Skiles Westfield Mediator 250 0
Amir_Spencer Polk City Epidemiologist 250 11
Angel.Thiel Hamilton Keying machine operator 250 21
Annalise_Langosh East Northport Fitness director 250 24
And so on list continues, now the company can find the top customers, this step can be performed easily on MySQL, but only when data is very small when data is in gigabits Hadoop comes in to save the day.
b) Now the analyst can find which job sector is their best client, ie analyse the jobs of the people who purchase the most, so as to increase the products of that sector.
hive> select distinct occupation from shopping where purchaseditem = 250;;
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=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201507220045_0006, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201507220045_0006
Kill Command = /usr/lib/hadoop/bin/hadoop job -Dmapred.job.tracker=localhost:8021 -kill job_201507220045_0006
2015-07-22 01:09:52,904 Stage-1 map = 0%, reduce = 0%
2015-07-22 01:09:54,914 Stage-1 map = 100%, reduce = 0%
2015-07-22 01:10:01,968 Stage-1 map = 100%, reduce = 100%
Ended Job = job_201507220045_0006
OK
Account clerk
Accounting clerk
Actuarie
Administrative assistant.
And so on.
Now knowing that an account clerk is one of the top job sector from which people are buying stuff, they can increase the sales in this department.
4. Using Pig
Now we will use pig to perform some other functions such as collecting the groups of usernames from particular city, and these cities are the top consumers.
a) Load the data in pig
grunt> fulldata = load '/user/training/case1/' using PigStorage(',') as
(username:chararray,city:chararray,occupation:chararray,purchaseditem:int,returneditem:int);
grunt>dump fulldata;
b) Now we will group the usernames of the top consumers according to cities.
For segregating records of top consumers
Hive>top = filter fulldata by purchaseditem==250;
Grouping the top consumer cities now, to obtain the usernames as a collection.
The data is grouped but is unordered.
So we need to order it now.
So in this way the final data is ordered.
Now we can use this data. Similarly we can extract the cities with least consumers, in those cities the company can define more budget for the advertising and publicity campaigns so that more people interact with the portal.
And for the cities and consumers where there is high consumption rate, company can launch new products and expand their circle.
If you have any doubts or queries, mention them in the comments box below.
Related links you will like:
Comments