ClickHouse Query Auditing in Spring Boot Applications

December 06, 2025

#1

Auditing database queries is important for monitoring usage, troubleshooting, and ensuring compliance. Let’s set up query auditing with ClickHouse in Spring Boot.

1. Create Audit Table in ClickHouse

CREATE TABLE query_audit (
  id UUID,
  query String,
  executed_at DateTime,
  user String
) ENGINE = MergeTree()
ORDER BY executed_at;

This stores all query logs.

2. Intercept Queries in Spring Boot

Implement a simple interceptor to capture queries before execution.

public class AuditInterceptor implements StatementInspector {
    @Override
    public String inspect(String sql) {
        logQuery(sql);
        return sql;
    }

    private void logQuery(String sql) {
        jdbcTemplate.update(
          "INSERT INTO query_audit (id, query, executed_at, user) VALUES (?,?,?,?)",
          UUID.randomUUID(), sql, LocalDateTime.now(), "app-user"
        );
    }
}

3. Register the Interceptor

In application.properties:

spring.jpa.properties.hibernate.session_factory.statement_inspector=com.example.AuditInterceptor

This enables automatic auditing.

4. Query the Audit Logs

List<Map<String,Object>> audits = jdbcTemplate.queryForList(
  "SELECT * FROM query_audit ORDER BY executed_at DESC LIMIT 20"
);

Expose this through a REST API for monitoring.

5. Dashboard for Query Insights

Integrate the audit API with Grafana or a frontend chart to analyze query frequency, latency, or usage by user.