Skip to main content

ClickHouse

Databases Analytics

Synopsis​

Creates a ClickHouse target that sends log data to a ClickHouse database server for analytics and storage. Supports batch processing and field normalization.

Schema​

- name: <string>
  description: <string>
  type: clickhouse
  pipelines: <pipeline[]>
  status: <boolean>
  properties:
    address: <string>
    port: <integer>
    secure: <boolean>
    username: <string>
    password: <string>
    database: <string>
    table: <string>
    field_format: <string>
    timeout: <integer>
    debug:
      status: <boolean>
      dont_send_logs: <boolean>

Configuration​

The following fields are used to define the target:

FieldRequiredDefaultDescription
nameYTarget name
descriptionN-Optional description
typeYMust be clickhouse
pipelinesN-Optional post-processor pipelines
statusNtrueEnable/disable the target

Connection​

FieldRequiredDefaultDescription
addressY-ClickHouse server address. A bare host name or IP address, without a scheme and without a port
portN8123ClickHouse HTTP port. Use the HTTPS port, typically 8443, together with secure: true
secureNfalseConnect over HTTPS. Must match the port the server serves TLS on
usernameN-ClickHouse user name. Left empty, the server's default user is used
passwordN-Password for that user
databaseY-ClickHouse database name
tableY-ClickHouse table name
max_bytesN67108864 (64 MB)Maximum size of a single request body or batch, in bytes
timeoutN30Seconds allowed for a single insert request, including the response
note

max_bytes caps the size of a single request body or batch. It behaves differently from max_size on the file-writing targets, and the difference is easy to get wrong:

  • An explicit max_bytes: 0 IS honoured and removes the ceiling on targets that have no ceiling of their own. The value is read with Int64, which returns what you wrote — unlike max_size, which is read with MustInt64 and silently substitutes its default for a zero. Where a target has a fixed ceiling of its own, its page says so and a zero is clamped to that ceiling instead.
  • A negative value is rejected at configuration time with max_bytes must not be negative.

The ceiling is validated when the target is built and enforced again on every batch, so a value the receiving service will refuse fails early rather than per request.

note

The target inserts over ClickHouse's HTTP interface. Set port to the HTTP port of the server, not to the port of the native protocol.

Processing​

FieldRequiredDefaultDescription
field_formatN-Data normalization format. See applicable Normalization section
note

batch_size is a Director service-level setting configured in vmetric.yml (DefaultWriteBatchSize = 1000) and cannot be overridden per target.

Scheduling​

See Scheduling and Pool Behavior for interval and cron fields shared by all targets.

Debug Options​

FieldRequiredDefaultDescription
debug.statusNfalseEnable debug logging
debug.dont_send_logsNfalseProcess logs but don't send to target (testing)

Details​

The ClickHouse target inserts log data in batches over ClickHouse's HTTP interface, on port 8123 by default, or on the HTTPS port when secure is enabled. Logs are accumulated until the batch size is reached, then sent to the server. The default batch size is defined by the service configuration, but can be overridden.

The target supports field normalization to convert log data into standard formats like Elastic Common Schema (ECS), Common Information Model (CIM), or Advanced Security Information Model (ASIM) before sending it to ClickHouse.

Prerequisites​

  1. A running ClickHouse server
  2. Its HTTP port reachable from the Director host
  3. A database and table already created in ClickHouse
  4. A user with write permissions to the specified table
note

The ClickHouse table should have a schema compatible with the log data being sent. At minimum, it should include columns for timestamp and message fields.

warning

For high-volume logging, ensure your ClickHouse server is properly configured for performance, including appropriate settings for inserts, memory usage, and disk I/O.

Examples​

Basic​

Minimum configuration for sending logs to ClickHouse:

targets:
  - name: basic_clickhouse
    type: clickhouse
    properties:
      address: "192.168.1.100"
      username: "default"
      password: "password"
      database: "logs"
      table: "system_logs"

Custom Port​

Configuration with a non-default port, here the HTTPS port with TLS enabled:

targets:
  - name: custom_port_clickhouse
    type: clickhouse
    properties:
      address: "clickhouse.example.com"
      port: 8443
      secure: true
      username: "logger"
      password: "secure_password"
      database: "logs"
      table: "application_logs"

With Normalization​

Configuration using field normalization:

targets:
  - name: normalized_clickhouse
    type: clickhouse
    properties:
      address: "clickhouse.example.com"
      username: "logger"
      password: "secure_password"
      database: "logs"
      table: "security_logs"
      field_format: "ecs"

With Pipeline​

Using a pipeline for additional log processing:

targets:
  - name: pipeline_clickhouse
    type: clickhouse
    pipelines:
      - enrich_logs
    properties:
      address: "clickhouse.example.com"
      username: "logger"
      password: "secure_password"
      database: "logs"
      table: "enriched_logs"

Secure Configuration​

Using environment variables for credentials:

targets:
  - name: secure_clickhouse
    type: clickhouse
    properties:
      address: "${CLICKHOUSE_ADDRESS}"
      username: "${CLICKHOUSE_USER}"
      password: "${CLICKHOUSE_PASSWORD}"
      database: "logs"
      table: "security_logs"

Troubleshooting​

This section covers the errors you are most likely to see with the clickhouse target, what causes each one, and how to fix it.

Where to look:

  • Director logs. Target errors are tagged with the target name and carry "Section":"SenderPool". The part after Reason: or after the last colon is the actual cause.
  • The target's connection status in the web interface. It shows the same reason as the log line.

See Target Delivery Errors for how Director logs and retries target failures.

note

The target contacts the server only when it sends a batch. A wrong port, a wrong password, a missing grant and a missing table all surface on the first flush rather than at startup, so the connection status can look healthy until data first arrives.

Which permission or setting is missing?​

A refused insert comes back as an HTTP status followed by a ClickHouse exception code. The code is the part you act on. Match it against this table first.

Error textWhat is missingWhere to fix it
HTTP 403 with Code: 516, Code: 192, Code: 193 or Code: 194A user whose name and password match username and passwordThe user definition on the ClickHouse server
HTTP 403 with Code: 195, typicallyA user whose HOST clause accepts connections from the Director hostThe user definition on the ClickHouse server
HTTP 403 with Code: 497, typicallyInsert permission on the table, for example GRANT INSERT ON logs.system_logs TO loggerThe grants of the user in username
HTTP 404 with Code: 81, typicallyThe database named in database. The target never creates oneThe ClickHouse server
HTTP 404 with Code: 60, typicallyThe table named in table. The target never creates oneThe ClickHouse server
HTTP 400 with Code: 27, or Code: 16 typicallyColumns that match the records you sendThe table definition
Code: 164, typicallyA user profile that allows per-query settings, because each insert sets input format optionsThe profile of the user in username
connection refused, or malformed HTTP responseA reachable HTTP portport, secure, and the firewall between Director and the server

"connection refused", "malformed HTTP response", or a batch rejected on the native port​

[Error] [director] [target-<target id>] [basic_clickhouse] Sender worker 3 Finalize failed on flush for target "basic_clickhouse": clickhouse insert request: Post "http://clickhouse.example.com:9000/?database=logs&query=INSERT+INTO+system_logs+FORMAT+JSONEachRow": dial tcp ...: connect: connection refused

Cause: port is not an HTTP port of the server. The target inserts over the ClickHouse HTTP interface, so a port that serves the native protocol cannot answer it. The symptom depends on what is listening.

Error textWhat it means
connect: connection refusedNothing listens on that port, or a firewall rejects the connection
malformed HTTP response "\x15\x03\x01..."A plain request reached a TLS port. Add secure: true, or use the plain HTTP port
http: server gave HTTP response to HTTPS clientsecure: true was used against the plain HTTP port. Remove secure, or use the HTTPS port
HTTP 400 with no Code: prefixThe request reached the native protocol port, which typically answers with a plain 400 telling you to use the HTTP port instead

Fix: set port to the HTTP port of the server, 8123 by default, and pair secure: true with the HTTPS port, typically 8443. Keep address a bare host name or IP address. A value such as http://clickhouse.example.com is treated as a host name and fails with dial tcp: lookup http: no such host.

Connection failures are retried until you fix them, and nothing is lost.

warning

The 400 answer from a native protocol port is the exception. Any 400 counts as a permanent rejection, so that batch is attempted four times and then given up on. Data sent to the wrong port this way is lost, not queued.

"HTTP 403" with "Code: 516"​

[Error] [director] [target-<target id>] [basic_clickhouse] Sender worker 2 Finalize failed on flush for target "basic_clickhouse": clickhouse insert failed: HTTP 403: Code: 516. DB::Exception: ... (AUTHENTICATION_FAILED)

Cause: the server refused the credentials. The text after DB::Exception: typically says that the password is incorrect or that no user of that name exists. Code: 192, Code: 193, Code: 194 and Code: 195 separate the same failure into unknown user, wrong password, password required, and host not allowed for this user.

Fix: check username and password against the user on the server, and watch for whitespace pasted along with either value. A ${VAR} or $secret{...} reference that resolves to an empty string leaves username empty, and an empty username connects as the ClickHouse default user instead, which is rarely what you intended. If the credentials are right, check that the HOST clause of the user accepts the Director host.

Nothing is lost. The batch stays queued and is retried every few seconds until the server accepts it.

"HTTP 403" with "Code: 497"​

Sender worker 5 Finalize failed on flush for target "basic_clickhouse": clickhouse insert failed: HTTP 403: Code: 497. DB::Exception: ... (ACCESS_DENIED)

Cause: the user authenticated, but it has no insert permission on the table. The message typically names the grant the server wanted, down to the individual columns.

Fix: grant insert on the exact database and table the target writes to, then wait for the next retry:

GRANT INSERT ON logs.system_logs TO logger

Nothing is lost. The batch is retried until the grant is in place.

"HTTP 404" with "Code: 60" or "Code: 81"​

Sender worker 1 Finalize failed on flush for target "basic_clickhouse": clickhouse insert failed: HTTP 404: Code: 60. DB::Exception: ... (UNKNOWN_TABLE)

Cause: the target never creates databases or tables. Code: 60 typically means the table named in table does not exist, and Code: 81 that the database named in database does not exist. A typo in either field looks exactly like an object that was never created.

Fix: create the objects, or correct database and table. Keep the database in database only. A value such as logs.system_logs in table is rejected before the target starts, because both fields accept letters, digits and underscores only. The table also needs columns that match what your pipeline emits, which for raw lines means a message column of type String and an @timestamp column.

Nothing is lost. The batch is retried until the object exists.

"record rejected by target" with "Code: 27"​

Sender worker 6 Finalize failed on flush for target "basic_clickhouse": record rejected by target: clickhouse insert failed: HTTP 400: Code: 27. DB::ParsingException: ... (CANNOT_PARSE_INPUT_ASSERTION_FAILED)
Sender worker 6 dropping natsobj:/vm-pipeline-payloads/sender.basic_clickhouse.<id> for target "basic_clickhouse" after 4 rejected flush attempts — the endpoint refuses this batch deterministically, so its records are given up on

Cause: at least one row in the batch does not fit the table. A string arrived in a numeric column, a date could not be parsed, a null arrived in a column that is not Nullable, or a record was not valid JSON. Code: 41, Code: 117 and Code: 349 typically point at the same class of problem, and Code: 16 means the column the batch writes to is not in the table at all.

warning

This is the error on this page that loses data. The rejection is permanent, so the batch is attempted four times and then given up on. One bad row takes the whole batch with it, and a batch holds up to the service batch_size records, or max_bytes of data.

Fix: align the table with the data. Add the columns your records carry, make a column Nullable where the value can be absent, and keep a String column for the message when the pipeline emits raw lines. The message typically quotes the text the server choked on and the row number inside the batch, which tells you which record to correct in the pipeline that feeds the target.

"HTTP 413", or "record size ... exceeds configured max_bytes"​

Sender worker 3 Finalize failed on flush for target "basic_clickhouse": record rejected by target: clickhouse insert failed: HTTP 413: ...
Sender worker 3 execute() failed for basic_clickhouse: record rejected by target: record size 1048612 exceeds configured max_bytes 1048576

Cause: a 413 comes from a proxy or load balancer in front of ClickHouse whose body limit is smaller than max_bytes, which defaults to 64 MB. ClickHouse itself publishes no limit on the size of an insert body. The second message is a different case: one single record is larger than max_bytes, which only happens once you set max_bytes below the size of your largest event.

Fix: for the 413, lower max_bytes under the limit of the proxy, or raise the limit of the proxy. For the oversized record, raise max_bytes, or trim the event in a pipeline before it reaches the target. max_bytes is counted on the message text, so the request body on the wire is somewhat larger than the number you set.

Both are permanent rejections. The 413 gives up on the whole batch after four attempts, and the oversized record gives up on the payload that carries it. Those records are lost.

"HTTP 429" with "Code: 202", or an "HTTP 500"​

Sender worker 7 Finalize failed on flush for target "basic_clickhouse": clickhouse insert failed: HTTP 429: Code: 202. DB::Exception: ... (TOO_MANY_SIMULTANEOUS_QUERIES)

Cause: the server is refusing the work, not the data. Code: 202 means the number of queries running at once has reached the limit of the server. The 500 answers are the same pressure from another angle: Code: 241 typically for a batch too large for the memory of the server, Code: 252 typically when merges cannot keep up with the rate of inserts, and Code: 242 or Code: 243 typically for a table in read-only mode or a disk with no space left.

Fix: send fewer and larger inserts, and give the server room. Raise the limit on concurrent queries, reduce the number of sender workers, and lower max_bytes when the server runs out of memory. For Code: 252, flush on a schedule with interval or cron as described under Scheduling and Pool Behavior, so that fewer and bigger inserts arrive.

Nothing is lost. Throttling and server errors are retried until the server accepts the batch.

"no such host", "i/o timeout", or "certificate signed by unknown authority"​

Sender worker 0 Finalize failed on flush for target "custom_port_clickhouse": clickhouse insert request: Post "https://clickhouse.example.com:8443/?database=logs&...": tls: failed to verify certificate: x509: certificate signed by unknown authority

Cause: Director cannot reach the server, or cannot trust it. The text after the URL says which step failed.

  • dial tcp: lookup clickhouse.example.com: no such host means the name does not resolve on the Director host. A scheme left in address appears here as lookup http: no such host.
  • i/o timeout, or context deadline exceeded (Client.Timeout exceeded while awaiting headers), means the traffic is dropped on the way, or the insert took longer than timeout, which defaults to 30 seconds. A large batch on a slow link fails this way on every attempt.
  • x509: certificate signed by unknown authority means the server presents a certificate from a private CA. Point tls.ca_name at that CA certificate.
  • x509: certificate is valid for ..., not ... means you connected by IP address, or under a name the certificate does not cover. Use the name in the certificate, or set tls.server_name.
  • proxyconnect tcp: ... means HTTP_PROXY or HTTPS_PROXY is set for the Director service and the proxy cannot be reached. Add the ClickHouse host to NO_PROXY.

Fix: correct address, open the port, or supply the CA certificate. For a slow link, raise timeout or lower max_bytes so that each request is smaller. Nothing is lost while this lasts, and the batch is retried until it succeeds.

"address is required", and other configuration errors​

These stop the target from starting. They appear as ValidateConfig failed for target "basic_clickhouse": ..., or as Failed to reinitialize target "basic_clickhouse" (attempt N). Reason: ... for the TLS files, and are re-checked until you correct the configuration. Nothing is sent in the meantime, and incoming data waits in the queue.

Reason textFix
address is required for clickhouse targetSet address to a bare host name or IP address
database is required for clickhouse targetSet database
table is required for clickhouse targetSet table
invalid database name "..." for clickhouse targetNames take letters, digits and underscores, and cannot start with a digit. Rename the database
invalid table name "..." for clickhouse targetThe same rule. logs.system_logs is not accepted here: put the database in database and the bare name in table
clickhouse target: max_bytes must not be negative, got ...Use a positive value, or 0 to remove the ceiling
failed to build TLS config: ...The rest of the line names the option at fault: a certificate path that is not under the service root, a file that holds no PEM certificate, an unresolvable ${VAR}, a certificate without its key, or a wrong passphrase

The target is healthy but nothing arrives in ClickHouse​

Nothing fails here, so there is no error to search for. Check the following in order.

  1. debug.dont_send_logs is enabled. Records are processed by the pipeline and then discarded before anything is buffered. No counter moves and no insert is made, while the target reports healthy. The only trace is one line at startup, and only when debug.status is enabled as well:

    Log sending is disabled for this target (basic_clickhouse). Logs are processed but not sent.

  2. The table does not declare the fields you send. Each record is inserted as JSON, and the insert asks the server to skip fields the table does not know. A record whose keys match no column typically becomes a row of column defaults rather than an error, and the target counts it as delivered. Name the columns after the keys your pipeline emits, and query the table for recent rows before assuming the data never arrived.

  3. Asynchronous inserts are acknowledged before they are written. With async_insert: true and without async_insert_wait: true, the server acknowledges the insert as soon as it is buffered. Rows that are then lost from that buffer are never reported back. Set async_insert_wait: true, or leave async_insert off.

  4. A batch was given up on. A dropped batch moves no counter of its own. Search the logs for after 4 rejected flush attempts and for dropping (giving up), and see the parsing and size entries above for the causes.

  5. Normalization failed quietly. A record whose field_format conversion fails is still sent as it stands. If the result is not valid JSON it can fail the parsing check for the whole batch, so a silent normalization problem surfaces as the drop described above.

  6. The record had an empty body. A normalized record with no message body is skipped by the encoder but still counted as delivered, so the counts can exceed the rows in the table. Drop empty records in a pipeline if the difference matters.