Setting up ClickHouse on Alibaba Cloud

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.

Prerequisites

For this tutorial, you will need the following:

You must have Alibaba Cloud Elastic Compute Service (ECS) activated and have verified a valid payment method. Alibaba Cloud supports PayPal and Credit Card payments. If you are a new user, you can get $450 to $1,300 credits in your Alibaba Cloud personal or enterprise account. If you don't know about how to set up an ECS instance, you can refer to this tutorial or this quick-start guide.
Your ECS instance (server) must have at least 1 GB of RAM and 1 core processor. However, a 2 core processor is recommended for this tutorial.
You should have your server's hostname set up.
You should have access to the VNC console on Alibaba Cloud, or have an SSH client installed on your PC.

Update the Ubuntu System

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.

Add a User

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.

Adding the Required APT Repositories

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

Update the System

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.

Install the ClickHouse 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.

Install the ClickHouse Client

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.

Starting the Server

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.

Common Queries

There are many SQL operations that can be performed on this console. I've listed a few of them below.

To create database:

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.

To select a database, execute this query:

USE db_name

To create a table:

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.

To insert new records in your database:

INSERT INTO table_name VALUES (column_value_1, column_value_2,……..)

To update value of any column:

ALTER TABLE table_name UPDATE  col_1 = val_1, col_2 = val_2 ...  WHERE  filter_conditions;

To add a new column in a table:

ALTER TABLE table_name ADD COLUMN column_name column_type;

To drop a column in a table:

ALTER TABLE table_name DROP COLUMN column_name;

To delete records from a table:

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;

Setting up Firewalls for HTTP, HTTPS, and Other Required Ports

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.

Community

Setting up ClickHouse on Alibaba Cloud

Prerequisites

Update the Ubuntu System

Add a User

Adding the Required APT Repositories

Update the System

Install the ClickHouse Server

Install the ClickHouse Client

Starting the Server

Common Queries

Setting up Firewalls for HTTP, HTTPS, and Other Required Ports

Read previous post:

Read next post:

Alibaba Clouder

You may also like

Comments

Alibaba Clouder

Related Products

ECS(Elastic Compute Service)

OSS(Object Storage Service)