Загрузка и анализ данных из Object Storage в Managed ClickHouse®

Перед началом работы

1. Зарегистрируйтесь в личном кабинете Cloud.ru.

   Если вы уже зарегистрированы, войдите под своей учетной записью.

2. Создайте и загрузите SSH-ключ в облако.

3. Установите DBeaver.

1. Разверните ресурсы в облаке

1. Создайте виртуальную сеть с названием web-analytics-VPC.

2. Создайте подсеть со следующими параметрами:

   • Название — web-analytics-subnet.

   • Адрес — 10.10.1.0/24.

   • VPC — web-analytics-VPC.

   • DNS-серверы — 8.8.8.8.

3. Создайте новую группу безопасности со следующими параметрами:

   • Зона доступности — укажите ту же зону доступности, что выбрана для подсети.

   • Название — web-analytics-sg.

   • Добавьте правила входящего и исходящего трафика:

     Трафик	Протокол	Порт	Тип источника/адресата	Источник/Адресат
     Входящий	TCP	9000	IP-адрес	0.0.0.0/0
     Входящий	TCP	8123	IP-адрес	0.0.0.0/0
     Входящий	TCP	22	IP-адрес	0.0.0.0/0
     Исходящий	Любой	—	IP-адрес	0.0.0.0/0

4. Создайте виртуальную машину со следующими параметрами:

   • Название — web-analytics-vm.

   • Зона доступности — укажите ту же зону доступности, что выбрана для подсети.

   • Образ — на вкладке Публичные выберите образ Ubuntu 24.04.

   • Сетевой интерфейс — выберите тип Подсеть с публичным IP.

   • VPC — web-analytics-VPC.

   • Подсеть — web-analytics-subnet.

   • Публичный IP — оставьте Арендовать новый или выберите IP-адрес из списка арендованных.

   • Группы безопасности — web-analytics-sg и группа безопасности по умолчанию.

   • Логин — оставьте значение по умолчанию или укажите новый.

   • Метод аутентификации — Публичный ключ и Пароль.

   • Пароль — задайте пароль пользователя.

   • Остальные параметры оставьте по умолчанию или выберите на свое усмотрение.

5. Создайте кластер Managed ClickHouse® со следующими параметрами:

   • Название кластера — web-analytics.

   • Тип — Single-node.

   • Зона доступности — укажите ту же зону доступности, что выбрана для подсети.

   • Подсеть — web-analytics-subnet.

6. Убедитесь, что ресурсы созданы и отображаются в личном кабинете:

   1. На странице сервиса VPC:

      • отображается сеть web-analytics-VPC;

      • в списке подсетей отображается web-analytics-subnet.

   2. На странице сервиса «Группы безопасности» отображается группа безопасности web-analytics-sg со статусом «Создана».

   3. На странице сервиса «Виртуальные машины» отображается виртуальная машина web-analytics-vm со статусом «Запущена».

   4. На странице сервиса Managed ClickHouse® отображается кластер web-analytics со статусом «Доступен».

2. Загрузите данные в хранилище

1. Создайте бакет в Object Storage с названием web-analytics-bucket.

2. Создайте файл event.csv с тестовыми данными:

   event_date,user_id,event_type,product_id,revenue
   2024-03-20,1001,view,AB123,0
   2024-03-20,1001,purchase,AB123,29.99
   2024-03-20,1002,view,CD456,0
   2024-03-21,1001,view,CD456,0
   2024-03-21,1003,purchase,EF789,49.99
   2024-03-22,1002,purchase,AB123,29.99
   
   

3. Загрузите в бакет файл event.csv.

3. Настройте nginx для проксирования запросов к базе данных

1. Подключитесь к виртуальной машине по SSH.

2. Обновите систему:

   sudo apt update && sudo apt upgrade -y
   
   

3. Установите nginx и clickhouse-client:

   sudo apt install -y nginx-full clickhouse-client
   
   

4. Откройте файл конфигурации для подключения stream-блока:

   sudo nano /etc/nginx/nginx.conf
   
   

5. Добавьте блок перед блоком http:

   stream {
   
   # Native ClickHouse (TCP)
   upstream ch_native {
      server <db_private_ip>:9000;
      }
   server {
      listen 9000; proxy_pass ch_native;
      }
   
   # HTTP ClickHouse (для DBeaver / JDBC / BI))
   upstream ch_http {
      server <db_private_ip>:8123;
      }
   server {
      listen 8123; proxy_pass ch_http;
      }
   }
   
   

   Где <db_private_ip> — внутренний IP-адрес со страницы информации о кластере из блока Данные для подключения.

6. Примените конфигурацию:

   sudo nginx -t
   sudo systemctl restart nginx
   
   

4. Подключитесь к кластеру Managed ClickHouse® из DBeaver

1. Создайте пользователя с именем analytics-user и ролью cloud_admin.

2. Создайте базу данных с названием analytics.

3. Откройте DBeaver и в панели сверху нажмите Новое соединение.

4. В списке соединений выберите ClickHouse.

5. Нажмите Далее и заполните поля на вкладке Главное:

   • Хост — публичный IP виртуальной машины web-analytics-vm.

   • Порт — 8123.

   • Пользователь — analytics-user.

   • БД/Схема — analytics.

   • Пароль — пароль, заданный при создании пользователя analytics-user.

   

6. Нажмите Тест соединения.

7. Нажмите Готово.

5. Загрузите данные в DBeaver и выполните запросы

1. Создайте таблицу events в базе analytics:

   CREATE TABLE analytics.events
   (
      event_date Date,
      user_id UInt32,
      event_type String,
      product_id String,
      revenue Float32
   )
   ENGINE = MergeTree
   ORDER BY (event_date, user_id);
   
   

2. Загрузите данные из файла event.csv, который был добавлен в хранилище на втором шаге:

   INSERT INTO analytics.events
   SELECT
      event_date,
      user_id,
      event_type,
      product_id,
      revenue
   FROM url(
      '<s3_link>',
      'CSVWithNames'
   );
   
   

   Где <s3_link> — ссылка на файл в бакете.

3. Проверьте количество загруженных строк:

   SELECT count() FROM analytics.events;
   
   

4. Постройте отчет по ежедневной активности и конверсии:

   SELECT
      event_date,
      uniq(user_id) as daily_active_users,
      countIf(event_type = 'purchase') as purchases,
      sumIf(revenue, event_type = 'purchase') as daily_revenue,
      (purchases / daily_active_users) * 100 as conversion_rate_percent
   FROM analytics.events
   GROUP BY event_date
   ORDER BY event_date;
   
   

6. Удалите доступ по SSH для виртуальной машины

1. В личном кабинете перейдите в сервис «Виртуальные машины» и выберите машину web-analytics-vm, созданную на первом шаге.

2. Перейдите на вкладку Сетевые параметры.

3. Нажмите Изменить группы безопасности и удалите группу SSH-access_ru.

4. Нажмите Сохранить.

5. Убедитесь, что доступа нет — попробуйте подключиться к виртуальной машине по SSH.

Результат