Install ClickHouse on Ubuntu 20.04 - Step by step process to perform it

Ibmi Media
Server Management Service
44

ClickHouse is a column-oriented database designed to address OLAP – Online Analytical Processing.

OLAP is a technique for advanced big data analysis.

The language ClickHouse uses is a variation of SQL, which helps beginners learn this query language faster.

Install of ClickHouse on Ubuntu involves a series of steps that includes adjusting the configuration file to enable listening over other IP address and remote access.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform Ubuntu related Software Installation tasks.

In this context, we shall look into the steps to install ClickHouse on Ubuntu.

How to install ClickHouse on Ubuntu?

ClickHouse is an open-source analytics database developed for big data use cases.

Typically, the installation process involves the steps below:

i. Installing ClickHouse

ii. Starting the Service

iii. Change the listening IP address

iii. Enable Tabix

iv. Setting Up Firewall Rules

Next, Let us discuss each of them in detail.

1. Installing ClickHouse

In this section, we will install the ClickHouse server and client programs using apt.

Here, we need to add the repository of ClickHouse.

To add the repository's GPG key and then to add the repository to the APT repositories, use the command below:

$ sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4
$ echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list

Now, we shall update the packages:

$ sudo apt update

Next, we shall proceed to install the clickhouse-server and clickhouse-client packages:

$ sudo apt install clickhouse-server clickhouse-client

Here, it will ask to set a password for the default ClickHouse user.

We will require this password to access ClickHouse server later.

2. Starting the Service

After the ClickHouse server and client installation, let us proceed to start the database service.

i. We can start the clickhouse-server service and verify its status with the commands below:

$ sudo service clickhouse-server start
$ sudo service clickhouse-server status

The output of the status command confirms that the server is running.

ii. Further, we can also enable ClickHouse to run on boot with the command below:

$ sudo systemctl enable clickhouse-server

iii. Now that we have enabled ClickHouse, we can access ClickHouse with the password that we set during installation.

$ clickhouse-client --password

This will take us to the prompt where we can execute SQL statements create, update and delete databases, tables, etc.

We can also execute queries to retrieve filtered data from this client prompt.

For instance, to create a test database use the command below:

ch:) CREATE DATABASE test;

3. Change the listening IP address

By default ClickHouse only listens on localhost.

Thus we can access it only from the same server. However, we can change the listening IP address, by editing the /etc/clickhouse-server/config.xml file.

Open this file with any available text editor, find the string listen_host, and uncomment the line.

To make the ClickHouse server listen only on a specific IP address, we can edit the line as follows:

<listen_host>xxx.xxx.xxx.xxx</listen_host>

Replace xxx.xxx.xxx.xxx with the actual IP address.

4. Enable Tabix

To access the ClickHouse server through a web browser, we need to enable Tabix by editing the config.xml file as mentioned before.

Open the config.xml file as we did earlier, find the string http_server_default_response and uncomment the line.

After making changes to the config.xml file, we need to restart our clickhouse-server service:

# sudo systemctl restart clickhouse-server

Now, we can access ClickHouse over a browser with the link http://Your_IP_Address:8123 and log in using the default user and password that we specified earlier in the installation process.

5. Setting Up Firewall Rules

This step should be followed if we intend to connect to the ClickHouse database server remotely.

Remote connection requires ClickHouse to listen to an IP address other than localhost.

Thus changing the listening IP address that we discussed earlier is the primary step to enable remote access.

ClickHouse's server listens on port 8123 for HTTP connections and port 9000 for connections from clickhouse-client.

Allow access to both ports for the remote server IP address with the following command:

$ sudo ufw allow from remote_server_ip/32 to any port 8123
$ sudo ufw allow from remote_server_ip/32 to any port 9000

We can add additional IPs such as our local machine’s address in the same manner if required.

Now to verify the remote connection, install the clickhouse-client on the remote server with the steps that we followed initially. Then, start a client session by executing:

$ clickhouse-client --host your_server_ip --password

We will see an output that we have connected to the server.

How to Start ClickHouse Session ?

To start working with ClickHouse databases, launch the ClickHouse client.

When you start a session, the procedure is similar to other SQL management systems.

To start the client, use the command:

$ clickhouse-client

You may get this error:

“Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: default: Authentication failed: password is incorrect or there is no user with such name.”

When that error occurs, you need to define the password entered during the installation for the default user.

To do so, enter:

$ clickhouse-client --password test1234 --user default

Replace the sample password with your own.

The session starts.