clickhouse元件介紹

ikestu小猪發表於2024-09-04
元件

寫在前面

今天學習clickhouse部分的知識。

ClickHouse

OLTP (聯機事務處理系統)

例如 MySQL 等關係型資料庫，適用於小資料量時的快速查詢和分析。OLTP 主要針對增刪改操作，資料經常發生變化。

OLAP (聯機分析處理系統)

適用於資料長期不變且有大量歷史資料的場景，主要進行分析操作，增刪改操作較少。

OLAP 特點

  1. 絕大多數是讀請求。
  2. 資料以批次（> 1000 行）更新，或根本沒有更新。
  3. 已新增到資料庫的資料不能修改。
  4. 提取大量行，但僅提取列的一小部分。
  5. 寬表，每個表包含大量列。
  6. 查詢較少（每秒查詢數百次或更少）。
  7. 簡單查詢允許約 50 毫秒延遲。
  8. 列中的資料相對較小：數字和短字串（例如，每個 URL 60 位元組）。
  9. 高吞吐量處理單個查詢（每秒可達數十億行）。
  10. 事務不是必須的。
  11. 對資料一致性要求低。
  12. 每個查詢涉及一個大表，其他表很小。
  13. 查詢結果明顯小於源資料（資料經過過濾或聚合）。

資料型別

整數型別

  • 負數:

    • Int8: [-128 : 127]
    • Int16: [-32768 : 32767]
    • Int32: [-2147483648 : 2147483647]
    • Int64: [-9223372036854775808 : 9223372036854775807]
    • Int128: [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]
    • Int256: [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]

  • 只有0和正數:

    • UInt8: [0 : 255]
    • UInt16: [0 : 65535]
    • UInt32: [0 : 4294967295]
    • UInt64: [0 : 18446744073709551615]
    • UInt128: [0 : 340282366920938463463374607431768211455]
    • UInt256: [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935]

字串型別

  • String: 可變長字串
  • FixedString(length): 固定長字串，引數為位元組數，執行效率較高

日期型別

  • Date: 年-月-日
  • Date32: 年-月-日
  • DateTime: 年-月-日 時-分-秒
  • DateTime64: 年-月-日 時-分-秒.毫秒

示例

-- 建表語句
CREATE TABLE date_test (
    date1 Date,
    date2 Date32,
    date3 DateTime,
    date4 DateTime64
) ENGINE = TinyLog;

-- 插入語句
INSERT INTO date_test VALUES ('2023-11-21', '2023-11-21', '2023-11-21', '2023-11-21');
INSERT INTO date_test VALUES (1711435333589, 1711435333589, 1711435333589, 1711435333589); 
-- 結果：2024-03-26 15:33:38

UUID 型別

ClickHouse 提供了一個函式 generateUUIDv4()，生成 UUID，例如 bee32020-a6cb-49a6-a10b-427381b11613

可為空（Nullable）

  • 使用 Nullable 處理不確定的欄位值
CREATE TABLE test2 (
    id Int32,
    name Nullable(String)
) ENGINE = TinyLog;

INSERT INTO test2 VALUES (1001, NULL);

陣列

  • 使用 Array(T) 型別，資料型別在建表時指定。MergeTree 表引擎不允許出現陣列巢狀。

示例

CREATE TABLE t1 (
    col1 Array(Int8)
) ENGINE = TinyLog;

INSERT INTO t1 VALUES (array(11, 12, 13));

小數型別

  • Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S)
    • P: 精度，範圍：[1:38]
    • S: 規模，範圍：[0:P]

示例

-- Decimal(4,2)
-- Decimal(7,5)

表操作

注意事項

  1. 建表時資料型別嚴格區分大小寫。
  2. 建表時必須指定表引擎。

建表語句

CREATE TABLE users3 (
    id Int8,
    name FixedString(12),
    gender Nullable(FixedString(3)),
    clazz String
) ENGINE = TinyLog;

插入資料

-- 基本格式
INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...

-- 示例
CREATE TABLE IF NOT EXISTS bigdata31.students_test (
    id Int32,
    name String,
    gender FixedString(6),
    clazz String
) ENGINE = TinyLog;

-- 插入資料
INSERT INTO students_test VALUES 
(1001, '陸澳', '男', '特訓營24期'),
(1002, '李佳豪', '男', '特訓營24期'),
(1003, '郭香香', '女', '特訓營24期');

INSERT INTO students_test VALUES 
(1004, '王宇傑', '男', '特訓營24期'),
(1005, '張懷遠', '男', '特訓營24期'),
(1006, '史俊超', '男', '特訓營24期');

INSERT INTO students_test (name, gender, clazz) VALUES 
('張瑋', '男', '特訓營24期');

-- 檢視錶結構
DESC 表名;

引擎

資料庫引擎

  • Atomic: 預設引擎，支援非阻塞的 DROP TABLE 和 RENAME TABLE 查詢。支援原子的 EXCHANGE TABLES 查詢。
  • MySQL: 用於將遠端 MySQL 伺服器中的表對映到 ClickHouse 中。支援 INSERT 和 SELECT 查詢，不支援 RENAME, CREATE TABLE, ALTER。

建立資料庫並對映遠端 MySQL 服務

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password');

-- 示例
CREATE DATABASE IF NOT EXISTS shujia_ck_mysql ENGINE = MySQL('192.168.136.100:3306', 'bigdata31', 'root', '123456');

資料表引擎

Log

  • 不支援索引，適用於臨時資料、write-once 表、測試或演示目的。

TinyLog

  • 簡單的表引擎，適合相對較小的表（建議最多 1,000,000 行）。不支援索引。

StripeLog

  • 將資料塊逐列寫入，支援並行讀取，不支援 ALTER UPDATE 和 ALTER DELETE 操作。
建表語句
CREATE TABLE stripe_log_table (
    timestamp DateTime,
    message_type String,
    message String
) ENGINE = StripeLog;

-- 插入資料
INSERT INTO stripe_log_table VALUES 
(now(), 'REGULAR', 'The first regular message'),
(now(), 'REGULAR', 'The second regular message'),
(now(), 'WARNING', 'The first warning message');

-- 示例
CREATE TABLE students_stripelog (
    id Int32,
    name String,
    gender FixedString(3),
    clazz String
) ENGINE = StripeLog;

-- 新增資料
INSERT INTO students_stripelog VALUES 
(1001, '陸澳', '男', '特訓營24期'),
(1002, '李佳豪', '男', '特訓營24期'),
(1003, '郭香香', '女', '特訓營24期');

MergeTree

  • ClickHouse 中最強大的表引擎，用於快速寫入大量資料，支援分割槽、資料副本和資料取樣。
    注意：預設是針對每一批資料按照分割槽欄位的值進行分割槽
建表語句
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (
  name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
  name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
  ...
  INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
  ...
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...];
PARTITION BY：用於資料分割槽，以提高查詢效率。
ORDER BY：定義資料的排序方式。
PRIMARY KEY：指定主鍵索引。
SAMPLE BY：取樣資料的列。
TTL：定義資料過期時間。
SETTINGS：表級別的設定。

示例

CREATE TABLE IF NOT EXISTS bigdata31.students (
  id Int32,
  name String,
  gender FixedString(6),
  clazz String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY id
PRIMARY KEY id;

-- 插入資料

INSERT INTO students VALUES 
  (1001, '陸澳', '男', '特訓營24期'),
  (1002, '李佳豪', '男', '特訓營24期');...

ClickHouse 表引擎和資料最佳化

資料表最佳化

  • 手動最佳化
    使用 OPTIMIZE TABLE 命令來合併分割槽資料，減少碎片，提高查詢效能。

    OPTIMIZE TABLE goods_orders FINAL;

    FINAL 引數用於執行最終的合併操作，確保資料在所有分割槽和資料副本中都被合併。此操作會將分割槽中的資料合併到一個單一的檔案中，消除資料的冗餘和碎片。

  • 自動合併
    ClickHouse 自動合併資料，以維護表的效能和儲存空間。這是透過後臺程序定期進行的。

表引擎選擇

  • TinyLog
    適用於小規模表，不支援索引，通常用於臨時資料和測試。適用於行數較少、更新頻繁的場景。

  • MergeTree
    適用於大規模資料表，支援分割槽、排序和索引，提供高效的資料讀取和寫入效能。適用於資料量較大、查詢複雜的場景。

常用函式

算術函式

  • plus(a, b) 或 a + b
    計算兩個數值的總和。也可以將 Date 或 DateTime 與整數相加，表示增加相應的天數或秒數。

    SELECT plus(5, 10); -- 結果: 15
SELECT toDate('2024-01-01') + 10; -- 結果: '2024-01-11'

  • minus(a, b) 或 a - b
    計算兩個數值之間的差。也可以將 Date 或 DateTime 減去整數，表示減少相應的天數或秒數。

    SELECT minus(15, 5); -- 結果: 10
SELECT toDate('2024-01-11') - 10; -- 結果: '2024-01-01'

  • multiply(a, b) 或 a * b
    計算兩個數值的乘積。

    SELECT multiply(3, 4); -- 結果: 12

  • divide(a, b) 或 a / b
    計算兩個數值的商。結果型別為浮點數。

    SELECT divide(10, 3); -- 結果: 3.3333333

  • intDiv(a, b)
    計算整數的商，結果向下舍入（按絕對值），除以零或將最小負數除以 -1 時會丟擲異常。

    SELECT intDiv(10, 3); -- 結果: 3

  • max2(value1, value2)
    返回兩個值中的最大值。

    SELECT max2(5, 10); -- 結果: 10

比較函式

比較函式始終返回 0 或 1（UInt8），用於比較數值、字串、日期和日期時間型別。

  • 等於：a = b 或 a == b
  • 不等於：a != b 或 a <> b
  • 小於：a < b
  • 大於：a > b
  • 小於等於：a <= b
  • 大於等於：a >= b

字串按位元組進行比較，較短的字串小於較長的字串。

資料型別轉換

將資料從一種型別轉換為另一種型別時，需注意可能的資料丟失問題。通常，資料丟失發生在以下情況：

  • 將較大的資料型別轉換為較小的資料型別時，例如從 Int64 轉換為 Int32。
  • 不同資料型別之間的轉換，例如從 Float64 轉換為 Int32。

例如，以下示例演示瞭如何進行型別轉換：

-- 從 Float64 轉換為 Int32，可能會丟失小數部分
SELECT toInt32(12.34); -- 結果: 12

-- 從 Int32 轉換為 String
SELECT toString(123); -- 結果: '123'

-- 從 String 轉換為 Date
SELECT toDate('2024-01-01'); -- 結果: 2024-01-01

相關文章