Run ClickHouse for analytics

3 min read Updated 1 day ago

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:

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

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

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:

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.