# Quick Start

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

## Prerequisites

- [Exoscale CLI](/reference/cli/exo/) version 1.97 or later. Earlier releases expose no
  ClickHouse commands at all.
- [ClickHouse client](https://clickhouse.com/docs/concepts/features/interfaces/client) installed, or
  [Docker](https://docs.docker.com/desktop/) 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](/reference/cli/exo/) (version 1.97 or later), choosing a
[plan](/product/dbaas/service-specific/clickhouse/service-boundaries/limits-and-quotas/#plans)
and a [zone](/platform/dc-zones/), and restricting access to your IP address:

```bash
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`:

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

Retrieve the `avnadmin` password and the connection URI:

```bash
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:

```bash
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](https://jmespath.org/) 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:

```bash
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:

```bash
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-service}

[Connect to your new service](/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/)
using the
[ClickHouse client](https://clickhouse.com/docs/concepts/features/interfaces/client).

> [!TIP]
> Discover more tools for connecting to Managed ClickHouse in
> [Connect to Managed ClickHouse®](/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/).

### 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`:

```text
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:

```bash
exo x get-dbaas-service-clickhouse my-clickhouse -z ch-gva-2 -q '"ip-filter"'
```

Then set it to your current public IP address:

```bash
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.

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

   ```sql
   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](/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/#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.

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

   ```sql
   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®](/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/).

1. Check the load:

   ```sql
   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](/product/dbaas/service-specific/clickhouse/how-to/fetch-query-statistics/).

## Query data

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

- Count the trips:

  ```sql
  SELECT count() FROM trips
  ```

- Read the average tip:

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

- Compare the average total amount paid by number of passengers:

  ```sql
  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:

  ```sql
  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:

  ```sql
  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®](/product/dbaas/service-specific/clickhouse/how-to/create-dictionary/).

## Clean up

When you are done experimenting, delete the service to stop the billing:

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

## Next steps

- [Service architecture](/product/dbaas/service-specific/clickhouse/overview/service-architecture/)
- [Secure a Managed ClickHouse® service](/product/dbaas/service-specific/clickhouse/how-to/secure-service/)
- [Manage Managed ClickHouse® users and roles](/product/dbaas/service-specific/clickhouse/how-to/manage-users-roles/)
- [Manage Managed ClickHouse® database and tables](/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/)
- [Run federated queries in Managed ClickHouse®](/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/)
- [Create dictionaries in Managed ClickHouse®](/product/dbaas/service-specific/clickhouse/how-to/create-dictionary/)