Справочная документация по pg_clickhouse

Описание

pg_clickhouse — это расширение PostgreSQL, которое позволяет выполнять удалённые запросы к базам данных ClickHouse, включая [обёртку внешних данных (foreign data wrapper)]. Оно поддерживает PostgreSQL 13 и новее и ClickHouse 23 и новее.

Начало работы

Самый простой способ попробовать pg_clickhouse — использовать Docker image, который представляет собой стандартный Docker-образ PostgreSQL с расширением pg_clickhouse:

docker run --name pg_clickhouse -e POSTGRES_PASSWORD=my_pass \
       -d ghcr.io/clickhouse/pg_clickhouse:18
docker exec -it pg_clickhouse psql -U postgres

См. руководство, чтобы начать импортировать таблицы ClickHouse и настроить проброс запросов в ClickHouse.

Использование

CREATE EXTENSION pg_clickhouse;
CREATE SERVER taxi_srv FOREIGN DATA WRAPPER clickhouse_fdw
       OPTIONS(driver 'binary', host 'localhost', dbname 'taxi');
CREATE USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (user 'default');
CREATE SCHEMA taxi;
IMPORT FOREIGN SCHEMA taxi FROM SERVER taxi_srv INTO taxi;

Политика версионирования

pg_clickhouse следует Semantic Versioning для своих публичных релизов.

• Основная (major) версия увеличивается при изменениях API
• Минорная (minor) версия увеличивается при обратно совместимых изменениях SQL
• Патч-версия (patch) увеличивается при изменениях, касающихся только бинарных файлов

После установки PostgreSQL отслеживает два варианта версии:

• Версия библиотеки (определяется PG_MODULE_MAGIC в PostgreSQL 18 и выше) включает полную семантическую версию, которая видна в выводе функции pg_get_loaded_modules().
• Версия расширения (определяется в control-файле) включает только основную и минорную версии, видна в таблице pg_catalog.pg_extension, в выводе функции pg_available_extension_versions() и в \dx pg_clickhouse.

На практике это означает, что релиз, в котором увеличивается патч-версия, например с v0.1.0 до v0.1.1, применяется ко всем базам данных, которые загрузили v0.1, и им не нужно выполнять ALTER EXTENSION, чтобы получить преимущества обновления.

Релиз, в котором увеличивается минорная или основная версия, напротив, будет сопровождаться SQL-скриптами обновления, и все существующие базы данных, содержащие расширение, должны выполнить ALTER EXTENSION pg_clickhouse UPDATE, чтобы получить преимущества обновления.

Справочник по SQL

В следующих SQL-примерах используется pg_clickhouse.

CREATE EXTENSION

Используйте CREATE EXTENSION, чтобы добавить pg_clickhouse в базу данных:

CREATE EXTENSION pg_clickhouse;

Используйте WITH SCHEMA, чтобы установить расширение в определённую схему (рекомендуется):

CREATE SCHEMA ch;
CREATE EXTENSION pg_clickhouse WITH SCHEMA ch;

ALTER EXTENSION

Используйте ALTER EXTENSION, чтобы изменить расширение pg_clickhouse. Примеры:

• После установки нового релиза pg_clickhouse используйте предложение UPDATE:

  ALTER EXTENSION pg_clickhouse UPDATE;
  

• Используйте SET SCHEMA, чтобы перенести расширение в новую схему:

  CREATE SCHEMA ch;
  ALTER EXTENSION pg_clickhouse SET SCHEMA ch;
  

DROP EXTENSION

Используйте DROP EXTENSION, чтобы удалить расширение pg_clickhouse из базы данных:

DROP EXTENSION pg_clickhouse;

Эта команда завершится ошибкой, если существуют какие‑либо объекты, зависящие от pg_clickhouse. Используйте предложение CASCADE, чтобы удалить их также:

DROP EXTENSION pg_clickhouse CASCADE;

CREATE SERVER

Используйте CREATE SERVER, чтобы создать удалённый сервер, который подключается к серверу ClickHouse. Пример:

CREATE SERVER taxi_srv FOREIGN DATA WRAPPER clickhouse_fdw
       OPTIONS(driver 'binary', host 'localhost', dbname 'taxi');

Поддерживаемые параметры:

• driver: драйвер подключения к ClickHouse — либо "binary", либо "http". Обязательный параметр.
• dbname: база данных ClickHouse, используемая при подключении. По умолчанию — "default".
• host: имя хоста сервера ClickHouse. По умолчанию "localhost".
• port: порт для подключения к серверу ClickHouse. Значения по умолчанию:
  • 9440, если driver — "binary" и host — хост ClickHouse Cloud
  • 9004, если driver — "binary" и host не является хостом ClickHouse Cloud
  • 8443, если driver — "http" и host — хост ClickHouse Cloud
  • 8123, если driver — "http" и host не является хостом ClickHouse Cloud

ALTER SERVER

Используйте ALTER SERVER для изменения внешнего сервера. Например:

ALTER SERVER taxi_srv OPTIONS (SET driver 'http');

Параметры те же, что и у CREATE SERVER.

DROP SERVER

Используйте DROP SERVER для удаления внешнего сервера:

DROP SERVER taxi_srv;

Эта команда приведёт к ошибке, если от сервера зависят какие-либо другие объекты. Используйте CASCADE, чтобы также удалить эти зависимости:

DROP SERVER taxi_srv CASCADE;

CREATE USER MAPPING

Используйте CREATE USER MAPPING, чтобы сопоставить пользователя PostgreSQL с пользователем ClickHouse. Например, чтобы сопоставить текущего пользователя PostgreSQL с удалённым пользователем ClickHouse при подключении к внешнему серверу taxi_srv:

CREATE USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (user 'demo');

Поддерживаемые параметры:

• user: Имя пользователя ClickHouse. Значение по умолчанию — "default".
• password: Пароль пользователя ClickHouse.

ALTER USER MAPPING

Используйте ALTER USER MAPPING, чтобы изменить определение отображения пользователя:

ALTER USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (SET user 'default');

Параметры те же, что и для CREATE USER MAPPING.

DROP USER MAPPING

Используйте DROP USER MAPPING, чтобы удалить отображение пользователя:

DROP USER MAPPING FOR CURRENT_USER SERVER taxi_srv;

IMPORT FOREIGN SCHEMA

Используйте оператор IMPORT FOREIGN SCHEMA, чтобы импортировать все таблицы, определённые в базе данных ClickHouse как внешние таблицы в схему PostgreSQL:

CREATE SCHEMA taxi;
IMPORT FOREIGN SCHEMA demo FROM SERVER taxi_srv INTO taxi;

Используйте LIMIT TO, чтобы выполнять импорт только для определённых таблиц:

IMPORT FOREIGN SCHEMA demo LIMIT TO (trips) FROM SERVER taxi_srv INTO taxi;

Используйте EXCEPT, чтобы исключить таблицы:

IMPORT FOREIGN SCHEMA demo EXCEPT (users) FROM SERVER taxi_srv INTO taxi;

pg_clickhouse получит список всех таблиц в указанной базе данных ClickHouse («demo» в приведённых выше примерах), извлечёт определения столбцов для каждой из них и выполнит команды CREATE FOREIGN TABLE для создания внешних таблиц. Столбцы будут определены с использованием поддерживаемых типов данных и, где это можно обнаружить, параметров, поддерживаемых CREATE FOREIGN TABLE.

CREATE FOREIGN TABLE

Используйте IMPORT FOREIGN SCHEMA, чтобы создать внешнюю таблицу, которая позволяет выполнять запросы к данным в базе данных ClickHouse:

CREATE FOREIGN TABLE uact (
    user_id    bigint NOT NULL,
    page_views int,
    duration   smallint,
    sign       smallint
) SERVER taxi_srv OPTIONS(
    table_name 'uact'
    engine 'CollapsingMergeTree'
);

Поддерживаемые параметры таблицы:

• database: Имя удалённой базы данных. По умолчанию используется база данных, определённая для внешнего сервера.
• table_name: Имя удалённой таблицы. По умолчанию используется имя, указанное для внешней таблицы.
• engine: [Движок таблицы], используемый таблицей ClickHouse. Для CollapsingMergeTree() и AggregatingMergeTree() pg_clickhouse автоматически применяет параметры к функциональным выражениям, выполняемым над таблицей.

Используйте тип данных, соответствующий удалённому типу данных ClickHouse каждого столбца. Для столбцов типа AggregateFunction Type и SimpleAggregateFunction Type сопоставьте тип данных с типом ClickHouse, передаваемым функции, и укажите имя агрегатной функции через соответствующий параметр столбца:

• AggregateFunction: Имя агрегатной функции, применяемой к столбцу типа AggregateFunction Type
• SimpleAggregateFunction: Имя агрегатной функции, применяемой к столбцу типа SimpleAggregateFunction Type

Пример:

(aggregatefunction 'sum')

CREATE FOREIGN TABLE test (
    column1 bigint  OPTIONS(AggregateFunction 'uniq'),
    column2 integer OPTIONS(AggregateFunction 'anyIf'),
    column3 bigint  OPTIONS(AggregateFunction 'quantiles(0.5, 0.9)')
) SERVER clickhouse_srv;

Для столбцов типа AggregateFunction pg_clickhouse автоматически добавит Merge к агрегатной функции, вычисляющей значение столбца.

ALTER FOREIGN TABLE

Используйте оператор [ALTER FOREIGN TABLE], чтобы изменить определение внешней таблицы:

ALTER TABLE table ALTER COLUMN b OPTIONS (SET AggregateFunction 'count');

Поддерживаемые параметры таблиц и столбцов совпадают с параметрами для [CREATE FOREIGN TABLE].

DROP FOREIGN TABLE

Используйте [DROP FOREIGN TABLE], чтобы удалить внешнюю таблицу:

DROP FOREIGN TABLE uact;

Эта команда завершится с ошибкой, если существуют какие-либо объекты, зависящие от внешней таблицы. Используйте опцию CASCADE, чтобы удалить и их также:

DROP FOREIGN TABLE uact CASCADE;

Справочник функций и операторов

Типы данных

pg_clickhouse сопоставляет следующие типы данных ClickHouse с типами данных PostgreSQL:

ClickHouse	PostgreSQL	Примечания
Bool	boolean
Date	date
DateTime	timestamp
Decimal	numeric
Float32	real
Float64	double precision
IPv4	inet
IPv6	inet
Int16	smallint
Int32	integer
Int64	bigint
Int8	smallint
JSON	jsonb	Только для HTTP-движка
String	text
UInt16	integer
UInt32	bigint
UInt64	bigint	Вызывает ошибку для значений > максимального BIGINT
UInt8	smallint
UUID	uuid

Функции

Эти функции предоставляют интерфейс для выполнения запросов к базе данных ClickHouse.

clickhouse_raw_query

SELECT clickhouse_raw_query(
    'CREATE TABLE t1 (x String) ENGINE = Memory',
    'host=localhost port=8123'
);

Подключается к сервису ClickHouse через его HTTP-интерфейс, выполняет один запрос и отключается. Необязательный второй аргумент задаёт строку подключения, по умолчанию используется host=localhost port=8123. Поддерживаются следующие параметры подключения:

• host: Хост для подключения; обязательный параметр.
• port: HTTP-порт для подключения; по умолчанию 8123, если host не является хостом ClickHouse Cloud; в противном случае по умолчанию 8443.
• dbname: Имя базы данных, к которой требуется подключиться.
• username: Имя пользователя, под которым выполняется подключение; по умолчанию default.
• password: Пароль, используемый для аутентификации; по умолчанию пароль отсутствует.

Полезно для запросов, которые не возвращают записей; результаты запросов, которые возвращают значения, будут возвращены как одно текстовое значение:

SELECT clickhouse_raw_query(
    'SELECT schema_name, schema_owner from information_schema.schemata',
    'host=localhost port=8123'
);

      clickhouse_raw_query       
---------------------------------
 INFORMATION_SCHEMA      default+
 default default                +
 git     default                +
 information_schema      default+
 system  default                +
 
(1 row)

Функции pushdown

Все встроенные функции PostgreSQL, используемые в условных выражениях (в конструкциях HAVING и WHERE) при выполнении запросов к внешним таблицам ClickHouse, автоматически передаются на исполнение (pushdown) в ClickHouse с теми же именами и сигнатурами. Однако у некоторых функции имена или сигнатуры отличаются, и их необходимо сопоставить с эквивалентами. pg_clickhouse сопоставляет следующие функции:

• date_part:
  • date_part('day'): toDayOfMonth
  • date_part('doy'): toDayOfYear
  • date_part('dow'): toDayOfWeek
  • date_part('year'): toYear
  • date_part('month'): toMonth
  • date_part('hour'): toHour
  • date_part('minute'): toMinute
  • date_part('second'): toSecond
  • date_part('quarter'): toQuarter
  • date_part('isoyear'): toISOYear
  • date_part('week'): toISOYear
  • date_part('epoch'): toISOYear
• date_trunc:
  • date_trunc('week'): toMonday
  • date_trunc('second'): toStartOfSecond
  • date_trunc('minute'): toStartOfMinute
  • date_trunc('hour'): toStartOfHour
  • date_trunc('day'): toStartOfDay
  • date_trunc('month'): toStartOfMonth
  • date_trunc('quarter'): toStartOfQuarter
  • date_trunc('year'): toStartOfYear
• array_position: indexOf
• btrim: trimBoth
• strpos: position
• regexp_like: match

Пользовательские функции

Эти пользовательские функции, созданные pg_clickhouse, обеспечивают pushdown удалённых запросов (foreign query pushdown) для некоторых функций ClickHouse, не имеющих эквивалентов в PostgreSQL. Если какую-либо из этих функций нельзя протолкнуть (push down), будет возбуждено исключение.

• dictGet

Проталкивание приведений типов

pg_clickhouse проталкивает приведения типов, такие как CAST(x AS bigint), для совместимых типов данных. Для несовместимых типов проталкивание завершится ошибкой; если x в этом примере имеет тип ClickHouse UInt64, ClickHouse откажется приводить это значение.

Чтобы проталкивать приведения к несовместимым типам данных, pg_clickhouse предоставляет следующие функции. Они вызывают исключение в PostgreSQL, если приведение не было протолкнуто.

• toUInt8
• toUInt16
• toUInt32
• toUInt64
• toUInt128

Агрегаты с pushdown

Эти агрегатные функции PostgreSQL могут быть протолкнуты для выполнения в ClickHouse (pushdown).

• count

Пользовательские агрегатные функции

Эти пользовательские агрегатные функции, созданные pg_clickhouse, поддерживают foreign query pushdown для ряда агрегатных функций ClickHouse, не имеющих эквивалентов в PostgreSQL. Если для какой-либо из этих функций pushdown невозможен, будет сгенерировано исключение.

• argMax
• argMin
• uniq
• uniqCombined
• uniqCombined64
• uniqExact
• uniqHLL12
• uniqTheta
• quantile
• quantileExact

Pushdown агрегатных функций Ordered Set

Эти ordered-set aggregate functions отображаются на ClickHouse Parametric aggregate functions, при этом их direct argument передаётся как параметр, а выражения ORDER BY — как аргументы. Например, такой PostgreSQL-запрос:

SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a) FROM t1;

Соответствует следующему запросу ClickHouse:

SELECT quantile(0.25)(a) FROM t1;

Обратите внимание, что суффиксы ORDER BY DESC и NULLS FIRST, отличные от значения по умолчанию, не поддерживаются и вызовут ошибку.

• percentile_cont(double): quantile
• quantile(double): quantile
• quantileExact(double): quantileExact

Настройки сессии

Установите параметр времени выполнения pg_clickhouse.session_settings, чтобы задать [настройки ClickHouse], которые будут применяться к последующим запросам. Пример:

SET pg_clickhouse.session_settings = 'join_use_nulls 1, final 1';

Значение по умолчанию — join_use_nulls 1. Установите пустую строку, чтобы использовать настройки, заданные на сервере ClickHouse.

SET pg_clickhouse.session_settings = '';

Синтаксис представляет собой список пар ключ-значение, разделённых запятыми и отделённых одним или несколькими пробелами. Ключи должны соответствовать ClickHouse settings. Экранируйте пробелы, запятые и обратные косые черты в значениях с помощью обратной косой черты:

SET pg_clickhouse.session_settings = 'join_algorithm grace_hash\,hash';

Или используйте значения в одинарных кавычках, чтобы избежать экранирования пробелов и запятых; рассмотрите вариант использования dollar quoting, чтобы не приходилось обрамлять значения двойными кавычками:

SET pg_clickhouse.session_settings = $$join_algorithm 'grace_hash,hash'$$;

Если для вас важна читаемость и нужно задать много параметров, используйте несколько строк, например:

SET pg_clickhouse.session_settings TO $$
    connect_timeout 2,
    count_distinct_implementation uniq,
    final 1,
    group_by_use_nulls 1,
    join_algorithm 'prefer_partial_merge',
    join_use_nulls 1,
    log_queries_min_type QUERY_FINISH,
    max_block_size 32768,
    max_execution_time 45,
    max_result_rows 1024,
    metrics_perf_events_list 'this,that',
    network_compression_method ZSTD,
    poll_interval 5,
    totals_mode after_having_auto
$$;

pg_clickhouse не проверяет настройки, а передаёт их в ClickHouse для каждого запроса. Таким образом, он поддерживает все настройки для каждой версии ClickHouse.

Обратите внимание, что pg_clickhouse должен быть загружен до установки pg_clickhouse.session_settings: либо используйте library preloading, либо просто обратитесь к одному из объектов расширения, чтобы гарантировать его загрузку.

Авторы

• David E. Wheeler
• Ildus Kurbangaliev
• Ibrar Ahmed

Авторские права

• Copyright (c) 2025, ClickHouse

• Отдельные части Copyright (c) 2023-2025, Ildus Kurbangaliev

• Отдельные части Copyright (c) 2019-2023, Adjust GmbH

• Отдельные части Copyright (c) 2012-2019, PostgreSQL Global Development Group