ClickHouse is an open-source, fast, and modern database management system used for online analytical processing (OLAP). It is column-oriented and stores records in blocks grouped by columns instead of rows. It allows you to generate analytical reports using SQL queries in real time. Also, column-oriented databases spend less time reading data while completing queries compared to traditional row-based systems. It can be used in a system with a small amount of RAM, even with 2 GB of RAM, but it requires additional tuning and can ingest at a low rate.
In this post, we will show you how to install ClickHoust on Ubuntu 22.04.
Prerequisites
- A server running Ubuntu 22.04.
- A root password is configured on your server.
Install Required Dependencies
Before starting, it is recommended to update and upgrade all system packages to the updated version. You can update them by running the following command:
apt update -y apt upgrade -y
After upgrading all the packages, run the following command to install the other required packages:
apt-get install curl gnupg2 wget git apt-transport-https ca-certificates -y
Once all the required packages are installed, you can proceed to the next step.
Install ClickHouse
By default, the ClickHouse package is not included in the Ubuntu 22.04 default repository. So you will need to add the ClickHouse repository to your system.
First, download and add the GPG key using the following command:
gpg --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754 gpg --export --armor 8919F6BD2B48D754 | gpg --dearmour -o /etc/apt/trusted.gpg.d/clickhouse-key.gpg
Next, add the ClickHouse repository to the APT using the following command:
echo "deb [arch=amd64] https://packages.clickhouse.com/deb stable main" | tee /etc/apt/sources.list.d/clickhouse.list
Next, update the repository and install the ClickHouse server and client packages using the following command:
apt-get update -y apt-get install clickhouse-server clickhouse-client -y
During the installation, you will be asked to provide a password for the ClickHouse admin user as shown below:
Enter password for default user: Password for default user is saved in file /etc/clickhouse-server/users.d/default-password.xml. Setting capabilities for clickhouse binary. This is optional. chown -R clickhouse:clickhouse '/etc/clickhouse-server' ClickHouse has been successfully installed. Start clickhouse-server with: sudo clickhouse start Start clickhouse-client with: clickhouse-client --password Synchronizing state of clickhouse-server.service with SysV service script with /lib/systemd/systemd-sysv-install. Executing: /lib/systemd/systemd-sysv-install enable clickhouse-server Created symlink /etc/systemd/system/multi-user.target.wants/clickhouse-server.service → /lib/systemd/system/clickhouse-server.service.
Once the installation has been finished, you can proceed to the next step.
Start ClickHouse Service
You can now start the ClickHouse service and enable it to start at system reboot with the following command:
systemctl start clickhouse-server systemctl enable clickhouse-server
You can now verify the status of the ClickHouse with the following command:
systemctl status clickhouse-server
You will get the following output:
? clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
Loaded: loaded (/lib/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2022-12-05 14:47:40 UTC; 6s ago
Main PID: 13369 (clckhouse-watch)
Tasks: 205 (limit: 4579)
Memory: 65.5M
CPU: 1.128s
CGroup: /system.slice/clickhouse-server.service
??13369 clickhouse-watchdog "" "" "" "" "" "" "" --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/cl>
??13370 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-serve>
Dec 05 14:47:40 ubuntu2204 systemd[1]: Started ClickHouse Server (analytic DBMS for big data).
Dec 05 14:47:40 ubuntu2204 clickhouse-server[13369]: Processing configuration file '/etc/clickhouse-server/config.xml'.
Dec 05 14:47:40 ubuntu2204 clickhouse-server[13369]: Logging trace to /var/log/clickhouse-server/clickhouse-server.log
Dec 05 14:47:40 ubuntu2204 clickhouse-server[13369]: Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
Dec 05 14:47:41 ubuntu2204 clickhouse-server[13370]: Processing configuration file '/etc/clickhouse-server/config.xml'.
Dec 05 14:47:41 ubuntu2204 clickhouse-server[13370]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/config.xml>
Dec 05 14:47:41 ubuntu2204 clickhouse-server[13370]: Processing configuration file '/etc/clickhouse-server/users.xml'.
Dec 05 14:47:41 ubuntu2204 clickhouse-server[13370]: Merging configuration file '/etc/clickhouse-server/users.d/default-password.xml'.
Dec 05 14:47:41 ubuntu2204 clickhouse-server[13370]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/users.xml'.
How to Use ClickHouse
In this section, we will show you how to connect to the ClickHouse, and create a database and table.
First, connect to the ClickHouse using the following command:
clickhouse-client --password
You will be asked to provide the default password as shown below:
ClickHouse client version 22.11.2.30 (official build). Password for user (default): Connecting to localhost:9000 as user default. Connected to ClickHouse server version 22.11.2 revision 54460. Warnings: * Maximum number of threads is lower than 30000. There could be problems with handling a lot of simultaneous queries. ubuntu2204 :)
Now, create a database named wpdb using the following command:
ubuntu2204 :) CREATE DATABASE wpdb;
You will get the following output:
CREATE DATABASE wpdb Query id: 9f65d3fc-239a-44e6-90a7-64e28472c40c Ok. 0 rows in set. Elapsed: 0.005 sec.
Next, change the database to wpdb using the following command:
ubuntu2204 :) USE wpdb;
You will get the following output:
USE wpdb Query id: b0ccf487-4906-477a-bc6b-4b5306067eee Ok. 0 rows in set. Elapsed: 0.002 sec.
Next, create a table named student using the following command:
ubuntu2204 :) CREATE TABLE student (? id UInt64,? name String,? url String,? created DateTime?) ENGINE = MergeTree()?PRIMARY KEY id?ORDER BY id;
You will get the following output:
CREATE TABLE student
(
`id` UInt64,
`name` String,
`url` String,
`created` DateTime
)
ENGINE = MergeTree
PRIMARY KEY id
ORDER BY id
Query id: 552a34ae-ff63-4682-89e2-a88be04fe5c2
Ok.
0 rows in set. Elapsed: 0.008 sec.
Next, insert some value in the table using the following command:
ubuntu2204 :) INSERT INTO student VALUES (1, 'hit', 'http://howtoforge.com', '2021-12-31 00:01:01');
You will get the following output:
INSERT INTO student FORMAT Values Query id: b54035a3-0355-463c-bc59-c8369b1d8010 Ok. 1 row in set. Elapsed: 0.006 sec.
Next, add a new column using the following command:
ubuntu2204 :) ALTER TABLE student ADD COLUMN location String;
You will get the following output:
ALTER TABLE student
ADD COLUMN `location` String
Query id: d15b0cff-677f-4650-81cb-d099b1259d5b
Ok.
0 rows in set. Elapsed: 0.008 sec.
If you want to retrieve the data from the table, run the following command:
ubuntu2204 :) SELECT url, name FROM student WHERE url = 'http://howtoforge.com' LIMIT 1;
You will get the following output:
SELECT
url,
name
FROM student
WHERE url = 'http://howtoforge.com'
LIMIT 1
Query id: 71d588aa-2402-4b97-8d8f-bffd37f8cc0a
??url?????????????????????name??
? http://howtoforge.com ? hit ?
????????????????????????????????
1 row in set. Elapsed: 0.005 sec.
To remove a column, run the following command:
ubuntu2204 :) ALTER TABLE student DROP COLUMN location;
To remove a table, run the following command:
ubuntu2204 :) DROP TABLE student;
To remove a database, run the following command:
ubuntu2204 :) DROP DATABASE wpdb;
To exit from the ClickHouse shell, run the following command:
ubuntu2204 :) exit;
Enable ClickHouse Web Interface
ClickHouse provides a web-based interface to manage the databases. But, it is disabled by default. You can enable it by editing the ClickHouse configuration file:
nano /etc/clickhouse-server/config.xml
Uncomment and change the following lines:
<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>
Save and close the file then restart the ClickHouse service to apply the configuration changes:
systemctl restart clickhouse-server
Access ClickHouse Web Interface
At this point, ClickHouse web UI is configured and listens on port 8123. You can check it using the following command:
ss -antpl | grep 8123
You should see the following output:
LISTEN 0 4096 0.0.0.0:8123 0.0.0.0:* users:(("clickhouse-serv",pid=13607,fd=153))
Now, open your web browser and access the ClickHouse Web UI using the URL http://your-server-ip:8123. You should see the following screen:
Provide your default username, password and click on the Sign In button. Once you are logged in, you should see the following screen:
Conclusion
Congratulations! you have successfully installed ClickHouse on Ubuntu 22.04. You can now use ClickHouse as a database-backed for your application and start managing it from the web browser. Feel free to ask me if you have any questions.