Setting up Clickhouse cluster of base and predict customer shopping data with visualisation of results

Tools:

1. EDA, data processing, feature engineering, machine learning:

• Python with Google colab enviroment:
  • Pandas
  • Numpy
  • CatBoost
  • Sklearn

2. Developer Enviroment:

• Devcontainer

3. Deploy Infrastructure:

• Yandex.Cloud
• Terraform

4. Data Pipelines:

• Airbyte

5. Data modeling:

• DBT

6. Data visualization

• Yandex DataLens
• Power BI

Plan

• Creating a Forecast Using ML Methods in Google Colab
• Configure Developer Environment
• Deploy Infrastructure to Yandex.Cloud with Terraform
  • Get familiar with Yandex.Cloud web UI
  • Configure yc CLI
  • Populate .env file, Set environment variables
  • Deploy using Terraform: VM with Airbyte installed, S3 Bucket, Clickhouse
• Access Airbyte
• Configure Data Pipelines
  • Configure Object Storage Source
  • Configure Clickhouse Destination
  • Sync data to Destination
• Create data marts with dbt
  • Create DBT Model
  • Run DBT model
• Create dashboards with Yandex Datalens and Power BI
• Delete cloud resources

Infrastructure

1. Create a Forecast of revenue with CatBoost Library

https://github.com/neworderby/dbt_ml_retail/blob/d12a59c2e44f998559813fd1470da6eeda7bd625/Forecast/Forecast_Retail_CatBoost.ipynb

https://github.com/neworderby/Python_EDA_ML_DataViz/blob/c8f921eb90524208cd05177dfad38f85ed088466/Forecast_Retail_CatBoost.ipynb

2. Configure Developer Environment with devcontainer

1. Install Docker on your local machine.

2. Install devcontainer CLI:

   Open command palette (CMD + SHIFT+ P) type Install devcontainer CLI

   

3. Next build and open dev container:

   # build dev container
   devcontainer build .
   
   # open dev container
   devcontainer open .

Verify you are in a development container by running commands:

terraform -v

yc --version

dbt --version

If any of these commands fails printing out used software version then you are probably running it on your local machine not in a dev container!

3. Deploy Infrastructure to Yandex.Cloud with Terraform

1. Get familiar with Yandex.Cloud web UI

   We will deploy:

   • Yandex Compute Cloud
   • Yandex Object Storage
   • Yandex Managed Service for ClickHouse

   

2. Configure yc CLI: Getting started with the command-line interface by Yandex Cloud

   yc init

3. Populate .env file

   .env is used to store secrets as environment variables.

   Copy template file .env.template to .env file:

   cp .env.template .env

   Open file in editor and set your own values.

   ❗️ Never commit secrets to git

4. Set environment variables:

   export YC_TOKEN=$(yc iam create-token)
   export YC_CLOUD_ID=$(yc config get cloud-id)
   export YC_FOLDER_ID=$(yc config get folder-id)
   export TF_VAR_folder_id=$(yc config get folder-id)
   export $(xargs < .env)
   
   ## DEBUG
   # export TF_LOG_PATH=./terraform.log
   # export TF_LOG=trace

5. Deploy using Terraform

   Configure YC Terraform provider:

   cp terraformrc ~/.terraformrc

   Get familiar with Cloud Infrastructure: main.tf and variables.tf

   terraform init
   terraform validate
   terraform fmt
   terraform plan
   terraform apply

   

   

   Store terraform output values as Environment Variables:

   export CLICKHOUSE_HOST=$(terraform output -raw clickhouse_host_fqdn)
   export DBT_HOST=${CLICKHOUSE_HOST}
   export DBT_USER=${CLICKHOUSE_USER}
   export DBT_PASSWORD=${TF_VAR_clickhouse_password}

   [EN] Reference: Getting started with Terraform by Yandex Cloud

   [RU] Reference: Начало работы с Terraform by Yandex Cloud

4. Access Airbyte

1. Get VM's public IP:

   terraform output -raw yandex_compute_instance_nat_ip_address

2. Lab's VM image already has Airbyte installed

   I have prepared VM image and made it publicly available:

   https://cloud.yandex.com/en-ru/docs/compute/concepts/image#public

   yc resource-manager cloud add-access-binding y-cloud \
       --role compute.images.user \
       --subject system:allAuthenticatedUsers

   TODO: define VM image with Packer so that everyone is able to build his own image

   However if you'd like to do it yourself:

   ssh airbyte@{yandex_compute_instance_nat_ip_address}
   
   sudo mkdir airbyte && cd airbyte
   sudo wget https://raw.githubusercontent.com/airbytehq/airbyte-platform/main/{.env,flags.yml,docker-compose.yaml}
   sudo docker-compose up -d

3. Log into web UI at {yandex_compute_instance_nat_ip_address}:8000

   With credentials:

   airbyte
   password
   

   

5. Configure Data Pipelines

1. Put the data to object storage

   

2. Configure Object Storage Source

   

3. Configure Clickhouse Destination

   terraform output -raw clickhouse_host_fqdn

   

4. Sync the data to Clickhouse Destination for each source

   

   

5. Check the status of replication for each source

   

   

6. Check the data in Clickhouse

   

6. Create data marts with dbt

1. First prepare your model in marts.yaml

   

Export variables to allow connection to Clickhouse in your Yandex Cloud:

export CLICKHOUSE_HOST=$(terraform output -raw clickhouse_host_fqdn)
export DBT_HOST=${CLICKHOUSE_HOST}
export DBT_USER=${CLICKHOUSE_USER}
export DBT_PASSWORD=${TF_VAR_clickhouse_password}

Make sure it works:

dbt debug

Run data modeling:

dbt build

Check your marts in Clickhouse:

7. Create dashboards with Yandex DataLens and Power BI

In Yandex DataLens:

1. First change the settings in Clickhouse cluster to access the DataLens tool

2. Sync your cluster with DataLens

3. Set the data model

4. Create widgets

5. Create the dashboard

In Power BI

1. Set your source

2. Set the data model

3. Create measures

4. Create the dashboard

https://github.com/neworderby/dbt_ml_retail/blob/main/Power%20BI/Retail%20Dashboard.pbix

8. Delete cloud resources

terraform destroy