pg_clickhouse Tutorial

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/tools/data-integration/pg_clickhouse/tutorial.md)

## Overview[​](#overview "Direct link to Overview")

This tutorial follows the [ClickHouse tutorial] but runs all its queries via
pg\_clickhouse.

## Start ClickHouse[​](#start-clickhouse "Direct link to Start ClickHouse")

First, create a ClickHouse database if you don't already have one. A quick way
to start is with the Docker image:

```
docker run -d --network host --name clickhouse -p 8123:8123 -p9000:9000 --ulimit nofile=262144:262144 clickhouse
docker exec -it clickhouse clickhouse-client
```

## Create a Table[​](#create-a-table "Direct link to Create a Table")

Let's borrow from the [ClickHouse tutorial] to create a simple database with The New York
City taxi dataset:

```
CREATE DATABASE taxi;
CREATE TABLE taxi.trips
(
    trip_id UInt32,
    vendor_id Enum8(
        '1'      =  1, '2'      =  2, '3'      =  3, '4'      =  4,
        'CMT'    =  5, 'VTS'    =  6, 'DDS'    =  7, 'B02512' = 10,
        'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14,
        ''       = 15
    ),
    pickup_date Date,
    pickup_datetime DateTime,
    dropoff_date Date,
    dropoff_datetime DateTime,
    store_and_fwd_flag UInt8,
    rate_code_id UInt8,
    pickup_longitude Float64,
    pickup_latitude Float64,
    dropoff_longitude Float64,
    dropoff_latitude Float64,
    passenger_count UInt8,
    trip_distance Float64,
    fare_amount Decimal(10, 2),
    extra Decimal(10, 2),
    mta_tax Decimal(10, 2),
    tip_amount Decimal(10, 2),
    tolls_amount Decimal(10, 2),
    ehail_fee Decimal(10, 2),
    improvement_surcharge Decimal(10, 2),
    total_amount Decimal(10, 2),
    payment_type Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    trip_type UInt8,
    pickup FixedString(25),
    dropoff FixedString(25),
    cab_type Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    pickup_nyct2010_gid Int8,
    pickup_ctlabel Float32,
    pickup_borocode Int8,
    pickup_ct2010 String,
    pickup_boroct2010 String,
    pickup_cdeligibil String,
    pickup_ntacode FixedString(4),
    pickup_ntaname String,
    pickup_puma UInt16,
    dropoff_nyct2010_gid UInt8,
    dropoff_ctlabel Float32,
    dropoff_borocode UInt8,
    dropoff_ct2010 String,
    dropoff_boroct2010 String,
    dropoff_cdeligibil String,
    dropoff_ntacode FixedString(4),
    dropoff_ntaname String,
    dropoff_puma UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;
```

## Add the Data Set[​](#add-the-data-set "Direct link to Add the Data Set")

And then import the data:

```
INSERT INTO taxi.trips
SELECT * FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
    'TabSeparatedWithNames', "
    trip_id UInt32,
    vendor_id Enum8(
        '1'      =  1, '2'      =  2, '3'      =  3, '4'      =  4,
        'CMT'    =  5, 'VTS'    =  6, 'DDS'    =  7, 'B02512' = 10,
        'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14,
        ''       = 15
    ),
    pickup_date Date,
    pickup_datetime DateTime,
    dropoff_date Date,
    dropoff_datetime DateTime,
    store_and_fwd_flag UInt8,
    rate_code_id UInt8,
    pickup_longitude Float64,
    pickup_latitude Float64,
    dropoff_longitude Float64,
    dropoff_latitude Float64,
    passenger_count UInt8,
    trip_distance Float64,
    fare_amount Decimal(10, 2),
    extra Decimal(10, 2),
    mta_tax Decimal(10, 2),
    tip_amount Decimal(10, 2),
    tolls_amount Decimal(10, 2),
    ehail_fee Decimal(10, 2),
    improvement_surcharge Decimal(10, 2),
    total_amount Decimal(10, 2),
    payment_type Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    trip_type UInt8,
    pickup FixedString(25),
    dropoff FixedString(25),
    cab_type Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    pickup_nyct2010_gid Int8,
    pickup_ctlabel Float32,
    pickup_borocode Int8,
    pickup_ct2010 String,
    pickup_boroct2010 String,
    pickup_cdeligibil String,
    pickup_ntacode FixedString(4),
    pickup_ntaname String,
    pickup_puma UInt16,
    dropoff_nyct2010_gid UInt8,
    dropoff_ctlabel Float32,
    dropoff_borocode UInt8,
    dropoff_ct2010 String,
    dropoff_boroct2010 String,
    dropoff_cdeligibil String,
    dropoff_ntacode FixedString(4),
    dropoff_ntaname String,
    dropoff_puma UInt16
") SETTINGS input_format_try_infer_datetimes = 0
```

Make sure we can query it then quit the client:

```
SELECT count() FROM taxi.trips;
quit
```

### Install pg\_clickhouse[​](#install-pg_clickhouse "Direct link to Install pg_clickhouse")

Build and install pg\_clickhouse from [PGXN](https://pgxn.org/dist/pg_clickhouse "pg_clickhouse on PGXN") or [GitHub](https://github.com/ClickHouse/pg_clickhouse/releases "pg_clickhouse Releases on GitHub"). Or spin up a
Docker container using the [pg\_clickhouse image](https://github.com/ClickHouse/pg_clickhouse/pkgs/container/pg_clickhouse "pg_clickhouse OCI Image on GitHub"), which simply adds
pg\_clickhouse to the Docker [Postgres image](https://hub.docker.com/_/postgres "Postgres OCI Image on Docker Hub"):

```
docker run -d --network host --name pg_clickhouse -e POSTGRES_PASSWORD=my_pass \
       -d ghcr.io/clickhouse/pg_clickhouse:18
```

### Connect pg\_clickhouse[​](#connect-pg_clickhouse "Direct link to Connect pg_clickhouse")

Now connect to Postgres:

```
docker exec -it pg_clickhouse psql -U postgres
```

And create pg\_clickhouse:

```
CREATE EXTENSION pg_clickhouse;
```

Create a foreign server using the host name, port, and database for your
ClickHouse database.

```
CREATE SERVER taxi_srv FOREIGN DATA WRAPPER clickhouse_fdw
       OPTIONS(driver 'binary', host 'localhost', dbname 'taxi');
```

Here we've elected to use the binary driver, which uses the ClickHouse binary
protocol. You can also use the "http" driver, which uses the HTTP interface.

Next, map a PostgreSQL user to a ClickHouse user. The simplest way to do so
is just to map the current PostgreSQL user to a remote user for the foreign
server:

```
CREATE USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (user 'default');
```

You can also specify a `password` option.

Now, add the taxi table, just import it all of the tables from the remote
ClickHouse database into a Postgres schema:

```
CREATE SCHEMA taxi;
IMPORT FOREIGN SCHEMA taxi FROM SERVER taxi_srv INTO taxi;
```

And now the table should be imported: In [psql](https://www.postgresql.org/docs/current/app-psql.html "PostgreSQL Client Applications: psql"), use `\det+` to see it:

```
taxi=# \det+ taxi.*
                                       List of foreign tables
 Schema | Table |  Server  |                        FDW options                        | Description
--------+-------+----------+-----------------------------------------------------------+-------------
 taxi   | trips | taxi_srv | (database 'taxi', table_name 'trips', engine 'MergeTree') | [null]
(1 row)
```

Success! Use `\d` to show all the columns: