clickhouse-logger

The clickhouse-logger plugin pushes request and response logs to ClickHouse database in batches and supports the customization of log formats.

Examples

The examples below demonstrate how you can configure clickhouse-logger plugin for different scenarios.

To follow along the examples, start a sample ClickHouse server with user default and empty password:

docker run -d -p 8123:8123 -p 9000:9000 -p 9009:9009 --name clickhouse-server clickhouse/clickhouse-server

Log in the Default Log Formats

The following example demonstrates how you can log in the default request body.

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 default.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()
' --user default:

Create a route with clickhouse-logger as follows:

curl "http://127.0.0.1:9180/apisix/admin/routes" -X PUT \
  -H "X-API-KEY: ${ADMIN_API_KEY}" \
  -d '{
    "id": "clickhouse-logger-route",
    "uri": "/get",
    "plugins": {
      "clickhouse-logger": {
        "user": "default",
        "password": "",
        "database": "default",
        "logtable": "default_logs",
        "endpoint_addrs": ["http://127.0.0.1:8123"]
      }
    },
    "upstream": {
      "type": "roundrobin",
      "nodes": {
        "httpbin.org": 1
      }
    }
  }'

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

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

You should see an HTTP/1.1 200 OK response.

Send a request to ClickHouse to see the log entries:

echo 'SELECT * FROM default.default_logs FORMAT Pretty' | curl "http://127.0.0.1:8123/?" -d @-

You should see a log entry similar to the following:

┏━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ host ┃ client_ip  ┃ route_id                ┃ service_id ┃ start_time    ┃ latency         ┃ upstream_latency ┃ apisix_latency  ┃ consumer ┃ request                                                                                                                                                                  ┃ response                                                                                                                                                                                                                                                      ┃ server                                        ┃
┡━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│      │ 172.19.0.1 │ clickhouse-logger-route │            │ 1703026935235 │ 481.00018501282 │ 473              │ 8.0001850128174 │          │ {"method":"GET","uri":"/get","headers":{"host":"127.0.0.1:9080","user-agent":"curl/7.29.0","accept":"*/*"},"url":"http://127.0.0.1:9080/get","querystring":{},"size":81} │ {"headers":{"access-control-allow-credentials":"true","access-control-allow-origin":"*","content-type":"application/json","content-length":"299","date":"Tue,19 Dec 2023 23:02:15 GMT","connection":"close","server":"APISIX/3.8.0"},"status":200,"size":526} │ {"hostname":"85cf6f06914e","version":"3.8.0"} │
└──────┴────────────┴─────────────────────────┴────────────┴───────────────┴─────────────────┴──────────────────┴─────────────────┴──────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴───────────────────────────────────────────────┘

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 default.custom_logs (
    host String,
    client_ip String,
    route_id String,
    service_id String,
    `@timestamp` String, 
    PRIMARY KEY(`@timestamp`)
  )
  ENGINE = MergeTree()
' --user default:

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" -X PUT \
  -H "X-API-KEY: ${ADMIN_API_KEY}" \
  -d '{
    "id": "clickhouse-logger-route",
    "uri": "/get",
    "plugins": {
      "clickhouse-logger": {
        "user": "default",
        "password": "",
        "database": "default",
        "logtable": "custom_logs",
        "endpoint_addrs": ["http://127.0.0.1:8123"]
      }
    },
    "upstream": {
      "type": "roundrobin",
      "nodes": {
        "httpbin.org": 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}' \
  -d '{
    "log_format": {
      "host": "$host",
      "client_ip": "$remote_addr",
      "route_id": "$route_id",
      "service_id": "$service_id",
      "@timestamp": "$time_iso8601"
    }
  }'