Case Study Adventure Works

Aim

To perform Hive analytics on Sales and Customer Demographics data using big data tools such as Sqoop, Spark, and HDFS..

Problem Statement

We will dig deeper into some of the Hive's analytical features for this hive project. Using SQL is still highly popular, and it will be for the foreseeable future. Most big data technologies have been modified to allow users to interact with them using SQL. This is due to the years of experience and expertise put into training, acceptance, tooling, standard development, and re-engineering. So, in many circumstances, employing these excellent SQL tools to access data may answer many analytical queries without resorting to machine learning, business intelligence, or data mining. This big data project will look at Hive's capabilities to run analytical queries on massive datasets. We will use the Adventure works dataset in a MySQL dataset for this project, and we'll need to ingest and modify the data. We'll use Adventure works sales and Customer demographics data to perform analysis and answer the following questions:

• Which age group of customers contribute to more sales?
• To find the upper and lower discount limits offered for any product
• Sales contributions by customer
• To Understand customer persona purchasing pattern based on gender, education and yearly income
• To find the sales contribution by customers on the overall year to date sales belong to categorized by same gender, yearly income.
• To identify the top performing territory based on sales
• To find the territory-wise sales and their adherence to the defined sales quota.

Technologies used to work in project

MySQL

HDFS Ecosystem

• Sqoop
• Hive
• PySpark

Data Source Description

Adventure Works is a free sample database of retail sales data. In this project, we will be only using Customer test, Individual test, Credit card, Sales order details, Store, Sales territory, Salesperson, Sales order header, Special offer tables from this database.

      Customer Individual: This table contain all customer data related information.

      Individual: This table contain all Individual data information.

      Credit Card: This table contain all credit card data information.

Project Architecture

Steps performed to achive the task

• Create tables in MySQL database.
• Load data from MySQL into HDFS storage using Sqoop commands.
• Move data from HDFS to Hive.
• Integrate Hive into Spark and perform data cleaning.
• Using Pyspark, extract Customer demographics information from data and store it as parquet files.
• Move parquet files from Spark to Hive.
• Create tables in Hive and load data from Parquet files into tables.
• Perform Hive analytics on Sales and Customer demographics data.