Импорт данных из внешних источников в Managed ClickHouse

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

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

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

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

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

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

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

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

   • Зона доступности — ru.AZ-1.

   • VPC — VPC-tutorial.

   • Адрес — 10.10.1.0/24.

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

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

   • Зона доступности — ru.AZ-1.

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

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

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

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

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

   • Зона доступности — ru.AZ-1.

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

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

   • VPC — VPC-tutorial.

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

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

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

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

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

   • Публичный ключ — укажите ключ, созданный ранее.

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

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

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

   • Название кластера — clickhouse_tutorial.

   • Тип — Single-node.

   • Зона доступности — ru.AZ-1.

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

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

   1. На странице сервиса VPC отображается сеть web-analytics-VPC со статусом «Создана».

   2. На странице сервиса «Подсети» отображается подсеть subnet-tutorial со статусом «Создана».

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

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

   5. На странице сервиса Managed ClickHouse отображается кластер clickhouse_tutorial со статусом «Доступен».

2. Настройте 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
   
   

3. Подключитесь к базе данных через DBeaver или clickhouse-client

1. Создайте пользователя с именем tutorial_user и ролью cloud_admin, сохраните пароль пользователя.

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

3. Подключитесь к базе данных:

4. Импортируйте данные из CSV

1. Создайте файл 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
   
   

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

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

   Скрипт можно выполнить в DBeaver или в clickhouse-client, предварительно подключившись к нужной базе данных.

3. Загрузите данные в таблицу:

5. Импортируйте данные из JSON

1. Создайте файл data.json с тестовыми данными в формате JSONEachRow:

   [
      {"id": 1, "name": "Item1", "value": 10.5, "date": "2023-01-01", "category": "A"},
      {"id": 2, "name": "Item2", "value": 20.3, "date": "2023-01-02", "category": "B"},
      {"id": 3, "name": "Item3", "value": 30.7, "date": "2023-01-03", "category": "C"},
      {"id": 4, "name": "Item4", "value": 40.1, "date": "2023-01-04", "category": "A"},
      {"id": 5, "name": "Item5", "value": 50.9, "date": "2023-01-05", "category": "B"}
   ]
   
   

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

   CREATE TABLE tutorial_db.data
   (
      id UInt32,
      name String,
      value Float64,
      date Date,
      category Enum ('A', 'B', 'C')
   )
   ENGINE = MergeTree()
   ORDER BY id;
   
   

   Скрипт можно выполнить в DBeaver или в clickhouse-client, предварительно подключившись к нужной базе данных.

3. Для загрузки данных через clickhouse-client предварительно скопируйте файл на сервер ClickHouse®:

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

   2. С виртуальной машины подключитесь к узлу ClickHouse® внутри кластера:

      ssh <node_user>@<node_ip_or_hostname>
      
      

      Где:

      • <node_user> — имя пользователя узла ClickHouse®.

      • <node_ip_or_hostname> — IP-адрес или доменное имя одного из узлов кластера.

   3. Создайте директорию для хранения входящего файла на узле ClickHouse®:

      mkdir -p /var/lib/clickhouse/incoming && sudo chmod -R 755 /var/lib/clickhouse/incoming
      
      

   4. Скопируйте файл с локального компьютера на узел ClickHouse® через виртуальную машину:

      1. Чтобы перенести файл на виртуальную машину, на локальном ПК выполните команду:

         scp path/to/local/file.json <username>@<vm_ip_or_hostname>:/tmp/
         
         

         Где:

         • path/to/local/file.json — путь к файлу на вашем локальном компьютере.

         • <username> — имя пользователя виртуальной машины.

         • <vm_ip_or_hostname> — публичный IP-адрес или доменное имя виртуальной машины.

      2. Чтобы переместить файл с виртуальной машины на узел ClickHouse®, находясь на виртуальной машине, выполните:

         scp /tmp/file.json <node_user>@<node_ip_or_hostname>:/var/lib/clickhouse/incoming/
         
         

         Где:

         • /tmp/file.json — путь к файлу на виртуальной машине.

         • <node_user> — имя пользователя узла ClickHouse®.

         • <node_ip_or_hostname> — IP-адрес или доменное имя одного из узлов кластера.

   5. Чтобы проверить наличие файла, переподключитесь к узлу кластера ClickHouse® и убедитесь, что файл передан:

      ls -lh /var/lib/clickhouse/incoming/
      
      

   6. Загрузите данные из файла в заранее подготовленную таблицу:

      clickhouse-client --query="INSERT INTO tutorial_db.data FORMAT JSONEachRow " < /var/lib/clickhouse/incoming/data.json
      
      

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

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

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

3. В правом верхнем углу блока нужного сетевого интерфейса нажмите и выберите Изменить группы безопасности.

4. В поле Группы безопасности удалите группу SSH-access_ru.

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

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

Результат