Percona-Lab/clickhousedb_fdw

What is clickhousedb_fdw? *

The clickhousedb_fdw is open-source. It is a Foreign Data Wrapper (FDW) for one of the fastest column store databases; "Clickhouse". This FDW allows you to SELECT from, and INSERT into, a ClickHouse database from within a PostgreSQL server. The FDW supports advanced features like aggregate pushdown and joins pushdown. These significantly improve performance by utilizing the remote server’s resources for these resource intensive operations.

Documentation

1. Supported PostgreSQL Versions
2. Installation
3. Setup
4. User Guide
5. Release Notes
6. License
7. Submitting Bug Reports
8. Copyright Notice

Supported PostgreSQL Versions

The clickhousedb_fdw should work on the latest version of PostgreSQL but is only tested with these PostgreSQL versions:

Installation

Installing from source code

You can download the source code of the latest release of clickhousedb_fdw from this GitHub page or using git:

git clone git://github.com/Percona-Lab/clickhousedb_fdw.git

Compile and install the extension

cd clickhousedb_fdw
make USE_PGXS=1
make USE_PGXS=1 install

Create the extension using the CREATE EXTENSION command.

CREATE EXTENSION clickhousedb_fdw;
CREATE EXTENSION

Install using deb / rpm packages.

sudo yum install clickhousedb_fdw
sudo apt-get install clickhousedb_fdw

CREATE SERVER clickhouse_svr
FOREIGN DATA WRAPPER clickhousedb_fdw 
OPTIONS(dbname 'test_database', driver '/home/vagrant/percona/clickhousedb_fdw/lib/clickhouse-odbc/driver/libclickhouseodbc.so', host '127.0.0.1');
   
CREATE USER MAPPING FOR CURRENT_USER SERVER clickhouse_svr;

CREATE FOREIGN TABLE tax_bills_nyc 
    (
        bbl int8,
        owner_name text,
        address text,
        tax_class text,
        tax_rate text,
        emv Float,
        tbea Float,
        bav Float,
        tba text,
        property_tax text,
        condonumber text,
        condo text,
        insertion_date Time 
    ) SERVER clickhouse_svr;

SELECT bbl,tbea,bav,insertion_date FROM tax_bills_nyc LIMIT 5;
        bbl     | tbea  |  bav   | insertion_date 
    ------------+-------+--------+----------------
    4001940057 | 18755 | 145899 | 15:25:42
    1016830130 |  2216 |  17238 | 15:25:42
    4012850059 | 69562 | 541125 | 15:25:42
    1006130061 | 55883 | 434719 | 15:25:42
    3033540009 | 33100 | 257490 | 15:25:42
    (5 rows)
    
CREATE TABLE tax_bills ( bbl bigint, owner_name text) ENGINE = MergeTree PARTITION BY bbl ORDER BY (bbl)
    
INSERT INTO tax_bills SELECT bbl, tbea from tax_bills_nyc LIMIT 100;
    
EXPLAIN VERBOSE SELECT bbl,tbea,bav,insertion_date FROM tax_bills_nyc LIMIT 5;
                                         QUERY PLAN                                         
    --------------------------------------------------------------------------------------------
    Limit  (cost=0.00..0.00 rows=1 width=32)
    Output: bbl, tbea, bav, insertion_date
     ->  Foreign Scan on public.tax_bills_nyc  (cost=0.00..0.00 rows=0 width=32)
             Output: bbl, tbea, bav, insertion_date
             Remote SQL: SELECT bbl, tbea, bav, insertion_date FROM test_database.tax_bills_nyc
    (5 rows)

To learn more about Percona-Lab/clickhousedb_fdw configuration and usage, see User Guide.

Submitting Bug Reports

If you found a bug in clickhousedb_fdw, please submit the report to the Jira issue tracker

Start by searching the open tickets for a similar report. If you find that someone else has already reported your issue, then you can upvote that report to increase its visibility.

If there is no existing report, submit your report following these steps:

Sign in to Jira issue tracker. You will need to create an account if you do not have one.

In the Summary, Description, Steps To Reproduce, Affects Version fields describe the problem you have detected.

As a general rule of thumb, try to create bug reports that are:

• Reproducible: describe the steps to reproduce the problem.

• Unique: check if there already exists a JIRA ticket to describe the problem.

• Scoped to a Single Bug: only report one bug in one JIRA ticket.

Copyright Notice

Portions Copyright © 2018-2020, Percona LLC and/or its affiliates

Portions Copyright © 2019-2020, Adjust GmbH

Portions Copyright © 1996-2020, PostgreSQL Global Development Group

Portions Copyright © 1994, The Regents of the University of California

• Master branch is under heavy development phase, you may face some compilation issues. The REL_0_1_ still can be used.