Hive Mini Project to Build a Data Warehouse for e-Commerce
In this hive project, you will design a data warehouse for e-commerce application to perform Hive analytics on Sales and Customer Demographics data using big data tools such as Sqoop, Spark, and HDFS.
๐๐ด๐ฒ๐ป๐ฑ๐ฎ
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.
๐๐ถ๐บ
To perform Hive analytics on Sales and Customer Demographics data using big data tools such as Sqoop, Spark, and HDFS.
๐๐ฎ๐๐ฎ ๐๐ฒ๐๐ฐ๐ฟ๐ถ๐ฝ๐๐ถ๐ผ๐ป
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.
๐ง๐ฒ๐ฐ๐ต ๐ฆ๐๐ฎ๐ฐ๐ธ
โ Language: SQL, Scala
โ Services: AWS EC2, Docker, MySQL, Sqoop, Hive, HDFS, Spark
๐๐ช๐ฆ ๐๐๐ฎ
Amazon EC2 instance is a virtual server on Amazon's Elastic Compute Cloud (EC2) for executing applications on the Amazon Web Services (AWS) architecture. Corporate customers can use the Amazon Elastic Compute Cloud (EC2) service to run applications in a computer environment. Amazon EC2 eliminates the need for upfront hardware investment, allowing customers to design and deploy projects quickly. Users can launch up to 10 virtual servers, configure security and networking, and manage storage on Amazon EC2.
๐๐ผ๐ฐ๐ธ๐ฒ๐ฟ
Docker is a free and open-source containerization platform, and it enables programmers to package programs into containers. These standardized executable components combine application source code with the libraries and dependencies required to run that code in any environment.
๐ ๐๐ฆ๐ค๐ MySQL is a SQL (Structured Query Language) based relational database management system. The platform can be used for data warehousing, e-commerce, logging applications, etc.
๐ฆ๐พ๐ผ๐ผ๐ฝ
Sqoop is a data transfer mechanism for Hadoop and relational database servers. It is used to import data from relational databases such as MySQL and Oracle into Hadoop HDFS, Hive, and export data from the Hadoop file system to relational databases.
๐ฆ๐ฐ๐ฎ๐น๐ฎ Scala is a multi-paradigm, general-purpose, high-level programming language. It's an object-oriented programming language that also supports functional programming. Scala applications can be converted to bytecodes and run on the Java Virtual Machine (JVM). Scala is a scalable programming language, and JavaScript runtimes are also available.
๐๐ถ๐๐ฒ
Apache Hive is a fault-tolerant distributed data warehouse that allows for massive-scale analytics. Using SQL, Hive allows users to read, write, and manage petabytes of data. Hive is built on top of Apache Hadoop, an open-source platform for storing and processing large amounts of data. As a result, Hive is inextricably linked to Hadoop and is designed to process petabytes of data quickly. Hive is distinguished by its ability to query large datasets with a SQL-like interface utilizing Apache Tez or MapReduce.
๐๐ฝ๐ฝ๐ฟ๐ผ๐ฎ๐ฐ๐ต
โ Create an AWS EC2 instance and launch it.
โ Create docker images using docker-compose file on EC2 machine via ssh.
โ Create tables in MySQL.
โ Load data from MySQL into HDFS storage using Sqoop commands.
โ Move data from HDFS to Hive.
โ Integrate Hive into Spark.
โ Using scala programming language, 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.
๐ฃ๐ฟ๐ผ๐ท๐ฒ๐ฐ๐ ๐ง๐ฎ๐ธ๐ฒ๐ฎ๐๐ฎ๐๐
โ Understanding various services provided by AWS
โ Creating an AWS EC2 instance
โ Connecting to an AWS EC2 instance via SSH
โ Introduction to Docker
โ Visualizing the complete Architecture of the system
โ Usage of docker-composer and starting all tools
โ Copying a file from a local machine to an EC2 machine
โ Understanding the schema of the dataset
โ Data ingestion/transformation using Sqoop, Spark, and Hive
โ Moving the data from MySQL to HDFS
โ Creating Hive table and troubleshooting it
โ Using Parquet and Xpath to access schema
โ Understanding the use of GROUP BY, GROUPING SETS, ROLL-UP, CUBE clauses
โ Understanding different analytic functions in Hive
๐ก๐ผ๐๐ฒ:
โ Postgresql jar file
- Command to move jar file from local machine to ec2 machine =>scp -r -i "demo_hive.pem" postgresql-42.3.1.jar
ec2-user@ec2-3-93-63-210.compute-1.amazonaws.com:/home/ec2-user/ - Command to move jar file from ec2 machine to Spark container =>docker cp /home/ec2-user/postgresql-42.3.1.jar
hdp_spark-master:/spark/jars
โ Hive-site.xml file
- Command to copy hive-site.xml file from Hive container and paste it in ec2 machine =>docker cp ra_hive-server:/opt/hive/conf/hive-site.xml /home/ec2-user
- Command to copy hive-site.xml file from ec2 machine and paste it in
Spark container =>docker cp /home/ec2-user/hive-site.xml hdp_spark-master:/spark/conf