How to Install ClickHouse on Oracle Linux 8

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!