By Roha Gagan
Today we're going to set up ClickHouse on an Alibaba Cloud Elastic Compute Service (ECS) instance.
ClickHouse is an open-source columnar database management system, which can be used for online analytical processing. ClickHouse comes with several great features, including support for linear scalability and compression and hard disk drive optimization. It also supports approximate and sampling calculations as well as distributed and parallel query processing, and it can store and process huge amounts of data.
But probably what makes ClickHouse a worthy choice is that it extremely useful when it comes to analyzing server logs, monitoring service's metrices, error rates and response times, and it is currently as the best option for Data Warehousing due to its high speed, with it being 100 times faster than MySQL or Hive.
As a web developer, you may want to check out Alibaba Cloud Elastic Compute Service, if you haven't already. ECS is a highly scalable, maintainable and flexible cloud service. You can easily access your ECS server through SSH or set up Remote Desktop access, and you can easily upgrade the server as your requirements change over time.
For this tutorial, you will need the following:
To start installing the different packages required, you will need to log in as a root user. After logging in, you will need to update the system by executing the command given below. If you skip this step, you won't be able to install all the packages.
apt-get update && apt-get upgrade
On successful the execution of the above command, you will be prompted with the question Is this ok? Type 'y' and hit the Enter key. It may take a few seconds to a few minutes to update your system depending upon the specifications of your ECS instance.
You will have to avoid executing any command directly as the root user. But you can add a non-root user with sudo rights, so that you can use this user to execute major commands when it is added to the sudo group. To add the user, execute the adduser gagan
command.
To add this user in the sudo group for assigning root privileges, execute the adduser gagan sudo
command.
Now log in as this new user. To do this, execute the su – gagan
command. Note that, for the command below, change gagan
to the name you chose as your username.
To install ClickHouse, download the latest version of ClickHouse, you can find this in a repository online. Add the GPG key of the repository so that you can securely download the validated packages of ClickHouse. To add the GPG key, execute the following command.
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4
Now there are two ways to add APT repositories to list. The first is to open /etc/apt/sources.list.d/clickhouse.list
using the nano
text editor. You can do this with this command.
sudo nano /etc/apt/sources.list.d/clickhouse.list
After you do that, you'll then need to add the following line of code and save it.
deb http://repo.yandex.ru/clickhouse/deb/stable/ main/
Alternatively, the second method is to simply copy the command below and execute it. It will automatically add the above code in the specified file.
echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list
To load the repositories added in the list, you will have to update the system again by executing the system update command.
sudo apt-get update
Now after it updates, you will be able to install the ClickHouse client and server.
To install the ClickHouse server, execute the sudo apt-get install clickhouse-server
command.
You will be prompted to ask type Y to continue the installation. After you type Y, don't forget to press the Enter key. You will see the screen for successful installation of ClickHouse server.
To install ClickHouse client, execute the sudo apt-get install clickhouse-client
command.
You will be prompted to type Y to continue the installation. Type Y and hit the Enter key. Soon later, you will see the screen for the successful installation of the ClickHouse client.
To start the ClickHouse server, execute the following command.
sudo service clickhouse-server start
Now verify the status of service by executing the following command.
sudo service clickhouse-server status
After starting service, you can access the ClickHouse client by executing command.
clickhouse-client.
If the ClickHouse client has been installed and configured properly, it will show your hostname. As in my case, it is learncybers.com
. Now you can execute SQL statements on the opened console.
There are many SQL operations that can be performed on this console. I've listed a few of them below.
CREATE DATABASE db_name
Note that db_name
should be replaced with your actual database name. Note also that there can be multiple databases, so before creating any table, you will have to choose the database in which you want to add your table.
USE db_name
CREATE TABLE table_name
(
column_name1 column_type [options],
column_name2 column_type [options],
...
) ENGINE = engine
Note that table_name
, column_name
, column_type
and options
should be replaced by your actual values. The most popular common column types are Datetime
, Date
, Float64
, UInt64
and String
.
INSERT INTO table_name VALUES (column_value_1, column_value_2,……..)
ALTER TABLE table_name UPDATE col_1 = val_1, col_2 = val_2 ... WHERE filter_conditions;
ALTER TABLE table_name ADD COLUMN column_name column_type;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DELETE WHERE filter_conditions;
To query a database for getting data:
SELECT fun_1(col_1), fun_2(col_2) FROM table_name WHERE filter_conditions row_options;
If you have activated firewalls, you will have to define a rule in an Alibaba Cloud security group for your cloud server to add exception for ports 80, 443, 8123 and 9000 because ClickHouse server listens to connections from the ClickHouse client on port 9000 and listens to HTTP requests on port 8123. You can enable these ports when creating an ECS instance. To do so, you can follow these steps below. Note that, by default, these ports are blocked by the firewalls.
Go to the Elastic Compute Service console.
Click on the more button for the ECS instance you are making ready for ClickHouse.
Click Configure Rules.
Click Quickly Create Rules.
Add the configurations as shown in screenshot below and click OK.
The views expressed herein are for reference only and don't necessarily represent the official views of Alibaba Cloud.
2,599 posts | 763 followers
FollowAlibaba Cloud Community - March 15, 2022
ApsaraDB - July 8, 2021
ApsaraDB - July 29, 2022
Alibaba EMR - February 10, 2022
ApsaraDB - May 7, 2021
ApsaraDB - March 15, 2024
2,599 posts | 763 followers
FollowElastic and secure virtual cloud servers to cater all your cloud hosting needs.
Learn MoreAn encrypted and secure cloud storage service which stores, processes and accesses massive amounts of data from anywhere in the world
Learn MoreLearn More
More Posts by Alibaba Clouder