ClickHouse入門

来源:https://www.cnblogs.com/xiaoQQya/archive/2022/05/26/16313669.html
-Advertisement-
Play Games

ClickHouse入門 1. 簡介 ClickHouse 是俄羅斯的 Yandex 於 2016 年開源的列式存儲資料庫（DBMS），使用 C++ 語言編寫，主要用於線上分析處理查詢（OLAP），能夠使用 SQL 查詢實時生成分析數據報告。 1.1 列式存儲 以下麵的表為例： Id Name Ag ...


ClickHouse入門

目錄

1. 簡介

ClickHouse 是俄羅斯的 Yandex 於 2016 年開源的列式存儲資料庫（DBMS），使用 C++ 語言編寫，主要用於線上分析處理查詢（OLAP），能夠使用 SQL 查詢實時生成分析數據報告。

1.1 列式存儲

以下麵的表為例：

Id Name Age
1 張三 18
2 李四 22
3 王五 34

  • 採用行式存儲時，數據在磁碟上的組織結構為：

    1 張三 18 2 李四 22 3 王五 34

    優點時向查詢某個人的所有屬性時，可以通過一次磁碟查找加順序讀取就可以。但是當想查所有人的年齡時，需要不停的查找，或者全表掃描才行，遍歷的很多數據都是不需要的。

  • 採用列式存儲時，數據在磁碟上的組織結構為：

    1 2 3 張三 李四 王五 18 22 34

    這時想查詢所有人的年齡只需把年齡那一列拿出來就可以了。

  • 列式存儲的優點：

    • 對於列的聚合，計數，求和等統計操作原因優於行式存儲。
    • 由於某一列的數據類型都是相同的，針對於數據存儲更容易進行數據壓縮，每一列選擇更優的數據壓縮演算法，大大提高了數據的壓縮比重。
    • 由於數據壓縮比較好，一方面節省了磁碟空間，另一方面對於 cache 也有了更大的發揮空間。

1.2 DBMS 的功能

幾乎覆蓋了標準 SQL 的大部分語法，包括 DDL 和 DML，以及配套的各種函數，用戶管理及許可權管理，數據的備份與恢復。

1.3 多樣化引擎

ClickHouse 和 MySQL 類似，把表級的存儲引擎插件化，根據表的不同需求可以設定不同的存儲引擎。目前包括合併樹（Merge Tree）、日誌、介面和其它四大類 20 多種引擎。

1.4 高吞吐寫入能力

ClickHouse 採用類 LMS Tree（Log Structured Merge Tree）的結構，數據寫入後定期在後臺 Compaction。通過類 LMS Tree 的結構，ClickHouse 在數據導入時全部都是順序 append 寫，寫入後數據段不可更改，在後臺 compaction 時也是多個段 merge sort 後順序寫回磁碟。順序寫的特性，充分利用了磁碟的吞吐能力，即便在 HDD 上也有著優異的寫入性能。

官方公開 benchmark 測試顯示能夠達到 50MB-200MB/s 的寫入吞吐能力，按照每行 100 Byte 估算，大約相當於 50W-200W條/s 的寫入速度。

1.5 數據分區與線程級並行

ClickHouse 將數據劃分為多個 Partition， 每個 Partition 再進一步劃分為多個 Index Granularity（索引粒度），然後通過多個 CPU 核心分別處理其中的一部分來實現並行數據處理。在這種設計下，單條 Query 就能利用整機所有 CPU。極致的並行處理能力，極大的降低了查詢延遲。

所以，ClickHouse 即使對於大量數據的查詢也能夠化整為零平行處理。但是有一個弊端就是對於單條查詢使用多 CPU，就不利用同時併發多條查詢。所以對於高 QPS（Queries-per-second） 的查詢業務，ClickHouse 並不是強項。

1.6 性能對比

  • 單表查詢
  • 關聯查詢

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

2. 安裝

2.1 準備工作

2.1.1 關閉防火牆

# CentOS
$ sudo systemctl stop firewalld
$ sudo systemctl status firewalld
● firewalld.service
   Loaded: masked (/dev/null; bad)
   Active: inactive (dead)

# Ubuntu
$ sudo ufw disable
$ sudo ufw status
Status: inactive

2.1.2 修改打開文件數量限制

編輯 /etc/security/limits.conf文件（可能同時需要修改/etc/security/limits.d文件夾下的配置文件），加入以下內容：

$ ulimit -a
-t: cpu time (seconds)              unlimited
-f: file size (blocks)              unlimited
-d: data seg size (kbytes)          unlimited
-s: stack size (kbytes)             8192
-c: core file size (blocks)         0
-m: resident set size (kbytes)      unlimited
-u: processes                       131072
-n: file descriptors                65536
-l: locked-in-memory size (kbytes)  65536
-v: address space (kbytes)          unlimited
-x: file locks                      unlimited
-i: pending signals                 63858
-q: bytes in POSIX msg queues       819200
-e: max nice                        0
-r: max rt priority                 0
-N 15:                              unlimited

$ sudo vim /etc/security/limits.conf
# 用戶@用戶組 | 軟限制soft 或 硬限制hard | 限制項 | 限制大小
# 打開文件數量限制
* soft nofile 65536
* hard nofile 65536
# 用戶進程數量限制
* soft nproc 131072
* hard nproc 131072

註：需要重啟或重新登錄後才能生效（測試 SSH 重新登錄不生效）。

2.2 單機安裝

2.2.1 Ubuntu

sudo apt-get install apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4

echo "deb https://repo.clickhouse.tech/deb/stable/ main/" | sudo tee \
    /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update

sudo apt-get install -y clickhouse-server clickhouse-client

sudo service clickhouse-server start
clickhouse-client

清華鏡像源加速：修改/etc/apt/sources.list.d/clickhouse.list文件，內容為

deb https://mirrors.tuna.tsinghua.edu.cn/clickhouse/deb/stable/ main/

2.2.2 CentOS

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/clickhouse.repo
sudo yum install clickhouse-server clickhouse-client

sudo /etc/init.d/clickhouse-server start
clickhouse-client

清華鏡像源加速：修改/etc/yum.repos.d/clickhouse.repo文件，內容為

[repo.yandex.ru_clickhouse_rpm_stable_x86_64]
name=clickhouse stable
baseurl=https://mirrors.tuna.tsinghua.edu.cn/clickhouse/rpm/stable/x86_64
enabled=1

2.3 配置文件

2.3.1 服務端配置

配置文件目錄為/etc/clickhouse-server，目錄結構如下：

  • config.d
  • config.xml：服務端配置
  • users.d
  • users.xml：許可權密碼，參數配置（CPU，記憶體等）

2.3.2 修改允許外部主機訪問

編輯/etc/clickhouse-server/config.xml，取消<!-- <listen_host>::</listen_host> -->的註釋。

2.4 啟動連接

# 啟動 ClickHouse 服務端
$ sudo systemctl start clickhouse-server
# 或
$ sudo clickhouse start

# 查看服務端狀態
$ sudo systemctl status clickhouse-server
# 或
$ sudo clickhouse status

# 開啟開機自啟動
$ sudo systemctl enable clickhouse-server
# 關閉開機自啟動
$ sudo systemctl disable clickhouse-server

# 客戶端連接，-m 參數設置語句允許換行
$ clickhouse-client -m -h localhost -p 9000

3. 數據類型

參考官方文檔：https://clickhouse.tech/docs/zh/sql-reference/data-types/

3.1 整型

固定長度的整型，包括有符號整型或無符號整型。

整型範圍（-2n-1 ~ 2n-1-1）：

  • Int8 - [-128 : 127]

  • Int16 - [-32768 : 32767]

  • Int32 - [-2147483648 - 2147483647]

  • Int63 - [-9223372036854775808 - 9223372036854775807]

無符號整型範圍（0 ~ 2n-1）：

  • UInt8 - [0 : 255]
  • UInt16 - [0 : 65535]
  • UInt32 - [0 : 4294967295]
  • UInt64 - [0 : 18446744073709551615]

使用場景：個數、數量、也可以存儲 ID。

3.2 浮點型

  • Float32 - float
  • Float64 - double

建議儘可能以整數形式存儲數據。例如，將固定精度的數字轉換為整數值，如時間用毫秒為單位表示，因為浮點型進行計算時可能引起四捨五入的誤差。

:) select 1.0 - 0.9;

SELECT 1. - 0.9

Query id: 29890dfc-cc42-4fd8-8bd4-edf9aca7136f

┌──────minus(1., 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

使用場景：一般數據值比較小，不涉及大量的統計計算，精度要求不高的時候。比如保存商品的重量。

3.3 布爾型

沒有單獨的類型來存儲布爾值。可以使用 UInt8 類型，取值限製為 0 或 1。

3.4 Decimal 型

有符號的浮點數，可在加、減和乘法運算過程中保持精度。對於除法，最低有效數字會被丟棄（不捨入）。

  • Decimal32(s)，相當於 Decimal(9-s,s)，有效位數 1~9
  • Decimal64(s)，相當於 Decimal(18-s,s)，有效位數1~18
  • Decimal128(s)，相當於 Decimal(38-s,s)，有效位數1~38

說明：s 標識小數位。

使用場景：一般金額、匯率、利率等欄位為了保證小數點精度，都使用 Decimal 進行存儲。

3.5 字元串

  • String

    字元串可以是任意長度的，它可以包含任意的位元組集，包含空位元組。

  • FixedString(N)

    固定長度 N 的字元串，N 必須是嚴格的正自然數。當服務端讀取長度小於 N 的字元串的時候，通過在字元串末尾添加空位元組來達到 N 位元組長度；當服務端讀取長度大於 N 的字元串的時候，將返回錯誤消息。

    與 String 相比，極少會使用 FixedString，因為使用起來不是很方便。

使用場景：名稱、文字描述、字元型編碼。固定長度的可以保存一些定長的內容，比如一些編碼、性別等，但是考慮到一定的變化風險，帶來收益不夠明顯，所以定長字元串使用意義有限。

3.6 枚舉類型

包括 Enum8 和 Enum16 類型。Enum 保存 ‘String’ = Integer 的對應關係。

  • Enum8 用 ‘String’ = Integer 對錶示
  • Enum16 用 ‘String’ = Integer 對錶示
-- 創建一個帶有 Enum8('hello' = 1, 'world' = 2) 類型的表
CREATE TABLE t_enum
(
    x Enum8('hello' = 1, 'world' = 2)
) ENGINE = TinyLog;

-- 插入數據
INSERT INTO t_enum
VALUES ('hello'),
       ('world'),
       ('hello');

-- 嘗試插入其它值
INSERT INTO t_enum
VALUES ('haha');

-- 查看枚舉對應數值
SELECT cast(x, 'Int8')
FROM t_enum;

使用場景：對一些狀態、類型等欄位算是一種空間優化，也算是一種數據約束。但是實際使用中往往會因為一些數據內容的變化增加一定的維護成本，甚至是數據丟失問題，所以謹慎使用。

3.7 時間類型

  • Date 接受年-月-日的字元串，比如：‘2021-08-01’
  • DateTime 接受年-月-日 時:分:秒的字元串，比如：‘2021-08-01 00:25:12’
  • DateTime64 接受年-月-日 時:分:秒.亞秒的字元串，比如：‘2021-08-01 00:25:12.66’

日期類型，用兩個位元組存儲，表示從 1970-01-01（無符號）到當前的日期值。

3.8 數組

Array(T)：由 T 類型元素組成的數組。

T 可以是任意類型，包含數組類型。但不推薦使用多維數組，ClickHouse 對多維數組的支持有限。例如，不能在 Merge Tree 表中存儲多維數組。

:) select array(1, 2) as x, toTypeName(x);

SELECT
    [1, 2] AS x,
    toTypeName(x)

Query id: cfd2208b-bdbd-4809-a9d5-2ba3441b24c6

┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8)            │
└───────┴─────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

4. 表引擎

表引擎是 ClickHouse 的一大特色。可以說，表引擎決定瞭如何存儲表的數據。包括：

  • 數據的存儲方式和位置，寫到哪裡以及從哪裡讀取數。
  • 支持哪些查詢以及如何支持。
  • 併發數據訪問。
  • 索引的使用（如果存在）。
  • 是否可以執行多線程請求。
  • 數據複製參數。

表引擎的使用方式就是必須顯式的在創建表時定義該表使用的引擎，以及引擎使用的相關參數。

註意：引擎的名稱大小寫敏感。

4.1 TinyLog

以列文件的形式保存在磁碟上，不支持索引，沒有併發控制。一般保存少量數據的小表，

生產環境上作用有限。可用於平時練習測試使用。

CREATE TABLE t_tinylog
(
    id   String,
    name String
) ENGINE = TinyLog;

4.2 Memory

記憶體引擎，數據以未壓縮的原始形式直接保存在記憶體當中，伺服器重啟數據就會消失。讀寫操作不會相互阻塞，不支持索引。簡單查詢下有非常非常高的性能表現（超過 10G/S）。

一般用的地方不多，除了用來測試，就是在需要非常高性能，同時數據量又不太大（上限大概 1 億行）的場景。

4.3 MergeTree

ClickHouse 中最強大的表引擎當屬 MergeTree（合併樹）引擎及該系列（*MergeTree）中的其它引擎，支持索引和分區，地位可以相當於 InnoDB 之於 MySQL。而且基於 MergeTree，還衍生出了很多子引擎，也是非常有特色的引擎。

-- 創建表
CREATE TABLE t_order_mt
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2),
    create_time  DateTime
) ENGINE = MergeTree
      PARTITION BY toYYYYMMDD(create_time)
      PRIMARY KEY (id)
      ORDER BY (id, sku_id);
 
-- 插入數據
INSERT INTO t_order_mt
VALUES (101, 'sku_001', 1000.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 11:00:00'),
       (102, 'sku_004', 2500.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 12000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 600.00, '2021-08-02 12:00:00');
       
-- 查看數據，按分區顯示
:) select * from t_order_mt;

SELECT *
FROM t_order_mt

Query id: 49467be5-462d-43a1-8293-6a8eea414c13

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2021-08-01 11:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_002 │     12000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

6 rows in set. Elapsed: 0.003 sec.

註意：ClickHouse 的主鍵並沒有自動添加唯一約束，可以重覆。

MergeTree 其實還有很多參數（絕大多數用預設值即可），但以上三個參數是比較重要的，也涉及了關於 MergeTree 的很多概念。

4.3.1 PARTITION BY 分區（可選）

  • 作用：降低掃描的範圍，優化查詢速度。

  • 不分區：只會使用一個分區。

  • 分區目錄：MergeTree 是以列文件 + 索引文件 + 表定義文件組成的，但是如果設定了分區那麼這些文件就會保存到不同的分區目錄中。

  • 並行：分區後，面對涉及跨分區的查詢統計，ClickHouse 會以分區為單位進行處理。

  • 數據寫入與分區合併：任何一個批次的數據寫入都會產生一個臨時分區，不會納入任何一個已有的分區。寫入後的某個時刻（大概 10-15 分鐘後），ClickHouse 會自動執行合併操作（等不及也可以手動通過 optimize 執行），把臨時分區的數據合併到已有分區中。

    -- 優化 xxx 表
optimize table xxx final;

-- 優化 xxx 表的 xxx 分區
optimize table xxx partition xxx final;

    例如：

    -- 再次插入上面的數據
INSERT INTO t_order_mt
VALUES (101, 'sku_001', 1000.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 11:00:00'),
       (102, 'sku_004', 2500.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 12000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 600.00, '2021-08-02 12:00:00');

-- 查看數據，多出兩個臨時分區
:) select * from t_order_mt;

SELECT *
FROM t_order_mt

Query id: 54943ffa-7d59-4b0b-8a7f-500f323c10a7

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2021-08-01 11:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_002 │     12000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2021-08-01 11:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_002 │     12000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

12 rows in set. Elapsed: 0.004 sec.

-- 手動執行優化命令
:) OPTIMIZE TABLE  t_order_mt FINAL;

-- 再次查看數據
:) select * from t_order_mt;

SELECT *
FROM t_order_mt

Query id: a4a9e510-b4a9-419b-8596-f9fe9016be1b

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2021-08-02 12:00:00 │
│ 102 │ sku_002 │       600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2021-08-01 12:00:00 │
│ 101 │ sku_001 │      1000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2021-08-01 11:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_002 │     12000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2021-08-01 11:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_002 │     12000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

12 rows in set. Elapsed: 0.004 sec.

4.3.2 PRIMARY KEY 主鍵（可選）

ClickHouse 中的主鍵，和其它資料庫不太一樣，它只提供了一級索引，但是卻不是唯一約束。這就意味著是可以存在相同 primary key 的數據的。

主鍵的設定主要依據是查詢語句中的 where 條件。

根據條件通過對主鍵進行某種形式的二分查找，能夠定位到對應的 index granularity，避免了全表掃描。

index_granularity：直譯為索引粒度，指在稀疏索引中兩個相鄰索引對應數據的間隔。ClickHouse 中的 MergeTree 引擎預設是 8192。官方不建議修改該值，除非該列存在大量重覆值，比如在一個分區中幾萬行才有一個不同數據。

稀疏索引：按指定間隔記錄索引值，優點是可以用很少的索引數據，定位更多的數據，代價就是只能定位到索引粒度的第一行，然後再進行行掃描。

4.3.3 ORDER BY （必填）

order by 設定了分區內的數據按照哪些欄位進行有序保存。

order by 是 MergeTree 中唯一一個必填項，甚至比 primary key 還重要，因為當用戶不設置主鍵的時候，很多處理會依照 order by 的欄位進行處理（比如去重和彙總）。

要求：主鍵必須是 order by 欄位的首碼欄位。比如 order by 欄位是 (id, sku_id)，那麼主鍵必須是 id 或者 (id, sku_id)。

4.3.4 二級索引

目前 ClickHouse 的官網上二級索引的功能在 v20.1.2.4 之前是被標註為實驗性的，在該版本之後預設是開啟的。

  1. 老版本使用二級索引前需要增加設置

    是否允許使用實驗性的二級索引（v20.1.2.4 開始，該參數已經被刪除，預設開啟）

    set allow_experimental_data_skipping_indices=1

  2. 創建測試表

    CREATE TABLE t_order_mt2
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2),
    create_time  DateTime,
    INDEX a total_amount TYPE minmax GRANULARITY 5
) ENGINE = MergeTree
      PARTITION BY toYYYYMMDD(create_time)
      PRIMARY KEY (id)
      ORDER BY (id, sku_id);

    說明：其中GRANULARITY N是設定二級索引對於一級索引粒度的粒度。

  3. 插入數據

    INSERT INTO t_order_mt2
VALUES (101, 'sku_001', 1000.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 11:00:00'),
       (102, 'sku_004', 2500.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 12000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 600.00, '2021-08-02 12:00:00');

  4. 測試效果

    $ clickhouse-client --send_logs_level=trace <<< 'select * from t_order_mt2 where total_amount > toDecimal32(900, 2)';
[Ubuntu18-006] 2021.08.06 11:34:17.603474 [ 29915 ] {4cc867dd-fd0d-4b7e-8ec3-f404e70b190d} <Debug> executeQuery: (from [::ffff:127.0.0.1]:34884, using production parser) select * from t_order_mt2 where total_amount > toDecimal32(900, 2)
[Ubuntu18-006] 2021.08.06 11:34:17.603873 [ 29915 ] {4cc867dd-fd0d-4b7e-8ec3-f404e70b190d} <Debug> InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "total_amount > toDecimal32(900, 2)" moved to PREWHERE
[Ubuntu18-006] 2021.08.06 11:34:17.604018 [ 29915 ] {4cc867dd-fd0d-4b7e-8ec3-f404e70b190d} <Trace> ContextAccess (default): Access granted: SELECT(id, sku_id, total_amount, create_time) ON default.t_order_mt2
[Ubuntu18-006] 2021.08.06 11:34:17.604077 [ 29915 ] {4cc867dd-fd0d-4b7e-8ec3-f404e70b190d} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[Ubuntu18-006] 2021.08.06 11:34:17.604248 [ 29915 ] {4cc867dd-fd0d-4b7e-8ec3-f404e70b190d} <Debug> default.t_order_mt2 (482d26fd-99ef-4e94-882d-26fd99efbe94) (SelectExecutor): Key condition: unknown
[Ubuntu18-006] 2021.08.06 11:34:17.604324 [ 29915 ] {4cc867dd-fd0d-4b7e-8ec3-f404e70b190d} <Debug> default.t_order_mt2 (482d26fd-99ef-4e94-882d-26fd99efbe94) (SelectExecutor): MinMax index condition: unknown
[Ubuntu18-006] 2021.08.06 11:34:17.607684 [ 29915 ] {4cc867dd-fd0d-4b7e-8ec3-f404e70b190d} <Debug> default.t_order_mt2 (482d26fd-99ef-4e94-882d-26fd99efbe94) (SelectExecutor): Index `a` has dropped 1/2 granules.
[Ubuntu18-006] 2021.08.06 11:34:17.607712 [ 29915 ] {4cc867dd-fd0d-4b7e-8ec3-f404e70b190d} <Debug> default.t_order_mt2 (482d26fd-99ef-4e94-882d-26fd99efbe94) (SelectExecutor): Selected 2/2 parts by partition key, 1 parts by primary key, 2/2 marks by primary key, 1 marks to read from 1 ranges
[Ubuntu18-006] 2021.08.06 11:34:17.607770 [ 29915 ] {4cc867dd-fd0d-4b7e-8ec3-f404e70b190d} <Debug> MergeTreeSelectProcessor: Reading 1 ranges from part 20210801_1_1_0, approx. 5 rows starting from 0
[Ubuntu18-006] 2021.08.06 11:34:17.608306 [ 29915 ] {4cc867dd-fd0d-4b7e-8ec3-f404e70b190d} <Information> executeQuery: Read 5 rows, 160.00 B in 0.004763978 sec., 1049 rows/sec., 32.80 KiB/sec.
[Ubuntu18-006] 2021.08.06 11:34:17.608325 [ 29915 ] {4cc867dd-fd0d-4b7e-8ec3-f404e70b190d} <Debug> MemoryTracker: Peak memory usage (for query): 0.00 B.
101     sku_001 1000.00 2021-08-01 12:00:00
102     sku_002 2000.00 2021-08-01 11:00:00
102     sku_002 2000.00 2021-08-01 13:00:00
102     sku_002 12000.00        2021-08-01 13:00:00
102     sku_004 2500.00 2021-08-01 12:00:00

4.3.5 數據 TTL

TTL 即 Time To Live，MergeTree 提供了可以管理數據或者列的生命周期的功能。

1. 列級別 TTL
-- 建立表，設置 total_amount 10 秒後過期
CREATE TABLE IF NOT EXISTS t_order_mt3
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2) TTL create_time + INTERVAL 10 SECOND,
    create_time  DateTime
) ENGINE = MergeTree
      PARTITION BY toYYYYMMDD(create_time)
      PRIMARY KEY (id)
      ORDER BY (id, sku_id);
      
-- 插入數據
INSERT INTO t_order_mt3
VALUES (106, 'sku_001', 1000.00, now()),
       (107, 'sku_002', 2000.00, now()),
       (108, 'sku_003', 600.00, now());
       
-- 查看數據
:) select * from t_order_mt3;

SELECT *
FROM t_order_mt3

Query id: 4f00babb-7d60-4764-9fa3-2e6b75032959

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 106 │ sku_001 │      1000.00 │ 2021-08-06 13:48:06 │
│ 107 │ sku_002 │      2000.00 │ 2021-08-06 13:48:06 │
│ 108 │ sku_003 │       600.00 │ 2021-08-06 13:48:06 │
└─────┴─────────┴──────────────┴─────────────────────┘

3 rows in set. Elapsed: 0.002 sec.

-- 10 秒後執行 optimize 命令
:) optimize table t_order_mt3 final;

OPTIMIZE TABLE t_order_mt3 FINAL

Query id: 9a809a08-b25d-4602-9861-d4a1af4d4866

Ok.

0 rows in set. Elapsed: 0.001 sec.

-- 再次查看數據
:) select * from t_order_mt3;

SELECT *
FROM t_order_mt3

Query id: 3c7e091e-e8e0-49f7-8472-9603711f0cf6

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 106 │ sku_001 │         0.00 │ 2021-08-06 13:48:06 │
│ 107 │ sku_002 │         0.00 │ 2021-08-06 13:48:06 │
│ 108 │ sku_003 │         0.00 │ 2021-08-06 13:48:06 │
└─────┴─────────┴──────────────┴─────────────────────┘

3 rows in set. Elapsed: 0.002 sec.
2. 表級別 TTL

修改表，設置數據在create_time之後 10 秒丟失。

-- 修改表 TTL
ALTER TABLE t_order_mt3
    MODIFY TTL create_time + INTERVAL 10 SECOND;
    
-- 查看數據，已經被丟棄
:) select * from t_order_mt3;

SELECT *
FROM t_order_mt3

Query id: c4a60da0-7dfe-444b-a149-3c8ea2803692

Ok.

0 rows in set. Elapsed: 0.002 sec.

註意：涉及判斷的欄位必須是 Date 或者 Datetime 類型，推薦使用分區的日期欄位，可用的時間周期包括 SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR 。

4.4 ReplacingMergeTree

ReplacingMergeTree 是 MergeTree 的一個變種，它存儲特性完全繼承 MergeTree，只是多了一個去重的功能。儘管 MergeTree 可以設置主鍵，但是 primary key 並沒有唯一約束的功能。如果想處理掉重覆的數據，可以藉助 ReplacingMergeTree 引擎。

  • 去重時機

    數據的去重只會在合併的過程中出現。合併會在未知的時間在後臺進行，所以你無法預先做出計劃。有一些數據可能仍未被處理。

  • 去重範圍

    如果表經過了分區，去重只會在分區內部進行，不能執行跨分區的去重。

    所以 ReplacingMergeTree 能力有限，ReplacingMergeTree 適用於在後臺清除重覆的數據以節省空間，但是它不能保證沒有重覆的數據出現。

-- 創建表
CREATE TABLE IF NOT EXISTS t_order_rmt
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2),
    create_time  DateTime
) ENGINE = ReplacingMergeTree(create_time)
      PARTITION BY toYYYYMMDD(create_time)
      PRIMARY KEY (id)
      ORDER BY (id, sku_id);

-- 插入數據
INSERT INTO t_order_rmt
VALUES (101, 'sku_001', 1000.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 11:00:00'),
       (102, 'sku_004', 2500.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 12000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 600.00, '2021-08-02 12:00:00');
INSERT INTO t_order_rmt
VALUES (101, 'sku_001', 1000.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 11:00:00'),
       (102, 'sku_004', 2500.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 12000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 600.00, '2021-08-02 12:00:00');
       
-- 查看數據
:) select * from t_order_rmt;

SELECT *
FROM t_order_rmt

Query id: d3ff4146-724d-4827-8c4a-ee163c2bbfb0

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │     12000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │     12000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

8 rows in set. Elapsed: 0.009 sec.

-- 優化表合併分區，會進行去重
:) optimize table t_order_rmt final;

OPTIMIZE TABLE t_order_rmt FINAL

Query id: 48df496b-b2b4-4ffc-b808-5fecfb343402

Ok.

0 rows in set. Elapsed: 0.002 sec.

-- 再次查看數據，已經進行了合併去重
:) select * from t_order_rmt;

SELECT *
FROM t_order_rmt

Query id: dfad17ce-33b1-4098-be90-fa174abaef79

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │     12000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

4 rows in set. Elapsed: 0.002 sec.

註意：ClickHouse 新版本在插入進行分區時會進行一次去重，所以需要插入兩批數據才能看到效果。

說明：ReplacingMergeTree() 填入的參數為版本欄位，重覆數據保留版本欄位值最大的；如果不填版本欄位，預設按照插入順序保留最後一條。

結論：

  • 實際上是使用 order by 欄位作為唯一鍵；
  • 去重不能跨分區；
  • 插入（新版本）或合併分區時才會進行去重；
  • 重覆數據保留版本欄位值最大的記錄，如果版本欄位相同則按插入順序保留最後一條記錄；

4.5 SummingMergeTree

對於不查詢明細，只關心以維度進行彙總聚合結果的場景。如果只使用普通的 MergeTree 的話，無論是存儲空間的開銷，還是查詢時臨時聚合的開銷都比較大。

ClickHouse 為了這種場景，提供了一種能夠”預聚合“的引擎 SummingMergeTree 。

-- 創建表
CREATE TABLE IF NOT EXISTS t_order_smt
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2),
    create_time  DateTime
) ENGINE = SummingMergeTree(total_amount)
      PARTITION BY toYYYYMMDD(create_time)
      PRIMARY KEY (id)
      ORDER BY (id, sku_id);
      
-- 插入數據
INSERT INTO t_order_smt
VALUES (101, 'sku_001', 1000.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 11:00:00'),
       (102, 'sku_004', 2500.00, '2021-08-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 12000.00, '2021-08-01 13:00:00'),
       (102, 'sku_002', 600.00, '2021-08-02 12:00:00');

-- 查看數據，進行了預聚合
:) select * from t_order_smt;

SELECT *
FROM t_order_smt

Query id: 8cff82df-89b3-4149-8835-210266715922

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │     16000.00 │ 2021-08-01 11:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

4 rows in set. Elapsed: 0.003 sec.

-- 繼續插入
INSERT INTO t_order_smt
VALUES (101, 'sku_001', 2000.00, '2021-08-01 13:00:00');

-- 查看數據
:) select * from t_order_smt;

SELECT *
FROM t_order_smt

Query id: a430dc59-953e-4a2f-b92a-7eac55156b89

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      2000.00 │ 2021-08-01 13:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │     16000.00 │ 2021-08-01 11:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

5 rows in set. Elapsed: 0.002 sec.

-- 手動優化合併
:) optimize table t_order_smt final;

OPTIMIZE TABLE t_order_smt FINAL

Query id: 5661ed80-def3-4b50-84c3-cd6775209cee

Ok.

0 rows in set. Elapsed: 0.002 sec.

-- 再次查看數據
:) select * from t_order_smt;

SELECT *
FROM t_order_smt

Query id: 7ae5dbb5-9ac3-4135-afba-ade1596b495e

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      3000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │     16000.00 │ 2021-08-01 11:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

4 rows in set. Elapsed: 0.002 sec.

結論：

  • 分區內進行聚合彙總，不在一個分區的數據不會被聚合；
  • 插入（新版本）或分區合併時進行聚合彙總；
  • 以 SummingMergeTree() 中指定的列作為彙總數據列，可以填寫多列必須數字列；如果不填，以所有非維度列且為數字列的欄位為彙總數據列；
  • 以 order by 的列為準，作為維度列；
  • 其它列按插入順序保留第一條記錄；

建議：

設計聚合表時，唯一鍵值、流水號可以去掉，所有欄位全部是維度、度量或者時間戳。

問題：

能夠直接通過以下 SQL 得到彙總值？

SELECT total_amount
FROM t_order_smt
WHERE id = 101;

答案是不可，因為可能會包含一些還沒來得及聚合的臨時分區。

如果要獲取彙總值，還是需要使用 sum 進行聚合，這樣效率會有一定的提高，但本身 ClickHouse 是列式存儲的，效率提升有限，不會特別明顯。

SELECT sum(total_amount)
FROM t_order_smt
WHERE id = 101;

5. SQL 操作

5.1 Insert

基本與標準 SQL(MySQL) 一致。

  • 標準

    INSERT INTO [table_name] values (...), (...);

  • 表到表插入

    INSERT INTO [table_name] select a, b, c from [table_name_2];

5.2 Update 和 Delete

ClickHouse 提供了 Update 和 Delete 的功能，這類操作被稱為 Mutation 查詢，它可以看作 Alter 的一種。

雖然可以實現修改和刪除，但是和一般的 OLTP 資料庫不一樣，Mutation 語句是一種很“重”的操作，而且不支持事務。

“重”的原因主要是每次修改或者刪除都會導致放棄目標數據的原有分區，重建新分區。所以儘量做批量的變更，不要進行頻繁小數據的操作。

-- 刪除操作
ALTER TABLE t_order_smt DELETE WHERE sku_id = 'sku_001';

-- 修改操作
ALTER TABLE t_order_smt UPDATE total_amount = toDecimal32(2000.00, 2) WHERE id = 102;

由於操作比較“重”，所以 Mutation 語句分兩步執行，同步執行的部分其實只是進行新增數據新增分區和把舊分區打上邏輯上的失效標記。直到觸發分區合併的時候，才會刪除舊數據釋放磁碟空間，一般不會開放這樣的功能給用戶，由管理員完成。

5.3 Select

ClickHouse 查詢基本與標準 SQL 差別不大。

  • 支持子查詢；

  • 支持 CTE(Common Table Expression 公用表表達式 with 字句)；

  • 支持 JOIN，但是 JOIN 操作無法使用緩存，所以即使是兩次相同的 JOIN 語句，ClickHouse 也會是為兩條新 SQL；

  • 視窗函數（實驗功能）；

  • 不支持自定義函數；

  • GROUP BY 操作增加了 with rollup\with cube\with totals 用來計算小計和總數；

    緯度為 a,b

    • rollup：上捲
      • group by
      • group by a
      • group by a, b
    • cube：多維分析
      • group by
      • group by a
      • group by b
      • group by a, b
    • totals：總計
      • group by
      • group by a, b
-- 清空數據
ALTER
TABLE
t_order_mt
DELETE
WHERE 1 = 1;

-- 插入數據
INSERT INTO t_order_mt
VALUES (101, 'sku_001', 1000.00, '2021-10-01 12:00:00'),
       (101, 'sku_002', 2000.00, '2021-10-01 12:00:00'),
       (103, 'sku_004', 2500.00, '2021-10-01 12:00:00'),
       (104, 'sku_002', 2000.00, '2021-10-01 12:00:00'),
       (105, 'sku_003', 600.00, '2021-10-02 12:00:00'),
       (106, 'sku_001', 1000.00, '2021-10-04 12:00:00'),
       (107, 'sku_002', 2000.00, '2021-10-04 12:00:00'),
       (108, 'sku_004', 2500.00, '2021-10-04 12:00:00'),
       (109, 'sku_002', 2000.00, '2021-10-04 12:00:00'),
       (110, 'sku_003', 600.00, '2021-10-01 12:00:00');
       
-- with rollup 上捲，從右至左去掉維度進行小計
:) SELECT id, sku_id, sum(total_amount)
:-] FROM t_order_mt
:-] GROUP BY id, sku_id
:-] WITH ROLLUP;

SELECT
    id,
    sku_id,
    sum(total_amount)
FROM t_order_mt
GROUP BY
    id,
    sku_id
    WITH ROLLUP

Query id: 463748fe-afc1-49d3-8cba-4f041a9f5506

┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │               600 │
│ 109 │ sku_002 │              2000 │
│ 107 │ sku_002 │              2000 │
│ 106 │ sku_001 │              1000 │
│ 104 │ sku_002 │              2000 │
│ 101 │ sku_002 │              2000 │
│ 103 │ sku_004 │              2500 │
│ 108 │ sku_004 │              2500 │
│ 105 │ sku_003 │               600 │
│ 101 │ sku_001 │              1000 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │        │               600 │
│ 106 │        │              1000 │
│ 105 │        │               600 │
│ 109 │        │              2000 │
│ 107 │        │              2000 │
│ 104 │        │              2000 │
│ 103 │        │              2500 │
│ 108 │        │              2500 │
│ 101 │        │              3000 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │             16200 │
└────┴────────┴───────────────────┘

20 rows in set. Elapsed: 0.006 sec.

-- with cube 從右至左去掉維度進行小計，再從左至右去掉維度進行小計
:) SELECT id, sku_id, sum(total_amount)
:-] FROM t_order_mt
:-] GROUP BY id, sku_id
:-] WITH CUBE;

SELECT
    id,
    sku_id,
    sum(total_amount)
FROM t_order_mt
GROUP BY
    id,
    sku_id
    WITH CUBE

Query id: aa14eb94-d618-4edc-9112-548d3471f28e

┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │               600 │
│ 109 │ sku_002 │              2000 │
│ 107 │ sku_002 │              2000 │
│ 106 │ sku_001 │              1000 │
│ 104 │ sku_002 │              2000 │
│ 101 │ sku_002 │              2000 │
│ 103 │ sku_004 │              2500 │
│ 108 │ sku_004 │              2500 │
│ 105 │ sku_003 │               600 │
│ 101 │ sku_001 │              1000 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │        │               600 │
│ 106 │        │              1000 │
│ 105 │        │               600 │
│ 109 │        │              2000 │
│ 107 │        │              2000 │
│ 104 │        │              2000 │
│ 103 │        │              2500 │
│ 108 │        │              2500 │
│ 101 │        │              3000 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id──┬─sum(total_amount)─┐
│  0 │ sku_003 │              1200 │
│  0 │ sku_004 │              5000 │
│  0 │ sku_001 │              2000 │
│  0 │ sku_002 │              8000 │
└────┴─────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │             16200 │
└────┴────────┴───────────────────┘

24 rows in set. Elapsed: 0.006 sec.

-- with totals 總計
:) SELECT id, sku_id, sum(total_amount)
:-] FROM t_order_mt
:-] GROUP BY id, sku_id
:-] WITH TOTALS;

SELECT
    id,
    sku_id,
    sum(total_amount)
FROM t_order_mt
GROUP BY
    id,
    sku_id
    WITH TOTALS

Query id: 2d88fa13-a60d-4c66-a8ae-f5167a1a990d

┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │               600 │
│ 109 │ sku_002 │              2000 │
│ 107 │ sku_002 │              2000 │
│ 106 │ sku_001 │              1000 │
│ 104 │ sku_002 │              2000 │
│ 101 │ sku_002 │              2000 │
│ 103 │ sku_004 │              2500 │
│ 108 │ sku_004 │              2500 │
│ 105 │ sku_003 │               600 │
│ 101 │ sku_001 │              1000 │
└─────┴─────────┴───────────────────┘

Totals:
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │             16200 │
└────┴────────┴───────────────────┘

10 rows in set. Elapsed: 0.004 sec.

5.4 Alter

同 MySQL 的修改欄位基本一致。

  • 新增欄位

    alter table tableName add column new colname String after col1;

  • 修改欄位類型

    alter table tableName modify column newcolname String;

  • 刪除欄位

    alter table tableName drop column newcolname

5.5 導出數據

$ clickhouse-client --query "select * from t_order_mt where create_time='2021-10-01 12:00:00'" --format CSVWithNames > ~/rs.csv

更多支持格式參照：https://clickhouse.com/docs/en/interfaces/formats/

6. 副本

副本的作用主要是保證數據的高可用性，即使一臺 ClickHouse 節點宕機，那麼也可以從其它伺服器獲得相同的數據。

6.1 副本寫入流程

6.2 配置步驟

  1. 啟動 zookeeper 集群；

  2. 新建/etc/clickhouse-server/config.d/metrika.xml文件，添加以下內容；

    <?xml version="1.0"?>
<yandex>
	<zookeeper-servers>
        <node index="1">
            <host>example1</host>
            <port>2181</port>
        </node>
        <node index="2">
            <host>example2</host>
            <port>2181</port>
        </node>
        <node index="3">
            <host>example3</host>
            <port>2181</port>
        </node>
    </zookeeper-servers>
</yandex>

  3. 修改配置文件所屬用戶和用戶組為 clickhouse；

    $ sudo chown clickhouse:clickhouse /etc/clickhouse-server/config.d/metrika.xml

  4. 修改/etc/clickhouse-server/config.xml配置文件，增加 zookeeper 外部配置文件的引用；

    <!-- 僅新版本需要 -->
<zookeeper incl="zookeeper-servers" optional="true"/>
<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>

  5. 同步配置到副本伺服器節點；

  6. 重啟 clickhouse；

    $ sudo systemctl restart clickhouse-server

註意：副本只能同步數據，不能同步表結構，所以需要在每台機器上手動建表。

6.3 測試

-- 節點一
CREATE TABLE t_order_rep2
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2),
    create_time  DateTime
) ENGINE = ReplicatedMergeTree('/clickhouse/table/01/t_order_rep', 'rep1')
      PARTITION BY toYYYYMMDD(create_time)
      PRIMARY KEY (id)
      ORDER BY (id, sku_id);
      
-- 節點二
CREATE TABLE t_order_rep2
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2),
    create_time  DateTime
) ENGINE = ReplicatedMergeTree('/clickhouse/table/01/t_order_rep', 'rep2')
      PARTITION BY toYYYYMMDD(create_time)
      PRIMARY KEY (id)
      ORDER BY (id, sku_id);

說明：ReplicatedMergeTree 參數一為 zookeeper 地址，需要保持一致；參數二為副本名稱，必須不一樣。

7. 分片集群

副本雖然能夠提高數據的可用性，降低丟失風險，但是每台伺服器實際上必須容納全量數據，對數據的橫向擴容沒有解決。

要解決數據水平切分的問題，需要引入分片的概念。通過分片把一份完整的數據進行切分，不同的分片分佈到不同的節點上，再通過 Distributed 表引擎把數據拼接起來一同使用。

Distributed 表引擎本身不存儲數據，類似於 MyCat 之於 MySQL，成為一種中間件，通過分散式邏輯表來寫入、分發、路由多台節點不同分片的分散式數據。

註意：ClickHouse 的集群是表級別的，實際企業中，大部分做了高可用，但是沒有用分片，避免降低查詢性能以及操作集群的複雜性。

7.1 集群寫入流程

3 分片 2 副本共 6 個節點：

7.2 集群讀取流程

3 分片 2 副本共 6 個節點：


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Failed to start docker.service: Unit not found
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 在VMware上搭建docker的時候報了Failed to start docker.service: Unit not found。查看了好多 博主的分享，但是因為圖片有限，不能確定是否問題一樣，查到這位博主的時候眼前一亮，一毛一樣啊！並且博 ...
  • v87.01 鴻蒙內核源碼分析 (內核啟動篇) | 從彙編到 main () | 百篇博客分析 OpenHarmony 源碼
    本篇關鍵詞：內核重定位、MMU、SVC棧、熱啟動、內核映射表 內核彙編相關篇為: v74.01 鴻蒙內核源碼分析(編碼方式) | 機器指令是如何編碼的 v75.03 鴻蒙內核源碼分析(彙編基礎) | CPU上班也要打卡 v76.04 鴻蒙內核源碼分析(彙編傳參) | 如何傳遞複雜的參數 v77.01 ...
  • 阿裡雲Linux伺服器文件上傳與文件編輯
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 部署rocketmq和可視化客戶端 一、 伺服器資源 服務名稱：Linux伺服器 IP：[請查看資源分配文檔] 操作系統：CentOS 7.8 x64 二、rocketmq安裝 2.1下載 下載地址：rocketmq.apache.org/dow ...
  • linux命令_tree
    tree Linux tree命令用於以樹狀圖列出目錄的內容。 執行tree指令，它會列出指定目錄下的所有文件，包括子目錄里的文件。 語法 tree [-aACdDfFgilnNpqstux][-I <範本樣式>][-P <範本樣式>][目錄...] 參數說明： - -a 顯示所有文件和目錄。 - ...
  • 分散式開源協調服務——Zookeeper
    一、ZooKeeper概述 Apache ZooKeeper 是一個集中式服務，用於維護配置信息、命名、提供分散式同步和提供組服務，ZooKeeper 致力於開發和維護一個開源伺服器，以實現高度可靠的分散式協調，其實也可以認為就是一個分散式資料庫，只是結構比較特殊，是樹狀結構。官網文檔：https: ...
  • MySQL的Explain總結
    Explain簡介 MySQL優化器在基於成本的計算和基於規則的SQL優化會生成一個所謂的執行計劃，我們就可以使用執行計劃查看MySQL對該語句具體的執行方式。 介紹這個好啰嗦就是了，我們可以通過這個優化器展示的執行計劃，查看優化器對我們的SQL進行優化的步驟，連接轉換成單表訪問時的優化。以及對於之 ...
  • Spark: 單詞計數(Word Count)的MapReduce實現(Java/Python)
    我們在上一篇博客中學習瞭如何用Hadoop-MapReduce實現單詞計數，現在我們來看如何用Spark來實現同樣的功能。Spark框架也是MapReduce-like模型，採用“分治-聚合”策略來對數據分佈進行分佈並行處理。不過該框架相比Hadoop-MapReduce，具有以下兩個特點：對大數據... ...
  • ClickHouse高級
    ClickHouse高級 1. 執行計劃 在 ClickHouse 20.6 版本之前要查看 SQL 語句的執行計劃需要設置日誌級別為 TRACE 才可以看到，並且只能真正執行 SQL，在執行日誌裡面查看。在 20.6 版本引入了原生的執行計劃語法，併在 20.6.3.28 版本成為正式功能。 1. ...
一周排行
    -Advertisement-
    Play Games
  • GoF之工廠模式
    GoF之工廠模式 @目錄GoF之工廠模式每博一文案1. 簡單說明“23種設計模式”1.2 介紹工廠模式的三種形態1.3 簡單工廠模式（靜態工廠模式）1.3.1 簡單工廠模式的優缺點:1.4 工廠方法模式1.4.1 工廠方法模式的優缺點:1.5 抽象工廠模式1.6 抽象工廠模式的優缺點：2. 總結:3 ...
  • dotnet C# 簡單的追加文件夾到 ZipArchive 壓縮文件的方法
    新改進提供的Taurus Rpc 功能，可以簡化微服務間的調用，同時可以不用再手動輸出模塊名稱，或調用路徑，包括負載均衡，這一切，由框架實現並提供了。新的Taurus Rpc 功能，將使得服務間的調用，更加輕鬆、簡約、高效。 ...
  • Elasticsearch 系列（六）- ES數據同步和ES集群
    本章將和大家分享ES的數據同步方案和ES集群相關知識。廢話不多說，下麵我們直接進入主題。 一、ES數據同步 1、數據同步問題 Elasticsearch中的酒店數據來自於mysql資料庫，因此mysql數據發生改變時，Elasticsearch也必須跟著改變，這個就是Elasticsearch與my ...
  • 自動化測試數據生成：Asp.Net Core單元測試利器AutoFixture詳解
    引言 在我們之前的文章中介紹過使用Bogus生成模擬測試數據，今天來講解一下功能更加強大自動生成測試數據的工具的庫"AutoFixture"。 什麼是AutoFixture? AutoFixture 是一個針對 .NET 的開源庫，旨在最大程度地減少單元測試中的“安排（Arrange）”階段，以提高 ...
  • .NET Emit 入門教程：第七部分：實戰項目1：將 DbDataReader 轉實體
    經過前面幾個部分學習，相信學過的同學已經能夠掌握 .NET Emit 這種中間語言，並能使得它來編寫一些應用，以提高程式的性能。隨著 IL 指令篇的結束，本系列也已經接近尾聲，在這接近結束的最後，會提供幾個可供直接使用的示例，以供大伙分析或使用在項目中。 ...
  • Windows下綁定線程到指定的CPU核心
    當從不同來源導入Excel數據時，可能存在重覆的記錄。為了確保數據的準確性，通常需要刪除這些重覆的行。手動查找並刪除可能會非常耗費時間，而通過編程腳本則可以實現在短時間內處理大量數據。本文將提供一個使用C# 快速查找並刪除Excel重覆項的免費解決方案。 以下是實現步驟： 1. 首先安裝免費.NET ...
  • mORMot 1.18 第07章 簡單的讀寫操作
    C++ 異常處理 C++ 異常處理機制允許程式在運行時處理錯誤或意外情況。它提供了捕獲和處理錯誤的一種結構化方式，使程式更加健壯和可靠。 異常處理的基本概念： 異常: 程式在運行時發生的錯誤或意外情況。 拋出異常: 使用 throw 關鍵字將異常傳遞給調用堆棧。 捕獲異常: 使用 try-catch ...
  • mORMot 1.18 第06章 概念
    優秀且經驗豐富的Java開發人員的特征之一是對API的廣泛瞭解，包括JDK和第三方庫。 我花了很多時間來學習API，尤其是在閱讀了Effective Java 3rd Edition之後 ，Joshua Bloch建議在Java 3rd Edition中使用現有的API進行開發，而不是為常見的東西編 ...
  • php 開發規範
    框架 · 使用laravel框架，原因：tp的框架路由和orm沒有laravel好用 · 使用強制路由，方便介面多時，分多版本，分文件夾等操作 介面 · 介面開發註意欄位類型，欄位是int，查詢成功失敗都要返回int（對接java等強類型語言方便） · 查詢介面用GET、其他用POST 代碼 · 所 ...
  • 2024 年 4 月 28 日 周日 晴 熱（1253 字）
    正文 下午找企業的人去鎮上做貸後。 車上聽同事跟那個司機對罵，火星子都快出來了。司機跟那同事更熟一些，連我在內一共就三個人，同事那一手指桑罵槐給我都聽愣了。司機也是老社會人了，馬上聽出來了，為那個無辜的企業經辦人辯護，實際上是為自己辯護。 “這個事情你不能怪企業。”“但他們總不能讓銀行的人全權負責， ...
所有分類