Skip to main content

ClickHouse

The ClickHouse backend supports both ingestion and querying via a ClickHouse HTTP endpoint.

Behavior and Configuration​

Configuration​

The following values are required when creating a ClickHouse backend:

  • url: (string, required) a valid HTTP(S) endpoint.
  • username: (string, required) used for auth.
  • password: (string, required) used for auth.
  • database: (string, required) the database to use.
  • port: (number, required) the port to use.

User Grants​

The ClickHouse backend requires the following user grants:

  • INSERT
  • SELECT
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE,
  • CREATE VIEW
  • DROP VIEW
note

It is suggested that you create a dedicated database and user specifically for Logflare to isolate data and permissions.

Implementation Details​

The ClickHouse backend will automatically attempt to provision required tables and views after receiving the first log event.

The ingest table schema is as follows:

  • id: The log event UUID.
  • event_message: The provided or generated event message of the log event, stored as String
  • body: The processed log event, stored as serialized JSON in a String column.
  • timestamp: Unix microsecond, stored as DateTime64(6)

Table Engine​

By default, the ClickHouse backends will utilize the MergeTree engine.

Note that when using ClickHouse Cloud, replication is handled automatically as mentioned in the data replication documentaion.

Querying​

ClickHouse backends support SQL querying through Logflare Endpoints and Alerts. The backend uses ClickHouse SQL dialect, which supports standard SQL features including:

  • Common Table Expressions (CTEs) with WITH clauses
  • Complex aggregations and window functions
  • Array and nested data type operations
  • ClickHouse-specific functions (e.g., tuple(), arraySlice(), JSONExtractString())

Sandboxed Queries​

ClickHouse backends fully support sandboxed queries within Endpoints, allowing you to create secure, parameterized API endpoints where consumers can provide custom SQL while being restricted to pre-defined data subsets.

Example sandboxed ClickHouse endpoint:

WITH filtered_logs AS (
    SELECT id, event_message, timestamp
    FROM my_clickhouse_source
    WHERE timestamp > now() - interval 1 day
)
SELECT * FROM filtered_logs

Consumers can then query within the sandbox via the sql= parameter:

SELECT event_message, count(*) as count
FROM filtered_logs
GROUP BY event_message
ORDER BY count DESC

See the Endpoints documentation for more details on sandboxed queries and security features.