1. Главная
  2. Хаб
  3. Сценарии
  4. Аналитика поверх PostgreSQL: ClickHouse читает данные приложения через интеграцию и словари

Сценарий 12

Аналитика поверх PostgreSQL: ClickHouse читает данные приложения через интеграцию и словари

Отчёты по заказам тормозят рабочую базу. Подключаем к Managed PostgreSQL управляемый ClickHouse: запросы прямо к таблицам PostgreSQL, регулярная выгрузка заказов в колоночное хранилище и справочник клиентов в виде словаря, который ClickHouse обновляет сам.

45 минСредний7 шагов PostgreSQL, ClickHouse обновлено

Содержание
  1. 01Подготовьте данные и пользователя в PostgreSQL
  2. 02Создайте ClickHouse в той же сети
  3. 03Заведите пользователя ClickHouse и подключите интеграцию
  4. 04Запросите таблицы PostgreSQL из ClickHouse
  5. 05Скопируйте заказы в ClickHouse и догружайте изменения
  6. 06Подключите справочник клиентов как словарь
  7. 07Постройте отчёт
  8. Проверка результата
  9. Как удалить ресурсы
  10. Типичные ошибки
  11. Сколько стоит

Что получится

Интеграция shop_pg от пользователя PostgreSQL только на чтение, таблица shop.orders в ClickHouse с инкрементальной загрузкой по расписанию, словарь shop.customers с обновлением раз в 5 минут и отчёт по выручке в разрезе сегментов клиентов, который не нагружает PostgreSQL.

Что понадобится

  • Managed PostgreSQL с базой приложения shop — например, из сценария «Managed PostgreSQL для приложения».
  • Сервер в той же сети проекта для SQL-клиентов и задания по расписанию.
  • CLI h3 с API-ключом проекта (h3 init).
  1. Шаг 1. Подготовьте данные и пользователя в PostgreSQL

    ClickHouse будет читать PostgreSQL от отдельного пользователя только на чтение. Для примера создадим в базе shop две таблицы — клиентов и заказов. Колонка updated_at нужна для инкрементальной загрузки.

    Консоль

    1. В карточке PostgreSQL в строке «Пользователи и базы» нажмите Открыть.
    2. В разделе «Пользователи» нажмите Создать пользователя. В окне «Новый пользователь»: Имя пользователя ch_reader, Доступ к базам — у базы shop «Только чтение». Нажмите Создать и закройте окно с паролем — пароль ClickHouse возьмёт сам.

    CLI

    Терминал
    echo '{"name":"ch_reader","grants":[{"database":"shop","preset":"readonly"}]}' | h3 db user create <id PostgreSQL> --from-file -
    Тестовые данные (от владельца базы shop, с сервера в сети проекта)
    psql "host=<адрес PostgreSQL в VPC> port=5432 dbname=shop user=shop sslmode=require" <<'SQL'
    CREATE TABLE customers (id bigint PRIMARY KEY, segment text, city text);
    CREATE TABLE orders (id bigint PRIMARY KEY, customer_id bigint REFERENCES customers, amount numeric(12,2),
                         created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now());
    INSERT INTO customers SELECT g, (ARRAY['b2c','b2b','vip'])[1 + g % 3], (ARRAY['Москва','Казань','Пермь'])[1 + g % 3]
      FROM generate_series(1, 1000) g;
    INSERT INTO orders (id, customer_id, amount, created_at, updated_at)
      SELECT g, 1 + g % 1000, (random() * 5000)::numeric(12,2), now() - g * interval '1 minute', now() - g * interval '1 minute'
      FROM generate_series(1, 100000) g;
    SQL

    Проверка

    У пользователя ch_reader в колонке «Доступ» — shop, «Только чтение». Пресет действует и на таблицы, которые владелец создаст позже.

  2. Шаг 2. Создайте ClickHouse в той же сети

    Интеграция работает только между базами одного проекта и одной VPC: ClickHouse ходит в PostgreSQL по его адресу в VPC.

    Консоль

    1. Базы данных → Создать. СУБД — ClickHouse 26.8. vCPU — 2, RAM — 4 ГБ, Диски — 20 ГБ.
    2. Имя базы данных — shop-analytics. Сеть — та же, что у PostgreSQL. Нажмите Создать базу данных.

    CLI

    Терминал
    h3 db create --name shop-analytics --engine clickhouse --version 26.8 --cpu 2 --memory 4Gi --size 20

    Проверка

    Обе базы в статусе «Работает». В блоке «Подключение» каждой базы на вкладке «Внутри VPC» есть Адрес в VPC.

  3. Шаг 3. Заведите пользователя ClickHouse и подключите интеграцию

    etl — владелец базы shop в ClickHouse (пресет admin) и единственный, кому выдана интеграция. Пароль ch_reader платформа берёт из своих записей: в SQL, журнале запросов и ответах API его нет.

    CLI

    Терминал
    h3 db user create <id ClickHouse> --name etl
    h3 db database create <id ClickHouse> --name shop --owner etl
    h3 api POST /api/databases/v1/<id ClickHouse>/integrations -d '{
      "name": "shop_pg",
      "kind": "postgresql",
      "sourceDatabaseId": "<id PostgreSQL>",
      "user": "ch_reader",
      "database": "shop",
      "grantTo": ["etl"]
    }'
    h3 api GET /api/databases/v1/<id ClickHouse>/integrations

    Проверка

    Интеграция shop_pg в статусе READY.

    Если не получилось

    • 409 SOURCE_NOT_REACHABLE — PostgreSQL в другой сети проекта или у него не заполнен Адрес в VPC: ClickHouse подключается только по нему.
    • UNKNOWN_USER — в grantTo указан встроенный app или несуществующий пользователь. Нужен пользователь, созданный через h3 db user create.
  4. Шаг 4. Запросите таблицы PostgreSQL из ClickHouse

    Табличная функция postgresql(shop_pg, table = …) читает таблицу PostgreSQL прямо во время запроса. Это удобно для разовых сверок, но каждый такой запрос нагружает PostgreSQL — регулярные отчёты лучше строить по копии (следующий шаг).

    Подключение к ClickHouse — только по TLS. В карточке ClickHouse нажмите Скачать сертификат CA и сопоставьте имя db.h3llo.cloud, на которое выдан сертификат, с адресом ClickHouse.

    На сервере в сети проекта
    echo "<адрес ClickHouse в VPC> db.h3llo.cloud" | sudo tee -a /etc/hosts
    curl https://clickhouse.com/ | sh
    cat > ch.xml <<'EOF'
    <config>
      <openSSL><client>
        <loadDefaultCAFile>false</loadDefaultCAFile>
        <caConfig>/home/user/h3llo-db-ca.pem</caConfig>
        <verificationMode>strict</verificationMode>
        <invalidCertificateHandler><name>RejectCertificateHandler</name></invalidCertificateHandler>
      </client></openSSL>
    </config>
    EOF
    ./clickhouse client --config-file ch.xml --secure --host db.h3llo.cloud --port 9440 --user etl --password '<пароль etl>' \
      --query "SELECT count(), sum(amount) FROM postgresql(shop_pg, table = 'orders')"

    Проверка

    Запрос возвращает 100000 заказов и их сумму — те же числа, что SELECT count(*), sum(amount) FROM orders в PostgreSQL.

    Если не получилось

    • ACCESS_DENIED на postgresql — пользователь не в grantTo интеграции. Добавьте его: h3 api PUT /api/databases/v1/<id ClickHouse>/integrations/shop_pg/grants -d '{"grantTo":["etl"]}'.
    • permission denied for table orders — у ch_reader нет доступа к базе shop в PostgreSQL. Проверьте пресет в «Пользователи и базы».
  5. Шаг 5. Скопируйте заказы в ClickHouse и догружайте изменения

    Копия в MergeTree отвечает на аналитические запросы за миллисекунды и не трогает PostgreSQL. ReplacingMergeTree(updated_at) хранит последнюю версию строки: повторная загрузка изменённого заказа не создаёт дубль в итогах с FINAL.

    orders.sql
    CREATE TABLE shop.orders
    (
        id          UInt64,
        customer_id UInt64,
        amount      Decimal(12, 2),
        created_at  DateTime,
        updated_at  DateTime
    )
    ENGINE = ReplacingMergeTree(updated_at)
    PARTITION BY toYYYYMM(created_at)
    ORDER BY id;
    
    INSERT INTO shop.orders
    SELECT id, customer_id, amount, created_at, updated_at
    FROM postgresql(shop_pg, table = 'orders');
    /usr/local/bin/orders-sync.sh
    #!/usr/bin/env bash
    set -euo pipefail
    . "$HOME/.ch.env"   # CH_PASSWORD=<пароль etl>
    "$HOME/clickhouse" client --config-file "$HOME/ch.xml" --secure --host db.h3llo.cloud --port 9440 \
      --user etl --password "$CH_PASSWORD" --query "
        INSERT INTO shop.orders
        SELECT id, customer_id, amount, created_at, updated_at
        FROM postgresql(shop_pg, table = 'orders')
        WHERE updated_at > (SELECT max(updated_at) - INTERVAL 5 MINUTE FROM shop.orders)"
    echo "$(date -Is) ok"
    Первая загрузка и расписание
    ./clickhouse client --config-file ch.xml --secure --host db.h3llo.cloud --port 9440 \
      --user etl --password '<пароль etl>' --queries-file orders.sql
    sudo nano /usr/local/bin/orders-sync.sh && sudo chmod 755 /usr/local/bin/orders-sync.sh
    ( crontab -l 2>/dev/null; echo '*/5 * * * * /usr/local/bin/orders-sync.sh >> $HOME/orders-sync.log 2>&1' ) | crontab -

    Проверка

    В shop.orders 100000 строк, а через 5 минут после изменения заказа в PostgreSQL (UPDATE orders SET amount = 1, updated_at = now() WHERE id = 1) в ClickHouse видна новая сумма: SELECT amount FROM shop.orders FINAL WHERE id = 1.

    Если не получилось

    • Условие WHERE updated_at > … ClickHouse передаёт в PostgreSQL, поэтому в PostgreSQL нужен индекс: CREATE INDEX ON orders (updated_at);.
    • Удаления так не переносятся: для них заведите в PostgreSQL флаг deleted_at и учитывайте его в отчётах.
  6. Шаг 6. Подключите справочник клиентов как словарь

    Справочники (клиенты, товары, тарифы) удобнее держать словарём: ClickHouse загружает таблицу PostgreSQL в память целиком и обновляет её раз в lifetimeSeconds. В запросах — функция dictGet, без JOIN.

    CLI

    Терминал
    h3 api POST /api/databases/v1/<id ClickHouse>/dictionaries -d '{
      "name": "customers",
      "database": "shop",
      "integration": "shop_pg",
      "table": "customers",
      "schema": "public",
      "columns": [
        {"name": "id", "type": "UInt64"},
        {"name": "segment", "type": "String"},
        {"name": "city", "type": "String"}
      ],
      "primaryKey": "id",
      "layout": "hashed",
      "lifetimeSeconds": 300
    }'
    h3 api GET /api/databases/v1/<id ClickHouse>/dictionaries

    Проверка

    Словарь shop.customers в статусе LOADED (или NOT_LOADED до первого обращения), поле error пустое.

    Если не получилось

    • primaryKey must be one of the columns — ключ словаря должен быть среди columns.
    • Имена колонок словаря — латинские строчные буквы, цифры и _, как в таблице PostgreSQL.
  7. Шаг 7. Постройте отчёт

    Выручка по сегментам клиентов за 30 дней
    SELECT
        dictGet('shop.customers', 'segment', customer_id) AS segment,
        count()     AS orders,
        sum(amount) AS revenue
    FROM shop.orders FINAL
    WHERE created_at > now() - INTERVAL 30 DAY
    GROUP BY segment
    ORDER BY revenue DESC;

    Проверка

    Три строки — b2c, b2b, vip — с числом заказов и выручкой. Запрос работает только с ClickHouse: в журнале PostgreSQL его нет.

Проверка результата

Аналитика живёт отдельно от рабочей базы: копия обновляется каждые 5 минут, справочник — сам. Журнал загрузок:

Терминал
tail -n 5 ~/orders-sync.log

Как удалить ресурсы

Удаление ClickHouse не затрагивает PostgreSQL и его данные.

CLI

Терминал
h3 db delete <id ClickHouse>
h3 db user delete <id PostgreSQL> ch_reader
  1. Уберите задание: crontab -l | grep -v orders-sync | crontab -.
  2. Базы данных → shop-analytics → меню «…» → Удалить.
  3. В PostgreSQL удалите пользователя ch_reader: «Пользователи и базы» → меню пользователя → Удалить.

Типичные ошибки

Почему не репликация изменений (CDC) в реальном времени?

Выгрузка по `updated_at` раз в несколько минут закрывает большинство отчётов и не требует прав репликации в PostgreSQL. Если событий много и нужна секундная задержка, публикуйте их из приложения в Managed Kafka и забирайте в ClickHouse интеграцией Kafka.

Можно ли написать адрес и пароль PostgreSQL прямо в `postgresql(...)`?

Нет: табличные функции с адресами пользователям не выдаются. Только интеграции — так пароль не попадает в SQL и журналы запросов, а адрес нельзя подменить.

Словарь не обновился после изменения клиента

Словарь перечитывает таблицу через случайное время между `lifetimeSeconds` и полуторным значением. Для частых изменений уменьшите `lifetimeSeconds` (от 60 секунд): удалите словарь `h3 api DELETE /api/databases/v1/<id ClickHouse>/dictionaries/shop/customers` и создайте заново.

Сколько можно создать словарей и интеграций?

До 50 словарей и до 20 интеграций на базу ClickHouse.

Связанные продукты и материалы

Сколько стоит

≈ 5,58 ₽ в час · 4 014 ₽ в месяц при работе 24/7

vCPU ClickHouse2 × 1 035 ₽
RAM ClickHouse, ГБ4 × 414 ₽
Диск ClickHouse до 20 000 IOPS*, ГБ20 × 14,40 ₽

PostgreSQL и сервер уже есть в проекте и в расчёт не входят. Интеграции и словари отдельно не оплачиваются, трафик между базами внутри VPC бесплатен.

Цены из прайс-листа, по которому работает биллинг; списание поминутное, месяц — 720 часов. Если удалить ресурсы после проверки, сценарий обойдётся в рубли.

Новым пользователям — 4 000 ₽ на 30 дней на любые сервисы: бонуса хватит, чтобы попробовать этот сценарий целиком. Бонус начисляется после подтверждения телефона и привязки карты.

* IOPS растут с объёмом диска; точные значения для вашего объёма видны в консоли при выборе.