Executing analytical queries in Yandex Managed Service for PostgreSQL with processing in Yandex Managed Service for ClickHouse® using pg_clickhouse and Yandex Data Transfer

You can run analytical queries to data from Yandex Managed Service for PostgreSQL using Yandex Managed Service for ClickHouse® computing resources for query processing. To do this, the Managed Service for PostgreSQL data is copied to Managed Service for ClickHouse® using Yandex Data Transfer. The data copied is then kept up-to-date. The pg_clickhouse extension is added to the Managed Service for PostgreSQL database. Using this extension, external tables are created in Managed Service for PostgreSQL with references to tables in Managed Service for ClickHouse®. You can work with external tables the same way as with ordinary tables. Analytical queries to external tables are run in Managed Service for PostgreSQL and processed on the Managed Service for ClickHouse® side. After that, the result of these queries is returned to Managed Service for PostgreSQL.

The pg_clickhouse extension is available in Managed Service for PostgreSQL clusters of version 17 and higher.

To run analytical queries:

1. Set up your infrastructure.
2. Prepare your test data.
3. Prepare and activate your transfer.
4. Test the transfer.
5. Configure a connection to Managed Service for ClickHouse® and create external tables.
6. Run analytical queries to external tables in Managed Service for PostgreSQL.

If you no longer need the resources you created, delete them.

Getting startedGetting started

Sign up for Yandex Cloud and create a billing account:

1. Navigate to the management console and log in to Yandex Cloud or create a new account.
2. On the Yandex Cloud Billing page, make sure you have a billing account linked and it has the ACTIVE or TRIAL_ACTIVE status. If you do not have a billing account, create one and link a cloud to it.

If you have an active billing account, you can create or select a folder for your infrastructure on the cloud page.

Learn more about clouds and folders here.

Required paid resourcesRequired paid resources

• Managed Service for PostgreSQL cluster: use of computing resources allocated to hosts, storage and backup size (see Managed Service for PostgreSQL pricing).
• Managed Service for ClickHouse® cluster: use of computing resources allocated to hosts, storage and backup size (see Managed Service for ClickHouse® pricing).
• Public IP addresses if public access is enabled for cluster hosts (see Yandex Virtual Private Cloud pricing).
• Each transfer: use of computing resources and the number of transferred data rows (see Data Transfer pricing).

Set up your infrastructureSet up your infrastructure

1. Create a cloud network named demo-network.

   When creating a network, three subnets in different availability zones are created automatically.

2. In demo-network, create a security group named mch-sg for the Managed Service for ClickHouse® cluster and add to the group the following rules required to connect to the cluster over the internet:

   • Rule for incoming traffic allowing connections on port 8443:

     • Port range: 8443.
     • Protocol: TCP.
     • Destination name: Address range.
     • IPv4 CIDR: 0.0.0.0/0.

   • Rule for incoming traffic allowing connections on port 9440:

     • Port range: 9440.
     • Protocol: TCP.
     • Destination name: Address range.
     • IPv4 CIDR: 0.0.0.0/0.

3. In demo-network, create a security group named mpg-sg for the Managed Service for PostgreSQL cluster and add to the group the following rules:

   • Rule for incoming traffic allowing connections to the cluster over the internet:

     • Port range: 6432.
     • Protocol: TCP.
     • Destination name: Address range.
     • IPv4 CIDR: 0.0.0.0/0.

   • Rule for outgoing traffic allowing connections to Managed Service for ClickHouse®:

     • Port range: 9440.
     • Protocol: TCP.
     • Destination name: Address range.
     • IPv4 CIDR: 0.0.0.0/0.

4. Create a Managed Service for ClickHouse® cluster in any suitable configuration with the following settings:

   • Network: demo-network.

   • Security group: mch-sg.

   • Public access to hosts is enabled.

   • Database: chdb.

   • Username: chuser.

5. Create a Managed Service for PostgreSQL cluster in any suitable configuration with the following settings:

   • Version: 17 or higher.

   • Network: demo-network.

   • Security group: mpg-sg.

   • Public access to hosts is enabled.

   • Database: pgdb.

   • Username: pguser.

6. In the Managed Service for PostgreSQL cluster, add the pg_clickhouse extension to the pgdb database.

7. In the Managed Service for PostgreSQL cluster, assign the following roles to the pguser user:

   • mdb_replication: To replicate data using Data Transfer.
   • mdb_admin: To connect to Managed Service for ClickHouse® via pg_clickhouse.

Prepare your test dataPrepare your test data

1. Connect to the Managed Service for PostgreSQL cluster.

2. Create two tables named customers and 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. Populate the tables with data:

   INSERT INTO public.customers (id, name, city) VALUES
   (1, 'Anna', 'Volgograd'),
   (2, 'Ivan', 'Novosibirsk'),
   (3, 'Victoria', 'Voronezh'),
   (4, 'Boris', 'Krasnodar'),
   (5, 'Maria', 'Nizhny Novgorod');
   
   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');
   

Prepare and activate a transferPrepare and activate a transfer

1. Create a source endpoint with the following settings:

   • Database type: PostgreSQL.
   • Connection type: Self-managed.
   • Installation type: Managed Service for PostgreSQL cluster.
   • Managed database cluster: Name of the previously created Managed Service for PostgreSQL cluster.
   • Database: pgdb.
   • User: pguser.
   • Password: pguser password.

2. Create a target endpoint with the following settings:

   • Database type: ClickHouse.
   • Connection type: Self-managed.
   • Installation type: Managed cluster.
   • Managed cluster: Name of the previously created Managed Service for ClickHouse® cluster.
   • Database: chdb.
   • User: chuser.
   • Password: chuser password.

3. Create a transfer configured to use the new endpoints. As the transfer type, select Snapshot and replication.

4. Activate the transfer.

5. Wait for the transfer status to switch to Replicating.

Test the transferTest the transfer

1. Connect to the Managed Service for ClickHouse® cluster.

2. Make sure the customers and orders tables have been created in the chdb database:

   SHOW TABLES FROM chdb;
   

3. Make sure the tables have been populated with data:

   SELECT * FROM customers;
       
   SELECT * FROM orders;
   

4. In the Managed Service for PostgreSQL cluster, add an row to the orders table:

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

5. Run this query in the Managed Service for ClickHouse® cluster to make sure the new row has been added to the orders table:

   SELECT * FROM orders;
   

Configure a connection to Managed Service for ClickHouse® and create external tablesConfigure a connection to Managed Service for ClickHouse® and create external tables

1. Connect to the Managed Service for PostgreSQL cluster.

2. Create an external data source:

   CREATE SERVER chserver
   FOREIGN DATA WRAPPER clickhouse_fdw
   OPTIONS (
     driver 'binary',
     host 'c-<ID_of_Managed_Service_for_Clickhouse_cluster>.rw.mdb.yandexcloud.net',
     port '9440',
     dbname 'chdb'
   );
   

   You can get the cluster ID with the list of clusters in the folder.

3. Create a mapping between a local user and a user in the external data source:

   CREATE USER MAPPING FOR CURRENT_USER
   SERVER chserver
   OPTIONS (
     user 'chuser',
     password '<password>'
   );
   

4. Create a schema:

   CREATE SCHEMA mch;
   

5. In your mch schema, create external tables with references to tables in the chdb database in Managed Service for ClickHouse®:

   IMPORT FOREIGN SCHEMA chdb
   FROM SERVER chserver
   INTO mch;
   

6. Make sure the external tables have been successfully created:

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

   The output must contain the customers and orders tables.

Run analytical queries to external tables in Managed Service for PostgreSQLRun analytical queries to external tables in Managed Service for PostgreSQL

1. For each city, get the number of orders and their total amount:

   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. Build a query execution plan to make sure it is being processed in 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;
   

   If the query execution plan contains Foreign Scan or Remote SQL, it means that the query is being processed in Managed Service for ClickHouse®.

   Example of a query execution plan:

   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
   

Delete the resources you createdDelete the resources you created

Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them:

1. Deactivate and delete the transfer.
2. Delete the source and target endpoints.
3. Delete the Managed Service for ClickHouse® cluster.
4. Delete the Managed Service for PostgreSQL cluster.

ClickHouse® is a registered trademark of ClickHouse, Inc.