clickhouse的一些相關知識

yu_lu發表於2024-06-25

clickhouse概述

OLTP(聯機事務處理系統)

例如mysql等關係型資料庫，在對於儲存小資料量的時候，查詢資料並分析速度很快，OLTP本身其實是一個邏輯上的概念，指的是某個資料庫，主要是針對增刪改操作的。 裡面的資料經常發生變化

OLAP(聯機分析處理系統)

指的是資料庫中的資料長期不變，有著大量的歷史資料，並且可以隨時的做分析，而增刪改操作很少。

OLAP 種類系統架構的的特點

  1. 絕大多數是讀請求
  2. 資料以相當大的批次(>1000行)更新，而不是單行更新；或者根本沒有更新
  3. 已新增到資料庫的資料不能修改。
  4. 對於讀取，從資料庫中提取相當多的行，但是提取列的一小部分。
  5. 寬表，即每個表包含著大量的列
  6. 查詢相對較少(通常每臺伺服器每秒查詢數百次或更少)
  7. 對於簡單查詢，允許延遲大約50毫秒
  8. 列中的資料相對較小：數字和短字串（例如，每個URL 60個位元組）
  9. 處理單個查詢時需要高吞吐量(每臺伺服器每秒可達數十億行)
  10. 事務不是必須的
  11. 對資料一致性要求低

表操作

資料型別

注意：

1、建表寫資料型別的時候，嚴格區分大小寫

2、建表的時候，必須要指定表引擎

  1. 整數型別
UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256
  1. 字串型別
String:可變長字串
FixedString(長度)：固定長度字串，引數是位元組數，執行效率比String要高
  1. 日期型別
Date 年-月-日

Date32 年-月-日

DateTime 年-月-日 時-分-秒

DateTime64 年-月-日 時-分-秒.毫秒
  1. UUID型別
clickhouse提供了一個函式：generateUUIDv4() 生成一個 00000000-0000-0000-0000-000000000000 的編號 編號的型別就是UUID型別
例：bee32020-a6cb-49a6-a10b-427381b11613
  1. 可為空Nullable
例如建表的時候，有一個id欄位型別時Int32，如果當id不確定的時候，我們應該使用null進行填充，而不應該用預設值0，所以，我們這裡應該新增的是null
Nullable(Int32)
  1. 陣列Array(T)
欄位型別是陣列，對於同一個陣列，在建表的時候指定資料型別，注意：在MergeTree表引擎中是不允許出現陣列巢狀的
注意：需要使用array()函式，將元素組成陣列，將來還可以使用toTypeName()檢視某一列的資料型別
當陣列是整數型別時，返回地址值
  1. 小數型別
# Decimal(P,S),Decimal32(S),Decimal64(S),Decimal128(S)
有符號的定點數，可在加、減和乘法運算過程中保持精度。對於除法，最低有效數字會被丟棄（不捨入）
P - 精度。有效範圍：[1:38]，決定可以有多少個十進位制數字（包括分數）。
S - 規模。有效範圍：[0：P]，決定數字的小數部分中包含的小數位數。

1、 建表語句

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

2、插入資料

# 基本格式：
INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
# 舉例
insert into students_test values (1001,'奧德表','男','一班'),(1002,'蔡坤坤','男','二班'),(1003,'歐陽辣辣','女','三班');
#檢視錶結構
desc 表名

引擎

1、資料庫引擎

  1. Atomic

    clickhouse資料庫建庫預設指定的資料庫引擎

    它支援非阻塞的DROP TABLE和RENAME TABLE查詢和原子的EXCHANGE TABLES t1 AND t2查詢。預設情況下使用Atomic資料庫引擎。

  2. Mysql

    MySQL引擎用於將遠端的MySQL伺服器中的表對映到ClickHouse中，並允許您對錶進行INSERT和SELECT查詢，以方便您在ClickHouse與MySQL之間進行資料交換

    MySQL資料庫引擎會將對其的查詢轉換為MySQL語法併傳送到MySQL伺服器中，因此您可以執行諸如SHOW TABLES或SHOW CREATE TABLE之類的操作。

    但您無法對其執行以下操作：

    • RENAME
    • CREATE TABLE
    • ALTER
    # 在clickhouse中建立資料庫並指定遠端的MySQL服務，將其中的某一個資料庫對映過來(就將這新建的資料庫看成一個遠端客戶端連線了mysql)
# 建庫語句
    CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
    ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
    
# 舉例
	create database IF NOT EXISTS clickhouse_mysql ENGINE = MySQL('192.168.160.100:3306','clickhousedb','root','123456');
	
# 引數理解：
    host:port — MySQL服務地址，既可以是ip地址，也可以是主機名（如果是主機名，要配置hosts對映）
    database — MySQL資料庫名稱
    user — MySQL使用者名稱
    password — MySQL使用者密碼
# 操作注意事項
1、mysql的資料和ck資料庫對映的資料幾乎是同步的
2、在任意一端新增資料，另一端哦都可以觀察新增後的結果
3、對於刪除資料，只能在mysql端刪除，不能夠在ck端刪除

2、表引擎

  1. 日誌引擎

    a.log

    Log 與 TinyLog 的不同之處在於，«標記» 的小檔案與列檔案存在一起。這些標記寫在每個資料塊上，並且包含偏移量，這些偏移量指示從哪裡開始讀取檔案以便跳過指定的行數。這使得可以在多個執行緒中讀取表資料。對於併發資料訪問，可以同時執行讀取操作，而寫入操作則阻塞讀取和其它寫入。Log引擎不支援索引。同樣，如果寫入表失敗，則該表將被破壞，並且從該表讀取將返回錯誤。Log引擎適用於臨時資料，write-once 表以及測試或演示目的。而且日誌引擎表中資料不能刪除

    b. TinyLog

    最簡單的表引擎，用於將資料儲存在磁碟上。每列都儲存在單獨的壓縮檔案中。寫入時，資料將附加到檔案末尾。

    併發資料訪問不受任何限制：

    • 如果同時從表中讀取並在不同的查詢中寫入，則讀取操作將丟擲異常
    • 如果同時寫入多個查詢中的表，則資料將被破壞。

    這種表引擎的典型用法是 write-once：首先只寫入一次資料，然後根據需要多次讀取。查詢在單個流中執行。換句話說，此引擎適用於相對較小的表（建議最多1,000,000行）。如果您有許多小表，則使用此表引擎是適合的，因為它比Log引擎更簡單（需要開啟的檔案更少）。當您擁有大量小表時，可能會導致效能低下，但在可能已經在其它 DBMS 時使用過，則您可能會發現切換使用 TinyLog 型別的表更容易。不支援索引。

    c.StripeLog

    寫資料：

    StripeLog 引擎將所有列儲存在一個檔案中。對每一次 Insert 請求，ClickHouse 將資料塊追加在表檔案的末尾，逐列寫入。

    ClickHouse 為每張表寫入以下檔案：

    • data.bin — 資料檔案。
    • index.mrk — 帶標記的檔案。標記包含了已插入的每個資料塊中每列的偏移量。

    StripeLog 引擎不支援 ALTER UPDATE 和 ALTER DELETE 操作。

    讀取資料：

    帶標記的檔案使得 ClickHouse 可以並行的讀取資料。這意味著 SELECT 請求返回行的順序是不可預測的。使用 ORDER BY 子句對行進行排序。

  2. 合併樹家族

MergeTree

Clickhouse 中最強大的表引擎當屬 MergeTree （合併樹）引擎及該系列（*MergeTree）中的其他引擎。

MergeTree 系列的引擎被設計用於插入極大量的資料到一張表當中。資料可以以資料片段的形式一個接著一個的快速寫入，資料片段在後臺按照一定的規則進行合併。相比在插入時不斷修改（重寫）已儲存的資料，這種策略會高效很多。

主要特點:

  • 儲存的資料按主鍵排序。

    這使得您能夠建立一個小型的稀疏索引來加快資料檢索。

  • 如果指定了 分割槽鍵 的話，可以使用分割槽。

    在相同資料集和相同結果集的情況下 ClickHouse 中某些帶分割槽的操作會比普通操作更快。查詢中指定了分割槽鍵時 ClickHouse 會自動擷取分割槽資料。這也有效增加了查詢效能。

  • 支援資料副本。

    ReplicatedMergeTree 系列的表提供了資料副本功能。更多資訊，請參閱 資料副本 一節。

  • 支援資料取樣。

    需要的話，您可以給表設定一個取樣方法。

# 建表語句規範：
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,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

optimize table 表名 final;
-- 產生結果不會立刻的將所有相同的分割槽進行合併，如果想要很快的看到結果，可以手動的進行合併

開發的時候，常用的表引擎：針對資料量小的表引擎用TinyLog，資料量大表引擎就用MergeTree

常用函式

1、 算數函式

對於所有算術函式，結果型別為結果適合的最小數值型別（如果存在這樣的型別）。最小數值型別是根據數值的位數，是否有符號以及是否是浮點型別而同時進行的。如果沒有足夠的位，則採用最高位型別。簡單理解：會自動的根據我們的數值大小，來選用最適合的資料型別儲存。

# plus(a, b), a + b operator
計算數值的總和。 您還可以將Date或DateTime與整數進行相加。在Date的情況下，和整數相加整數意味著新增相應的天數。對於DateTime，這意味著新增相應的秒數。

# minus(a, b), a - b operator
計算數值之間的差，結果總是有符號的。

您還可以將Date或DateTime與整數進行相減。見上面的’plus’。

# multiply(a, b), a * b operator
計算數值的乘積。

# divide(a, b), a / b operator
計算數值的商。結果型別始終是浮點型別。 它不是整數除法。對於整數除法，請使用’intDiv’函式。 當除以零時，你得到’inf’，‘- inf’或’nan’。

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

# max2(a,b)
value1 — 第一個值，型別為Int/UInt或Float。
value2 — 第二個值，型別為Int/UInt或Float。

2、比較函式

比較函式始終返回0或1（UInt8）。

可以比較以下型別：

  • 數字
  • String 和 FixedString
  • 日期
  • 日期時間

以上每個組內的型別均可互相比較，但是對於不同組的型別間不能夠進行比較。

例如，您無法將日期與字串進行比較。您必須使用函式將字串轉換為日期，反之亦然。

字串按位元組進行比較。較短的字串小於以其開頭並且至少包含一個字元的所有字串。

等於，a=b和a==b 運算子
不等於，a!=b和a<>b 運算子
少, < 運算子
大於, > 運算子
小於等於, <= 運算子
大於等於, >= 運算子

3、資料型別轉換

當你把一個值從一個型別轉換為另外一個型別的時候，你需要注意的是這是一個不安全的操作，可能導致資料的丟失。資料丟失一般發生在你將一個大的資料型別轉換為小的資料型別的時候，或者你把兩個不同的資料型別相互轉換的時候。

相關文章