How to Install ClickHouse on Ubuntu 20.04

ClickHouse is a free, open-source, fast OLAP database management system used to generate analytical reports using SQL queries in real-time. It is column-oriented and has other important characteristics like user-friendliness, scalability, and security. It stores records in blocks grouped by columns instead of rows. Compared to row-based systems, column-oriented databases spend less time reading and computing the data.

In this post, we will show you how to install and ClickHouse on Ubuntu 20.04 server.

Step 1 – Add ClickHouse Repository

By default, ClickHouse is not included in the Ubuntu 20.04 default repository, so you will need to add the ClickHouse repository to your system.

First, install the required dependencies using the following command:

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

Once all the dependencies are installed, add the GPG key with the following command:

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

Output:

Executing: /tmp/apt-key-gpghome.kaYYOnkSVy/gpg.1.sh --keyserver keyserver.ubuntu.com --recv E0C56BD4
gpg: key C8F1E19FE0C56BD4: public key "ClickHouse Repository Key <[email protected]>" imported
gpg: Total number processed: 1
gpg:               imported: 1

Next, add the ClickHouse repository to APT with the following command:

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

Next, update the repository with the following command:

apt-get update -y

Step 2 – Install ClickHouse

Now, install the ClickHouse server and client with the following command:

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

Once the ClickHouse is installed, start the ClickHouse service and enable it to start at system reboot:

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

Output:

● 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 Wed 2021-05-05 03:17:41 UTC; 4s ago
   Main PID: 26649 (clckhouse-watch)
      Tasks: 46 (limit: 2353)
     Memory: 46.4M
     CGroup: /system.slice/clickhouse-server.service
             ├─26649 clickhouse-watchdog        --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server>
             └─26650 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server>

May 05 03:17:41 ubuntu2004 systemd[1]: Started ClickHouse Server (analytic DBMS for big data).
May 05 03:17:41 ubuntu2004 clickhouse-server[26649]: Processing configuration file '/etc/clickhouse-server/config.xml'.
May 05 03:17:41 ubuntu2004 clickhouse-server[26649]: Logging trace to /var/log/clickhouse-server/clickhouse-server.log
May 05 03:17:41 ubuntu2004 clickhouse-server[26649]: Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
May 05 03:17:41 ubuntu2004 clickhouse-server[26650]: Processing configuration file '/etc/clickhouse-server/config.xml'.
May 05 03:17:41 ubuntu2004 clickhouse-server[26650]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/config.xml'.
May 05 03:17:41 ubuntu2004 clickhouse-server[26650]: Processing configuration file '/etc/clickhouse-server/users.xml'.
May 05 03:17:41 ubuntu2004 clickhouse-server[26650]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/users.xml'.

Step 3 – Set ClickHouse Password

By default, ClickHouse is configured without a password, so you will need to set the password for the default user. You can set it by editing the file users.xml:

nano /etc/clickhouse-server/users.xml

Find the following line:

            <password></password>

And, replaced it with the following command:

            <password>MyPassword</password>

Save and close the file, then restart the ClickHouse service to apply the changes:

systemctl restart clickhouse-server

Step 4 – Working with ClickHouse

In this section, we will show you how to interact with the ClickHouse database.

First, connect ClickHouse using the following command:

clickhouse-client --password

You will be asked to provide a password for the default user as shown below:

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

Provide your password and hit ENTER. Once you are connected, you will be redirected to the ClickHouse shell:

Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.4.6 revision 54447.

ubuntu2004 :) 

To create a database named mydb, run the following command:

ubuntu2004 :) CREATE DATABASE mydb;

Output:

 
CREATE DATABASE mydb

Query id: 9f82c771-a072-495e-b2a7-cc9618d9b73f

Ok.

0 rows in set. Elapsed: 0.008 sec. 

Change the database to mydb, run the following command:

ubuntu2004 :) USE mydb;

Output:

USE mydb

Query id: d94a4fb5-f3fd-41a9-a27f-0bb9efa9e020

Ok.

0 rows in set. Elapsed: 0.002 sec. 

To create a table named data, run the following command:

ubuntu2004 :) CREATE TABLE data  (　 id UInt64,　 name String,　 url String,　 created DateTime　) ENGINE = MergeTree()　PRIMARY KEY id　ORDER BY id;

Output:

CREATE TABLE data
(
    `id` UInt64,
    `name` String,
    `url` String,
    `created` DateTime
)
ENGINE = MergeTree
PRIMARY KEY id
ORDER BY id

Query id: 6039b4d9-c296-4115-b4bb-06a1bbe5499f

Ok.

0 rows in set. Elapsed: 0.011 sec. 

Insert some data into the table with the following command:

ubuntu2004 :) INSERT INTO data VALUES (1, 'hitesh', 'http://example.com', '2021-05-01 00:01:01');

Output:

INSERT INTO data VALUES

Query id: f7dfd959-688a-4481-8fb8-85e49c669b9e

Ok.

1 rows in set. Elapsed: 0.022 sec. 

To add a new column, run the following command:

ubuntu2004 :) ALTER TABLE data ADD COLUMN location String;

Output:

ALTER TABLE data
    ADD COLUMN `location` String

Query id: 7d40bfee-0d0c-4113-bcd9-fd89ac42b82b

Ok.

0 rows in set. Elapsed: 0.038 sec. 

To retrieve the data from the table, run the following command:

ubuntu2004 :) SELECT url, name FROM data WHERE url = 'http://example.com' LIMIT 1;

Output:

SELECT
    url,
    name
FROM data
WHERE url = 'http://example.com'
LIMIT 1

Query id: 60dd1e11-f2d9-4b33-8403-6e1859fa266a

┌─url────────────────┬─name───┐
│ http://example.com │ hitesh │
└────────────────────┴────────┘

1 rows in set. Elapsed: 0.007 sec. 

To delete a column from the data table, run the following command:

ubuntu2004 :) ALTER TABLE data DROP COLUMN location;

Output:

ALTER TABLE data
    DROP COLUMN location

Query id: 6feffa20-7d33-490f-b8b5-a2d3c1ad93a1

Ok.

0 rows in set. Elapsed: 0.017 sec. 

To delete a data table, run the following command:

ubuntu2004 :) DROP TABLE data;

Output:

DROP TABLE data

Query id: bbac3bd1-8f1d-40b9-8f35-e5816de45855

Ok.

0 rows in set. Elapsed: 0.008 sec. 

To delete a mydb database, run the following command:

ubuntu2004 :) DROP DATABASE mydb;

Output:

DROP DATABASE mydb

Query id: 6fa79629-e2a9-4dcc-ba11-4a6684a085c3

Ok.

0 rows in set. Elapsed: 0.002 sec. 

Next, exit from the ClickHouse shell with the following command:

ubuntu2004 :) exit;

Step 5 – Enable ClickHouse Web UI

By default, ClickHouse web UI is disabled. You will need to enable it by editing the file config.xml:

nano /etc/clickhouse-server/config.xml

Uncomment and change the listen_host line and http_server_default_response as shown below:

 <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 server to apply the changes:

systemctl restart clickhouse-server

Now, open your web browser and type the URL http://your-server-ip:8123. You should see the ClickHouse login page:

Provide your default username and password then click on the Sign In button. You should see the ClickHouse dashboard on the following page:

Conclusion

Congratulations! You have successfully installed and configured ClickHouse on Ubuntu 20.04. I hope you have now enough knowledge of how to interact with a ClickHouse database on your VPS hosting account. For more information, you can visit the ClickHouse documentation page. https://clickhouse.yandex/docs/en/