Quick Start

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

Prerequisites

Exoscale CLI version 1.97 or later. Earlier releases expose no ClickHouse commands at all.
ClickHouse client installed, or Docker to run it from the official image without installing anything.

Create a Managed ClickHouse® service

Create a service with the low-level API commands (exo x) of the Exoscale CLI (version 1.97 or later), choosing a plan and a zone, and restricting access to your IP address:

echo '{"plan":"startup-16","ip-filter":["<your-ip>/32"]}' | \
  exo x create-dbaas-service-clickhouse my-clickhouse -z ch-gva-2

Watch the service until the state is running:

exo x get-dbaas-service-clickhouse my-clickhouse -z ch-gva-2 -q 'state'

Retrieve the avnadmin password and the connection URI:

exo x reveal-dbaas-clickhouse-user-password my-clickhouse avnadmin -z ch-gva-2
exo x get-dbaas-service-clickhouse my-clickhouse -z ch-gva-2 -q 'uri'

A service exposes several endpoints on the same host, one per protocol. List them with their ports to pick the right one:

exo x get-dbaas-service-clickhouse my-clickhouse -z ch-gva-2 -q 'components'

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

The -q flag takes a JMESPath expression, where a bare hyphen reads as a subtraction. Quote every key that contains one, otherwise the command fails with SyntaxError: Invalid token: -q '"ip-filter"', -q '"node-count"'.

Note

Managed ClickHouse® is in early access: services are managed through the exo x API commands shown on this page. Dedicated exo dbaas subcommands will replace them at general availability.

Configure the service

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

echo '{"ip-filter":["203.0.113.0/24"]}' | \
  exo x update-dbaas-service-clickhouse my-clickhouse -z ch-gva-2

Or set the automatic maintenance window:

echo '{"maintenance":{"dow":"sunday","time":"04:00:00"}}' | \
  exo x update-dbaas-service-clickhouse my-clickhouse -z ch-gva-2

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 x get-dbaas-service-clickhouse my-clickhouse -z ch-gva-2 -q '"ip-filter"'

Then set it to your current public IP address:

echo "{\"ip-filter\":[\"$(curl -s -4 https://ifconfig.me)/32\"]}" | \
  exo x update-dbaas-service-clickhouse my-clickhouse -z ch-gva-2

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:

exo x delete-dbaas-service-clickhouse my-clickhouse -z ch-gva-2

Next steps

Last updated on August 18, 2026