ClickHouse is a free, open-source, and fast column-oriented database management system. It is used for generating analytical data reports in real-time using SQL queries. ClickHouse stands for “Clickstream Data Warehouse” which was initially built for web analytics in Yandex Metrica. It is capable of processing billions of rows and tens of gigabytes of data per server per second. It has built-in async replication features and can be deployed across multiple data centers.
In this post, we will show you how to install ClickHouse on Oracle Linux 8.
Step 1 – Install ClickHouse
By default, the ClickHouse package is not included in the Oracle Linux default repo, so you will need to create a ClickHouse repo. You can create it with the following commands:
dnf update -y
nano /etc/yum.repos.d/clickhouse.repo
Add the following lines:
[altinity_clickhouse] name=altinity_clickhouse baseurl=https://packagecloud.io/altinity/clickhouse/el/7/$basearch repo_gpgcheck=1 gpgcheck=0 enabled=1 gpgkey=https://packagecloud.io/altinity/clickhouse/gpgkey sslverify=1 sslcacert=/etc/pki/tls/certs/ca-bundle.crt metadata_expire=300 [altinity_clickhouse-source] name=altinity_clickhouse-source baseurl=https://packagecloud.io/altinity/clickhouse/el/7/SRPMS repo_gpgcheck=1 gpgcheck=0 enabled=1 gpgkey=https://packagecloud.io/altinity/clickhouse/gpgkey sslverify=1 sslcacert=/etc/pki/tls/certs/ca-bundle.crt metadata_expire=300
Save and close the file, then enable the created repo using the following command:
dnf -q makecache -y --disablerepo='*' --enablerepo='altinity_clickhouse'
Next, run the following command to install the ClickHouse server and client packages:
dnf install -y clickhouse-server clickhouse-client
Once both packages are installed, you can proceed to start the ClickHouse service.
Step 2 – Manage ClickHouse Service
By default, the ClickHouse service is managed by systemd. You can start it with the following command:
systemctl start clickhouse-server
To enable the ClickHouse service, run the following command:
systemctl enable clickhouse-server
To check the ClickHouse service status, run the following command:
systemctl status clickhouse-server
You should see the following output:
● clickhouse-server.service - LSB: Yandex clickhouse-server daemon Loaded: loaded (/etc/rc.d/init.d/clickhouse-server; generated) Active: active (exited) since Thu 2022-08-11 05:30:46 EDT; 20s ago Docs: man:systemd-sysv-generator(8) Process: 6029 ExecStart=/etc/rc.d/init.d/clickhouse-server start (code=exited, status=0/SUCCESS) Aug 11 05:30:44 oraclelinux8 su[6066]: pam_unix(su:session): session opened for user clickhouse by (uid=0) Aug 11 05:30:44 oraclelinux8 su[6066]: pam_unix(su:session): session closed for user clickhouse Aug 11 05:30:44 oraclelinux8 su[6069]: (to clickhouse) root on none Aug 11 05:30:44 oraclelinux8 su[6069]: pam_unix(su:session): session opened for user clickhouse by (uid=0) Aug 11 05:30:44 oraclelinux8 su[6069]: pam_unix(su:session): session closed for user clickhouse Aug 11 05:30:44 oraclelinux8 su[6077]: (to clickhouse) root on none Aug 11 05:30:45 oraclelinux8 su[6077]: pam_unix(su:session): session opened for user clickhouse by (uid=0) Aug 11 05:30:45 oraclelinux8 su[6077]: pam_unix(su:session): session closed for user clickhouse Aug 11 05:30:46 oraclelinux8 clickhouse-server[6029]: DONE Aug 11 05:30:46 oraclelinux8 systemd[1]: Started LSB: Yandex clickhouse-server daemon.
Step 3 – Create a Database in ClickHouse
At this point, ClickHouse is started and running. To connect to ClickHouse, run the following command:
clickhouse-client --multiline
Once you are connected to ClickHouse, you will get the following shell:
ClickHouse client version 20.8.3.18. Connecting to localhost:9000 as user default. Connected to ClickHouse server version 20.8.3 revision 54438. oraclelinux8 :)
To create a database named db1, run the following command:
CREATE DATABASE db1;
You should see the following output:
CREATE DATABASE db1 Ok.
To verify the created database, run the following command:
show databases;
You should see the following output:
SHOW DATABASES ┌─name───────────────────────────┐ │ _temporary_and_external_tables │ │ db1 │ │ default │ │ system │ └────────────────────────────────┘
Step 4 – Create a Table in ClickHouse
To create a table, first switch the database to db1 using the following command:
USE db1;
You will get the following output:
USE db1 Ok.
Next, create a table named visits and run the following command:
CREATE TABLE visits ( id UInt64, duration Float64, url String, created DateTime ) ENGINE = MergeTree() PRIMARY KEY id ORDER BY id;
You should see the following output:
CREATE TABLE visits ( `id` UInt64, `duration` Float64, `url` String, `created` DateTime ) ENGINE = MergeTree() PRIMARY KEY id ORDER BY id Ok. 0 rows in set. Elapsed: 0.007 sec.
Next, to insert some value into the first, second, and third rows, run the following commands:
INSERT INTO visits VALUES (1, 12.5, 'http://linuxbuz.com', '2022-05-01 00:01:01'); INSERT INTO visits VALUES (2, 25.2, 'http://atlantic.net', '2022-06-06 10:01:01'); INSERT INTO visits VALUES (3, 20, 'http://example.com', '2022-07-04 02:01:01');
To retrieve the specific value from the table, run the following command:
SELECT url, duration FROM visits WHERE url = 'http://atlantic.net.com' LIMIT 2;
You should see the following output:
SELECT url, duration FROM visits WHERE url = 'http://atlantic.net.com' LIMIT 2 Ok.
To retrieve the total duration of visits, run the following command:
SELECT SUM(duration) FROM visits;
You should see the following output:
SELECT SUM(duration) FROM visits ┌─SUM(duration)─┐ │ 57.7 │ └───────────────┘ 1 rows in set. Elapsed: 0.009 sec.
To get information about the top two URLs, run the following command:
SELECT topK(2)(url) FROM visits;
You will get the following output:
SELECT topK(2)(url) FROM visits ┌─topK(2)(url)──────────────────────────────────┐ │ ['http://linuxbuz.com','http://atlantic.net'] │ └───────────────────────────────────────────────┘
Step 5 – Delete a Database and Table in ClickHouse
If you want to delete a table, run the following command:
DROP TABLE visits;
To delete a database, run the following command:
DROP DATABASE db1;
Conclusion
In this post, we explained how to install ClickHouse on Oracle Linux 8. We also explained how to create a database and table and retrieve information in ClickHouse. You can now use ClickHouse as a database in your application. Try it on dedicated hosting from Atlantic.Net!