Log with ClickHouse

APISIX can send structured request and response logs to ClickHouse for querying, analysis, and troubleshooting. The clickhouse-logger plugin batches log entries and writes them to a table whose columns match the configured log format.

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 starts a local ClickHouse instance, configures APISIX to send a custom access-log format, and verifies the stored record.

Prerequisite(s)

Install Docker.
Install cURL to send requests to the services for validation.
Follow the Getting Started tutorial to start APISIX with Docker.
To configure APISIX with ADC, install ADC.

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 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:26.8.5.13

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

docker exec -it quickstart-clickhouse-server \
  clickhouse-client \
  --user quickstart-user \
  --password quickstart-pass

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, ClickHouse returns Ok.

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.

Enable the clickhouse-logger plugin globally:

curl -i "http://127.0.0.1:9180/apisix/admin/global_rules/clickhouse" -X PUT \
  -H "Content-Type: application/json" \
  -d '{
    "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"]
      }
    }
}'

➊ log_format: Fields that correspond to columns in the ClickHouse table.

➋ user, password, database, logtable, and endpoint_addrs: Connection and destination-table settings for the local ClickHouse instance.

Create a sample route on which you will collect logs:

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

Global rules cannot be isolated with a label selector. Export the complete global-rule collection before adding this rule:

adc dump -o adc-global-rule.yaml --with-id \
  --include-resource-type global_rule

Add the clickhouse-logger entry while preserving every other global rule in the exported file:

adc-global-rule.yaml (global_rules excerpt)

global_rules:
  # Keep all other global rules from the exported file.
  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: clickhouse-howto
    routes:
      - uris:
          - /ip
        name: getting-started-ip
    upstream:
      type: roundrobin
      nodes:
        - host: httpbin.org
          port: 80
          weight: 1

Preview the complete global-rule collection and confirm that the diff contains no unintended updates or deletions:

adc diff -f adc-global-rule.yaml \
  --include-resource-type global_rule

Synchronize the reviewed global rules:

adc sync -f adc-global-rule.yaml \
  --include-resource-type global_rule

Preview the service-scoped route change:

adc diff -f adc-route.yaml \
  --include-resource-type service \
  --label-selector docs-example=clickhouse-howto

Synchronize the reviewed service configuration:

adc sync -f adc-route.yaml \
  --include-resource-type service \
  --label-selector docs-example=clickhouse-howto

Submit Logs in Batches

The clickhouse-logger plugin uses a batch processor to reduce the number of requests sent to ClickHouse.

By default, the batch processor submits data after five seconds without a new entry or when a batch reaches 1,000 entries. You can adjust the idle interval with inactive_timeout and the maximum number of entries with batch_max_size. The following configuration uses a ten-second idle interval and up to 2,000 entries per batch:

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

Update the complete global-rule configuration to set inactive_timeout to 10 seconds and batch_max_size to 2,000 entries:

adc-global-rule.yaml

global_rules:
  # Keep all other global rules from the exported file.
  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

Preview the complete global-rule collection and confirm that no unrelated rule changes are included:

adc diff -f adc-global-rule.yaml \
  --include-resource-type global_rule

Synchronize the reviewed global rules:

adc sync -f adc-global-rule.yaml \
  --include-resource-type global_rule

Verify Logging

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

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

Query the most recent record with clickhouse-client:

docker exec quickstart-clickhouse-server \
  clickhouse-client \
  --user quickstart-user \
  --password quickstart-pass \
  --query 'SELECT * FROM quickstart_db.test ORDER BY `@timestamp` DESC LIMIT 1 FORMAT PrettyCompactMonoBlock'

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 │ 192.168.155.1 │ getting-started-ip │ 2026-09-16T12:30:37+00:00 │
   └───────────┴───────────────┴─────────────────────┴───────────────────────────┘

Next Steps

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