Apache APISIX access logs to ClickHouse

Apache APISIX access logs to ClickHouse for high-volume API analytics

When gateway traffic outgrows file logs or general search tools, a platform team needs API analytics that can answer questions over large volumes of request data. APISIX can write request logs directly into a ClickHouse table through the clickhouse-logger plugin, so the gateway becomes the producer of structured analytics rows without an intermediate log shipper. ClickHouse is open source under the Apache License 2.0 (SPDX: Apache-2.0), and the design, configuration and verification of this pipeline happen inside infrastructure you control, including the ClickHouse cluster, the network path from the gateway and the table layout.

The method is straightforward in outline: the plugin is enabled on the routes or globally, it sends batches of log entries to a ClickHouse HTTP endpoint, and ClickHouse inserts them into a table designed for the queries your team actually runs, such as per-consumer request counts, latency percentiles and error rates by route. The decisions that matter most are which fields become columns, how the table is partitioned and ordered, and how long rows are retained. Confirm the exact plugin field names, default batching values and retry behavior against the APISIX plugin documentation for the release you deploy, because these are not part of the supplied facts and they change the shape of the table you create.

Before go-live, verify three things in a pre-production environment: that the endpoint, database and table settings resolve and authenticate, that batches flush at the expected size and interval under realistic request rates, and that a deliberately unavailable ClickHouse endpoint produces the retry and drop behavior you can accept. Confirm the ClickHouse release line you run with the ClickHouse releases page, and plan who owns the table schema when the gateway log format changes.

What you need before enabling clickhouse-logger

The clickhouse-logger plugin only works when the gateway, the ClickHouse server and the network path between them agree on versions, credentials and certificates. Each card below is one prerequisite to confirm before you change any route or plugin configuration.

APISIX release line and deployment mode

Confirm which APISIX release line you run and whether it is traditional, decoupled or standalone. In traditional mode the gateway listens on port 9080 for client traffic and port 9180 for the Admin API, so you know where configuration changes land. In standalone mode, plugin settings live in conf/apisix.yaml or conf/apisix.json and are hot-reloaded when the file changes, so review the file in version control before rollout. Check the release notes for the plugin version you plan to use, since the downloads page marks no release as long-term support.

Supported ClickHouse version

Choose a ClickHouse line that appears in the project's security policy as supported with security updates. Stable releases suit most production use, while LTS releases ship twice a year and receive a year of support. Read the changelog before upgrading, because minor backward-incompatible changes can occur. Ask your DBA which version your analytics team already queries, so the logging cluster does not diverge from the rest of your estate.

Reachable HTTP endpoint from the gateway

The gateway must open an HTTP connection to the ClickHouse endpoint, so confirm the network path from every APISIX node, including those in separate zones or Kubernetes clusters. Check security group rules, DNS resolution and any proxy or load balancer in between. Test the path from the gateway host itself, not from your workstation, because firewall rules often differ between the two. Note the port your ClickHouse HTTP interface uses and confirm that no egress policy blocks it.

Database user with insert rights

Create a dedicated ClickHouse user for the gateway with INSERT rights only on the target table, not on the whole database. Store its password in a secret store or an environment variable reference rather than in plain route configuration. Confirm the user can also write to the table through the HTTP interface, since a successful login alone does not prove insert permission. Review the access control documentation to decide between a local user and an external authenticator for this account.

TLS certificate handling

Decide whether the connection to ClickHouse uses HTTPS, and if so which certificate authority signs the server certificate. Gateways that trust a private CA need that CA bundle deployed to every node, and certificate renewal must update all of them together. Plan who owns the rotation and how a failed handshake shows up in the error log. ClickHouse supports SSL X.509 certificates as an external authenticator, so decide whether client certificates are part of the design before you start.

Pre-production validation before cutover

ClickHouse production guidance recommends a pre-production environment with representative data and queries before cutover. Use a non-production ClickHouse cluster and replay realistic request volumes through a test gateway. Measure insert latency, batch behavior and table growth, and confirm that analytics queries return what your team expects. Record the rollback step, which is to disable the plugin on the route, so the change can be reversed quickly if the table misbehaves.

Configuring the clickhouse-logger plugin in sequence

This sequence takes the clickhouse-logger plugin from an agreed table design to a verified test route, so each test request can be traced from the gateway to a ClickHouse row. Each step closes with a check that your platform team can repeat before any production route changes.

Agree the table schema before building
Decide which columns the analytics team needs before anyone runs a DDL statement. List each APISIX log field you plan to keep, such as the request time, route, upstream status, latency and a request ID you can search for, and note the ClickHouse data type and sort key you expect to filter on. Confirm the exact field names the plugin writes in the plugin documentation for your APISIX release. The step is done when the approved field list and sort key are recorded in your change record, so later checks compare against a fixed target.
Create the database and table
Run the CREATE DATABASE and CREATE TABLE statements first in a non-production ClickHouse environment, using a MergeTree-family table that matches the approved schema. Use an administrative account separate from the one APISIX will use. If you run a replicated cluster, decide whether the table is replicated before writing the DDL, because that choice changes the statement. Check the result with SHOW TABLES and DESCRIBE TABLE, and confirm that each column name and type matches the approved list.
Create a least-privilege ClickHouse user
Create a dedicated ClickHouse user for APISIX and grant it INSERT on the target table only. Keep administrative roles and access to other databases out of its grants. Define the grants using the access control model in the ClickHouse access control documentation, and confirm which authentication method your environment uses. Keep the password in your secrets manager and out of version control. The check is a login as this user, where an insert into the target table succeeds and a query against a system table is refused.
Set the endpoint and credentials
Add the clickhouse-logger plugin configuration with the ClickHouse endpoint, database, table and the dedicated user's credentials. In traditional deployments, apply it through the Admin API on port 9180. In standalone mode, edit the YAML or JSON configuration file, since APISIX checks that file for changes every second. The deployment modes documentation explains where each mode takes its configuration. Check the stored configuration and confirm that the endpoint and database match the values you planned.
Enable the plugin on a test route
Attach the plugin to a dedicated test route so that only your controlled requests reach the ClickHouse table during verification. Point the route at a non-production upstream that returns a known response. Apply it through the same Admin API or file workflow used in the previous step, and save a copy of the route definition for rollback. The check is a successful request to the route that returns the expected status code before any logged traffic is sent.
Send controlled test requests
Send a fixed batch of requests to the test route from a script, with a unique request ID header on each one. The batch size is set by your test plan, for example a short run of 50 requests as an illustrative figure. Include a successful response, an error response from the upstream and a request the route rejects, so you can see how each status is logged. Record the send time and the list of request IDs, because the plugin batches writes and rows can arrive after a short delay.
Verify rows and insert errors
Query the target table for the test request IDs and confirm that each sent request appears exactly once. Compare the total row count with the number of requests you sent. Repeat both checks after the batch has flushed, since rows can arrive late. Then review the ClickHouse query log in the system.query_log table for failed inserts from the APISIX user, and read any error text against the approved schema. The step is complete when IDs match, counts agree and no insert errors remain.

Mapping APISIX log fields to ClickHouse columns

This table proposes a column for each gateway log attribute, with a suggested ClickHouse type, the sort-key role it plays, and whether the value should be excluded or masked before it is stored. Use it as a starting schema for the target table, then check every source field against the log format your APISIX release produces, because field names and availability depend on the logger configuration in use. The main takeaway is that credentials and token-bearing query strings should be removed at the gateway, not cleaned up later in ClickHouse.

Gateway log attribute	Proposed ClickHouse column and type	Sort key or query role	Exclude or mask
Request timestamp	request_time, DateTime64(3)	Leading sort key and partition basis	Keep
Client address	client_ip, IPv4 or IPv6	Abuse and traffic analysis, secondary filter	Keep, or truncate per your privacy policy
HTTP method	http_method, LowCardinality(String)	Filter on endpoint behavior	Keep
Request URI path	request_path, String	Second sort key candidate after route	Keep path only, never the query string
Query string	Not stored as raw text	None	Exclude, or store only allowlisted parameter names
Response status	status_code, UInt16	Error rate queries	Keep
Latency fields (request, upstream, APISIX)	request_time_ms, upstream_time_ms, Float32 or UInt32	Percentile analysis, not a key	Keep
Upstream address	upstream_addr, String	Per-backend health views	Keep, confirm it does not expose internal hosts you consider sensitive
Route and service identifiers	route_id, LowCardinality(String)	Strong sort key candidate with consumer	Keep
Consumer identifier	consumer_name, LowCardinality(String)	Per-client usage analysis	Keep the name or ID, never the credential
User agent	user_agent, String	Client mix analysis	Keep, truncate long values
Authorization header and API keys	Not stored	None	Exclude at the source, do not rely on ClickHouse filtering

Batching, retries and what a dropped batch means for analytics

The batch settings on the clickhouse-logger plugin decide two things at once: how efficiently ClickHouse receives inserts and how many log entries you can lose during an outage. The plugin buffers entries and sends them in batches, so the batch size, the flush interval and the retry count together set the ceiling on how long an entry sits in memory and how many attempts APISIX makes before it gives up on a batch. Confirm the exact key names, defaults and retry semantics for your APISIX version in the plugin documentation before you set them, and test them against a deliberately stopped ClickHouse node in a pre-production environment.

A batch that exhausts its retries is dropped by the gateway. ClickHouse replication protects stored rows from a replica failure, but it cannot recover entries that never reached the server. For API analytics, billing or audit evidence, that gap matters, so decide before go-live whether you need a second path: a local file copy written by the gateway, a dead-letter table, or a parallel stream into Kafka or Loki. Each option has its own operating cost, and a second stream is only useful if it is also monitored and replayed. Record the decision and the acceptable loss window in your data retention documentation so that the analytics owners and the platform team share the same expectation.

Small, frequent inserts are the most common way a ClickHouse log table degrades. Each insert creates a data part, and many tiny parts increase merge work and slow queries. Measure the insert rate, the batch sizes actually received, and the active part count per table, for example by querying the system parts table, and compare them against the batch settings you chose. If part counts climb steadily, raise the batch size or flush interval within the limit you accepted for in-memory loss, rather than reducing the number of tables. Ask ClickHouse and your DeployOpen team to set alerts on part growth as well as on failed inserts at the gateway.

Securing the log path from gateway to ClickHouse

Access logs carry tokens, client identifiers and personal data, so the path from APISIX to the ClickHouse table needs controls at each hop. The cards below cover transport, database identity, secret handling, data minimization and network reach, and they should be decided together before the logger is enabled in production.

TLS with certificate verification

Send log batches to the ClickHouse HTTP interface over HTTPS and configure APISIX to verify the server certificate against your internal certificate authority, not just accept any certificate. Confirm the ClickHouse HTTPS port and certificate setup in your own build, and check how the logger handles a certificate that fails verification, since a silent fallback would send logs in the clear or to the wrong host. Test the failure path in staging.

Dedicated insert-only database user

Create a ClickHouse user used only by the gateway, with grants limited to inserting into the access log table and nothing else. Keep reporting and administrative users separate so that a leaked gateway credential cannot read other tables or change schema. Define the grants using the access control model in the ClickHouse access rights documentation, and review them whenever the table layout changes.

Credential storage outside logs

The ClickHouse password belongs in a secret store or environment reference, not in plain route or plugin configuration that is copied into backups, dashboards or support tickets. Check that the Admin API output and APISIX error logs do not echo the credential or the full endpoint URL with embedded userinfo. Rotate the credential on a schedule you can prove, and confirm with your secrets team how rotation reaches running gateways.

Masking tokens and personal data

Access logs often capture bearer tokens, API keys in query strings, email addresses and client IP addresses. Remove or hash these fields with a custom log format or upstream filter before the logger sends the record, so the table never holds raw secrets. Decide with your privacy team which fields are needed for analytics, and document the retention period for each field before you define the table columns.

Network reach to the endpoint

Restrict which hosts can open connections to the ClickHouse HTTP port, using security groups, firewall rules or Kubernetes network policies that allow only the APISIX data plane nodes and the required reporting paths. Keep the ClickHouse interface off public networks, and confirm that analytics clients use separate routes. Verify the rules with a connection test from an unapproved host.

Querying side authentication options

For people and tools that read the table, ClickHouse supports LDAP, Kerberos, SSL X.509 certificates, an HTTP authenticator and JWT as external authenticators. Choose the option that matches your identity provider, and confirm the choice with your identity team before rollout, because each option changes how analysts sign in and how access is revoked. The gateway write path should stay on its own dedicated account either way.

Common failures when logs stop reaching ClickHouse

This table maps each common failure in the APISIX to ClickHouse logging path to its likely cause, fix and a check you can run. Work through the rows in order: confirm the gateway can reach ClickHouse and authenticate first, then check the table format, batching and retry behavior.

Symptom	Likely cause	Fix	Check to run
Authentication rejected	The logger user, password or endpoint path is wrong, or the user lacks INSERT on the target table	Create a dedicated ClickHouse user with INSERT on the log table only, and store its password where APISIX can resolve it	Run a test insert with clickhouse-client as that user and confirm the same credentials work from the gateway host
Table schema does not match the posted format	The log format sends fields the table lacks, or column types cannot hold the values, such as timestamps or nested JSON	Align the table columns with the log format field names and types, or change the format to match the table	Post one sample log line in the configured format to a test table and read the ClickHouse error text
TLS verification errors	The certificate chain is not trusted by the gateway, the hostname does not match, or the endpoint serves a different certificate than expected	Install the internal CA chain on the gateway host or in the configured trust store, and use the hostname on the certificate	Run an openssl s_client handshake to the ClickHouse HTTPS port from the APISIX host and compare the subject and issuer
Batches dropped after retries	ClickHouse stayed unreachable or slow for longer than the retry budget, so the logger discarded the batch	Size the retry budget to cover normal ClickHouse restarts and maintenance, keep a second log stream such as a file or Kafka topic for loss-sensitive data, and alert on logger errors	Search the APISIX error log for logger retry and drop messages, and compare gateway request counts with ClickHouse row counts for the same window
Insert latency backs up the gateway buffer	Slow inserts from large batches, heavy merges, or a ClickHouse node with too little CPU, memory or disk I/O, so the logger queue fills	Tune batch size and flush interval against measured insert times, reduce concurrent writers, and scale or tune the ClickHouse nodes that take inserts	Read insert durations from ClickHouse query logs and watch the APISIX logger queue or error output during peak traffic
Too many small parts	Very small batches or short flush intervals create many small data parts that ClickHouse must merge in the background	Increase batch size and flush interval within your latency target, and avoid per-request inserts	Count active parts per partition in the system.parts table and check whether the count keeps rising during steady traffic
Duplicate rows after retries	A request timed out after ClickHouse accepted the insert, so the logger resent the same batch	Add a unique request identifier to each row and choose a table design or query pattern that removes duplicates, then confirm how your table engine handles identical blocks	Pick a window with a known retry, then count rows grouped by request identifier and look for values above one
No rows arrive and no errors appear	The endpoint, database or table name in the plugin configuration does not match the server, or the plugin is not enabled on the routes you expect	Correct the endpoint, database and table settings, and confirm the plugin is attached to the routes or global configuration that receive traffic	Send one request through the route, then query the target table for rows with a recent timestamp and the expected route name

Choosing a ClickHouse topology for log volume

A single ClickHouse server is the simplest topology and suits non-critical analytics, where a gap in recent logs is acceptable. There is no replica, so the data sits on one host and its disks. If that host fails, ingestion and queries stop until it is repaired or rebuilt.

No failover target: a host, disk or operating system fault interrupts log writes from APISIX until the server returns. Confirm how the logger handles retries during that window.
No Keeper requirement: ReplicatedMergeTree and ClickHouse Keeper do not apply, so there are no extra hosts to run or patch.
Backups carry the recovery burden: schedule BACKUP jobs to local disk, S3-compatible storage or Azure Blob Storage, and copy them off the host. The ClickHouse backup documentation explains the options.
Human error has no second copy: a dropped table or a bad delete exists only on this server, so backups are the sole protection against it.
Restore tests on a spare host: the backup documentation warns that a backup never test-restored will probably not restore when needed, so restore regularly to a spare server.

Ask your team how long a rebuilt server would take to accept logs again and whether the applications behind the gateway can tolerate that gap.

For production log retention, ReplicatedMergeTree tables coordinated by ClickHouse Keeper keep data available when one replica fails. A recovered replica automatically syncs missing data from a healthy one, as described in the ClickHouse replication guide. Replication protects availability, not history, so it does not replace backups.

Keeper quorum: the replication guide recommends at least three Keeper nodes, on dedicated hosts in production, with about 4 GB of RAM each. Plan these hosts separately from the data nodes.
What replication covers: a failed replica is bypassed, and missing data is resynced when it returns without manual steps.
What it does not cover: a bad DROP, a faulty pipeline writing corrupt rows or a human mistake is copied to every replica.
Backup and restore on top: run full and incremental backups to off-host storage, and test restores on a spare cluster on a set schedule.
Upgrade discipline: read the changelog before each upgrade and rehearse in a pre-production environment with representative data and queries.

Confirm who owns the Keeper hosts, their patching and monitoring, and how restore tests are scheduled and recorded.

Questions buyers ask about APISIX to ClickHouse logging

How much effort does sending APISIX logs to ClickHouse take, and what drives it?

The effort depends mostly on four things: the table schema, the expected log volume, the retention period, and the compliance review that the logged fields must pass. A schema designed around the questions analysts actually ask (routes, consumers, status codes, latency, upstream timing) takes more design time than a flat dump of every field. Volume determines batching, insert sizing and the ClickHouse cluster shape. Retention determines partitioning, TTL rules and backup scope. Each of these decisions can change the configuration of the clickhouse-logger plugin and the table definition, so they should be settled in writing before the build starts. Ask for the work to be split into design, build and verification so each part can be reviewed.

Can our own team build the APISIX to ClickHouse logging integration?

Often yes, if the team already runs APISIX and ClickHouse and has someone comfortable with both. The plugin itself is configured through the APISIX Admin API or declarative configuration, so the technical steps are not large. The harder parts are the table design, the batching and retry trade-offs under load, the access model for the logging user, and proving that restores work. Teams without recent ClickHouse operating experience usually find those parts take longer than the plugin configuration. A useful first step is to write down who owns the schema, who approves the logged fields, and who runs restore tests, then decide which of those your team can cover.

What do we need to provide for a ClickHouse logging integration?

You need a ClickHouse environment you control, a non-production environment that resembles production, and named data owners who can approve the field list. Before work starts, confirm that you can create a database, a table and a dedicated ingest user with narrow insert rights. Provide an APISIX test instance that can send traffic, and a sample of real request shapes with headers and bodies that you consider sensitive. Your security or compliance lead should state which fields may be stored and for how long. Access to your change process for both APISIX and ClickHouse is also needed, since the integration touches both systems.

How long does the integration take, and why is there no fixed figure here?

The timeline depends on scope, not on a single standard duration. A small integration with one route group, a short retention period and a simple schema is a different job from a multi-team analytics platform with several log formats, strict retention rules and an audit review. The number of environments, the number of approvers, and whether the ClickHouse cluster already exists also change the timeline. DeployOpen does not publish durations. The written scope sets the schedule, and it lists the milestones, the dependencies on your teams and the test evidence each milestone produces. Ask for those items in any proposal you receive.

What ongoing work follows the initial ClickHouse logging integration?

Three recurring tasks matter most. First, ClickHouse versions need to move along supported lines. The ClickHouse security policy lists which versions get security updates, and the production FAQ recommends rehearsing upgrades in a pre-production environment and reading the changelog first, since minor backward-incompatible changes can occur. Second, APISIX upgrades can change plugin behavior or configuration keys, so the logger settings need retesting after each upgrade. Third, backups need regular restore tests. ClickHouse documentation warns that a backup never test-restored will probably not restore properly when needed. Ask whether a support retainer covers these tasks and how the restore test results are documented.

Can we query access logs by sensitive fields such as tokens or user identifiers?

You can query by sensitive fields only if you decide to store them, and the safer default is to exclude raw credentials entirely. Authorization headers, API keys and bearer tokens should not be written to the table. Where analysts need to correlate requests with a user, a consumer name or a hashed identifier is usually enough. Decide which fields are personal data under your policies, whether the table needs column-level restrictions or separate roles for analysts, and how long those fields are kept. ClickHouse supports role-based access control, and you can confirm which options fit your deployment using the access control documentation. Review the field list with your compliance owner before the first production insert.

How is a fixed-scope quote produced for this integration?

A quote follows from a written scope. DeployOpen agrees on the scope, the deliverables and the timeline in writing before work starts, and the quote is based on that document. To get a comparable quote, provide the APISIX deployment mode and version you run, the ClickHouse version and topology, the expected log volume and retention, the list of fields to log, the compliance requirements that apply, and who will approve the design. Ask each bidder to state which items are included, such as schema design, batching and retry tuning, restore testing, documentation and handover, and which are optional. Compare proposals on that basis rather than on headline numbers.

How DeployOpen scopes and delivers the ClickHouse logging integration

DeployOpen plans and delivers the APISIX clickhouse-logger integration as a fixed-scope piece of work inside infrastructure you control. We agree the endpoint, database, table, batching and retry settings in writing, then build, test, document and hand over the result to your team.

How we scope

Confirm which APISIX routes and consumers must write access logs into ClickHouse, and which fields the table must hold
Agree the ClickHouse HTTP endpoint, database, table, user and credential handling in the written scope
Record the batching, retry and backlog limits your team expects, and where the expected delivery loss would be unacceptable

How we build

Configure the clickhouse-logger plugin on a test APISIX deployment and map the log format to the agreed table columns
Test batching and retry behavior against a ClickHouse instance holding representative volumes and query patterns
Verify that tokens, cookies and request bodies are excluded from logged fields before any configuration reaches production

How we hand over

Deliver the plugin configuration, table definition and log format as versioned files your team can review and reproduce
Document the failure modes, including what happens when ClickHouse is unreachable and how to inspect dropped batches
Walk your operators through the runbook for changing the table schema, rotating credentials and tuning batch settings

How we support

Scope ongoing support, APISIX and ClickHouse version reviews, and log pipeline checks as a retainer if you want continued help
Plan ClickHouse upgrades in a pre-production environment with representative data before changing the production cluster
Confirm with your team which backup and restore tests cover the analytics table, since replication alone does not protect against human error

Apache APISIX access logs to ClickHouse