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.
Restoring a logical backup of a database with a materialized view using a foreign table can fail. For this reason, either do not use foreign tables in materialized views or use them in databases with physical backups enabled.
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> |
Preparation
Before you get started, make sure the wrappers
extension is installed on your database:
_10create extension if not exists wrappers with schema extensions;
and then create the foreign data wrapper:
_10create foreign data wrapper clickhouse_wrapper_10 handler click_house_fdw_handler_10 validator click_house_fdw_validator;
Secure your credentials (optional)
By default, Postgres stores FDW credentials inide 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=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.
Creating Foreign Tables
The ClickHouse Wrapper supports data reads and writes from ClickHouse.
Integration | Select | Insert | Update | Delete | Truncate |
---|---|---|---|---|---|
ClickHouse | ✅ | ✅ | ✅ | ✅ | ❌ |
For example:
_10create foreign table my_clickhouse_table (_10 id bigint,_10 name text_10)_10 server clickhouse_server_10 options (_10 table 'people'_10 );
Foreign table options
The full list of foreign table options are below:
-
table
- Source table name in ClickHouse, required.This can also be a subquery enclosed in parentheses, for example,
_10table '(select * from my_table)'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,_13create foreign table test_vw (_13id bigint,_13col1 text,_13col2 bigint,_13_param1 text,_13_param2 bigint_13)_13server clickhouse_server_13options (_13table '(select * from my_view(column1=${_param1}, column2=${_param2}))'_13);_13_13select * from test_vw where _param1='aaa' and _param2=32; -
rowid_column
- Primary key column name, optional for data scan, required for data modify
Query Pushdown Support
This FDW supports where
, order by
and limit
clause pushdown, as well as parametrized view (see above).
Examples
Some examples on how to use ClickHouse foreign tables.
Basic example
This will create a "foreign table" inside your Postgres database called people
:
_11-- Run below SQLs on ClickHouse to create source table_11drop table if exists people;_11create table people (_11 id Int64,_11 name String_11)_11engine=MergeTree()_11order by id;_11_11-- Add some test data_11insert into people values (1, 'Luke Skywalker'), (2, 'Leia Organa'), (3, 'Han Solo');
Create foreign table on Postgres database:
_16create foreign table people (_16 id bigint,_16 name text_16)_16 server clickhouse_server_16 options (_16 table 'people'_16 );_16_16-- data scan_16select * from people;_16_16-- data modify_16insert into people values (4, 'Yoda');_16update people set name = 'Princess Leia' where id = 2;_16delete from people where id = 3;