Quick Start - ClickHouse – Exoscale Documentation

Quick Start - ClickHouse

Start using Managed ClickHouse® by creating and configuring a service, connecting to it, and loading sample data.

Prerequisites

Exoscale CLI version 1.100.0 or later, the first release with ClickHouse support in the exo dbaas commands.
ClickHouse client installed, or Docker to run it from the official image without installing anything.

Create a Managed ClickHouse® service

You can create a service from the Portal or from the command line. The Portal is the quickest way to get a first service running; the CLI is what you script against.

From the Exoscale Portal

Open the Exoscale Portal and go to Databases.
Click Add and select ClickHouse.
Choose a zone and a plan. Plans with Public access are self-service, from Startup to Premium; plans with Request Only access are enabled per organization on request.
Under IP filter, restrict access to the addresses that need it. A service is reachable from anywhere by default, so set this before you load any data.
Click Create. The service reaches the running state in a few minutes, and the connection details appear on its overview page.

From the command line

Create a service with the exo dbaas commands of the Exoscale CLI (version 1.100.0 or later), choosing a plan and a zone, and restricting access to your IP address:

exo dbaas create clickhouse startup-16 my-clickhouse -z ch-gva-2 \
  --clickhouse-ip-filter <your-ip>/32

Watch the service until the state is running:

exo dbaas show my-clickhouse -z ch-gva-2 --output-template '{{ .State }}'

Retrieve the avnadmin password:

exo dbaas user reveal-secrets my-clickhouse avnadmin -z ch-gva-2

A service exposes several endpoints on the same host, one per protocol. Show the service to read its connection URI and, under Components, each endpoint with its port:

exo dbaas show my-clickhouse -z ch-gva-2

Component	Protocol
`clickhouse`	Native protocol over TLS, used by `clickhouse-client`
`clickhouse_https`	HTTPS interface, used by most drivers and by `curl`
`clickhouse_mysql`	MySQL wire protocol
`clickhouse_arrowflight`	Arrow Flight SQL

Note

exo dbaas update and exo dbaas delete ask for a confirmation before they run. The examples in this documentation add -f to skip it. Always add it in scripts: without a terminal to answer the prompt, the command exits without applying the change.

Configure the service

Update the service configuration with exo dbaas update. For example, change the allowed IP range:

exo dbaas update my-clickhouse -z ch-gva-2 -f \
  --clickhouse-ip-filter 203.0.113.0/24

The flag replaces the whole list. To allow several ranges, separate them with commas or repeat the flag.

Or set the automatic maintenance window, giving the day and the time together:

exo dbaas update my-clickhouse -z ch-gva-2 -f \
  --maintenance-dow sunday --maintenance-time 04:00:00

Connect to the service

Connect to your new service using the ClickHouse client.

Tip

Discover more tools for connecting to Managed ClickHouse in Connect to Managed ClickHouse®.

Connection times out

If the client hangs and then fails with a socket timeout, your source address is not allowed by the service ip-filter:

Code: 209. DB::NetException: Timeout: connect timed out: 203.0.113.10:21699 (SOCKET_TIMEOUT)

The filter drops the packets rather than rejecting them, which is why you get a timeout instead of a connection refused. This is the most common failure when connecting from a home or office line, where the public IP address changes over time and no longer matches the one used when the service was created. Check the current filter:

exo dbaas show my-clickhouse -z ch-gva-2 --output-template '{{ .Clickhouse.IPFilter }}'

Then set it to your current public IP address:

exo dbaas update my-clickhouse -z ch-gva-2 -f \
  --clickhouse-ip-filter "$(curl -s -4 https://ifconfig.me)/32"

The new filter applies immediately, without restarting the service.

Load a dataset

Managed ClickHouse® can read directly from S3-compatible object storage, so the quickest way to get a real dataset in is to let the service pull it itself: nothing is downloaded to your machine, and the transfer happens between the object storage and your service.

This example uses the New York City taxi dataset, roughly two million trips with fares, tips, distances and pickup or dropoff neighborhoods.

Create the trips table in the default database, which was created automatically with your service.

CREATE TABLE 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` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `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;

The statement returns one row per node rather than empty output, because databases use the Replicated engine. See Read the output of a DDL statement.

Note

Keep the column types exactly as they are. The pickup_nyct2010_gid column is Int8 because the source files genuinely contain negative values in that column, and widening it to an unsigned type makes the load fail with Cannot parse input ... is not like UInt16. Those negative rows have no matching neighborhood, which is why looking them up in a taxi zone dictionary returns unknown.

Load the data. The s3 table function reads the two compressed files and streams the rows straight into your table:

INSERT INTO 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` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `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;

Note

The main service user avnadmin already holds the S3 and URL grants, so this runs without any GRANT beforehand. Other users need them explicitly. See Run federated queries in Managed ClickHouse®.

Check the load:
```
SELECT count() FROM trips
```
The table holds 1999657 rows. On a startup-16 service in ch-gva-2, that ingest took about six seconds and read 164 MiB. To measure it on your own service, read the statement back from system.query_log, as described in Fetch query statistics.

Query data

Once the data is loaded, you can run queries against the sample data you imported.

Count the trips:
```
SELECT count() FROM trips
```

Read the average tip:

SELECT round(avg(tip_amount), 2) FROM trips

Compare the average total amount paid by number of passengers:

SELECT
    passenger_count,
    ceil(avg(total_amount), 2) AS average_total_amount
FROM trips
GROUP BY passenger_count
ORDER BY passenger_count

Group trips by duration in minutes, longest first:

SELECT
    avg(tip_amount) AS avg_tip,
    avg(fare_amount) AS avg_fare,
    avg(passenger_count) AS avg_passenger,
    count() AS count,
    truncate(date_diff('second', pickup_datetime, dropoff_datetime) / 60) AS trip_minutes
FROM trips
WHERE trip_minutes > 0
GROUP BY trip_minutes
ORDER BY trip_minutes DESC
LIMIT 20

Find the rides that ended at LaGuardia or JFK:

SELECT
    pickup_datetime,
    dropoff_datetime,
    total_amount,
    CASE
        WHEN dropoff_nyct2010_gid = 138 THEN 'LGA'
        WHEN dropoff_nyct2010_gid = 132 THEN 'JFK'
    END AS airport_code
FROM trips
WHERE dropoff_nyct2010_gid IN (132, 138)
ORDER BY pickup_datetime
LIMIT 20

To map those neighborhood identifiers to borough names without a join against a table, load them into a dictionary. See Create dictionaries in Managed ClickHouse®.

Clean up

When you are done experimenting, delete the service to stop the billing. Services created with the CLI are protected against termination by default, so lift the protection first:

exo dbaas update my-clickhouse -z ch-gva-2 -f --termination-protection=false
exo dbaas delete my-clickhouse -z ch-gva-2 -f

Next steps

Last updated on September 17, 2026