Case-Study-AdventureWorks

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:

Total Purchase based on Education and Occupation

Numbers of cars Owned based on TerritoryID and Gender

Total Purchase based on Education and Yearly Income

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 Test: This table contain all customer data related information.

      Individual Text: 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.