Getting started with ClickHouse, a column database

Introduction to the

ClickHouse is a column database for online analytics (OLAP), developed and open-source by a Russian search engine company.

Key features of an OLAP scenario

• Most are read requests
• Data is written in large quantities (>1000 rows)
• The added data is not modified
• Each query reads a large number of rows from the database, but requires only a few columns
• Wide tables, each containing a large number of columns
• Fewer queries
• A lot of business is necessary
• Low data consistency requirements
• But query throughput is high (up to 1 billion rows per second per server)
• Each query is small except for a large table
• The query result is smaller than the source data

architecture

Core features

Complete DBMS functionality

A relational model is used to describe data and provide the basic functionality of a traditional database. Supports DDL, DML, permission control, data backup and recovery, and distributed management

Column storage and data compression

ClickHouse uses a columnar storage structure. For a user table, there is a lot of automatic storage. For column storage, only the two columns of data are read, effectively reducing the scan range and improving I/O efficiency. The nature of the compression algorithm is to transform the encoding of repeated data to reduce the length of data. ClickHouse uses LZ4 compression algorithm by default

Vectorization execution engine

Vectorization execution requires the use of Single Instruction Multiple Data (SIMD) instructions of THE CPU. Single Instruction operates Multiple Data and improves performance through Data parallelism. Its principle is to realize Data parallel operation at the LEVEL of CPU registers.

Diverse table engines

Distributed and multithreaded

Vectorization execution improves performance through data-level parallelism Multithreading improves performance through thread-level parallelism support partitioning – vertical scaling, use multithreading support sharding – horizontal scaling, use distributed principles

The main architecture more

In the multi-master architecture, all nodes in a cluster have equal roles. Clients can access any node to obtain the same effect.

The Log table engine

Log is used for scenarios where fast reads and writes are entered into a small table (1 million rows) and then read all of them

• Partitions and indexes are not supported
• Delete and update are not supported
• Data sequence Append disk
• Atomic writes are not supported
• Insert blocks select

Performance sorting:

• Tinylog: provides the lowest performance. It does not support concurrent read and poor query performance. It is suitable for storing intermediate data temporarily
• Striplog: Supports concurrent read. All columns are stored in one file
• Log: Supports concurrent read. Each column stores files separately

Special table engine

memory

Mysql memory table type, data stored in memory, restart data will be lost

buffer

Buffer(database, table, num_layers, MIN_time, max_time, min_ROWS, max_rows, min_bytes, max_bytes)

create table test.buffer_to_memory_1 as test.memory_1 engine = Buffer(test, memory_1, 16, 10 ,100, 1000, 1000000, 10000000, 100000000); Data inserted into the buffer table may be written to the target table in different order and in different blocks to satisfy all min_, or any max_ may be flushed to the memory_1 table

file

Stored directly in a file

Merge tree table engine

MergeTree

• Support the partition
• Storing ordered
• The primary key index
• Data TTL
• Sparse index

Primary key data can be repeated

create table test_1( id UInt16, create_time Date )ENGINE=MergeTree() PARTITION BY toYYYYMMDD(create_time) ORDER BY (id,create_time) PRIMARY KEY (id,create_time) TTL create_time + INTERVAL 1 MONTH

ToYYYYMMDD (create_time) Partition data BY day TTL create_time + INTERVAL 1 MONTH Saves data for a MONTH

ReplacingMergeTree

To solve the problem that the same primary key of MergeTree cannot be removed, we introduced ReplacingMergeTree but also need to force the optimize table test_re problem

• Optimize is a back-end action that cannot meet business timely query requirements
• When sharding, data is in different shards and deduplication cannot be implemented

More is used to ensure that data is eventually de-duplicated

CollapingMergeTree

Further improve ReplacingMergeTree, Sign create table test_2(id UInt16, ViewNum UInt16, create_time Date, Sign Int8 )ENGINE=CollapingMergeTree(Sign) GROUP BY ID; If Sign=1, data is valid. If Sign=-1, data is deleted

INSERT INTO TEST_2(… INSERT INTO TEST_2(… , 1)

In multithreaded write cases, -1 records are written first, which results in failure to fold properly

SQL SELECT id,sum(ViewNum * Sign) FROM test_2 GROUP BY id HAVING sum(Sign)>0;

VersionedCollapsingMergeTree

In the CollapingMergeTree view, a new column with the same Version primary key, the same Version, and the opposite Sign is added. Create table test_3(ID UInt16, ViewNum UInt16, create_time Date, Sign Int8, Version UInt8 )ENGINE=VersionedCollapsingMergeTree(Sign,Version) GROUP BY ID;

SQL SELECT id,sum(ViewNum * Sign) FROM test_3 GROUP BY id HAVING sum(Sign)>0;

SummingMergeTree

Primary key columns can be pre-aggregated. During background merge, rows with the same primary key are summed to reduce disk storage space and speed up data query

• The data is aggregated only when merging in the background, so the query SQL will still have GROUP BY
• During preaggregation, the sum of all columns except the primary key is performed, and if the column is not numeric, the values in the row are randomly selected

AggregatingMergeTree

SummingMergeTree is a kind of pre-aggregation engine that aggregates non-primary key columns and the AggregatingMergeTree can specify various aggregation functions

Use with materialized views

CREATE TABLE test.visits(UserID UInt64, CounterID UInt8, StartDate Date, Sign Int8 )ENGINE CollapsingMergeTree(Sign) ORDER BY UserID; CREATE MATERIALIZED VIEW test. Basic ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate) AS SELECT CounterID, StartDate, sumState(Sign) AS Visits, uniqState(UserID) AS Users FROM test.visits GROUP BY CounterID, StartDate; INSERT INTO test.visits VALUES (1, 1, '2020-08-11', 1), (2, 11, '2020-08-11', 1);  SELECT StartDate, sumMerge(Visits) as Visits, uniqMerge(Users) as Users FROM test.basic GROUP BY StartDate ORDER BY StartDate;Copy the code