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.