# Run ClickHouse for analytics

> Use a fast column-oriented database for analytics and big data workloads.

## Run ClickHouse for analytics

ClickHouse is a fast column-oriented database built for analytics and big data. Where a row-oriented database pulls whole rows to answer a query, ClickHouse stores each column separately, so analytical queries only read the columns they need. That makes it very fast at aggregations over large volumes of data, and a good fit for workloads like real-time analytics, log analysis, time-series and data warehousing.

This article covers adding a fully managed ClickHouse service to your application. Once it runs, ClickHouse exposes two protocols: HTTP on port `8123` and the native protocol on port `9000`. The HTTP interface is the easiest way to interact with it, and the native protocol is what any of the official client libraries use.

### Adding it

1. Open your application and go to the **Custom services** card.
2. Click **Create service**.
3. Pick **ClickHouse**.
4. Choose a version. Available versions are `23.8` (the default), `23.3` and `22.8`.
5. Choose how much CPU and memory it gets, then click **Create service**.

The service runs the `clickhouse/clickhouse-server` image. Its storage is persistent, so it survives restarts and redeploys.

### Connecting from your application

Once the service is running, Ploi Cloud injects these environment variables into your application:

- **CLICKHOUSE_URL** is the HTTP endpoint, in the form `http://HOST:8123`.
- **CLICKHOUSE_HOST** is the service hostname.
- **CLICKHOUSE_PORT** is the HTTP and CLI port, `8123`.
- **CLICKHOUSE_NATIVE_PORT** is the native protocol port, `9000`.

Use **CLICKHOUSE_URL** for anything that talks HTTP, and point official client libraries at the host with **CLICKHOUSE_NATIVE_PORT** when they expect the native protocol.

### Querying over HTTP

The HTTP interface accepts a query as a GET request with the query passed as a `query` parameter. A quick check against the `default` database looks like this:

```bash
curl "http://HOST:8123/?query=SELECT%201"
```

To run a real query, POST the SQL as the request body:

```bash
curl -X POST "http://HOST:8123/?query=SELECT%20count(*)%20FROM%20events"
```

You can pass user and password as URL parameters when the server requires them:

```bash
curl "http://HOST:8123/?query=SELECT%20*%20FROM%20events%20LIMIT%2010&user=default&password=YOUR_PASSWORD"
```

Replace `HOST` with the hostname from **CLICKHOUSE_HOST** in your application.

### Setting the user and password

Three settings are available on this service: **CLICKHOUSE_USER** (default `default`), **CLICKHOUSE_PASSWORD**, and **CLICKHOUSE_DB** (default `default`).

These are applied when the database is first created and are not editable afterwards, so they do not appear under **Advanced settings** once the service exists. Set them at creation time and they take effect the first time the database is initialised. If you need to change them after that, provision a new service with the values you want.

### Storage

Two persistent volumes back the service:

- `/var/lib/clickhouse` holds the ClickHouse data, sized at 50GB.
- `/var/log/clickhouse-server` holds the ClickHouse logs, sized at 5GB.

Because the data volume is persistent, your databases survive service restarts and redeploys. Changing the size of a volume after creation is handled separately from the service itself.

### Use cases

- **Real-time analytics.** Query live event and session data with sub-second latency over the HTTP interface.
- **Log analysis.** Store and aggregate large volumes of application logs, then slice them by time or any other dimension.
- **Time-series.** Keep long histories of metrics and sensor readings, and run range and aggregation queries over them.
- **Data warehousing.** Replace slow analytical queries over production data with a dedicated column store built for them.