ClickHouse database

Starting with version 4.9.0, AxoSyslog can send data to ClickHouse databases using its gRPC interface.

Prerequisites

  • A self-hosted ClickHouse installation.

  • The gRPC interface must be enabled in your ClickHouse configuration.

  • To configure AxoSyslog, you’ll need:

    • the name of an existing database and a table where you want to send your data, and
    • the credentials (username and password) to access the database.

Example configuration (sends data to the default localhost:9100 URL):

destination {
  clickhouse(
    database("default")
    table("demo_table")
    user("your-username")
    password("your-password")
    schema(
      "user_id" UInt32 => $R_MSEC,
      "message" String => "$MSG",
      "timestamp" DateTime => "$R_UNIXTIME",
      "metric" Float32 => 3.14
    )
  );
};

Options

This destination has the following options:

auth()

You can set authentication in the auth() option of the driver. By default, authentication is disabled (auth(insecure())).

The following authentication methods are available in the auth() block:

adc()

Application Default Credentials (ADC). This authentication method is only available for destinations.

alts()

Application Layer Transport Security (ALTS) is a simple to use authentication, only available within Google’s infrastructure. It accepts the target-service-account() option, where you can list service accounts to match against when authenticating the server.

source {
    opentelemetry(
      port(4317)
      auth(alts())
    );
  };
destination {
    loki(
      port(12345)
      auth(alts())
    );
  };
source {
    syslog-ng-otlp(
      port(4317)
      auth(alts())
    );
  };

insecure()

This is the default method, authentication is disabled (auth(insecure())).

tls()

tls() accepts the key-file(), cert-file(), ca-file() and peer-verify() (possible values: required-trusted, required-untrusted, optional-trusted and optional-untrusted) options.

destination {
    opentelemetry(
      url("your-otel-server:12346")
      auth(
        tls(
          ca-file("/path/to/ca.pem")
          key-file("/path/to/key.pem")
          cert-file("/path/to/cert.pem")
        )
      )
    );
  };
destination {
    loki(
      url("your-loki-server:12346")
      auth(
        tls(
          ca-file("/path/to/ca.pem")
          key-file("/path/to/key.pem")
          cert-file("/path/to/cert.pem")
        )
      )
    );
  };
destination {
    syslog-ng-otlp(
      url("your-otel-server:12346")
      auth(
        tls(
          ca-file("/path/to/ca.pem")
          key-file("/path/to/key.pem")
          cert-file("/path/to/cert.pem")
        )
      )
    );
  };

Note:

  • tls(peer-verify()) is not available for the opentelemetry() and loki() destination.
  • The gRPC-based drivers (opentelemetry() and loki()) have a different tls() block implementation from the network() or http() drivers. Most features are the same.

batch-bytes()

Accepted values: number [bytes]
Default: 4MB

Available in AxoSyslog version 4.6 and later.

Description: Sets the maximum size of payload in a batch. If the size of the messages reaches this value, AxoSyslog sends the batch to the destination even if the number of messages is less than the value of the batch-lines() option. The batch might be at most 1 message larger than the set limit.

Note that if the batch-timeout() option is enabled and the queue becomes empty, AxoSyslog flushes the messages only if batch-timeout() expires, or the batch reaches the limit set in batch-bytes().

OTLP has a default 4 MiB batch limit, therefore the default value for batch-bytes() is 4 MB, which is a bit below 4 MiB.

The batch size is calculated before compression, which is the same as the limit is calculated on the server.

batch-lines()

Type: number
Default: 0

Description: Specifies how many lines are flushed to a destination in one batch. The AxoSyslog application waits for this number of lines to accumulate and sends them off in a single batch. Increasing this number increases throughput as more messages are sent in a single batch, but also increases message latency.

For example, if you set batch-lines() to 100, AxoSyslog waits for 100 messages.

If the batch-timeout() option is disabled, the AxoSyslog application flushes the messages if it has sent batch-lines() number of messages, or the queue became empty. If you stop or reload AxoSyslog or in case of network sources, the connection with the client is closed, AxoSyslog automatically sends the unsent messages to the destination.

Note that if the batch-timeout() option is enabled and the queue becomes empty, AxoSyslog flushes the messages only if batch-timeout() expires, or the batch reaches the limit set in batch-lines().

For optimal performance, make sure that the AxoSyslog source that feeds messages to this destination is configured properly: the value of the log-iw-size() option of the source must be higher than the batch-lines()*workers() of the destination. Otherwise, the size of the batches cannot reach the batch-lines() limit.

batch-timeout()

Type: time in milliseconds
Default: -1 (disabled)

Description: Specifies the time AxoSyslog waits for lines to accumulate in the output buffer. The AxoSyslog application sends batches to the destinations evenly. The timer starts when the first message arrives to the buffer, so if only few messages arrive, AxoSyslog sends messages to the destination at most once every batch-timeout() milliseconds.

channel-args()

Type: arrow list
Default: -

Description: The channel-args() option is available in gRPC-based drivers. It accepts name-value pairs and sets channel arguments defined in the GRPC Core library documentation. For example:

channel-args(
    "grpc.loadreporting" => 1
    "grpc.minimal_stack" => 0
)

compression()

Type: boolean
Default: no

Available in AxoSyslog version 4.5.0 and later.

Description: Enables compression in gRPC requests. Although gRPC supports various compression methods, currently only deflate is supported (which is basically the same as gzip).

database()

Type: string
Default: default

Description: The database where AxoSyslog sends the data.

disk-buffer()

Description: This option enables putting outgoing messages into the disk buffer of the destination to avoid message loss in case of a system failure on the destination side. It has the following options:

capacity-bytes()

Type: number (bytes)
Default: 1MiB

Description: This is a required option. The maximum size of the disk-buffer in bytes. The minimum value is 1048576 bytes. If you set a smaller value, the minimum value will be used automatically. It replaces the old log-disk-fifo-size() option.

In AxoSyslog version 4.2 and earlier, this option was called disk-buf-size().

compaction()

Type: yes/no
Default: no

Description: If set to yes, AxoSyslog prunes the unused space in the LogMessage representation, making the disk queue size smaller at the cost of some CPU time. Setting the compaction() argument to yes is recommended when numerous name-value pairs are unset during processing, or when the same names are set multiple times.

dir()

Type: string
Default: N/A

Description: Defines the folder where the disk-buffer files are stored.

flow-control-window-bytes()

Type: number (bytes)
Default: 163840000

Description: Use this option if the option reliable() is set to yes. This option contains the size of the messages in bytes that is used in the memory part of the disk buffer. It replaces the old log-fifo-size() option. It does not inherit the value of the global log-fifo-size() option, even if it is provided. Note that this option will be ignored if the option reliable() is set to no.

In AxoSyslog version 4.2 and earlier, this option was called mem-buf-size().

flow-control-window-size()

Type: number(messages)
Default: 10000

Description: Use this option if the option reliable() is set to no. This option contains the number of messages stored in overflow queue. It replaces the old log-fifo-size() option. It inherits the value of the global log-fifo-size() option if provided. If it is not provided, the default value is 10000 messages. Note that this option will be ignored if the option reliable() is set to yes.

In AxoSyslog version 4.2 and earlier, this option was called mem-buf-length().

front-cache-size()

Type: number(messages)
Default: 1000

Description: The number of messages stored in the output buffer of the destination. Note that if you change the value of this option and the disk-buffer already exists, the change will take effect when the disk-buffer becomes empty.

Options reliable() and capacity-bytes() are required options.

In AxoSyslog version 4.2 and earlier, this option was called qout-size().

prealloc()

Type: yes/no
Default: no

Description:

By default, AxoSyslog doesn’t reserve the disk space for the disk-buffer file, since in a properly configured and sized environment the disk-buffer is practically empty, so a large preallocated disk-buffer file is just a waste of disk space. But a preallocated buffer can prevent other data from using the intended buffer space (and elicit a warning from the OS if disk space is low), preventing message loss if the buffer is actually needed. To avoid this problem, when using AxoSyslog 4.0 or later, you can preallocate the space for your disk-buffer files by setting prealloc(yes).

In addition to making sure that the required disk space is available when needed, preallocated disk-buffer files provide radically better (3-4x) performance as well: in case of an outage the amount of messages stored in the disk-buffer is continuously growing, and using large continuous files is faster, than constantly waiting on a file to change its size.

If you are running AxoSyslog on a dedicated host (always recommended for any high-volume settings), use prealloc(yes).

Available in AxoSyslog 4.0 and later.

reliable()

Type: yes/no
Default: no

Description: If set to yes, AxoSyslog cannot lose logs in case of reload/restart, unreachable destination or AxoSyslog crash. This solution provides a slower, but reliable disk-buffer option. It is created and initialized at startup and gradually grows as new messages arrive. If set to no, the normal disk-buffer will be used. This provides a faster, but less reliable disk-buffer option.

truncate-size-ratio()

Type: number((between 0 and 1))
Default: 1 (do not truncate)

Description: Limits the truncation of the disk-buffer file. Truncating the disk-buffer file can slow down the disk IO operations, but it saves disk space. By default, AxoSyslog version 4.0 and later doesn’t truncate disk-buffer files by default (truncate-size-ratio(1)). Earlier versions freed the disk-space when at least 10% of the disk-buffer file could be freed (truncate-size-ratio(0.1)).

AxoSyslog only truncates the file if the possible disk gain is more than truncate-size-ratio() times capacity-bytes().

  • Smaller values free disk space quicker.
  • Larger ratios result in better performance.

If you want to avoid performance fluctuations:

Example: Examples for using disk-buffer()

In the following case reliable disk-buffer() is used.

destination d_demo {
    network(
        "127.0.0.1"
        port(3333)
        disk-buffer(
            flow-control-window-bytes(10000)
            capacity-bytes(2000000)
            reliable(yes)
            dir("/tmp/disk-buffer")
        )
    );
};

In the following case normal disk-buffer() is used.

destination d_demo {
    network(
        "127.0.0.1"
        port(3333)
            disk-buffer(
            flow-control-window-size(10000)
            capacity-bytes(2000000)
            reliable(no)
            dir("/tmp/disk-buffer")
        )
    );
};

frac-digits()

Type: number
Default: 0

Description: The AxoSyslog application can store fractions of a second in the timestamps according to the ISO8601 format. The frac-digits() parameter specifies the number of digits stored. The digits storing the fractions are padded by zeros if the original timestamp of the message specifies only seconds. Fractions can always be stored for the time the message was received.

headers()

Type: arrow list
Default: empty

Available in AxoSyslog 4.8 and later.

Description: Adds custom gRPC headers to each RPC call. Version 4.8 supported only static header names and values. For example:

headers(
    "organization" => "Axoflow"
    "stream-name" => "axo-stream"
  )

Starting with version 4.9, you can use templates and macros in the header values.

headers(
    "organization" => "Axoflow"
    "stream-name" => "${HOST}"
  )

hook-commands()

Description: This option makes it possible to execute external programs when the relevant driver is initialized or torn down. The hook-commands() can be used with all source and destination drivers with the exception of the usertty() and internal() drivers.

Using hook-commands() when AxoSyslog starts or stops

To execute an external program when AxoSyslog starts or stops, use the following options:

startup()

Type: string
Default: N/A

Description: Defines the external program that is executed as AxoSyslog starts.

shutdown()

Type: string
Default: N/A

Description: Defines the external program that is executed as AxoSyslog stops.

Using the hook-commands() when AxoSyslog reloads

To execute an external program when the AxoSyslog configuration is initiated or torn down, for example, on startup/shutdown or during a AxoSyslog reload, use the following options:

setup()

Type: string
Default: N/A

Description: Defines an external program that is executed when the AxoSyslog configuration is initiated, for example, on startup or during a AxoSyslog reload.

teardown()

Type: string
Default: N/A

Description: Defines an external program that is executed when the AxoSyslog configuration is stopped or torn down, for example, on shutdown or during a AxoSyslog reload.

Example: Using hook-commands() with a network source

In the following example, the hook-commands() is used with the network() driver and it opens an iptables port automatically as AxoSyslog is started/stopped.

The assumption in this example is that the LOGCHAIN chain is part of a larger ruleset that routes traffic to it. Whenever the AxoSyslog created rule is there, packets can flow, otherwise the port is closed.

source {
    network(transport(udp)
    hook-commands(
          startup("iptables -I LOGCHAIN 1 -p udp --dport 514 -j ACCEPT")
          shutdown("iptables -D LOGCHAIN 1")
        )
     );
};

keep-alive()

Configures how AxoSyslog sends gRPC keepalive pings.

max-pings-without-data()

Type: integer
Default:

Description: The maximum number of gRPC pings that can be sent when there is no data/header frame to be sent. AxoSyslog won’t send any pings after this limit. Set it to 0 disable this restriction and keep sending pings.

time()

Type: number [milliseconds]
Default:

Description: The period (in milliseconds) after which AxoSyslog sends a gRPC keepalive ping.

timeout()

Type: number [milliseconds]
Default: 10

Description: The time (in milliseconds) AxoSyslog waits for an acknowledgement.

local-time-zone()

Type: name of the timezone, or the timezone offset
Default: The local timezone.

Description: Sets the timezone used when expanding filename and tablename templates.

The timezone can be specified by using the name, for example, time-zone("Europe/Budapest")), or as the timezone offset in +/-HH:MM format, for example, +01:00). On Linux and UNIX platforms, the valid timezone names are listed under the /usr/share/zoneinfo directory.

log-fifo-size()

Type: number
Default: Use global setting.

Description: The number of messages that the output queue can store.

on-error()

Type: One of: drop-message, drop-property, fallback-to-string, silently-drop-message, silently-drop-property, silently-fallback-to-string
Default: Use the global setting (which defaults to drop-message)

Description: Controls what happens when type-casting fails and AxoSyslog cannot convert some data to the specified type. By default, AxoSyslog drops the entire message and logs the error. Currently the value-pairs() option uses the settings of on-error().

  • drop-message: Drop the entire message and log an error message to the internal() source. This is the default behavior of AxoSyslog.
  • drop-property: Omit the affected property (macro, template, or message-field) from the log message and log an error message to the internal() source.
  • fallback-to-string: Convert the property to string and log an error message to the internal() source.
  • silently-drop-message: Drop the entire message silently, without logging the error.
  • silently-drop-property: Omit the affected property (macro, template, or message-field) silently, without logging the error.
  • silently-fallback-to-string: Convert the property to string silently, without logging the error.

password()

Type: string
Default: empty string

Description: The password used for authentication.

persist-name()

Type: string
Default: N/A

Description: If you receive the following error message during AxoSyslog startup, set the persist-name() option of the duplicate drivers:

Error checking the uniqueness of the persist names, please override it with persist-name option. Shutting down.

This error happens if you use identical drivers in multiple sources, for example, if you configure two file sources to read from the same file. In this case, set the persist-name() of the drivers to a custom string, for example, persist-name("example-persist-name1").

protobuf-schema()

Type: See the description
Default: -

Description: Sets the schema of the database table from a protobuf schema file.

protobuf-schema("/tmp/test.proto" => "$MESSAGE", "$PROGRAM", "$HOST", "$PID")

An example proto file when using the protobuf-schema() option:

syntax = "proto2";
message CustomRecord {
  optional string message = 1;
  optional string app = 2;
  optional string host = 3;
  optional int64 pid = 4;
}

Alternatively, you can set the schema with the schema() option.

retries()

Type: number (of attempts)
Default: 3

Description: If AxoSyslog cannot send a message, it will try again until the number of attempts reaches retries().

If the number of attempts reaches retries(), AxoSyslog will wait for time-reopen() time, then tries sending the message again.

schema()

Type: arrow list
Default:

Description: Sets the schema of the database table. On the left side of the arrow, set the name of the column and its type. On the right side, set any AxoSyslog template or macro, which gets evaluated on each log that is routed to the destination. For example:

schema(
  "user_id" UInt32 => $R_MSEC,
  "message" String => "$MSG",
  "timestamp" DateTime => "$R_UNIXTIME",
  "metric" Float32 => 3.14
)

Alternatively, you can set the schema with the protobuf-schema() option.

You can find the available column types in the official ClickHouse documentation.

send-time-zone()

Accepted values: name of the timezone, or the timezone offset
Default: local timezone

Description: Specifies the time zone associated with the messages sent by syslog-ng, if not specified otherwise in the message or in the destination driver. For details, see Timezones and daylight saving.

The timezone can be specified by using the name, for example, time-zone("Europe/Budapest")), or as the timezone offset in +/-HH:MM format, for example, +01:00). On Linux and UNIX platforms, the valid timezone names are listed under the /usr/share/zoneinfo directory.

table()

Type: string
Default: -

Description: The name of the table where AxoSyslog sends the data.

template-escape()

Type: yes or no
Default: no

Description: Turns on escaping for the ', ", and backspace characters in templated output files. This is useful for generating SQL statements and quoting string contents so that parts of the log message are not interpreted as commands to the SQL server.

Note: Starting with AxoSyslog version 4.5, template-escape(yes) escapes the top-level template function in case of nested template functions.

throttle()

Type: number
Default: 0

Description: Sets the maximum number of messages sent to the destination per second. Use this output-rate-limiting functionality only when using disk-buffer as well to avoid the risk of losing messages. Specifying 0 or a lower value sets the output limit to unlimited.

time-reopen()

Accepted values: number [seconds]
Default: 60

Description: The time to wait in seconds before a dead connection is reestablished.

time-zone()

Type: name of the timezone, or the timezone offset
Default: unspecified

Description: Convert timestamps to the timezone specified by this option. If this option is not set, then the original timezone information in the message is used. Converting the timezone changes the values of all date-related macros derived from the timestamp, for example, HOUR. For the complete list of such macros, see Date-related macros.

The timezone can be specified by using the name, for example, time-zone("Europe/Budapest")), or as the timezone offset in +/-HH:MM format, for example, +01:00). On Linux and UNIX platforms, the valid timezone names are listed under the /usr/share/zoneinfo directory.

ts-format()

Type: rfc3164, bsd, rfc3339, iso
Default: rfc3164

Description: Override the global timestamp format (set in the global ts-format() parameter) for the specific destination. For details, see ts-format().

url()

Type: string
Default: localhost:9100

Description: The URL of the gRPC receiver.

user()

Type: string
Default: empty string

Description: The username used for authentication.

worker-partition-key()

Type: template
Default:

Description: The worker-partition-key() option specifies a template: messages that expand the template to the same value are mapped to the same partition. When batching is enabled and multiple workers are configured, it’s important to add only those messages to a batch which generate identical URLs. To achieve this, set the worker-partition-key() option with a template that contains all the templates used in the url() option, otherwise messages will be mixed.

For example, you can partition messages based on the destination host:

worker-partition-key("$HOST")

workers()

Type: integer
Default: 1

Description: Specifies the number of worker threads (at least 1) that AxoSyslog uses to send messages to the server. Increasing the number of worker threads can drastically improve the performance of the destination.

Last modified November 11, 2024: [4.9][clickhouse] Minor corrections (c7fd74f)