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
Kubernetes

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

Create a Kubernetes manifest file for the ClickHouse deployment:

clickhouse-deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  namespace: aic
  name: clickhouse-server
spec:
  replicas: 1
  selector:
    matchLabels:
      app: clickhouse-server
  template:
    metadata:
      labels:
        app: clickhouse-server
    spec:
      containers:
      - name: clickhouse-server
        image: clickhouse/clickhouse-server
        ports:
        - containerPort: 8123
        - containerPort: 9000
        - containerPort: 9009

Create a Kubernetes manifest file for the ClickHouse service:

clickhouse-service.yaml
apiVersion: v1
kind: Service
metadata:
  namespace: aic
  name: clickhouse-server
spec:
  selector:
    app: clickhouse-server
  ports:
  - name: http
    port: 8123
    targetPort: 8123
  - name: native
    port: 9000
    targetPort: 9000
  type: ClusterIP

Apply the manifests:

kubectl apply -f clickhouse-deployment.yaml -f clickhouse-service.yaml

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:

Docker
Kubernetes

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:

kubectl exec -n aic deploy/clickhouse-server -- clickhouse-client --query "
  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()
"

Create a route with clickhouse-logger as follows:

Admin API
ADC
Ingress Controller

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

adc.yaml
services:
  - name: httpbin
    routes:
      - uris:
          - /get
        name: clickhouse-logger-route
        plugins:
          clickhouse-logger:
            user: default
            password: ""
            database: default
            logtable: default_logs
            endpoint_addrs:
              - "http://127.0.0.1:8123"
    upstream:
      type: roundrobin
      nodes:
        - host: httpbin.org
          port: 80
          weight: 1

Synchronize the configuration to the gateway:

adc sync -f adc.yaml

Gateway API
APISIX CRD

clickhouse-logger-ic.yaml
apiVersion: v1
kind: Service
metadata:
  namespace: aic
  name: httpbin-external-domain
spec:
  type: ExternalName
  externalName: httpbin.org
---
apiVersion: apisix.apache.org/v1alpha1
kind: PluginConfig
metadata:
  namespace: aic
  name: clickhouse-logger-plugin-config
spec:
  plugins:
    - name: clickhouse-logger
      config:
        user: default
        password: ""
        database: default
        logtable: default_logs
        endpoint_addrs:
          - "http://clickhouse-server.aic.svc:8123"
---
apiVersion: gateway.networking.k8s.io/v1
kind: HTTPRoute
metadata:
  namespace: aic
  name: clickhouse-logger-route
spec:
  parentRefs:
    - name: apisix
  rules:
    - matches:
        - path:
            type: Exact
            value: /get
      filters:
        - type: ExtensionRef
          extensionRef:
            group: apisix.apache.org
            kind: PluginConfig
            name: clickhouse-logger-plugin-config
      backendRefs:
        - name: httpbin-external-domain
          port: 80

clickhouse-logger-ic.yaml
apiVersion: apisix.apache.org/v2
kind: ApisixUpstream
metadata:
  namespace: aic
  name: httpbin-external-domain
spec:
  ingressClassName: apisix
  externalNodes:
  - type: Domain
    name: httpbin.org
---
apiVersion: apisix.apache.org/v2
kind: ApisixRoute
metadata:
  namespace: aic
  name: clickhouse-logger-route
spec:
  ingressClassName: apisix
  http:
    - name: clickhouse-logger-route
      match:
        paths:
          - /get
        methods:
          - GET
      upstreams:
        - name: httpbin-external-domain
      plugins:
        - name: clickhouse-logger
          config:
            user: default
            password: ""
            database: default
            logtable: default_logs
            endpoint_addrs:
              - "http://clickhouse-server.aic.svc:8123"

Apply the configuration:

kubectl apply -f clickhouse-logger-ic.yaml

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:

Docker
Kubernetes

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:

kubectl exec -n aic deploy/clickhouse-server -- clickhouse-client --query "
  CREATE TABLE default.custom_logs (
    host String,
    client_ip String,
    route_id String,
    service_id String,
    \`@timestamp\` String,
    PRIMARY KEY(\`@timestamp\`)
  )
  ENGINE = MergeTree()
"

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

Admin API
ADC
Ingress Controller

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

adc.yaml
services:
  - name: httpbin
    routes:
      - uris:
          - /get
        name: clickhouse-logger-route
        plugins:
          clickhouse-logger:
            user: default
            password: ""
            database: default
            logtable: custom_logs
            endpoint_addrs:
              - "http://127.0.0.1:8123"
    upstream:
      type: roundrobin
      nodes:
        - host: httpbin.org
          port: 80
          weight: 1

Synchronize the configuration to the gateway:

adc sync -f adc.yaml

Gateway API
APISIX CRD

clickhouse-logger-ic.yaml
apiVersion: v1
kind: Service
metadata:
  namespace: aic
  name: httpbin-external-domain
spec:
  type: ExternalName
  externalName: httpbin.org
---
apiVersion: apisix.apache.org/v1alpha1
kind: PluginConfig
metadata:
  namespace: aic
  name: clickhouse-logger-plugin-config
spec:
  plugins:
    - name: clickhouse-logger
      config:
        user: default
        password: ""
        database: default
        logtable: custom_logs
        endpoint_addrs:
          - "http://clickhouse-server.aic.svc:8123"
---
apiVersion: gateway.networking.k8s.io/v1
kind: HTTPRoute
metadata:
  namespace: aic
  name: clickhouse-logger-route
spec:
  parentRefs:
    - name: apisix
  rules:
    - matches:
        - path:
            type: Exact
            value: /get
      filters:
        - type: ExtensionRef
          extensionRef:
            group: apisix.apache.org
            kind: PluginConfig
            name: clickhouse-logger-plugin-config
      backendRefs:
        - name: httpbin-external-domain
          port: 80

clickhouse-logger-ic.yaml
apiVersion: apisix.apache.org/v2
kind: ApisixUpstream
metadata:
  namespace: aic
  name: httpbin-external-domain
spec:
  ingressClassName: apisix
  externalNodes:
  - type: Domain
    name: httpbin.org
---
apiVersion: apisix.apache.org/v2
kind: ApisixRoute
metadata:
  namespace: aic
  name: clickhouse-logger-route
spec:
  ingressClassName: apisix
  http:
    - name: clickhouse-logger-route
      match:
        paths:
          - /get
        methods:
          - GET
      upstreams:
        - name: httpbin-external-domain
      plugins:
        - name: clickhouse-logger
          config:
            user: default
            password: ""
            database: default
            logtable: custom_logs
            endpoint_addrs:
              - "http://clickhouse-server.aic.svc:8123"

Apply the configuration:

kubectl apply -f clickhouse-logger-ic.yaml

Configure plugin metadata for clickhouse-logger:

Admin API
ADC
Ingress Controller

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

adc.yaml
plugin_metadata:
  - name: clickhouse-logger
    log_format:
      host: "$host"
      client_ip: "$remote_addr"
      route_id: "$route_id"
      service_id: "$service_id"
      "@timestamp": "$time_iso8601"

Synchronize the configuration to the gateway:

adc sync -f adc.yaml

clickhouse-logger-metadata.yaml
apiVersion: apisix.apache.org/v1alpha1
kind: GatewayProxy
metadata:
  namespace: aic
  name: apisix-config
spec:
  provider:
    type: ControlPlane
    controlPlane:
      service:
        name: apisix-admin
        port: 9180
      auth:
        type: AdminKey
        adminKey:
          value: edd1c9f034335f136f87ad84b625c8f1
  pluginMetadata:
    clickhouse-logger:
      log_format:
        host: "$host"
        client_ip: "$remote_addr"
        route_id: "$route_id"
        service_id: "$service_id"
        "@timestamp": "$time_iso8601"

Apply the configuration:

kubectl apply -f clickhouse-logger-metadata.yaml

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.custom_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 ┃ @timestamp                ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ 127.0.0.1 │ 172.19.0.1 │ clickhouse-logger-route │            │ 2023-12-19T23:25:43+00:00 │
└───────────┴────────────┴─────────────────────────┴────────────┴───────────────────────────┘

Examples​

Log in the Default Log Formats​

Customize Log Format With Plugin Metadata​

Examples

Log in the Default Log Formats

Customize Log Format With Plugin Metadata