Skip to content
A PostgreSQL foreign data wrapper for ClickHouse
Branch: master
Clone or download
Itai Shirav
Latest commit 04d61f4 Jun 24, 2017
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
src/infi custom like operator Jun 24, 2017
.gitignore added all project files Jun 11, 2017
LICENSE
README.md readme Jun 12, 2017
buildout.cfg Fix homepage setting Jun 24, 2017
setup.in Change license to BSD Jun 24, 2017

README.md

Overview

This project provides a PostgreSQL Foreign Data Wrapper (FDW) for the ClickHouse database. It allows ClickHouse tables to be queried as if they were stored in PostgreSQL.

The project is based on Multicorn and infi.clickhouse_orm.

Features

  • Automatic generation of foreign table definitions
  • Sorting and filtering is done inside ClickHouse
  • Hints about each column are provided to the query planner
  • EXPLAIN is supported (shows the query sent to ClickHouse)

Limitations

  • Supports only SELECT, no updates
  • Does not support complex ClickHouse datatypes (arrays, enums, nested tables)

Installation

First, install Multicorn. Then run:

easy_install infi.clickhouse_fdw

Usage

The simplest way to use this project is the included generate_clickhouse_fdw script. It generates all the required SQL statements, which you can then pipe to psql:

$ generate_clickhouse_fdw --help

Usage: generate_clickhouse_fdw [OPTIONS] [TABLE]...

  Generates SQL statements for defining Foreign Data Wrappers for ClickHouse
  tables.

  If no table names are specified, wrappers are generated for all tables in
  the ClickHouse database. If table names are given, wrappers will be
  generated only for those tables unless --exclude is present, in which case
  all tables EXCEPT those listed will be processed.

  If --pg-ver=9.4 is specified, explicit CREATE FOREIGN TABLE is generated
  for each table. Otherwise a single IMPORT FOREIGN SCHEMA statement is
  used.

Options:
  --db-url TEXT           ClickHouse URL [http://localhost:8123/]
  --db-name TEXT          ClickHouse database name [default]
  --server-name TEXT      FDW server name [clickhouse_server]
  --schema-name TEXT      Schema to define the tables in [public]
  --pg-ver [9.4|9.5|9.6]  PostgreSQL version [9.6]
  --exclude               Generate all tables except those named
  --help                  Show this message and exit.

For example:

$ generate_clickhouse_fdw --db-name=events table1 table2 table3

CREATE EXTENSION IF NOT EXISTS multicorn;

CREATE SERVER clickhouse_server 
FOREIGN DATA WRAPPER multicorn
OPTIONS (
  wrapper 'infi.clickhouse_fdw.main.ClickHouseDataWrapper'
);

IMPORT FOREIGN SCHEMA "events"
FROM SERVER "clickhouse_server" 
LIMIT TO ('table1', 'table2', 'table3')
INTO "public"
OPTIONS ( 
    db_url 'http://localhost:8123/', 
    db_name 'events'
);

Any warnings that are detected by the script are printed to stderr.

Contributing

To set up a development version, clone the project and run the following commands:

easy_install -U infi.projector
cd infi.clickhouse_fdw
projector devenv build

A setup.py file will be generated, which you can use to install the development version of the package:

python setup.py develop

Any changes you make to the code will take effect only after you restart PostgreSQL.

You can’t perform that action at this time.