Data Engineering Zoomcamp 2024 – Project 1

 

DE Zoomcamp 2024 – Project1

This repository contains a brief description of my DE Zoomcamp 2024 Project 1

Problem statement

The Retailrocket has collected a large dataset of E-commerce i.e a file with behaviour data (events.csv), a file with item properties (item_properties.сsv) and a file, which describes category tree (category_tree.сsv). The data has been collected from a real-world ecommerce website. It is raw data, i.e. without any content transformations, however, all values are hashed due to confidential issues. The purpose of publishing is to motivate researches in the field of recommender systems with implicit feedback. The goal of this project is to create a streamlined and efficient process for ingesting and analyzing e-commerce on Cloud by implementing Data Engineering concepts.

About the Dataset

Retailrocket recommender system

The dataset consists of three context files i.e. :

1. a file with behaviour data (events.csv)
2. a file, which describes category tree (category_tree.сsv).
3. a file with item properties (item_properties_part1.сsv & item_properties_part2.csv)

The data has been collected from a real-world ecommerce website. It is raw data, i.e. without any content transformations, however, all values are hashed due to confidential issues.

The behaviour data, i.e. events like clicks, add to carts, transactions, represent interactions that were collected over a period of 4.5 months. A visitor can make three types of events, namely view, addtocart or transaction.

Technologies / Tools

• Containerisation : Docker
• Cloud : GCP
• Infrastructure as code (IaC) : Terraform
• Workflow orchestration : Mage-ai
• Data Warehouse : BigQuery
• Batch processing : pyspark SQL
• IDE : VS Code, Jupyter Notebook
• Language : Python
• Visualisation : Google Looker Studio

Project Architecture

The end-to-end data pipeline includes the below steps:

• Kaggle dataset is downloaded into the Google VM.
• The downloaded CSV files (raw) are then uploaded to a folder in Google Cloud bucket (parquet) as Data Like.
• Next, the data will be stored in BigQuery with format and values same as the GCP bucket files.
• Last new tables are created from those original tables by using Spark SQL with correct data types as well as partitioned by Month and Clustered for optimised performance. These tables would be Data Warehouse tables.
• Spin up a dataproc clusters (master and worker) and execute the pyspark jobs for procusts analys purposes
• Configure Google Looker Studio to power dashboards from BigQuery Data Warehouse tables

You can find the detailed Architecture on the diagram below:

Reproducing from Scratch

Setup GCP

• Create GCP Account.
• Setup New Project and write down your Project ID.
• Configure Service Account to get access to the project and download auth-keys (.json). Change auth-keys name if required.
  Please provide the service account the permissions below (sorted by name):

1 2 3 4 5 6 7 8 9 10 11 12

1. BigQuery Admin 2. Cloud SQL Client 3. Compute Admin 4. Compute Engine Service Agent 5. Compute Network Admin 6. Compute Storage Admin 7. Dataproc Service Agent 8. Editor 9. Logs Bucket Writer 10. Owner 11. Storage Admin 12. Storage Object Admin

• Enable the following options under the APIs and services section:

1 2 3 4

1. Identity and Access Management (IAM) API 2. IAM service account credentials API 3. Cloud Dataproc API 4. Compute Engine API (if you are going to use VM instance)

Terraform as Internet as Code (IaC) to build infrastructure

• Download Terraform from here: https://www.terraform.io/downloads
• Under terraform folder, create files main.tf (required) and variables.tf (optional) to store terraform variables.
• main.td containt the following resources want to be applied:

1 2 3 4 5 6 7 8 9

1. Google Provider Versions 2. resource "google_service_account" 3. resource "google_project_iam_member" 4. resource "google_compute_firewall" 5. resource "google_storage_bucket" 6. resource "google_storage_bucket_iam_member" 7. resource "google_bigquery_dataset" 8. resource "google_dataproc_cluster" (cluster_config : master_config, worker_config, software_config : image_version = "2.2.10-debian12"     optional_components   = ["DOCKER", "JUPYTER"])

• terraform init or terraform init -upgrade: command initializes the directory, downloads, teh necesary plugins for the cinfigured provider, and prepares for use.
• terraform plan : too see execution plan
• terraform apply : to apply the changes

If you would like to remove your stack from the Cloud, use the terraform destroy command.

Reproducibility

• Assign External IP Address After terraform apply complete succeesfully, assign External IP Address for Master and Workers instances using Console. From VM Instance (Compute Engine) –> SSH     
• Setting up Mage-ai, PostgreSQL and pgAdmin through the Master VM Instance SSH. Copy repsistories.sh into VM. repsistories.sh is script for installing docker network and bring up docker containers of Mage-ai, postgresql and pgAdmin.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

#############Install Docker network############# #create a network most containers will use sudo docker network create dockernet >> /root/dockernet.log sudo docker network ls >> /root/dockernet.log   #############Bring up docker containers############ cat > /root/docker-compose.yml <<- "SCRIPT"   version: '3' services:   magic:     image: mageai/mageai:latest     command: mage start dezoomcamp     container_name: dezoomcamp-mage     build:       context: .       dockerfile: Dockerfile     environment:       USER_CODE_PATH: /home/src/dezoomcamp       POSTGRES_DBNAME: dezoomcampdb       POSTGRES_SCHEMA: public       POSTGRES_USER: postgres       POSTGRES_PASSWORD: postgres316       POSTGRES_HOST: vm-ikg-dezoomcamp       POSTGRES_PORT: 5432     ports:       - 6789:6789     volumes:       - .:/home/src/       - /root/.google/credentials/key-ikg-dezoomcamp-2024.json     restart: on-failure:5   postgres:     image: postgres:14     restart: on-failure     container_name: dezoomcamp-postgres     environment:       POSTGRES_DB: dezoomcampdb       POSTGRES_USER: postgres       POSTGRES_PASSWORD: postgres316     ports:       - 5432:5432   pgadmin:     image: dpage/pgadmin4     container_name: dezoomcamp-pgadmin     environment:       - PGADMIN_DEFAULT_EMAIL=admin@admin.com       - PGADMIN_DEFAULT_PASSWORD=root     ports:       - 8080:80   SCRIPT   sudo docker compose -f /root/docker-compose.yml up -d

chmod +x repositories.sh

sudo ./repositories.sh

==> Mage-ai, postgresql and pgAdmin would be installed and up running.

Check mage :

Check pgadmin :

Restart Juypyer Notebook

1 2 3 4 5 6 7 8 9 10 11 12

Stop : ``` sudo systemctl stop jupyter ```   Start by using port 8888 : ``` jupyter-notebook  --port=8888 --ip=0.0.0.0 --no-browser ``` Note: we use 0.0.0.0 just for demo purpose. Don't use this in production!   

• Increase memory size for cluster if required, and then restart Jupyter Notebook

1	jupyter notebook --generate-config

Open /home//.jupyter/jupyter_notebook_config.py

Edit file and modify parameter: c.NotebookApp.max_buffer_size

• Spark master and worker clusters Edit ~/.bashrc file and add lines below: export SPATH=$SPARK_HOME/bin:$SPARK/sbin:$PATH source ~/.bashrc which start-all.sh Start master and worker clusters start-all.sh Try run spark by using dataset on hdfs Copy dataset folder into /user/ hdfs dfs -mkdir /user/<some_folder> hdfs dfs -copyFromLocal ecommerce-dataset/ /user/s<some_folder> Login to Master Cluster web  Login to Worker Cluster web 

Mage-ai orchestration pipelines

Create two runtime variables: bucket_name and dataset. These variables used by all pipeline blocks.

All pipeline and its blocks available in mage-project1.tar files.

• Put mage-project1.tar into VM. You must have file copy authority to the master VM.

1 2 3

gcloud auth login gcloud config set project <project_name> --> allow to access of your google account

1	gcloud compute scp mage-project1.tar <username>@<project_name>:/home/<username>

• Open SSH on Master instance
• Copy mage-project1.tar into mage container (in this project named: dezoomcamp-mage)

1	docker cp mager-project1.tar <mage_container>:/home/src/<some_folder>   # in this project is "dezoomcamp'

• Go to mage container

1	docker exec -it <mage_container> bash

• Extract (untar) mage-project.tar file

1 2	cd /home/src/<some_folder> tar -xvf mage-project1.tar

• Open Mage application website : http://:6789

BigQuery Tables

events preview

events_dwh preview

item_properties preview

item_properties_dwh preview

Dashboard

The 10 Sold Items

Percentage of Events