大資料 - ClickHouse

https://clickhouse.com/

概念

ClickHouse 是俄羅斯的 Yandex 于 2016 年開源的列式存盤資料庫（DBMS），使用 C++語言撰寫，主要用于在線分析處理查詢（OLAP），能夠使用 SQL 查詢實時生成分析資料報告，
OLAP：一次寫入，多次讀取

ClickHouse憑借著其出色的分析查詢性能，尤其是在日志處理上的優勢，非常有效的解決了關系型資料庫在海量資料場景下的分析查詢短板問題

ClickHouse 的特點

? 采用行式存盤時，資料在磁盤上的組織結構為：

? 采用列式存盤時，資料在磁盤上的組織結構為：

DBMS 的功能
幾乎覆寫了標準 SQL 的大部分語法，包括 DDL 和 DML，以及配套的各種函式，用戶管理及權限管理，資料的備份與恢復，

多樣化引擎
ClickHouse 和 MySQL 類似，把表級的存盤引擎插件化，根據表的不同需求可以設定
不同的存盤引擎，目前包括合并樹、日志、介面和其他四大類 20 多種引擎，

高吞吐寫入能力
ClickHouse 采用類 LSM Tree 的結構，資料寫入后定期在后臺 Compaction，通過類 LSM tree 的結構，ClickHouse 在資料匯入時全部是順序 append 寫，寫入后資料段不可更改，在后臺 compaction 時也是多個段 merge sort 后順序寫回磁盤，順序寫的特性，充分利用了磁盤的吞吐能力，即便在 HDD 上也有著優異的寫入性能，
官方公開 benchmark 測驗顯示能夠達到 50MB-200MB/s 的寫入吞吐能力，按照每行 100Byte 估算，大約相當于 50W-200W 條/s 的寫入速度，

資料磁區與執行緒級并行
ClickHouse 將資料劃分為多個 partition，每個 partition 再進一步劃分為多個 index granularity(索引粒度)，然后通過多個 CPU 核心分別處理其中的一部分來實作并行資料處理，在這種設計下，單條 Query 就能利用整機所有 CPU，極致的并行處理能力，極大的降低了查詢延時，
所以，ClickHouse 即使對于大量資料的查詢也能夠化整為零平行處理，但是有一個弊端就是對于單條查詢使用多 cpu，就不利于同時并發多條查詢，所以對于高 QPS（Queries Per Second 每秒查詢率） 的查詢業務，ClickHouse 并不是強項，

性能對比
單表查詢

關聯查詢

結論: ClickHouse 像很多 OLAP 資料庫一樣，單表查詢速度由于關聯查詢，而且ClickHouse 的兩者差距更為明顯，

安裝

https://clickhouse.com/docs/en/install/

[root@VipSoft clickhouse]# pwd
/opt/clickhouse
# 下載安裝程式
[root@VipSoft clickhouse]# curl https://clickhouse.com/ | sh
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  2473    0  2473    0     0   1514      0 --:--:--  0:00:01 --:--:--  1515

Will download https://builds.clickhouse.com/master/amd64/clickhouse into clickhouse

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  502M  100  502M    0     0  5262k      0  0:01:37  0:01:37 --:--:-- 6022k

Successfully downloaded the ClickHouse binary, you can run it as:
    ./clickhouse

You can also install it:
    sudo ./clickhouse install
# 設定權限
[root@VipSoft clickhouse]# chmod -R 775 ./
[root@VipSoft clickhouse]# ll
total 4447904
-rwxrwxr-x 1 root root 2147479552 Dec 30 13:46 clickhouse
[root@VipSoft clickhouse]# ./clickhouse install
Copying ClickHouse binary to /usr/bin/clickhouse.new
Renaming /usr/bin/clickhouse.new to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-server to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-client to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-local to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-benchmark to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-copier to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-obfuscator to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-git-import to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-compressor to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-format to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-extract-from-config to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-keeper to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-keeper-converter to /usr/bin/clickhouse.
Creating symlink /usr/bin/clickhouse-disks to /usr/bin/clickhouse.
Creating clickhouse group if it does not exist.
 groupadd -r clickhouse
Creating clickhouse user if it does not exist.
 useradd -r --shell /bin/false --home-dir /nonexistent -g clickhouse clickhouse
Will set ulimits for clickhouse user in /etc/security/limits.d/clickhouse.conf.
Creating config directory /etc/clickhouse-server.
Creating config directory /etc/clickhouse-server/config.d that is used for tweaks of main server configuration.
Creating config directory /etc/clickhouse-server/users.d that is used for tweaks of users configuration.
Data path configuration override is saved to file /etc/clickhouse-server/config.d/data-paths.xml.
Log path configuration override is saved to file /etc/clickhouse-server/config.d/logger.xml.
User directory path configuration override is saved to file /etc/clickhouse-server/config.d/user-directories.xml.
OpenSSL path configuration override is saved to file /etc/clickhouse-server/config.d/openssl.xml.
Creating log directory /var/log/clickhouse-server.
Creating data directory /var/lib/clickhouse.
Creating pid directory /var/run/clickhouse-server.
 chown -R clickhouse:clickhouse '/var/log/clickhouse-server'
 chown -R clickhouse:clickhouse '/var/run/clickhouse-server'
 chown  clickhouse:clickhouse '/var/lib/clickhouse'
Enter password for default user: 【輸入密碼】# 輸入密碼
Password for default user is saved in file /etc/clickhouse-server/users.d/default-password.xml.
Setting capabilities for clickhouse binary. This is optional.
Cannot set 'net_admin' or 'ipc_lock' or 'sys_nice' or 'net_bind_service' capability for clickhouse binary. This is optional. Taskstats accounting will be disabled. To enable taskstats accounting you may add the required capability later manually.
Allow server to accept connections from the network (default is localhost only), [y/N]: y # 允許外面訪問
The choice is saved in file /etc/clickhouse-server/config.d/listen.xml.
 chown -R clickhouse:clickhouse '/etc/clickhouse-server'

ClickHouse has been successfully installed.

Start clickhouse-server with:
 sudo clickhouse start

Start clickhouse-client with:
 clickhouse-client --password
# 啟動服務
[root@VipSoft clickhouse]# clickhouse start
 chown -R clickhouse: '/var/run/clickhouse-server/'
Will run sudo -u 'clickhouse' /usr/bin/clickhouse-server --config-file /etc/clickhouse-server/config.xml --pid-file /var/run/clickhouse-server/clickhouse-server.pid --daemon
Waiting for server to start
Waiting for server to start
Waiting for server to start
Waiting for server to start
Waiting for server to start
Server started

客戶端工具

DBeaver、NineData

標籤：其他

上一篇：DTALK直播預約 | 金融行業嘉賓分享：金融機構資料治理實踐路徑

下一篇：SQLSERVER 的主鍵索引真的是物理有序嗎？