Database

ClickHouse

ClickHouse is a fast open-source column-oriented database management system that allows generating analytical data reports in real-time using SQL queries.

The ClickHouse Wrapper allows you to read and write data from ClickHouse within your Postgres database.

Supported Data Types

Postgres TypeClickHouse Type
booleanUInt8
smallintInt16
integerUInt16
integerInt32
bigintUInt32
bigintInt64
bigintUInt64
realFloat32
double precisionFloat64
textString
dateDate
timestampDateTime
*Nullable<T>

Preparation

Before you can query ClickHouse, you need to enable the Wrappers extension and store your credentials in Postgres.

Enable Wrappers

Make sure the wrappers extension is installed on your database:


_10
create extension if not exists wrappers with schema extensions;

Enable the ClickHouse Wrapper

Enable the clickhouse_wrapper FDW:


_10
create foreign data wrapper clickhouse_wrapper
_10
handler click_house_fdw_handler
_10
validator click_house_fdw_validator;

Store your credentials (optional)

By default, Postgres stores FDW credentials inside pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.


_10
-- Save your ClickHouse credential in Vault and retrieve the `key_id`
_10
insert into vault.secrets (name, secret)
_10
values (
_10
'clickhouse',
_10
'tcp://default:@localhost:9000/default'
_10
)
_10
returning key_id;

Connecting to ClickHouse

We need to provide Postgres with the credentials to connect to ClickHouse, and any additional options. We can do this using the create server command:


_10
create server clickhouse_server
_10
foreign data wrapper clickhouse_wrapper
_10
options (
_10
conn_string_id '<key_ID>' -- The Key ID from above.
_10
);

Some connection string examples:

  • tcp://user:password@host:9000/clicks?compression=lz4&ping_timeout=42ms
  • tcp://default:PASSWORD@abc.eu-west-1.aws.clickhouse.cloud:9440/default?connection_timeout=30s&ping_before_query=false&secure=true

Check more connection string parameters.

Create a schema

We recommend creating a schema to hold all the foreign tables:


_10
create schema if not exists clickhouse;

Options

The following options are available when creating ClickHouse foreign tables:

  • table - Source table name in ClickHouse, required

This can also be a subquery enclosed in parentheses, for example,


_10
table '(select * from my_table)'

Parametrized views

Parametrized view is also supported in the subquery. In this case, you need to define a column for each parameter and use where to pass values to them. For example,


_13
create foreign table test_vw (
_13
id bigint,
_13
col1 text,
_13
col2 bigint,
_13
_param1 text,
_13
_param2 bigint
_13
)
_13
server clickhouse_server
_13
options (
_13
table '(select * from my_view(column1=${_param1}, column2=${_param2}))'
_13
);
_13
_13
select * from test_vw where _param1='aaa' and _param2=32;

  • rowid_column - Primary key column name, optional for data scan, required for data modify

Entities

Tables

The ClickHouse Wrapper supports data reads and writes from ClickHouse tables.

Operations

ObjectSelectInsertUpdateDeleteTruncate
Tables✅✅✅✅❌

Usage


_10
create foreign table clickhouse.my_table (
_10
id bigint,
_10
name text
_10
)
_10
server clickhouse_server
_10
options (
_10
table 'people'
_10
);

Notes

  • Supports where, order by and limit clause pushdown
  • Supports parametrized views in subqueries
  • When using rowid_column, it must be specified for data modification operations

Query Pushdown Support

This FDW supports where, order by and limit clause pushdown, as well as parametrized view (see above).

Examples

Basic Query Example

This example demonstrates basic ClickHouse table operations.


_10
-- Run below SQLs on ClickHouse to create source table
_10
create table people (
_10
id Int64,
_10
name String
_10
)
_10
engine=MergeTree()
_10
order by id;
_10
_10
-- Add some test data
_10
insert into people values (1, 'Luke Skywalker'), (2, 'Leia Organa'), (3, 'Han Solo');

Create foreign table on Postgres database:


_16
create foreign table clickhouse.people (
_16
id bigint,
_16
name text
_16
)
_16
server clickhouse_server
_16
options (
_16
table 'people'
_16
);
_16
_16
-- data scan
_16
select * from clickhouse.people;
_16
_16
-- data modify
_16
insert into clickhouse.people values (4, 'Yoda');
_16
update clickhouse.people set name = 'Princess Leia' where id = 2;
_16
delete from clickhouse.people where id = 3;