Introduction to ClickHouse Database

1. Overview

Through the use of Online Analytical Processing (OLAP), businesses gain insights into their current operations and identify trends for improvement. This is typically done by performing complex analysis on the aggregated business data.

ClickHouse, an open-source column-oriented OLAP database, has gained popularity recently due to its excellent performance.

In this tutorial, we’ll explore integrating the ClickHouse database into a Spring Boot application. We’ll walk through the necessary configuration, establish a connection, and perform a few CRUD operations on our database table.

2. Setting up the Project

Before we can start interacting with our ClickHouse database, we’ll need to include a few SDK dependencies and configure our application correctly.

2.1. Dependencies

Let’s start by adding the necessary dependencies to our project’s pom.xml file:

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.7.1</version>
</dependency>
<dependency>
    <groupId>org.lz4</groupId>
    <artifactId>lz4-java</artifactId>
    <version>1.8.0</version>
</dependency>

The clickhouse-jdbc dependency provides an implementation of the JDBC API and enables us to establish a connection with our ClickHouse database and interact with it.

By default, ClickHouse uses the LZ4 compression to store data, for which we’ve added the lz4-java dependency.

2.2. Defining Database Table Using Flyway

Next, let’s define our database table which we’ll perform operations against.

We’ll use Flyway to manage our database migration. Let’s include the flyway-core and flyway-database-clickhouse dependencies:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-clickhouse</artifactId>
    <version>10.16.3</version>
</dependency>

With these dependencies added to the pom.xml, we’ll create a migration script named V001__create_table.sql in our src/main/resources/db/migration directory with the following content:

CREATE TABLE authors (
    id UUID,
    name String,
    email String,
    created_at DateTime
)
ENGINE = MergeTree()
PRIMARY KEY id;

Our script creates an authors table with id as the primary key and a few other columns. We use the MergeTree table engine, which is optimized for insert and query performance.

2.3. Data Model

Finally, we’ll create an Author record to represent the data in our authors table:

public record Author(
    UUID id,
    String name,
    String email,
    LocalDateTime createdAt) {

    public static Author create(String name, String email) {
        return new Author(
            UUID.randomUUID(),
            name,
            email,
            LocalDateTime.now()
        );
    }
}

We also add a static create() method to instantiate our Author record with a random UUID and the current timestamp.

3. Setting up Local Test Environment With Testcontainers

To facilitate local development and testing, we’ll use Testcontainers to set up our ClickHouse database.

The prerequisite for running our database via Testcontainers is an active Docker instance.

3.1. Test Dependencies

First, let’s add the necessary test dependencies to our pom.xml:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>clickhouse</artifactId>
    <scope>test</scope>
</dependency>

The spring-boot-testcontainers and the clickhouse testcontainers module dependencies provide the classes necessary to spin up an ephemeral Docker instance for our ClickHouse database.

3.2. Defining Testcontainers Beans

Next, let’s create a @TestConfiguration class that defines our Testcontainers beans:

@TestConfiguration(proxyBeanMethods = false)
class TestcontainersConfiguration {

    @Bean
    public ClickHouseContainer clickhouseContainer() {
        return new ClickHouseContainer("clickhouse/clickhouse-server:24.11");
    }

    @Bean
    public DynamicPropertyRegistrar dynamicPropertyRegistrar(ClickHouseContainer clickhouseContainer) {
        return registry -> {
            registry.add("spring.datasource.url", clickhouseContainer::getJdbcUrl);
            registry.add("spring.datasource.username", clickhouseContainer::getUsername);
            registry.add("spring.datasource.password", clickhouseContainer::getPassword);
            registry.add("spring.datasource.driver-class-name", clickhouseContainer::getDriverClassName);
        };
    }

}

We specify the latest stable version of the ClickHouse image when creating our ClickHouseContainer bean.

Then, we define a DynamicPropertyRegistrar bean to configure the necessary datasource properties. This allows our application to connect to our ClickHouse database container.

With the correct connection details configured, Spring Boot automatically creates a bean of JdbcTemplate that we’ll use later in the tutorial.

3.3. Using Testcontainers During Development

While Testcontainers is primarily used for integration testing, we can use it during local development too.

To achieve this, we’ll create a separate main class in our src/test/java directory:

class TestApplication {

    public static void main(String[] args) {
        SpringApplication.from(Application::main)
          .with(TestcontainersConfiguration.class)
          .run(args);
    }
}

We create a TestApplication class and, inside its main() method, start our main Application class with the TestcontainersConfiguration class.

This setup helps us to set up and manage our external services locally. We can run our Spring Boot application and have it connect to the external services, which are started via Testcontainers.

4. Performing CRUD Operations

Now that we have our local environment set up, let’s interact with our authors table using the JdbcTemplate bean:

Author author = Author.create("John Doe", "[email protected]");

jdbcTemplate.update(
    """
        INSERT INTO authors (id, name, email, created_at)
        VALUES (?, ?, ?, ?);
    """,
    author.id(),
    author.name(),
    author.email(),
    author.createdAt()
);

Here, we create a new Author instance using the create() method and then use the update() method of the JdbcTemplate to insert it into the authors table.

To verify that the author record is persisted successfully, let’s execute a read query with its id:

List<Author> retrievedAuthors = jdbcTemplate.query(
    "SELECT * FROM authors WHERE id = ?",
    (ResultSet resultSet, int rowNum) -> new Author(
        UUID.fromString(resultSet.getString("id")),
        resultSet.getString("name"),
        resultSet.getString("email"),
        resultSet.getObject("created_at", LocalDateTime.class)
    ),
    author.id()
);

assertThat(retrievedAuthors)
  .hasSize(1)
  .first()
  .satisfies(retrievedAuthor -> {
      assertThat(retrievedAuthor.id()).isEqualTo(author.id());
      assertThat(retrievedAuthor.name()).isEqualTo(author.name());
      assertThat(retrievedAuthor.email()).isEqualTo(author.email());
      assertThat(retrievedAuthor.createdAt()).isNotNull();
  });

We use the query() method of the JdbcTemplate to retrieve the saved author record by its id and assert that the retrieved author matches the one we saved earlier.

For demonstration, we only perform save and read operations. However, the SQL reference from ClickHouse can be used to learn more about its syntax and operators.

5. Conclusion

In this article, we’ve explored integrating ClickHouse database into our Spring Boot application.

We created an authors table inside our database using a Flyway migration script. Then, using Testcontainers, we started an ephemeral Docker container for the ClickHouse database, creating a local test environment.

Finally, we used the JdbcTemplate to perform save and read operations on the authors table.