Выполнение аналитических запросов в Yandex Managed Service for PostgreSQL с обработкой в Yandex Managed Service for ClickHouse® при помощи pg_clickhouse и Yandex Data Transfer

Вы можете выполнять аналитические запросы к данным из Yandex Managed Service for PostgreSQL, используя для обработки запросов вычислительные ресурсы Yandex Managed Service for ClickHouse®. Для этого в Managed Service for ClickHouse® с помощью Yandex Data Transfer создается копия данных из Managed Service for PostgreSQL, которая затем поддерживается в актуальном состоянии. В базу данных Managed Service for PostgreSQL добавляется расширение pg_clickhouse. С его помощью в Managed Service for PostgreSQL создаются внешние таблицы, которые ссылаются на таблицы в Managed Service for ClickHouse®. Работать с внешними таблицами можно так же, как с обычными таблицами. Аналитические запросы к внешним таблицам выполняются в Managed Service for PostgreSQL и обрабатываются на стороне Managed Service for ClickHouse®, после чего в Managed Service for PostgreSQL возвращается результат этих запросов.

Расширение pg_clickhouse доступно в кластерах Managed Service for PostgreSQL версии 17 и выше.

Чтобы выполнить аналитические запросы:

1. Подготовьте инфраструктуру.
2. Подготовьте тестовые данные.
3. Подготовьте и активируйте трансфер.
4. Проверьте работу трансфера.
5. Настройте подключение к Managed Service for ClickHouse® и создайте внешние таблицы.
6. Выполните аналитические запросы к внешним таблицам в Managed Service for PostgreSQL.

Если созданные ресурсы вам больше не нужны, удалите их.

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

Зарегистрируйтесь в Yandex Cloud и создайте платежный аккаунт:

1. Перейдите в консоль управления, затем войдите в Yandex Cloud или зарегистрируйтесь.
2. На странице Yandex Cloud Billing убедитесь, что у вас подключен платежный аккаунт, и он находится в статусе ACTIVE или TRIAL_ACTIVE. Если платежного аккаунта нет, создайте его и привяжите к нему облако.

Если у вас есть активный платежный аккаунт, вы можете создать или выбрать каталог, в котором будет работать ваша инфраструктура, на странице облака.

Подробнее об облаках и каталогах.

Необходимые платные ресурсыНеобходимые платные ресурсы

• Кластер Managed Service for PostgreSQL: использование выделенных хостам вычислительных ресурсов, объем хранилища и резервных копий (тарифы Managed Service for PostgreSQL).
• Кластер Managed Service for ClickHouse®: использование выделенных хостам вычислительных ресурсов, объем хранилища и резервных копий (тарифы Managed Service for ClickHouse®).
• Публичные IP-адреса, если для хостов кластеров включен публичный доступ (тарифы Yandex Virtual Private Cloud).
• Каждый трансфер: использование вычислительных ресурсов и количество переданных строк данных (тарифы Data Transfer).

Подготовьте инфраструктуруПодготовьте инфраструктуру

1. Создайте облачную сеть с именем demo-network.

   При создании сети автоматически создаются три подсети в разных зонах доступности.

2. В сети demo-network создайте группу безопасности mch-sg для кластера Managed Service for ClickHouse® и добавьте в группу правила, необходимые для подключения к кластеру через интернет:

   • Правило для входящего трафика, которое разрешает подключение на порт 8443:

     • Диапазон портов — 8443.
     • Протокол — TCP.
     • Назначение — Диапазон адресов.
     • IPv4 CIDR — 0.0.0.0/0.

   • Правило для входящего трафика, которое разрешает подключение на порт 9440:

     • Диапазон портов — 9440.
     • Протокол — TCP.
     • Назначение — Диапазон адресов.
     • IPv4 CIDR — 0.0.0.0/0.

3. В сети demo-network создайте группу безопасности mpg-sg для кластера Managed Service for PostgreSQL и добавьте в группу следующие правила:

   • Правило для входящего трафика, которое разрешает подключение к кластеру через интернет:

     • Диапазон портов — 6432.
     • Протокол — TCP.
     • Назначение — Диапазон адресов.
     • IPv4 CIDR — 0.0.0.0/0.

   • Правило для исходящего трафика, которое разрешает подключение к Managed Service for ClickHouse®:

     • Диапазон портов — 9440.
     • Протокол — TCP.
     • Назначение — Диапазон адресов.
     • IPv4 CIDR — 0.0.0.0/0.

4. Создайте кластер Managed Service for ClickHouse® любой подходящей конфигурации со следующими настройками:

   • Сеть — demo-network.

   • Группа безопасности — mch-sg.

   • Публичный доступ к хостам включен.

   • База данных — chdb.

   • Пользователь — chuser.

5. Создайте кластер Managed Service for PostgreSQL любой подходящей конфигурации со следующими настройками:

   • Версия — 17 или выше.

   • Сеть — demo-network.

   • Группа безопасности — mpg-sg.

   • Публичный доступ к хостам включен.

   • База данных — pgdb.

   • Пользователь — pguser.

6. В кластере Managed Service for PostgreSQL добавьте расширение pg_clickhouse в базу данных pgdb.

7. В кластере Managed Service for PostgreSQL назначьте пользователю pguser следующие роли:

   • mdb_replication — для репликации данных с помощью Data Transfer.
   • mdb_admin — для подключения к Managed Service for ClickHouse® через pg_clickhouse.

Подготовьте тестовые данныеПодготовьте тестовые данные

1. Подключитесь к кластеру Managed Service for PostgreSQL.

2. Создайте таблицы customers и orders:

   CREATE TABLE public.customers (
     id INT PRIMARY KEY,
     name TEXT NOT NULL,
     city TEXT
   );
   
   CREATE TABLE public.orders (
     id INT PRIMARY KEY,
     customer_id INT NOT NULL,
     amount NUMERIC(10, 2) NOT NULL,
     order_date DATE NOT NULL,
     status TEXT NOT NULL
   );
   

3. Заполните таблицы данными:

   INSERT INTO public.customers (id, name, city) VALUES
   (1, 'Анна', 'Волгоград'),
   (2, 'Иван', 'Новосибирск'),
   (3, 'Виктория', 'Воронеж'),
   (4, 'Борис', 'Краснодар'),
   (5, 'Мария', 'Нижний Новгород');
   
   INSERT INTO public.orders (id, customer_id, amount, order_date, status) VALUES
   (1, 1, 1500.00, '2024-03-01', 'new'),
   (2, 2, 2300.50, '2024-03-02', 'new'),
   (3, 1, 999.99, '2024-03-03', 'completed'),
   (4, 3, 4500.00, '2024-03-04', 'shipped'),
   (5, 4, 1200.75, '2024-03-05', 'new'),
   (6, 5, 3100.25, '2024-03-06', 'shipped'),
   (7, 2, 1750.00, '2024-03-07', 'completed'),
   (8, 3, 800.00, '2024-03-08', 'new'),
   (9, 4, 5500.99, '2024-03-09', 'shipped'),
   (10, 5, 2200.00, '2024-03-10', 'completed');
   

Подготовьте и активируйте трансферПодготовьте и активируйте трансфер

1. Создайте эндпоинт для источника со следующими настройками:

   • Тип базы данных — PostgreSQL.
   • Тип подключения — Ручная настройка.
   • Тип инсталляции — Кластер Managed Service for PostgreSQL.
   • Кластер управляемой БД — имя созданного ранее кластера Managed Service for PostgreSQL.
   • База данных — pgdb.
   • Пользователь — pguser.
   • Пароль — пароль пользователя pguser.

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

   • Тип базы данных — ClickHouse.
   • Тип подключения — Ручная настройка.
   • Тип инсталляции — Кластер Managed Service for ClickHouse.
   • Managed кластер — имя созданного ранее кластера Managed Service for ClickHouse®.
   • База данных — chdb.
   • Пользователь — chuser.
   • Пароль — пароль пользователя chuser.

3. Создайте трансфер, использующий созданные эндпоинты. В качестве типа трансфера выберите Копирование и репликация.

4. Активируйте трансфер.

5. Дождитесь, пока трансфер перейдет в статус Реплицируется.

Проверьте работу трансфераПроверьте работу трансфера

1. Подключитесь к кластеру Managed Service for ClickHouse®.

2. Проверьте, что в базе данных chdb созданы таблицы customers и orders:

   SHOW TABLES FROM chdb;
   

3. Проверьте, что данные загружены в таблицы:

   SELECT * FROM customers;
       
   SELECT * FROM orders;
   

4. В кластере Managed Service for PostgreSQL добавьте запись в таблицу orders:

   INSERT INTO public.orders (id, customer_id, amount, order_date, status) VALUES
   (11, 1, 520.00, '2024-03-17', 'new');
   

5. Выполните запрос в кластере Managed Service for ClickHouse®, чтобы проверить, что новая запись появилась в таблице orders:

   SELECT * FROM orders;
   

Настройте подключение к Managed Service for ClickHouse® и создайте внешние таблицыНастройте подключение к Managed Service for ClickHouse® и создайте внешние таблицы

1. Подключитесь к кластеру Managed Service for PostgreSQL.

2. Создайте внешний источник данных:

   CREATE SERVER chserver
   FOREIGN DATA WRAPPER clickhouse_fdw
   OPTIONS (
     driver 'binary',
     host 'c-<идентификатор_кластера_Managed_Service_for_Clickhouse>.rw.mdb.yandexcloud.net',
     port '9440',
     dbname 'chdb'
   );
   

   Идентификатор кластера можно получить со списком кластеров в каталоге.

3. Создайте сопоставление локального пользователя с пользователем на внешнем источнике:

   CREATE USER MAPPING FOR CURRENT_USER
   SERVER chserver
   OPTIONS (
     user 'chuser',
     password '<пароль>'
   );
   

4. Создайте схему:

   CREATE SCHEMA mch;
   

5. Создайте в схеме mch внешние таблицы, которые будут ссылаться на таблицы в базе данных chdb в Managed Service for ClickHouse®:

   IMPORT FOREIGN SCHEMA chdb
   FROM SERVER chserver
   INTO mch;
   

6. Проверьте, что внешние таблицы созданы:

   SELECT *
   FROM information_schema.foreign_tables
   WHERE foreign_table_schema = 'mch';
   

   В выводе должны быть таблицы customers и orders.

Выполните аналитические запросы к внешним таблицам в Managed Service for PostgreSQLВыполните аналитические запросы к внешним таблицам в Managed Service for PostgreSQL

1. Получите для каждого города количество заказов и их общую сумму:

   SELECT
     c.city,
     COUNT(*) AS orders_count,
     SUM(o.amount) AS total_amount
   FROM mch.orders o
   JOIN mch.customers c
     ON o.customer_id = c.id
   GROUP BY c.city
   ORDER BY total_amount DESC;
   

2. Постройте план выполнения запроса, чтобы убедиться, что он обрабатывается в Managed Service for ClickHouse®:

   EXPLAIN (VERBOSE, COSTS OFF)
   SELECT
     c.city,
     COUNT(*) AS orders_count,
     SUM(o.amount) AS total_amount
   FROM mch.orders o
   JOIN mch.customers c
     ON o.customer_id = c.id
   GROUP BY c.city
   ORDER BY total_amount DESC;
   

   Если в плане выполнения есть строки Foreign Scan и Remote SQL, то запрос обрабатывается в Managed Service for ClickHouse®.

   Пример плана выполнения:

   QUERY PLAN
   ----------
   Foreign Scan
     Output: c.city, (count(*)), (sum(o.amount))
     Relations: Aggregate on ((orders o) INNER JOIN (customers c))
     Remote SQL: SELECT r2.city, count(*), sum(r1.amount) FROM chdb.orders r1 ALL INNER JOIN chdb.customers r2 ON (((r1.customer_id = r2.id))) GROUP BY r2.city ORDER BY sum(r1.amount) DESC NULLS FIRST
   Query Identifier: -6142969501942783
   

Удалите созданные ресурсыУдалите созданные ресурсы

Некоторые ресурсы платные. Чтобы за них не списывалась плата, удалите ресурсы, которые вы больше не будете использовать:

1. Деактивируйте и удалите трансфер.
2. Удалите эндпоинты для источника и приемника.
3. Удалите кластер Managed Service for ClickHouse®.
4. Удалите кластер Managed Service for PostgreSQL.

ClickHouse® является зарегистрированным товарным знаком ClickHouse, Inc.