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#

Preparation#

Before you get started, make sure the wrappers extension is installed on your database:

and then create the foreign data wrapper:

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.

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:

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

Check more connection string parameters.

Creating Foreign Tables#

The ClickHouse Wrapper supports data reads and writes from ClickHouse.

For example:

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,

  
  

  _10

  table '(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,

  
  

  _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

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:

Create foreign table on Postgres database: