Metabase + Clickhouse tutorial on Yandex.Cloud

_assignment.md

Assignment

Data Warehouse Analyst – Analytics Engineer @ OTUS.ru

1. Deploy Metabase and connect to Clickhouse Playground

Follow instructions in metabase_clickhouse.md below step by step.

2. Build your dashboard with Metabase

Attach screenshots of your dashboard.

Definition of Done:

• 4-5 tiles
• 1-2 dashboard filters
• 3-4 different visualizations
• 1-2 custom calculations (custom columns with formulae)

_webinar.md

Business Intelligence – Webinar Plan

Data Warehouse Analyst – Analytics Engineer @ OTUS.ru

• Ways to deploy BI solution – Metabase
• Adding plug-ins (custom connectors): Clickhouse
• Configure connection
• 3 ways to ask questions: simple, advanced, plain SQL
• Visualizing questions: configure graphs, axis, lines
• Group tiles into Dashboards
• Manage users, groups, permissions
• Sharing, Embedding, Alerts & Notifications
• Metabase API
• Overview of Yandex.Datalens

metabase_clickhouse.md

Metabase + Clickhouse on Yandex.Cloud tutorial

1. Spin up a Virtual Machine

# Начало работы с интерфейсом командной строки
# Install: https://cloud.yandex.ru/docs/cli/quickstart#install
# Init profile: https://cloud.yandex.ru/docs/cli/quickstart#initialize

yc config list # check 

yc compute instance create \
    --name metabase-vm \
    --ssh-key ~/.ssh/key.pub \
    --create-boot-disk image-folder-id=standard-images,image-family=ubuntu-1804-lts,size=30,auto-delete=true \
    --network-interface subnet-name=default-ru-central1-a,nat-ip-version=ipv4 \
    --memory 2G \
    --cores 2 \
    --core-fraction 50 \
    --hostname metabase-vm

2. Configure VM: SSH, Docker

# Подключиться к виртуальной машине Linux по SSH
# https://cloud.yandex.ru/docs/compute/operations/vm-connect/ssh#vm-connect

# Создание пары ключей SSH: https://cloud.yandex.ru/docs/compute/operations/vm-connect/ssh#creating-ssh-keys

ssh -L 3000:localhost:3000 -i ~/.ssh/key {-user}@{external_ip} # ssh to VM

# Install Docker

sudo apt-get update
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg2 software-properties-common
curl -fsSL https://download.docker.com/linux/debian/gpg | sudo apt-key add --
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/debian buster stable"
sudo apt-get update
sudo apt-get install -y docker-ce docker-ce-cli containerd.io
sudo usermod -aG docker $USER
source ~/.profile

3. Download Clickhouse plug-in

https://www.metabase.com/docs/latest/operations-guide/installing-metabase.html https://github.com/enqueue/metabase-clickhouse-driver

mkdir -p ./metabase/plugins
wget https://github.com/enqueue/metabase-clickhouse-driver/releases/download/0.7.5/clickhouse.metabase-driver.jar -P ./metabase/plugins

sudo chmod -R 777 metabase/plugins/ # read-write-execute global on plugins folder

4. Deploy Metabase

sudo docker run -d -p 3000:3000 \
  --volume /home/{vm-user}/metabase/plugins:/plugins \
  --name metabase metabase/metabase

# Access at http://localhost:3000/

5. Connect to Clickhouse Playground

https://clickhouse.com/docs/en/getting-started/playground/

6. Visualize a question

SELECT
    StartDate,
    MobilePhoneModel AS Phone,
    count() * 10 AS PageViews
FROM datasets.visits_v1
SAMPLE 0.1                      -- In this example, the query is executed  on a
                                -- sample from 0.1 (10%) of data. Values of
                                -- aggregate functions are not corrected
                                -- automatically, so to get an approximate
                                -- result, the value 'count()' is manually
                                -- multiplied by 10.
WHERE NOT empty(Phone)
      AND StartDate BETWEEN '2014-03-17' AND '2014-03-30'
GROUP BY StartDate, Phone
ORDER BY PageViews DESC;