# 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](https://clickhouse.com/) 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)[​](#prerequisites "Direct link to Prerequisite(s)")

* Install [Docker](https://docs.docker.com/get-docker/).
* Install [cURL](https://curl.se/) to send requests to the services for validation.
* Follow the [Getting Started tutorial](https://docs.api7.ai/apisix/getting-started.md) to start APISIX with Docker.
* To configure APISIX with ADC, install [ADC](https://docs.api7.ai/apisix/reference/adc.md).
* If you use the Admin API examples and authentication is enabled, export an admin-role key as `ADMIN_API_KEY`. Configure the key under `deployment.admin.admin_key`. The Docker quickstart disables authentication for local evaluation; keep it enabled in production.

## Configure ClickHouse[​](#configure-clickhouse "Direct link to 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
```

Wait for the normal server listener rather than the temporary loopback initialization server:

```
for attempt in $(seq 1 60); do

  if docker exec quickstart-clickhouse-server clickhouse-client \

    --host quickstart-clickhouse-server --user quickstart-user \

    --password quickstart-pass --query "SELECT 1" >/dev/null 2>&1; then

    break

  fi

  sleep 1

done

docker exec quickstart-clickhouse-server clickhouse-client \

  --host quickstart-clickhouse-server --user quickstart-user \

  --password quickstart-pass --query "SELECT 1"
```

Once the server responds, connect with `clickhouse-client`:

```
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-clickhouse-logger-plugin "Direct link to enable-clickhouse-logger-plugin")

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

* Admin API
* ADC

Enable the `clickhouse-logger` plugin globally:

```
curl -i "http://127.0.0.1:9180/apisix/admin/global_rules/clickhouse" -X PUT \

  -H "X-API-KEY: ${ADMIN_API_KEY}" \

  -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 "X-API-KEY: ${ADMIN_API_KEY}" \

  -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[​](#submit-logs-in-batches "Direct link to 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:

* Admin API
* ADC

```
curl -i "http://127.0.0.1:9180/apisix/admin/global_rules/clickhouse" -X PATCH \

  -H "X-API-KEY: ${ADMIN_API_KEY}" \

  -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[​](#verify-logging "Direct link to 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[​](#next-steps "Direct link to Next Steps")

See [`clickhouse-logger`](https://docs.api7.ai/hub/clickhouse-logger.md) plugin doc to learn more about the plugin configuration options.