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.
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:
_10create extension if not exists wrappers with schema extensions;
Enable the ClickHouse Wrapper
Enable the clickhouse_wrapper FDW:
_10create 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`_10insert into vault.secrets (name, secret)_10values (_10 'clickhouse',_10 'tcp://default:@localhost:9000/default'_10)_10returning 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:
_10create 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=42mstcp://default:[email protected]: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:
_10create 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,
_10table '(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
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Tables | ✅ | ✅ | ✅ | ✅ | ❌ |
Usage
_10create 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 byandlimitclause 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).
Supported Data Types
| Postgres Type | ClickHouse Type |
|---|---|
| boolean | UInt8 |
| smallint | Int16 |
| integer | UInt16 |
| integer | Int32 |
| bigint | UInt32 |
| bigint | Int64 |
| bigint | UInt64 |
| real | Float32 |
| double precision | Float64 |
| text | String |
| date | Date |
| timestamp | DateTime |
| * | Nullable<T> |
Limitations
This section describes important limitations and considerations when using this FDW:
- Full result sets must be transferred from ClickHouse to PostgreSQL
- Large result sets consume significant PostgreSQL memory
- Only basic query clauses (WHERE, ORDER BY, LIMIT) support pushdown
- Limited data type mappings (see Supported Data Types section)
- Materialized views using foreign tables may fail during logical backups
Examples
Basic Query Example
This example demonstrates basic ClickHouse table operations.
_10-- Run below SQLs on ClickHouse to create source table_10create table people (_10 id Int64,_10 name String_10)_10engine=MergeTree()_10order by id;_10_10-- Add some test data_10insert into people values (1, 'Luke Skywalker'), (2, 'Leia Organa'), (3, 'Han Solo');
Create foreign table on Postgres database:
_16create 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_16select * from clickhouse.people;_16_16-- data modify_16insert into clickhouse.people values (4, 'Yoda');_16update clickhouse.people set name = 'Princess Leia' where id = 2;_16delete from clickhouse.people where id = 3;