Upsun User Documentation
Edit page

ClickHouse

Back to home

On this page

Sign up

Get your free trial by clicking the link below.

Get your Upsun free trial

ClickHouse is a high-performance column-oriented, distributed, OLAP (Online Analytical Processing) database.
It allows you to generate real-time analytical data reports using SQL queries.
For more information, see the ClickHouse documentation.

Supported versions Anchor to this heading

  • 24.3
  • 23.8

Upsun plans on supporting long-term support ClickHouse versions in priority.

Relationship reference Anchor to this heading

For each service defined via a relationship to your application, Upsun automatically generates corresponding environment variables within your application container, in the $<RELATIONSHIP-NAME>_<SERVICE-PROPERTY> format.

Here is example information available through the service environment variables themselves, or through the PLATFORM_RELATIONSHIPS environment variable.

You can obtain the complete list of available service environment variables in your app container by running upsun ssh env.

Note that the information about the relationship can change when an app is redeployed or restarted or the relationship is changed. So your apps should only rely on the service environment variables directly rather than hard coding any values.

CLICKHOUSE_USERNAME=main
CLICKHOUSE_FRAGMENT=
CLICKHOUSE_IP=123.456.78.90
CLICKHOUSE_CLUSTER=azertyuiop-main-afdwftq
CLICKHOUSE_HOST=clickhouse.internal
CLICKHOUSE_PATH=main
CLICKHOUSE_QUERY={'is_master': True}
CLICKHOUSE_PASSWORD=ChangeMe
CLICKHOUSE_PORT=9000
CLICKHOUSE_HOST_MAPPED=false
CLICKHOUSE_SERVICE=clickhouse
CLICKHOUSE_HOSTNAME=azertyuiopqsdfghjklm.clickhouse.service._.eu-1.platformsh.site
CLICKHOUSE_EPOCH=0
CLICKHOUSE_REL=clickhouse
CLICKHOUSE_SCHEME=https/http
CLICKHOUSE_TYPE=clickhouse:24
CLICKHOUSE_PUBLIC=false

For some advanced use cases, you can use the PLATFORM_RELATIONSHIPS environment variable. The structure of the PLATFORM_RELATIONSHIPS environment variable can be obtained by running upsun relationships in your terminal:

{
      "username": "main",
      "fragment": null,
      "ip": "123.456.78.90",
      "cluster": "azertyuiop-main-afdwftq",
      "host": "clickhouse.internal",
      "path": "main",
      "query": {
        "is_master": true
      },
      "password": "ChangeMe",
      "port": 9000,
      "host_mapped": false,
      "service": "clickhouse",
      "hostname": "azertyuiopqsdfghjklm.clickhouse.service._.eu-1.platformsh.site",
      "epoch": 0,
      "rel": "clickhouse",
      "scheme": "https",
      "type": "clickhouse:24",
      "public": false
    }

Here is an example of how to gather PLATFORM_RELATIONSHIPS environment variable information in a .environment file:

.environment
# Decode the built-in credentials object variable.
export RELATIONSHIPS_JSON=$(echo $PLATFORM_RELATIONSHIPS | base64 --decode)

# Set environment variables for individual credentials.
export APP_CLICKHOUSE_HOST=="$(echo $RELATIONSHIPS_JSON | jq -r '.clickhouse[0].host')"

Usage example Anchor to this heading

1. Configure the service Anchor to this heading

To define the service, use the clickhouse type:

.upsun/config.yaml
services:
    # The name of the service container. Must be unique within a project.
    <SERVICE_NAME>:
        type: clickhouse:<VERSION>

Note that changing the name of the service replaces it with a brand new service and all existing data is lost. Back up your data before changing the service.

2. Add the relationship Anchor to this heading

To define the relationship, use one of the following endpoints.

clickhouse endpoint Anchor to this heading

The clickhouse endpoint allows you to use the Native Protocol port (also known as ClickHouse TCP protocol). This protocol is used by ClickHouse apps and processes such as clickhouse-server, clickhouse-client, and native ClickHouse tools. It is also used for inter-server communication for distributed queries.

Use the following configuration:

.upsun/config.yaml
applications:
    # The name of the app container. Must be unique within a project.
    <APP_NAME>:
        # Relationships enable access from this app to a given service.
        relationships:
            <RELATIONSHIP_NAME>: "<SERVICE_NAME>:clickhouse"

services:
    # The name of the service container. Must be unique within a project.
    <SERVICE_NAME>:
        type: clickhouse:<VERSION>

You can define <SERVICE_NAME> and <RELATIONSHIP_NAME> as you like, but it’s best if they’re distinct. With this definition, the application container (<APP_NAME>) now has access to the service via the corresponding service environment variables.

clickhouse-http endpoint Anchor to this heading

The clickhouse-http endpoint allows you to use the HTTP API Port for HTTP requests. This protocol is used by JDBC, ODBC, and web interfaces.

Use the following configuration:

.upsun/config.yaml
applications:
    # The name of the app container. Must be unique within a project.
    <APP_NAME>:
        # Relationships enable access from this app to a given service.
        relationships:
            <RELATIONSHIP_NAME>: "<SERVICE_NAME>:clickhouse-http"

services:
    # The name of the service container. Must be unique within a project.
    <SERVICE_NAME>:
        type: clickhouse:<VERSION>

You can define <SERVICE_NAME> and <RELATIONSHIP_NAME> as you like, but it’s best if they’re distinct. With this definition, the application container (<APP_NAME>) now has access to the service via the corresponding service environment variables.

Example configuration Anchor to this heading

.upsun/config.yaml
applications:
    # The name of the app container. Must be unique within a project.
    myapp:
        # The location of the application's code.
        source:
            root: "myapp"
        # Relationships enable an app container's access to a service.
        relationships:
            clickhouse: "clickhouse:clickhouse"
services:
    # The name of the service container. Must be unique within a project.
    type: clickhouse:24
.upsun/config.yaml
applications:
    # The name of the app container. Must be unique within a project.
    myapp:
        # The location of the application's code.
        source:
            root: "myapp"
        # Relationships enable an app container's access to a service.
        relationships:
            clickhouse: "clickhouse:clickhouse-http"
services:
    # The name of the service container. Must be unique within a project.
    type: clickhouse:24

Multiple databases Anchor to this heading

You can configure multiple databases, much like with PostgreSQL. To do so, you can use a configuration similar to the following:

.upsun/config.yaml
# Complete list of all available properties: https://docs.upsun.com/create-apps/app-reference.html
applications:
  myapp:
    relationships:
      clickhouse-admin: "clickhouse:admin"
      clickhouse-reporter: "clickhouse:reporter"
      clickhouse-importer: "clickhouse:importer"

services:
  clickhouse:
    type: clickhouse:24
    configuration:
      databases:
        - main
        - legacy
      endpoints:
        admin:
          port: 9000 # binary port
          privileges:
            main: admin
            legacy: admin
        reporter:
          default_database: main
          port: 8123 # http port
          privileges:
            main: ro
        importer:
          default_database: legacy
          port: 9000 # binary port
          privileges:
            legacy: rw

Is this page helpful?