ClickHouse Logging and Auditing in Spring Boot

December 18, 2025

#1

Proper logging and auditing help you track queries, errors, and user activity in ClickHouse. Spring Boot makes it easy to add logging and auditing layers.

1. Enable SQL Logging

In application.properties:

logging.level.org.springframework.jdbc.core=DEBUG
logging.level.org.hibernate.SQL=DEBUG

This logs all SQL statements executed by Spring Boot.

2. Log Query Parameters

Enable parameter binding logs:

logging.level.org.hibernate.type.descriptor.sql=TRACE

This lets you see exact parameter values for better debugging.

3. Use Interceptors for Auditing

Create a HandlerInterceptor to log user and request data:

@Component
public class AuditInterceptor implements HandlerInterceptor {
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) {
        String user = request.getRemoteUser();
        String uri = request.getRequestURI();
        System.out.println("User: " + user + " accessed: " + uri);
        return true;
    }
}

Register it in a WebMvcConfigurer.

4. Store Audit Data

Create an audit table in ClickHouse:

CREATE TABLE audit_logs (
  id UUID DEFAULT generateUUIDv4(),
  user String,
  action String,
  timestamp DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY timestamp;

Insert audit data via Spring Boot services whenever critical actions occur.

5. Use Logback for Centralized Logging

In logback-spring.xml, define log appenders to send logs to a file or external system (like ELK or Grafana Loki).

6. Monitor Suspicious Activity

Audit logs can be used to detect:

• Unauthorized access
• Abnormal query patterns
• Failed login attempts