Clickhouse

An OLAP database deployed with docker compose

Keywords

clickhouse, docker, compose, nginx, ldap, olap

Description

Clickhouse is a OLAP Data Warehouse.

Deployment

It has many options for deployment. I wil use docker compose.

Requirements

• TLS certs or a reverse proxy configured for TLS termination.
• If using LDAP: an LDAP server.

Docker Compose

services:
  clickhouse-server:
    restart: unless-stopped
    image: clickhouse/clickhouse-server:25.3
    container_name: clickhouse-server
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    volumes:
      - ./etc:/etc/clickhouse-server
      - ./data:/var/lib/clickhouse
      - ./logs:/var/log/clickhouse-server
    network_mode: host
    cap_add:
      - SYS_NICE
      - NET_ADMIN
      - IPC_LOCK
      - SYS_PTRACE
    environment:
      CLICKHOUSE_DB: default
      CLICKHOUSE_USER: admin
      CLICKHOUSE_PASSWORD: <ADMIN_PASSWORD>
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1

• The contents of ./etc should be accquired by running a separate clickhouse container and using docker cp to get the /etc/clickhouse-server folder from inside the container.

Proxy Configuration

Nginx

map $http_upgrade $connection_upgrade_keepalive {
    default upgrade;
    ''      '';
}

server {
    # HTTP server config
    listen 80;
    listen [::]:80;
    server_name clickhouse.ddole.net;
    # 301 redirect to HTTPS
    return 301 https://$host$request_uri;
}
server {
    # HTTPS server config
    listen 443 ssl;
    listen [::]:443 ssl;
    server_name clickhouse.ddole.net;

    # TLS certificates
    ssl_certificate /etc/letsencrypt/live/ddole.net/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/ddole.net/privkey.pem;
    add_header Strict-Transport-Security "max-age=63072000" always;

    client_max_body_size 0;
    proxy_request_buffering off;
    proxy_buffering off;

    location / {
        proxy_pass http://clickhouse-http;
        proxy_http_version 1.1;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header Host $http_host;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection $connection_upgrade_keepalive;
    }
}

Configuration

SSO

I use the following block inside clickhouse’s config.xml to connect to my authentik LDAP outpost, using TLS and a self-signed cert. The bind DN can be found in an authentik ldap provider’s page.

<ldap_servers>
    <authentik>
        <host>ldap.ddole.net</host>
        <port>636</port>
        <bind_dn>cn={user_name},ou=users,dc=ldap,dc=ddole,dc=net</bind_dn>
        <enable_tls>yes</enable_tls>
        <tls_require_cert>allow</tls_require_cert>
    </authentik>
</ldap_servers>

Authentik groups are mapped to clickhouse roles in the following way: all required authentik groups are prefixed with data-: Data Engineers are in the data-engineers group. Analysts are in data-analysts. There are two roles in clickhouse: engineers and analysts. These roles must be created in clickhouse using SQL.

<user_directories>
    <!-- OTHER STUFF HERE -->
    <ldap>
        <server>authentik</server>
        <roles>
            <analysts/>
            <engineers/>
        </roles>
        <role_mapping>
        <base_dn>dc=ldap,dc=ddole,dc=net</base_dn>
        <search_filter>
            (&amp;(objectClass=groupOfUniqueNames)(member={bind_dn}))
        </search_filter>
        <prefix>data-</prefix>
        <attribute>cn</attribute>
        </role_mapping>
    </ldap>
</user_directories>