Log with ClickHouse

APISIX supports collecting route access information and recording it as logs, such as host, client IP, and request timestamp. This key information will be of great help in troubleshooting related problems.

ClickHouse is an open-source column-oriented database management system (DBMS) for online analytical processing (OLAP). It allows users to generate analytical reports such as log analytics using SQL queries in real-time.

This guide will show you how to enable the clickhouse-logger plugin to record the APISIX logs into ClickHouse databases.

Prerequisite(s)

• Install Docker.
• Install cURL to send requests to the services for validation.
• Follow the Getting Started tutorial to start a new APISIX instance in Docker.

Configure ClickHouse

Start a ClickHouse instance named quickstart-clickhouse-server with a default database quickstart_db, a default user quickstart-user and password quickstart-pass:

Docker

docker run -d \
  --name quickstart-clickhouse-server \
  --network=apisix-quickstart-net \
  -e CLICKHOUSE_DB=quickstart_db \
  -e CLICKHOUSE_USER=quickstart-user \
  -e CLICKHOUSE_PASSWORD=quickstart-pass \
  -e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
  --ulimit nofile=262144:262144 \
  clickhouse/clickhouse-server

Connect to the ClickHouse instance using the command line tool clickhouse-client in Docker:

docker exec -it quickstart-clickhouse-server clickhouse-client

Kubernetes

Install ClickHouse Operator:

IC_NAMESPACE=replace-with-your-namespace
curl -s "https://raw.githubusercontent.com/Altinity/clickhouse-operator/master/deploy/operator-web-installer/clickhouse-operator-install.sh" | OPERATOR_NAMESPACE=$IC_NAMESPACE bash

Create a manifest file for a simple 1 shard 1 replica installation:

clickhouse-installation.yaml

apiVersion: clickhouse.altinity.com/v1
kind: ClickHouseInstallation
metadata:
  namespace: ingress-apisix
  name: quickstart-clickhouse-server
spec:
  configuration:
    users:
      quickstart-user/password_sha256_hex: c019743ba8bc32d17800bca6d36de903feff9854897c61b6e63ce3e5e93c37ca
      quickstart-user/password: quickstart-pass
      quickstart-user/networks/ip:
        - 0.0.0.0/0
    clusters:
      - name: simple

Apply the configuration to your cluster:

kubectl apply -f clickhouse-installation.yaml

Check running pods:

kubectl get pod

You should see the ClickHouse server pod running:

NAME                                                         READY   STATUS    RESTARTS   AGE
chi-quickstart-clickhouse-server-simple-0-0-0                1/1     Running   0          11m

Connect to the ClickHouse instance:

kubectl exec -it chi-quickstart-clickhouse-server-simple-0-0-0 -- clickhouse-client

Create a database:

CREATE DATABASE quickstart_db

Create a table test in database quickstart_db with fields host, client_ip, route_id, @timestamp of String type, or adjust the command accordingly based on your needs:

CREATE TABLE quickstart_db.test (
  `host` String,
  `client_ip` String,
  `route_id` String,
  `@timestamp` String,
   PRIMARY KEY(`@timestamp`)
) ENGINE = MergeTree()

If successful, you should see Ok on the output.

Enter exit to exit the command line interface in Docker.

Enable clickhouse-logger Plugin

Enable the clickhouse-logger plugin globally. Alternatively, you can enable the plugin on a route.

Admin API

Enable the clickhouse-logger plugin globally:

curl -i "http://127.0.0.1:9180/apisix/admin/global_rules" -X PUT -d '
{
  "id": "clickhouse",
  "plugins": {
    "clickhouse-logger": {
      "log_format": {
        "host": "$host",
        "@timestamp": "$time_iso8601",
        "client_ip": "$remote_addr"
      },
      "user": "quickstart-user",
      "password": "quickstart-pass",
      "database": "quickstart_db",
      "logtable": "test",
      "endpoint_addrs": ["http://quickstart-clickhouse-server:8123"]
    }
  }
}'

➊ Specify fields corresponding to the ClickHouse table in the log format

➋ ClickHouse server information

Create a sample route on which you will collect logs:

curl -i "http://127.0.0.1:9180/apisix/admin/routes" -X PUT -d '
{
  "id": "getting-started-ip",
  "uri": "/ip",
  "upstream": {
    "type": "roundrobin",
    "nodes": {
      "httpbin.org:80": 1
    }
  }
}'

ADC

Enable the clickhouse-logger plugin globally:

adc-global-rule.yaml

global_rules:
  clickhouse-logger:
    log_format:
      host: "$host"
      "@timestamp": "$time_iso8601"
      client_ip: "$remote_addr"
    user: "quickstart-user"
    password: "quickstart-pass"
    database: "quickstart_db"
    logtable: "test"
    endpoint_addrs:
      - "http://quickstart-clickhouse-server:8123"

➊ Specify fields corresponding to the ClickHouse table in the log format.

➋ ClickHouse server information.

Create a sample route on which you will collect logs:

adc-route.yaml

services:
  - name: httpbin Service
    routes:
      - uris:
          - /ip
        name: getting-started-ip
    upstream:
      type: roundrobin
      nodes:
        - host: httpbin.org
          port: 80
          weight: 1

Synchronize the configuration to APISIX:

adc sync -f adc-global-rule.yaml -f adc-route.yaml

Ingress Controller

Gateway API

Create a Kubernetes manifest file to enable clickhouse-logger globally:

global-clickhouse.yaml

apiVersion: apisix.apache.org/v1alpha1
kind: GatewayProxy
metadata:
  namespace: ingress-apisix
  name: apisix-config
spec:
  plugins:
  - name: clickhouse-logger
    enabled: true
    config:
      log_format:
        host: "$host"
        "@timestamp": "$time_iso8601"
        client_ip: "$remote_addr"
      user: "quickstart-user"
      password: "quickstart-pass"
      database: "quickstart_db"
      logtable: "test"
      endpoint_addrs:
        - "http://clickhouse-quickstart-clickhouse-server:8123"

➊ Specify fields corresponding to the ClickHouse table in the log format.

➋ ClickHouse server information.

Create another Kubernetes manifest file for a sample route on which you will collect logs:

httpbin-route.yaml

apiVersion: v1
kind: Service
metadata:
  namespace: ingress-apisix
  name: httpbin-external-domain
spec:
  type: ExternalName
  externalName: httpbin.org
---
apiVersion: gateway.networking.k8s.io/v1
kind: HTTPRoute
metadata:
  namespace: ingress-apisix
  name: getting-started-ip
spec:
  parentRefs:
  - name: apisix
  rules:
  - matches:
    - path:
        type: Exact
        value: /ip
    backendRefs:
    - name: httpbin-external-domain
      port: 80

APISIX CRD

Create a Kubernetes manifest file to enable clickhouse-logger globally:

global-clickhouse.yaml

apiVersion: apisix.apache.org/v2
kind: ApisixGlobalRule
metadata:
  namespace: ingress-apisix
  name: global-clickhouse
spec:
  ingressClassName: apisix
  plugins:
    - name: clickhouse-logger
      enable: true
      config:
        log_format:
          host: "$host"
          "@timestamp": "$time_iso8601"
          client_ip: "$remote_addr"
        user: "quickstart-user"
        password: "quickstart-pass"
        database: "quickstart_db"
        logtable: "test"
        endpoint_addrs:
          - "http://clickhouse-quickstart-clickhouse-server:8123"

➊ Specify fields corresponding to the ClickHouse table in the log format.

➋ ClickHouse server information.

Create another Kubernetes manifest file for a sample route on which you will collect logs:

httpbin-route.yaml

apiVersion: apisix.apache.org/v2
kind: ApisixUpstream
metadata:
  namespace: ingress-apisix
  name: httpbin-external-domain
spec:
  externalNodes:
  - type: Domain
    name: httpbin.org
---
apiVersion: apisix.apache.org/v2
kind: ApisixRoute
metadata:
  namespace: ingress-apisix
  name: getting-started-ip
spec:
  ingressClassName: apisix
  http:
    - name: getting-started-ip
      match:
        paths:
          - /ip
      upstreams:
      - name: httpbin-external-domain

Apply the configuration to your cluster:

kubectl apply -f global-clickhouse.yaml -f httpbin-route.yaml

Submit Logs in Batches

The clickhouse-logger plugin supports using a batch processor to aggregate and process logs in batches. This avoids frequent submissions of log entries to ClickHouse, which slows down the operations.

By default, the batch processor submits data every 5 seconds or when the data size in a batch reaches 1000 KB. You can adjust the time interval of submission inactive_timeout and maximum batch size batch_max_size for the plugin. For example, this is how you can set inactive_timeout to 10 seconds and batch_max_size to 2000 KB:

Admin API

curl -i "http://127.0.0.1:9180/apisix/admin/global_rules/clickhouse" -X PATCH -d '
{
  "plugins": {
    "clickhouse-logger": {
      "batch_max_size": 2000,
      "inactive_timeout": 10
    }
  }
}'

ADC

Update your global rule configuration to set inactive_timeout to 10 seconds and batch_max_size to 2000 KB:

adc-global-rule.yaml

global_rules:
  clickhouse-logger:
    log_format:
      host: "$host"
      "@timestamp": "$time_iso8601"
      client_ip: "$remote_addr"
    user: "quickstart-user"
    password: "quickstart-pass"
    database: "quickstart_db"
    logtable: "test"
    endpoint_addrs:
      - "http://quickstart-clickhouse-server:8123"
    batch_max_size: 2000
    inactive_timeout: 10

Synchronize the configurations to APISIX:

adc sync -f adc-global-rule.yaml -f adc-route.yaml

Ingress Controller

Gateway API

global-clickhouse.yaml

apiVersion: apisix.apache.org/v1alpha1
kind: GatewayProxy
metadata:
  namespace: ingress-apisix
  name: apisix-config
spec:
  plugins:
  - name: clickhouse-logger
    enabled: true
    config:
      log_format:
        host: "$host"
        "@timestamp": "$time_iso8601"
        client_ip: "$remote_addr"
      user: "quickstart-user"
      password: "quickstart-pass"
      database: "quickstart_db"
      logtable: "test"
      endpoint_addrs:
        - "http://clickhouse-quickstart-clickhouse-server:8123"
      batch_max_size: 2000
      inactive_timeout: 10

APISIX CRD

global-clickhouse.yaml

apiVersion: apisix.apache.org/v2
kind: ApisixGlobalRule
metadata:
  namespace: ingress-apisix
  name: global-clickhouse
spec:
  ingressClassName: apisix
  plugins:
    - name: clickhouse-logger
      enable: true
      config:
        log_format:
          host: "$host"
          "@timestamp": "$time_iso8601"
          client_ip: "$remote_addr"
        user: "quickstart-user"
        password: "quickstart-pass"
        database: "quickstart_db"
        logtable: "test"
        endpoint_addrs:
          - "http://clickhouse-quickstart-clickhouse-server:8123"
        batch_max_size: 2000
        inactive_timeout: 10

Verify Logging

Send a request to the route to generate an access log entry:

curl -i "http://127.0.0.1:9080/ip"

Docker

Connect to the ClickHouse instance using the command line tool clickhouse-client in Docker:

docker exec -it quickstart-clickhouse-server clickhouse-client

Kubernetes

Connect to the ClickHouse database:

kubectl exec -it chi-quickstart-clickhouse-server-simple-0-0-0 -- clickhouse-client

Query all records in table quickstart_db.test:

SELECT * from quickstart_db.test

You should see an access record similar to the following, which verifies that the clickhouse-logger plugin works as intended.

   ┌─host──────┬─client_ip─┬─route_id─┬─@timestamp────────────────┐
1. │ 127.0.0.1 │ 127.0.0.1 │ 5e835ead │ 2025-08-12T09:17:04+00:00 │
   └───────────┴───────────┴──────────┴───────────────────────────┘

Next Steps

See clickhouse-logger plugin doc to learn more about the plugin configuration options.