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 in 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

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 from the command line interface in Docker.

Enable clickhouse-logger Plugin

Create a route:

Admin API

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

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-route.yaml

Enable the clickhouse-logger plugin globally for all requests, or on a specific route:

Global

You can configure APISIX through the Admin API or ADC:

Admin API

Enable the clickhouse-logger plugin on all routes:

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 all fields corresponding to the ClickHouse table in the log format

➋ ClickHouse server information

An HTTP/1.1 201 Created response verifies that the clickhouse-logger plugin is enabled successfully.

ADC

Enable the clickhouse-logger plugin on all routes:

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 all fields corresponding to the ClickHouse table in the log format

➋ ClickHouse server information

Synchronize the configurations to APISIX:

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

Route

You can configure APISIX through the Admin API or ADC:

Admin API

Enable the clickhouse-logger plugin on a specific route:

curl -i "http://127.0.0.1:9180/apisix/admin/routes/getting-started-ip" -X PATCH -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"]
    }
  }
}'

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

➋ ClickHouse server information

An HTTP/1.1 200 OK response verifies that the clickhouse-logger plugin is enabled successfully.

ADC

Update the route configuration to enable the clickhouse-logger plugin:

adc-route.yaml

services:
  - name: httpbin Service
    routes:
      - uris:
          - /ip
        name: getting-started-ip
        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"
    upstream:
        type: roundrobin
        nodes:
          - host: httpbin.org
            port: 80
            weight: 1

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

➋ ClickHouse server information

Synchronize the configuration to APISIX:

adc sync -f adc-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:

Global

You can configure APISIX through the Admin API or ADC:

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

Route

You can configure APISIX through the Admin API or ADC:

Admin API

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

ADC

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

adc-route.yaml

services:
  - name: httpbin Service
    routes:
      - uris:
          - /ip
        name: getting-started-ip
        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"
            batch_max_size: 2000
            inactive_timeout: 10
    upstream:
        type: roundrobin
        nodes:
          - host: httpbin.org
            port: 80
            weight: 1

Synchronize the configurations to APISIX:

adc sync -f adc-route.yaml

Verify Logging

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

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

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

docker exec -it quickstart-clickhouse-server 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 clickhouse-logger plugin works as intended.

┌─host──────┬─client_ip──┬─route_id───────────┬─@timestamp────────────────┐
│ 127.0.0.1 │ 172.18.0.1 │ getting-started-ip │ 2023-06-07T15:28:24+00:00 │
└───────────┴────────────┴────────────────────┴───────────────────────────┘

Next Steps

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