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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類