Getting Started with ClickHouse

ClickHouse offers easy-to-use, flexible, and stable hosting services in the cloud. A data warehouse can be created in minutes for massive real-time data query and analysis, improving the overall efficiency of data value mining. By leveraging the massively parallel processing (MPP) architecture, ClickHouse can query data several times faster than conventional data warehouses.

Background Information

Assume that there is a student score table and you need to use ClickHouse to perform the following operations:

Create the user information table demo_t.
Add the user gender and subject to the user information.
Query basic user information by user ID.

Delete the user information table after the service ends.

**Table 1** Score table
user_id	name	sex	subject	score	time
10000	A	1	Spanish	89	2023-07-01 09:00:00
10001	B	0	Math	132	2023-07-01 09:00:00
10002	C	0	Math	90	2023-07-02 09:00:00
10003	D	0	English	120	2023-07-01 14:00:00
10004	E	1	Spanish	101	2023-07-01 09:00:00
10005	F	1	Spanish	110	2023-07-01 09:00:00

**Table 2** Parameters
Parameter	Description
10000	User ID, which uniquely identifies a user.
2023-07-01 09:00:00	Data import time.
A	Student name.
1	Gender female (0 indicates male).
Spanish	Discipline.
89	Score.

Buying a ClickHouse Cluster

Log in to the CloudTable console.
Select a region in the upper left corner.
Click Cluster Management.
Click Buy Cluster in the upper right corner of the Cluster Management page and set related parameters.
Click Buy Now. On the displayed page, confirm the specifications and click Finish.
Return to the cluster list to view the cluster creation progress. If the cluster status is In service, the cluster is created. For details, see Creating a ClickHouse Cluster.

Downloading the ClickHouse Client

Log in to the CloudTable console.
Select a region in the upper left corner.
Click Help in the navigation pane.
Choose Download the ClickHouse Client under Helpful Links on the right of the help page to download the client installation package.

Preparing an ECS

Buy an ECS.
1. Log in to the ECS console.
2. Click in the upper left corner to select a region.
3. Click the service list icon and choose Compute > Elastic Cloud Server.
4. Click Buy ECS.
5. Configure ECS parameters.
  1. Select the CPU architecture and specifications.
    Figure 1 Configuring basic settings
  2. Select the image and disk specifications.
    Figure 2 Selecting the image and disk specifications
Click Next: Configure Network.
1. Select a VPC and a security group.
  Figure 3 Configuring network
2. Select Auto assign and set Billed By to Traffic.
  Figure 4 Configuring EIP type and bandwidth billing type
Click Next: Configure Advanced Settings.
Configure the ECS name and password.

Figure 5 Configuring advanced settings
Click Next: Confirm to confirm the configuration information.
Figure 6 Confirming the configuration information
Select an enterprise project, select the sentence in the Agreement area, confirm the configuration information, and click Submit.
Return to the ECS list page and view the ECS creation progress.
When the ECS status changes to Running, the ECS is created.

Installing the ClickHouse Client

You can manually install the client on an ECS.

Use the SSH login tool (such as PuTTY) to log in to the Linux ECS through the EIP.
For details about how to log in to the ECS, see "Remotely Logging In to a Linux ECS (Using an SSH Password)" in Logging In to a Linux ECS of the Elastic Cloud Server User Guide.
Transfer the client downloaded in 2 to the Linux ECS.

Install the client and connect to the cluster.

Use the SSH login tool to remotely log in to the Linux ECS through the EIP.
For details, see Login Using an SSH Password in the Elastic Cloud Server User Guide.
Go to the root directory of the SSH login tool.
```
cd /
```
Create a folder in the root directory.
```
mkdir Folder name
```
Go to the directory of the created folder.
```
cd /Folder name/
```
Place the client in the directory.
Decompress the client package.
```
tar  -zxf   Client package name
```
Load the .so file.
```
sh install.sh
```
Go to the bin directory.
```
cd bin/
```
Grant the 700 permission to the directory.
```
chmod 700 clickhouse
```

Connect to the port of the ClickHouse cluster.

Use the following command to connect to a normal cluster.

./clickhouse client --host Private IP address of the cluster   --port Port --user admin --password Password

Check the security cluster connection commands in Connecting to a Security Cluster.

./clickhouse client --host Private IP address of the cluster --port port --user admin --password Password --secure --config-file /root/config.xml

Private IP address of the cluster: cluster access address on the cluster details page. Replace it with the access address of the cluster you purchased.
Password: the password set when you purchase the cluster. If there are special characters, use backslashes (\) to escape them. If the password is enclosed in single quotation marks ('), the special characters do not need to be escaped.

**Table 3** Custom security rules
Direction	Action	Port/Range	Type	Destination/Source Address	Usage
Outbound	Allow	All	IPv4/IPv6	0.0.0.0/0	Permit in the outbound direction
Inbound	Allow	8123		Security group of the CloudTable ClickHouse cluster	ClickHouse HTTP port number
	Allow	9000			ClickHouse TCP port number
	Allow	8443			ClickHouse HTTPS port number
	Allow	9440			Secure TCP security port of ClickHouse

Inserting Data

Create a data table using the ClickHouse cluster and query the table data.

Create a database.
```
create database DB_demo;
```
Use the database.
```
use DB_demo;
```

Create a table.

create table DB_demo_t(user_id Int32,name String,sex Tinyint ,subject String,score Int32,time datetime)engine=TinyLog;

Insert data.

insert into DB_demo_t(user_id,name,sex,subject,score,time) values('10000','A','1','Spanish','89','2023-07-01 09:00:00');
insert into DB_demo_t(user_id,name,sex,subject,score,time) values('10001','B','0','Math','132','2023-07-01 09:00:00');
insert into DB_demo_t(user_id,name,sex,subject,score,time) values('10002','C','0','Math','90','2023-07-02 09:00:00');
insert into DB_demo_t(user_id,name,sex,subject,score,time) values('10003','D','0','English','120','2023-07-01 14:00:00');
insert into DB_demo_t(user_id,name,sex,subject,score,time) values('10004','E','1','Spanish','101','2023-07-01 09:00:00');
insert into DB_demo_t(user_id,name,sex,subject,score,time) values('10005','F','1','Spanish','110','2023-07-01 09:00:00');

Query data.

Query the imported data.

host-172-16-13-95 :) select * from DB_demo_t;
SELECT *
FROM DB_demo_t
Query id: 4e119f77-0592-4131-bbe2-31f42bc069a1
┌─user_id─┬─name─┬─sex─┬─subject─┬─score─┬────────────────time─┐
│   10000 │ A    │   1 │ Spanish   │    89 │ 2023-07-01 09:00:00 │
│   10001 │ B    │   0 │ Math   │   132 │ 2023-07-01 09:00:00 │
│   10002 │ C    │   0 │ Math   │    90 │ 2023-07-02 09:00:00 │
│   10003 │ D    │   0 │ English   │   120 │ 2023-07-01 14:00:00 │
│   10004 │ E    │   1 │ Spanish   │   101 │ 2023-07-01 09:00:00 │
│   10005 │ F    │   1 │ Spanish   │   110 │ 2023-07-01 09:00:00 │
└─────────┴──────┴─────┴────────┴───────┴─────────────────────┘
6 rows in set. Elapsed: 0.004 sec.