Что получится
Интеграция shop_pg от пользователя PostgreSQL только на чтение, таблица shop.orders в ClickHouse с инкрементальной загрузкой по расписанию, словарь shop.customers с обновлением раз в 5 минут и отчёт по выручке в разрезе сегментов клиентов, который не нагружает PostgreSQL.
Что понадобится
- Managed PostgreSQL с базой приложения
shop— например, из сценария «Managed PostgreSQL для приложения». - Сервер в той же сети проекта для SQL-клиентов и задания по расписанию.
- CLI
h3с API-ключом проекта (h3 init).
-
Шаг 1. Подготовьте данные и пользователя в PostgreSQL
ClickHouse будет читать PostgreSQL от отдельного пользователя только на чтение. Для примера создадим в базе
shopдве таблицы — клиентов и заказов. Колонкаupdated_atнужна для инкрементальной загрузки.Консоль
- В карточке PostgreSQL в строке «Пользователи и базы» нажмите Открыть.
- В разделе «Пользователи» нажмите Создать пользователя. В окне «Новый пользователь»: Имя пользователя
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. Создайте ClickHouse в той же сети
Интеграция работает только между базами одного проекта и одной VPC: ClickHouse ходит в PostgreSQL по его адресу в VPC.
Консоль
- Базы данных → Создать. СУБД — ClickHouse 26.8. vCPU — 2, RAM — 4 ГБ, Диски — 20 ГБ.
- Имя базы данных —
shop-analytics. Сеть — та же, что у PostgreSQL. Нажмите Создать базу данных.
CLI
Терминалh3 db create --name shop-analytics --engine clickhouse --version 26.8 --cpu 2 --memory 4Gi --size 20Проверка
Обе базы в статусе «Работает». В блоке «Подключение» каждой базы на вкладке «Внутри VPC» есть Адрес в VPC.
-
Шаг 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. Запросите таблицы 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. Скопируйте заказы в ClickHouse и догружайте изменения
Копия в MergeTree отвечает на аналитические запросы за миллисекунды и не трогает PostgreSQL.
ReplacingMergeTree(updated_at)хранит последнюю версию строки: повторная загрузка изменённого заказа не создаёт дубль в итогах сFINAL.orders.sqlCREATE 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.orders100000 строк, а через 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. Подключите справочник клиентов как словарь
Справочники (клиенты, товары, тарифы) удобнее держать словарём: 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. Постройте отчёт
Выручка по сегментам клиентов за 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 - Уберите задание:
crontab -l | grep -v orders-sync | crontab -. - Базы данных →
shop-analytics→ меню «…» → Удалить. - В 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.
Связанные продукты и материалы
- Сценарий · 35 минManaged PostgreSQL для приложения: пользователь, pgvector и восстановление на момент времени
- Сценарий · 45 минСайт на облачном сервере: публичный IP, домен и HTTPS
- Сценарий · 40 минПриложение в Managed Kubernetes из своего реестра образов
- Сценарий · 25 минРезервные копии сервера в S3 по расписанию: rclone, cron и ротация
- ПродуктPostgreSQL
- ПродуктClickHouse
Сколько стоит
≈ 5,58 ₽ в час · 4 014 ₽ в месяц при работе 24/7
| vCPU ClickHouse | 2 × 1 035 ₽ |
| RAM ClickHouse, ГБ | 4 × 414 ₽ |
| Диск ClickHouse до 20 000 IOPS*, ГБ | 20 × 14,40 ₽ |
PostgreSQL и сервер уже есть в проекте и в расчёт не входят. Интеграции и словари отдельно не оплачиваются, трафик между базами внутри VPC бесплатен.
Цены из прайс-листа, по которому работает биллинг; списание поминутное, месяц — 720 часов. Если удалить ресурсы после проверки, сценарий обойдётся в рубли.
Новым пользователям — 4 000 ₽ на 30 дней на любые сервисы: бонуса хватит, чтобы попробовать этот сценарий целиком. Бонус начисляется после подтверждения телефона и привязки карты.