Handling Analytics Queries in ClickHouse with Spring Boot

November 11, 2025

#1

This guide shows how to run analytics queries in ClickHouse using Spring Boot to process and fetch aggregated results.

1. Add Dependencies

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>com.clickhouse</groupId>
  <artifactId>clickhouse-jdbc</artifactId>
  <version>0.6.2</version>
</dependency>

2. Configure ClickHouse

spring.datasource.url=jdbc:clickhouse://localhost:8123/default
spring.datasource.driver-class-name=com.clickhouse.jdbc.ClickHouseDriver
spring.datasource.username=default
spring.datasource.password=

3. Sample Table

CREATE TABLE sales (
  id UInt32,
  product String,
  amount Float32,
  region String
) ENGINE = MergeTree()
ORDER BY id;

4. Repository with Native Query

@Repository
public interface SalesRepository extends CrudRepository<Sale, Long> {

  @Query(value = "SELECT region, SUM(amount) FROM sales GROUP BY region", nativeQuery = true)
  List<Object[]> totalSalesByRegion();

  @Query(value = "SELECT product, AVG(amount) FROM sales GROUP BY product", nativeQuery = true)
  List<Object[]> avgSalesByProduct();
}

5. Service Layer

@Service
public class SalesService {
  private final SalesRepository repo;

  public SalesService(SalesRepository repo) {
    this.repo = repo;
  }

  public List<Object[]> salesByRegion() {
    return repo.totalSalesByRegion();
  }

  public List<Object[]> avgByProduct() {
    return repo.avgSalesByProduct();
  }
}

6. REST Controller

@RestController
@RequestMapping("/analytics")
public class AnalyticsController {
  private final SalesService service;

  public AnalyticsController(SalesService service) {
    this.service = service;
  }

  @GetMapping("/region")
  public List<Object[]> region() {
    return service.salesByRegion();
  }

  @GetMapping("/product")
  public List<Object[]> product() {
    return service.avgByProduct();
  }
}

7. Test Queries

• GET /analytics/region → returns sales totals grouped by region.
• GET /analytics/product → returns average sales grouped by product.