Skip to main content
API7.ai Logo
Version: 3.19.0

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
      }
    }
}'

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
      }
    }
}'

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.