Skip to main content
API7.ai Logo

clickhouse-logger

The clickhouse-logger plugin sends request and response logs to ClickHouse in batches. The plugin can write the default gateway log entry or a custom format configured on the plugin instance or in plugin metadata.

Examples​

The examples below configure a current ClickHouse server and verify default and custom gateway log formats.

Local evaluation credentials

The examples use an HTTP connection and a fixed local password to keep the workflow reproducible. For production, use TLS, store the ClickHouse password in a secret manager, and keep ssl_verify enabled.

Start ClickHouse with a dedicated database and user. The Docker setup uses a dedicated network so that a running APISIX or API7 Gateway container can reach ClickHouse by container name.

Set GATEWAY_CONTAINER to the name of the running APISIX or API7 Gateway container. Create a dedicated network and connect the gateway to it:

export GATEWAY_CONTAINER=replace-with-gateway-container-name

docker network create gateway-clickhouse-net
docker network connect gateway-clickhouse-net "$GATEWAY_CONTAINER"

Start ClickHouse on the same network and bind its HTTP API to the host loopback interface:

docker run -d \
  --name clickhouse-server \
  --network gateway-clickhouse-net \
  -p 127.0.0.1:8123:8123 \
  -e CLICKHOUSE_DB=apisix_logs \
  -e CLICKHOUSE_USER=apisix_logger \
  -e CLICKHOUSE_PASSWORD=apisix-logger-pass \
  -e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
  --ulimit nofile=262144:262144 \
  clickhouse/clickhouse-server:26.8.5.13

Wait until the HTTP API is ready:

until curl -fsS -u "apisix_logger:apisix-logger-pass" \
  "http://127.0.0.1:8123/ping" | grep -q "Ok"; do
  sleep 2
done

Log Requests in the Default Format​

The following example writes the plugin's default request and response log entry to ClickHouse.

Create a table named default_logs in your ClickHouse database with columns corresponding to your log format:

curl "http://127.0.0.1:8123" -X POST -d '
  CREATE TABLE apisix_logs.default_logs (
    host String,
    client_ip String,
    route_id String,
    service_id String,
    start_time String,
    latency String,
    upstream_latency String,
    apisix_latency String,
    consumer String,
    request String,
    response String,
    server String,
    PRIMARY KEY(`start_time`)
  )
  ENGINE = MergeTree()
' -u "apisix_logger:apisix-logger-pass"

Create a route with clickhouse-logger as follows:

curl "http://127.0.0.1:9180/apisix/admin/routes/clickhouse-logger-route" -X PUT \
  -H "X-API-KEY: ${ADMIN_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
    "uri": "/anything/clickhouse",
    "plugins": {
      "clickhouse-logger": {
        "user": "apisix_logger",
        "password": "apisix-logger-pass",
        "database": "apisix_logs",
        "logtable": "default_logs",
        "endpoint_addrs": ["http://clickhouse-server:8123"]
      }
    },
    "upstream": {
      "type": "roundrobin",
      "nodes": {
        "httpbin.org:80": 1
      }
    }
  }'

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

curl -i "http://127.0.0.1:9080/anything/clickhouse"

You should see an HTTP/1.1 200 OK response.

Send a request to ClickHouse to see the log entries:

echo 'SELECT host, client_ip, route_id, start_time FROM apisix_logs.default_logs ORDER BY start_time DESC LIMIT 1 FORMAT PrettyCompactMonoBlock' | \
  curl "http://127.0.0.1:8123/?" \
  -u "apisix_logger:apisix-logger-pass" \
  --data-binary @-

You should see a log entry similar to the following:

   ┌─host─┬─client_ip─────┬─route_id────────────────┬─start_time────┐
1. │      │ 192.168.155.1 │ clickhouse-logger-route │ 1789560520546 │
   └──────┴───────────────┴─────────────────────────┴───────────────┘

Customize Log Format With Plugin Metadata​

The following example demonstrates how you can customize log format using plugin metadata.

Create a table named custom_logs in your ClickHouse database with columns corresponding to your customized log format:

curl "http://127.0.0.1:8123" -X POST -d '
  CREATE TABLE apisix_logs.custom_logs (
    host String,
    client_ip String,
    route_id String,
    service_id String,
    `@timestamp` String,
    PRIMARY KEY(`@timestamp`)
  )
  ENGINE = MergeTree()
' -u "apisix_logger:apisix-logger-pass"

Create a route with the clickhouse-logger plugin that is used to forward logs in the specified format to ClickHouse:

curl "http://127.0.0.1:9180/apisix/admin/routes/clickhouse-logger-route" -X PUT \
  -H "X-API-KEY: ${ADMIN_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
    "uri": "/anything/clickhouse",
    "plugins": {
      "clickhouse-logger": {
        "user": "apisix_logger",
        "password": "apisix-logger-pass",
        "database": "apisix_logs",
        "logtable": "custom_logs",
        "endpoint_addrs": ["http://clickhouse-server:8123"]
      }
    },
    "upstream": {
      "type": "roundrobin",
      "nodes": {
        "httpbin.org:80": 1
      }
    }
  }'

Configure plugin metadata for clickhouse-logger:

curl "http://127.0.0.1:9180/apisix/admin/plugin_metadata/clickhouse-logger" -X PUT \
  -H "X-API-KEY: ${ADMIN_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
    "log_format": {
      "host": "$host",
      "client_ip": "$remote_addr",
      "route_id": "$route_id",
      "service_id": "$service_id",
      "@timestamp": "$time_iso8601"
    }
  }'

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

curl -i "http://127.0.0.1:9080/anything/clickhouse"

You should see an HTTP/1.1 200 OK response.

Send a request to ClickHouse to see the log entries:

echo 'SELECT host, client_ip, route_id, `@timestamp` FROM apisix_logs.custom_logs ORDER BY `@timestamp` DESC LIMIT 1 FORMAT PrettyCompactMonoBlock' | \
  curl "http://127.0.0.1:8123/?" \
  -u "apisix_logger:apisix-logger-pass" \
  --data-binary @-

You should see a log entry similar to the following:

   ┌─host──────┬─client_ip─────┬─route_id────────────────┬─@timestamp────────────────┐
1. │ 127.0.0.1 │ 192.168.155.1 │ clickhouse-logger-route │ 2026-09-16T12:09:05+00:00 │
   └───────────┴───────────────┴─────────────────────────┴───────────────────────────┘