[Yandex Cloud documentation](../../index.md) > [Tutorials](../index.md) > [Building a data platform](index.md) > Executing analytical queries in Managed Service for PostgreSQL with processing in Managed Service for ClickHouse® # Executing analytical queries in Yandex Managed Service for PostgreSQL with processing in Yandex Managed Service for ClickHouse® using pg_clickhouse and Yandex Data Transfer # 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](../../managed-postgresql/index.md) using [Yandex Managed Service for ClickHouse®](../../managed-clickhouse/index.md) 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](../../data-transfer/index.md). 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](#infra). 1. [Prepare your test data](#prepare-test-data). 1. [Prepare and activate your transfer](#prepare-transfer). 1. [Test the transfer](#check-transfer). 1. [Configure a connection to Managed Service for ClickHouse® and create external tables](#connect-clickhouse). 1. [Run analytical queries to external tables in Managed Service for PostgreSQL](#execute-queries). If you no longer need the resources you created, [delete them](#clear-out). ## Getting started {#before-you-begin} Sign up for Yandex Cloud and create a [billing account](../../billing/concepts/billing-account.md): 1. Navigate to the [management console](https://console.yandex.cloud) and log in to Yandex Cloud or create a new account. 1. On the **[Yandex Cloud Billing](https://center.yandex.cloud/billing/accounts)** page, make sure you have a billing account linked and it has the `ACTIVE` or `TRIAL_ACTIVE` [status](../../billing/concepts/billing-account-statuses.md). If you do not have a billing account, [create one](../../billing/quickstart/index.md) and [link](../../billing/operations/pin-cloud.md) a cloud to it. If you have an active billing account, you can create or select a [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) for your infrastructure on the [cloud page](https://console.yandex.cloud/cloud). [Learn more about clouds and folders here](../../resource-manager/concepts/resources-hierarchy.md). ### Required paid resources {#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-postgresql/pricing.md)). * Managed Service for ClickHouse® cluster: use of computing resources allocated to hosts, storage and backup size (see [Managed Service for ClickHouse® pricing](../../managed-clickhouse/pricing.md)). * Public IP addresses if public access is enabled for cluster hosts (see [Yandex Virtual Private Cloud pricing](../../vpc/pricing.md)). * Each transfer: use of computing resources and the number of transferred data rows (see [Data Transfer pricing](../../data-transfer/pricing.md)). ## Set up your infrastructure {#infra} {% note info %} Public access to cluster hosts is required if you plan to connect to the cluster via the internet. This connection option is simpler and is recommended for the purposes of this guide. You can connect to non-public hosts as well but only from Yandex Cloud virtual machines located in the same cloud network as the cluster. {% endnote %} 1. [Create a cloud network](../../vpc/operations/network-create.md) named `demo-network`. When creating a network, three subnets in different availability zones are created automatically. 1. In `demo-network`, [create a security group](../../vpc/operations/security-group-create.md) named `mch-sg` for the Managed Service for ClickHouse® cluster and [add](../../vpc/operations/security-group-add-rule.md) 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`. 1. 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`. 1. [Create a Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-create.md) 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`. 1. [Create a Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/cluster-create.md) 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`. 1. In the Managed Service for PostgreSQL cluster, [add](../../managed-postgresql/operations/extensions/cluster-extensions.md#update-extensions) the `pg_clickhouse` extension to the `pgdb` database. 1. In the Managed Service for PostgreSQL cluster, [assign](../../managed-postgresql/operations/grant.md#grant-role) the following roles to the `pguser` user: * [mdb_replication](../../managed-postgresql/concepts/roles.md#mdb-replication): To replicate data using Data Transfer. * [mdb_admin](../../managed-postgresql/concepts/roles.md#mdb-admin): To connect to Managed Service for ClickHouse® via `pg_clickhouse`. ## Prepare your test data {#prepare-test-data} 1. [Connect to the Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/connect/clients.md). 1. Create two tables named `customers` and `orders`: ```sql 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 ); ``` 1. Populate the tables with data: ```sql 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 transfer {#prepare-transfer} 1. [Create a source endpoint](../../data-transfer/operations/endpoint/index.md#create) 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. 1. 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. 1. [Create a transfer](../../data-transfer/operations/transfer.md#create) configured to use the new endpoints. As the transfer [type](../../data-transfer/concepts/index.md#transfer-type), select **Snapshot and replication**. 1. [Activate the transfer](../../data-transfer/operations/transfer.md#activate). 1. Wait for the transfer status to switch to **Replicating**. ## Test the transfer {#check-transfer} 1. [Connect to the Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/connect/clients.md). 1. Make sure the `customers` and `orders` tables have been created in the `chdb` database: ```sql SHOW TABLES FROM chdb; ``` 1. Make sure the tables have been populated with data: ```sql SELECT * FROM customers; SELECT * FROM orders; ``` 1. In the Managed Service for PostgreSQL cluster, add an row to the `orders` table: ```sql INSERT INTO public.orders (id, customer_id, amount, order_date, status) VALUES (11, 1, 520.00, '2024-03-17', 'new'); ``` 1. Run this query in the Managed Service for ClickHouse® cluster to make sure the new row has been added to the `orders` table: ```sql SELECT * FROM orders; ``` ## Configure a connection to Managed Service for ClickHouse® and create external tables {#connect-clickhouse} 1. [Connect to the Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/connect/clients.md). 1. Create an external data source: ```sql CREATE SERVER chserver FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS ( driver 'binary', host 'c-.rw.mdb.yandexcloud.net', port '9440', dbname 'chdb' ); ``` You can get the cluster ID with the [list of clusters in the folder](../../managed-clickhouse/operations/cluster-list.md#list-clusters). 1. Create a mapping between a local user and a user in the external data source: ```sql CREATE USER MAPPING FOR CURRENT_USER SERVER chserver OPTIONS ( user 'chuser', password '' ); ``` 1. Create a schema: ```sql CREATE SCHEMA mch; ``` 1. In your `mch` schema, create external tables with references to tables in the `chdb` database in Managed Service for ClickHouse®: ```sql IMPORT FOREIGN SCHEMA chdb FROM SERVER chserver INTO mch; ``` 1. Make sure the external tables have been successfully created: ```sql 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 PostgreSQL {#execute-queries} 1. For each city, get the number of orders and their total amount: ```sql 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; ``` 1. Build a query execution plan to make sure it is being processed in Managed Service for ClickHouse®: ```sql 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: ```text 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 created {#clear-out} Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them: 1. [Deactivate](../../data-transfer/operations/transfer.md#deactivate) and [delete](../../data-transfer/operations/transfer.md#delete) the transfer. 1. [Delete](../../data-transfer/operations/endpoint/index.md#delete) the source and target endpoints. 1. [Delete the Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-delete.md). 1. [Delete the Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/cluster-delete.md). _ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._