ClickHouse Materialized Views with Spring Boot Applications

December 09, 2025

#1

Materialized views in ClickHouse help pre-aggregate and optimize query performance. Let’s integrate them with Spring Boot for faster analytics.

1. Create a Base Table

CREATE TABLE sensor_data (
  device_id String,
  ts DateTime,
  temperature Float32,
  humidity Float32
) ENGINE = MergeTree()
ORDER BY ts;

This stores raw IoT data.

2. Create a Materialized View

CREATE MATERIALIZED VIEW avg_temp_mv
ENGINE = SummingMergeTree()
ORDER BY device_id
AS SELECT device_id, avg(temperature) AS avg_temp
FROM sensor_data
GROUP BY device_id;

The view auto-updates when data is inserted.

3. Insert Data from Spring Boot

jdbcTemplate.update(
  "INSERT INTO sensor_data (device_id, ts, temperature, humidity) VALUES (?,?,?,?)",
  "sensor-1", LocalDateTime.now(), 24.5, 58.0
);

Data flows into the base table and materialized view.

4. Query Aggregated Data

List<Map<String,Object>> result = jdbcTemplate.queryForList(
  "SELECT * FROM avg_temp_mv WHERE device_id='sensor-1'"
);

This gives instant access to pre-computed averages.

5. Expose REST API

@GetMapping("/api/avg-temp/{device}")
public Map<String,Object> getAvgTemp(@PathVariable String device) {
    return jdbcTemplate.queryForMap(
      "SELECT * FROM avg_temp_mv WHERE device_id=?", device
    );
}

The API serves optimized query results.