How to Install and Connect to ClickHouse Using Java

by | Sep 2, 2024 | Backend Development, Boost Profit

Introduction

ClickHouse is a high-performance columnar database management system (DBMS) for online analytical processing (OLAP). In this blog, we’ll go through the steps to install ClickHouse on your machine, set it up, and connect to it using Java with Spring Boot.

Step 1: Installing ClickHouse

Before you can connect to ClickHouse from your Java application, you need to have ClickHouse installed on your machine. Follow these steps to install ClickHouse on Linux:

1.1. Install ClickHouse Server

First, add the ClickHouse repository to your package manager:

sudo apt-get install -y apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4
echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update

Now, install ClickHouse server and client:

bash

sudo apt-get install -y clickhouse-server clickhouse-client

1.2. Start the ClickHouse Server

Once installed, start the ClickHouse server using the following command

bash

sudo service clickhouse-server start

You can verify that the server is running by connecting to it with the ClickHouse client

bash

clickhouse-client

1.3. Basic Configuration

ClickHouse’s default configuration file is located at /etc/clickhouse-server/config.xml. If you need to change the listening port or set up custom users, you can do so here. After making changes, restart the server

bash

sudo service clickhouse-server restart

Step 2: Setting Up a Java Project to Connect to ClickHouse

Now that ClickHouse is installed and running, we can connect to it using Java. Below are the steps to set up a Spring Boot project that interacts with ClickHouse.

2.1. Include the ClickHouse JDBC Dependency

To begin, add the ClickHouse JDBC dependency to your pom.xml file:

This dependency allows Java applications to communicate with ClickHouse using the JDBC API.

2.2. Configure the DataSource in Spring Boot

Next, you need to configure the DataSource in your application.properties or application.yaml file:

For application.properties:

properties

spring.datasource.url=jdbc:clickhouse://:/
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=ru.yandex.clickhouse.ClickHouseDriver

For application.yaml:

2.3. Define DataSource and JdbcTemplate Beans

In your Spring Boot application, create a configuration class to define DataSource and JdbcTemplate beans:

java

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import javax.sql.DataSource;
@Configuration
public class AppConfig {
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        return dataSource;
    }
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

This configuration class pulls the database connection details from the application.properties or application.yaml file and sets up the DataSource and JdbcTemplate beans.

2.4. Create a Repository to Interact with ClickHouse

Now that the JdbcTemplate bean is configured, you can create a repository to interact with the ClickHouse database:

java

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class DataRepository {
    private final JdbcTemplate jdbcTemplate;
    public DataRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    // Your query methods here...
}

In this repository, you can define methods to execute SQL queries against your ClickHouse database.

Conclusion

By following the steps outlined in this blog, you’ve installed ClickHouse on your machine, set up a Spring Boot project, and connected to ClickHouse using Java. Now, you can leverage the power of ClickHouse for high-performance data processing in your applications. Whether you’re building a data analytics platform or simply need a powerful database backend, ClickHouse is an excellent choice for handling large-scale data efficiently.