Log Requests to ClickHouse

Send a minimal Apache APISIX access-log record to a pinned local ClickHouse server, verify the stored row, and clean up.

Deployments:DockerRoles:Platform engineerTime:20 minutesImpact:Changes live traffic

Goal

Enable clickhouse-logger on one isolated Route and write an explicitly allowlisted four-field record to a controlled ClickHouse table. Validate the exact Route ID in the stored row, then remove every resource.

Access logs can contain personal data, credentials, or regulated identifiers. Keep request and response bodies disabled, allowlist only necessary fields, and apply encryption, least privilege, retention, and deletion controls before adapting this local example.

Before you begin

Complete Get APISIX with the Docker quickstart.
Install Docker, cURL, and jq.
Use a shell that supports Bash or Zsh heredocs.
Reserve enough local memory for a disposable single-node ClickHouse server. This configuration is not a production topology.

Set the endpoints and secrets without placing keys or passwords in shell history:

export APISIX_ADMIN_URL="http://127.0.0.1:9180"
export GATEWAY_URL="http://127.0.0.1:9080"
export GUIDE_DIR="$(pwd)"
read -rsp "Admin API key (press Enter for an unchanged quickstart): " APISIX_ADMIN_KEY && export APISIX_ADMIN_KEY && echo
read -rsp "Temporary ClickHouse password: " CLICKHOUSE_PASSWORD && export CLICKHOUSE_PASSWORD && echo
test -n "${CLICKHOUSE_PASSWORD}"

Protect the fixed Route, containers, and local file used by this guide:

test "$(curl -sS --connect-timeout 3 --max-time 10 -o /dev/null -w '%{http_code}' "${APISIX_ADMIN_URL}/apisix/admin/routes/clickhouse-guide-route" -H "X-API-KEY: ${APISIX_ADMIN_KEY}")" = 404 || { echo 'Stop: route clickhouse-guide-route already exists'; exit 1; }
for container in clickhouse-guide-server clickhouse-guide-upstream; do
  test -z "$(docker ps -aq --filter "name=^/${container}$")" || { echo "Stop: container ${container} already exists"; exit 1; }
done
test ! -e clickhouse-guide-nginx.conf || { echo 'Stop: clickhouse-guide-nginx.conf already exists'; exit 1; }

Steps

Step 1: Start a pinned ClickHouse server

Start a disposable ClickHouse 26.3 server on the APISIX quickstart network. No database port is published to the host:

docker run -d --name clickhouse-guide-server \
  --network apisix-quickstart-net \
  -e CLICKHOUSE_DB=apisix_guide \
  -e CLICKHOUSE_USER=apisix_guide \
  -e CLICKHOUSE_PASSWORD="${CLICKHOUSE_PASSWORD}" \
  -e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
  --ulimit nofile=262144:262144 \
  clickhouse:26.3.33.24
for attempt in {1..60}; do
  docker exec clickhouse-guide-server clickhouse-client \
    --user apisix_guide --password "${CLICKHOUSE_PASSWORD}" \
    --query 'SELECT 1' >/dev/null 2>&1 && break
  sleep 1
done
docker exec clickhouse-guide-server clickhouse-client \
  --user apisix_guide --password "${CLICKHOUSE_PASSWORD}" \
  --query 'SELECT 1' | grep -qx 1

What changed: a fresh authenticated ClickHouse database is reachable only from containers on apisix-quickstart-net.

Step 2: Create an exact log table

Create a table whose columns match the plugin's custom log_format exactly:

docker exec clickhouse-guide-server clickhouse-client \
  --user apisix_guide --password "${CLICKHOUSE_PASSWORD}" \
  --query '
    CREATE TABLE apisix_guide.access_logs (
      host String,
      client_ip String,
      route_id String,
      timestamp String
    ) ENGINE = MergeTree()
    ORDER BY (timestamp, route_id)
  '
docker exec clickhouse-guide-server clickhouse-client \
  --user apisix_guide --password "${CLICKHOUSE_PASSWORD}" \
  --query "DESCRIBE TABLE apisix_guide.access_logs FORMAT JSONEachRow" | \
  jq -s -e 'map(.name) == ["host","client_ip","route_id","timestamp"] and all(.[]; .type == "String")'

The ClickHouse schema and plugin log format must have the same fields. A missing route_id field, for example, makes the batch insert incompatible with this table.

What changed: the isolated database has one empty four-column access-log table.

Step 3: Start a controlled upstream

Create a deterministic NGINX response and start it without publishing an upstream port:

cat > clickhouse-guide-nginx.conf <<'NGINX'
events {}
http {
  access_log off;
  server {
    listen 80;
    location = /clickhouse-guide { return 200 '{"source":"clickhouse-guide-upstream"}'; }
  }
}
NGINX
docker run -d --name clickhouse-guide-upstream \
  --network apisix-quickstart-net \
  -v "${GUIDE_DIR}/clickhouse-guide-nginx.conf:/etc/nginx/nginx.conf:ro" \
  nginx:1.29.1-alpine
for attempt in {1..15}; do
  docker exec apisix-quickstart bash -c '</dev/tcp/clickhouse-guide-upstream/80' >/dev/null 2>&1 && break
  sleep 1
done
docker exec apisix-quickstart bash -c '</dev/tcp/clickhouse-guide-upstream/80'

What changed: APISIX has one controlled local endpoint for generating a log record.

Step 4: Create the logging Route

Use jq to insert the password into one authenticated fixed-ID Route. batch_max_size: 1 is for deterministic local validation, not a production throughput recommendation:

jq -n --arg password "${CLICKHOUSE_PASSWORD}" '{
  name:"clickhouse-guide-route",
  uri:"/clickhouse-guide",
  methods:["GET"],
  plugins:{
    "clickhouse-logger":{
      endpoint_addrs:["http://clickhouse-guide-server:8123"],
      database:"apisix_guide",
      logtable:"access_logs",
      user:"apisix_guide",
      password:$password,
      log_format:{host:"$host",client_ip:"$remote_addr",route_id:"$route_id",timestamp:"$time_iso8601"},
      batch_max_size:1,
      inactive_timeout:1,
      buffer_duration:10,
      max_retry_count:2,
      retry_delay:1
    }
  },
  upstream:{type:"roundrobin",nodes:{"clickhouse-guide-upstream:80":1}}
}' | \
curl -sS --connect-timeout 3 --max-time 15 \
  "${APISIX_ADMIN_URL}/apisix/admin/routes/clickhouse-guide-route" -X PUT \
  -H "X-API-KEY: ${APISIX_ADMIN_KEY}" -H 'Content-Type: application/json' \
  --data-binary @- | \
jq -e '.value.id == "clickhouse-guide-route" and .value.plugins["clickhouse-logger"].log_format.route_id == "$route_id" and .value.plugins["clickhouse-logger"].batch_max_size == 1'

APISIX encrypts the plugin's password field in etcd, but operators with Admin API read access can still retrieve effective configuration. Restrict that access and use an APISIX Secret or environment reference for long-lived production credentials.

What changed: only this Route sends an allowlisted record to the isolated ClickHouse table.

Validate

Require the controlled upstream response, then poll until ClickHouse contains exactly one matching row:

curl --fail-with-body -sS --connect-timeout 3 --max-time 10 \
  "${GATEWAY_URL}/clickhouse-guide" | \
  jq -e '. == {source:"clickhouse-guide-upstream"}'
for attempt in {1..15}; do
  test "$(docker exec clickhouse-guide-server clickhouse-client --user apisix_guide --password "${CLICKHOUSE_PASSWORD}" --query "SELECT count() FROM apisix_guide.access_logs WHERE route_id = 'clickhouse-guide-route'")" = 1 && break
  sleep 1
done
docker exec clickhouse-guide-server clickhouse-client \
  --user apisix_guide --password "${CLICKHOUSE_PASSWORD}" \
  --query "SELECT host, client_ip, route_id, timestamp FROM apisix_guide.access_logs WHERE route_id = 'clickhouse-guide-route' FORMAT JSONEachRow" | \
  jq -s -e 'length == 1 and .[0].route_id == "clickhouse-guide-route" and (.[0].host | length > 0) and (.[0].client_ip | length > 0) and (.[0].timestamp | length > 0)'

Both assertions must pass. They prove that the intended upstream responded and that the plugin inserted one row with all four allowlisted fields.

Roll back / Clean up

Delete the Route before removing its logging destination and upstream, then remove the local file and secrets from the shell:

curl --fail-with-body -sS --connect-timeout 3 --max-time 15 -X DELETE \
  "${APISIX_ADMIN_URL}/apisix/admin/routes/clickhouse-guide-route" \
  -H "X-API-KEY: ${APISIX_ADMIN_KEY}"
docker rm -f clickhouse-guide-upstream clickhouse-guide-server
rm -f clickhouse-guide-nginx.conf
unset APISIX_ADMIN_URL APISIX_ADMIN_KEY GATEWAY_URL GUIDE_DIR CLICKHOUSE_PASSWORD

Expected: the fixed Route, both containers, database and table data, local file, and shell variables are absent.

Troubleshoot

Symptom	Cause	Fix
ClickHouse does not become ready.	The image cannot start with the available memory or file-descriptor limit.	Inspect `docker logs clickhouse-guide-server`; fix the local resource limit before creating the Route.
APISIX reports an insert error.	The table columns differ from `log_format`, authentication failed, or the database endpoint is unreachable.	Re-run the exact schema assertion, verify the container network, and recreate the fixed Route from the current password.
The upstream response succeeds but the row does not appear.	The batch worker has not flushed or exhausted its retries.	Inspect APISIX errors and ClickHouse logs; keep the bounded poll and do not increase the in-memory buffer before finding the delivery failure.
Logging creates excessive load.	A batch size of one sends every entry immediately.	Size `batch_max_size`, `inactive_timeout`, `buffer_duration`, and retry limits from measured production traffic and failure budgets.

ClickHouse Logger (opens in Plugin Hub docs) — schema, batching, secret references, and encrypted fields.
APISIX Secrets — reference long-lived logger credentials without embedding them in Route payloads.
ClickHouse Docker image — official image versions and local evaluation options.