ClickHouse is a powerful and efficient open-source database management system that specializes in online analytical processing (OLAP). It offers real-time data analysis and reporting capabilities, making it a popular choice for businesses dealing with large amounts of data. In this article, we will guide you through the process of installing ClickHouse on Debian 11, enabling you to leverage its advanced features for your data analysis needs.

Prerequisites

Before we begin, let’s ensure that your server meets the necessary prerequisites for installing ClickHouse:

1. A server running Debian 11.
2. A root password is configured on your server.

Getting Started

To get started, let’s update your system packages to the latest version. Open your terminal and run the following command:

apt-get update -y

Once the update process is complete, you can install the required packages by executing the following command:

apt-get install curl gnupg2 wget git apt-transport-https ca-certificates -y

These packages are essential for the successful installation of ClickHouse.

Install ClickHouse on Debian 11

By default, ClickHouse is not available in the Debian 11 default repository. Therefore, we need to add the ClickHouse repository to our system. Follow the steps below to add the repository:

1. Download and add the GPG key by running the following command:

apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4

2. Add the ClickHouse repository to the APT sources list by executing the following command:

echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | tee /etc/apt/sources.list.d/clickhouse.list

3. Update the repository and install ClickHouse by running the following commands:

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

During the installation process, you will be prompted to set a password for the ClickHouse admin user. Choose a strong password and keep it secure.

Once the installation is complete, you can manage the ClickHouse service.

Manage ClickHouse Service

To start the ClickHouse service and ensure it starts automatically on system reboot, execute the following commands:

systemctl start clickhouse-server
systemctl enable clickhouse-server

You can verify the status of the ClickHouse service by running the following command:

systemctl status clickhouse-server

If everything is running smoothly, you should see an output similar to the following:

? clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled)
Active: active (running) since Fri 2021-12-31 11:22:57 UTC; 9s ago
Main PID: 18152 (clckhouse-watch)
Tasks: 206 (limit: 2341)
Memory: 108.2M
CPU: 931ms
CGroup: /system.slice/clickhouse-server.service
??18152 clickhouse-watchdog --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-serve>
??18153 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-serve>

Dec 31 11:22:57 debian11 systemd[1]: Started ClickHouse Server (analytic DBMS for big data).
Dec 31 11:22:57 debian11 clickhouse-server[18152]: Processing configuration file '/etc/clickhouse-server/config.xml'.
Dec 31 11:22:57 debian11 clickhouse-server[18152]: Logging trace to /var/log/clickhouse-server/clickhouse-server.log
Dec 31 11:22:57 debian11 clickhouse-server[18152]: Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
Dec 31 11:22:58 debian11 clickhouse-server[18153]: Processing configuration file '/etc/clickhouse-server/config.xml'.
Dec 31 11:22:58 debian11 clickhouse-server[18153]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/config.xml'.
Dec 31 11:22:58 debian11 clickhouse-server[18153]: Processing configuration file '/etc/clickhouse-server/users.xml'.
Dec 31 11:22:58 debian11 clickhouse-server[18153]: Merging configuration file '/etc/clickhouse-server/users.d/default-password.xml'.
Dec 31 11:22:58 debian11 clickhouse-server[18153]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/users.xml'.

Congratulations! ClickHouse is now successfully installed on your Debian 11 server.

How to Use ClickHouse

Now that ClickHouse is up and running, let’s explore how to use it for data analysis. Follow the steps below to connect to ClickHouse, create a database, and manage tables.

Connecting to ClickHouse

To connect to ClickHouse, open your terminal and run the following command:

clickhouse-client --password

You will be prompted to enter the default password you set during the installation process. After entering the password correctly, you will see the ClickHouse shell, indicating a successful connection.

ClickHouse client version 21.12.3.32 (official build).
Password for user (default):

Creating a Database

To create a database in ClickHouse, use the following command:

CREATE DATABASE mydatabase;

Replace “mydatabase” with the desired name for your database. ClickHouse will create the database and provide an output confirming the operation’s success.

CREATE DATABASE mydatabase
Query id: a48b3031-a363-4357-b4f9-ffd3edd73b0e
Ok.
0 rows in set. Elapsed: 0.005 sec.

Managing Tables

ClickHouse organizes data into tables, allowing you to store and query your data efficiently. Let’s explore how to create and manage tables in ClickHouse.

Creating a Table

To create a table, use the following command:

CREATE TABLE mytable (
id UInt64,
name String,
age UInt8
) ENGINE = MergeTree()
ORDER BY id;

Replace “mytable” with the desired name for your table. Specify the columns and their data types within the parentheses. The ENGINE clause determines the storage engine used for the table.

Inserting Data

To insert data into a table, use the following command:

INSERT INTO mytable VALUES (1, 'John Doe', 30);

Replace the values in parentheses with the actual data you want to insert.

Querying Data

To query data from a table, use the SELECT statement:

SELECT * FROM mytable;

This will retrieve all the data stored in the table.

Additional ClickHouse Operations

ClickHouse supports a wide range of operations to manipulate and analyze data. Here are a few examples:

Altering a Table

To alter a table and modify its structure, use the ALTER TABLE statement:

ALTER TABLE mytable ADD COLUMN email String;

This will add a new column named “email” to the existing table.

Dropping a Table

To drop a table and permanently remove it from the database, use the DROP TABLE statement:

DROP TABLE mytable;

This will delete the specified table and all its associated data.

Dropping a Database

To drop a database and remove it from ClickHouse, use the DROP DATABASE statement:

DROP DATABASE mydatabase;

This will delete the specified database and all its tables.

Enabling ClickHouse Web UI

ClickHouse provides a web-based user interface for managing and visualizing your data. By default, the ClickHouse Web UI is disabled, but you can enable it by following these steps:

1. Open the ClickHouse configuration file in a text editor:

nano /etc/clickhouse-server/config.xml

2. Uncomment the following lines by removing the leading “#” symbol:

<listen_host>0.0.0.0</listen_host>
<http_server_default_response><![CDATA[<html ng-app="SMI2"><head><base href="http://ui.tabix.io/"></head><body><div ui-view="" class="content-ui"></div><script src="http://loader.tabix.io/master.js"></script></body></html>]]></http_server_default_response>

3. Save and close the file.
4. Restart the ClickHouse service to apply the changes:

systemctl restart clickhouse-server

Accessing ClickHouse Web UI

After enabling the ClickHouse Web UI, you can access it through your web browser. Follow the steps below to access the interface:

1. Open your web browser.
2. Enter the following URL, replacing “your-server-ip” with the IP address of your ClickHouse server:

http://your-server-ip:8123

3. You will be presented with the ClickHouse Web UI login page. Enter your credentials (default username is “shapehost” and the password is the one you set during the installation) and click on the Sign In button.
4. Once logged in, you can explore the ClickHouse Web UI and leverage its features for managing your data.

Conclusion

In this article, we have covered the installation process of ClickHouse, a powerful OLAP database system, on Debian 11. We have also explored how to create databases, manage tables, and enable the ClickHouse Web UI. By following these steps, you can leverage ClickHouse’s capabilities to analyze and process your data efficiently. If you have any questions or need further assistance, feel free to reach out to Shape.host, a reliable and trusted provider of  Linux SSD VPS hosting solutions.