Getting to know ClickHouse – Installation and getting started

Preface:

I’ve heard so much about ClickHouse that I haven’t looked into it in detail. Recently I looked at the ClickHouse documentation and decided to try it out. If you want to learn more about ClickHouse, learn along. This article introduces ClickHouse installation methods and the basics.

1. ClickHouse profile

ClickHouse is a column database management system (DBMS) for online analytics (OLAP). Open-source by Russian search engine giant Yandex. It is mainly used in the field of data analysis. At present, the domestic community is hot, and each big factory has followed up large-scale application in the field of OLAP.

Take a look at ClickHouse’s website to see some of its features:

• Fast: ClickHouse takes full advantage of all available hardware to process each query as quickly as possible. Peak processing performance for a single query exceeds 2 TB per second (after decompression, use only columns). In a distributed setup, reads are automatically balanced between healthy copies to avoid added latency.
• Fault tolerance: ClickHouse supports asynchronous replication across multiple hosts and can be deployed across multiple data centers. All nodes are equal, which avoids a single point of failure. The downtime of a single node or the entire data center does not affect the system’s read and write availability.
• Scalable: ClickHouse scales well in both vertical and horizontal directions. ClickHouse is easily tuned to execute on clusters with hundreds or thousands of nodes, on a single server, or even on a small virtual machine. Currently, the amount of data installed on each single node exceeds trillions of lines or hundreds of terabytes.
• Easy to use: ClickHouse is easy to use and out of the box. It simplifies all data processing: all structured data is absorbed into the system and immediately available to build reports. SQL allows the expression of desired results without involving any custom non-standard apis found in some DBMSS.

2. ClickHouse Installation tutorial

ClickHouse can run on any Linux, FreeBSD, or Mac OS X CPU architecture with X86_64, AArch64, or PowerPC64LE. ClickHouse does not appear to be installed on Windows, but Docker deployment is also supported. Windows can install ClickHouse under Docker.

According to the official documents: CentOS, RedHat and other Linux distributions can be installed using the official pre-compiled RPM packages. If your operating system does not support deb or RPM packages, you can install the Debian or Ubuntu system using the official pre-compiled deb package. You can also compile and install using TGZ packages or direct source code. ClickHouse is installed using RPM on CentOS.

Check the system version [root]@localhost ~]# more /etc/redhat-release 
CentOS Linux release 7.61810.(Core) # Check whether the current CPU supports SSE4.2
[root@localhost ~]# grep -q sse4_2 /proc/cpuinfo &&Echo "SSE 4.2 supported"||Echo "SSE 4.2 not supported" SSE4.2Supported # sudo yum install yum-utils
sudo rpm --import https://repo.clickhouse.tech/CLICKHOUSE-KEY.GPG
sudo yum-config-manager --add-repo https://repo.clickhouse.tech/rpm/stable/x86_64Sudo yum install clickhouse-server clickhouse-Client # starts Clickhouse Sudo/etc/init.d/clickhouse-server startEnter the clickHouse client root@localhost ~]# clickhouse-client
ClickHouse client version 20.12. 514. (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.12. 5 revision 54442.

localhost :) select 1;

SELECT 1

Query id: 42f5e5892 -f81-44e29 -fb8-De45e682acfc ┌ ─1─ ┐ │1│ └ ─ ─ ─ ┘1 rows in set. Elapsed: 0.002 sec. 

localhost :) select now();

SELECT now()

Query id: c4c867a9-ae0e4 -d6c-bb19-057e96Cf6624 ┌ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ now () ─ ┐ │2021- 01- 13 10:19:14│ └ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┘1 rows in set. Elapsed: 0.004 sec. 
Copy the code

3. ClickHouse is easy to operate

ClickHouse supports limited SQL operations, and the SQL syntax is similar to that of traditional relational databases. Here’s a quick overview of ClickHouse basic syntax:

# 1.Create a databaseCREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE =engine(...) ] # example: [root@localhost ~]# clickhouse-client     
ClickHouse client version 20.12. 514. (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.12. 5 revision 54442.

localhost :) create database test;

CREATE DATABASE test

Query id: c17cbf32-ab8c- 45a0- 8 -ebb-a7b0bdb88efe

Ok.

0 rows in set. Elapsed: 0.015 sec. 

localhost :) use test;

USE test

Query id: 4eeadf20-e8bc4 -b84-a953- 71.cf16e133b6

Ok.

0 rows in set. Elapsed: 0.001 sec.

# 2.Create a tableCREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE =Engine # example: root@localhost ~]# clickhouse-client
ClickHouse client version 20.12. 514. (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.12. 5 revision 54442.

localhost :) use test;

USE test

Query id: 37248a4f- 36ec4 -ed3-a08a-c345db228c98

Ok.

0 rows in set. Elapsed: 0.001 sec. 

localhost :) create table t1 (id Int32,name String) engine=TinyLog;

CREATE TABLE t1
(
    `id` Int32,
    `name` String
)
ENGINE = TinyLog

Query id: 8296c170- 72.fa- 4852.- 8447.-ab548fa3b7b8

Ok.

0 rows in set. Elapsed: 0.230 sec. 

localhost :) show tables;

SHOW TABLES

Query id: 7da8d2d5-e4ed- 45f1-b96c4 -D2392452 ba chrysene ─name─ ────── r1 rows in set. Elapsed: 0.007SEC. # TinyLog is the simplest table engine for storing data on disk. Often used for small tables. #3.Insert data # example: [root@localhost ~]# clickhouse-client
ClickHouse client version 20.12. 514. (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.12. 5 revision 54442.

localhost :) use test;

USE test

Query id: 71a1de2a- 17fe4 -a0b-b9b6-7e934016892e

Ok.

0 rows in set. Elapsed: 0.001 sec. 

localhost :) insert into t1 (id, name) values (1.'abc'), (2.'bbbb'), (3.'sdfg');

INSERT INTO t1 (id, name) VALUES

Query id: d4982851- 8 -b52- 4158.- 949.f-fa94cd7d8ff3

Ok.

3 rows in set. Elapsed: 0.002 sec. 

localhost :) select * from t1;

SELECT *
FROM t1

Query id: b73f366c-702e-4bda-b519-Cb087754bbad ┌ ─ ─ id ┬ ─ name ─ ┐ │1ABC │ │ │2 │ bbbb │
│  3│ SDFG │ └ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ┘3 rows in set. Elapsed: 0.002 sec. 
Copy the code

Although SQL syntax has similarities to relational databases, it is important to learn ClickHouse without thinking about it. In particular, data types, table engines, and other features are unfamiliar and difficult to learn.

Reference:

• clickhouse.tech/docs/zh/
• www.cnblogs.com/zhoujinyi/p…