ClickHouse更新數據詳解

ClickHouse是一個針對聯機分析處理（OLAP）場景的分析型資料庫管理系統，在存儲海量數據和追求高吞吐量、低延遲方面表現出色。除了查詢功能，ClickHouse還具有強大的更新功能。本文將從不同的角度詳細介紹ClickHouse更新數據的相關知識，包括性能、立即生效、空報錯、語句、數據遷移、表刪除、數據導出等。

一、ClickHouse更新數據性能

在ClickHouse中，更新數據是通過運行UPDATE語句來實現的。相比於傳統的關係型資料庫，在ClickHouse中更新數據具有強大的性能優勢。

ClickHouse利用列式存儲引擎和向量化計算的特性，使其在更新和插入數據時具有很高的速度。ClickHouse使用MergeTree引擎來存儲數據，每個MergeTree表對應一個磁碟目錄，每個目錄下會有一個或多個數據塊。在更新操作時，ClickHouse會創建一個新的數據塊，並將更新的數據寫入其中。當新數據塊的大小達到一定程度時，ClickHouse會開始合併數據塊，以保證數據的可壓縮性和查詢效率。

因此，如果需要快速更新數據，使用ClickHouse是一個不錯的選擇。具體可參考下面的示例：

-- 創建含有100萬條記錄的表test_table
CREATE TABLE test_table
(
    id UInt32,
    name String,
    age Int16,
    address String
)
ENGINE = MergeTree()
ORDER BY id;

-- 執行一條update語句，將100萬條記錄的age欄位都更新為18
UPDATE test_table SET age = 18;

二、ClickHouse更新數據立即生效

ClickHouse更新數據的最大特點是能夠立即生效。通常，傳統的關係型資料庫需要在事務結束之後才執行提交動作，才能生效更改。但在ClickHouse中，所有修改都是即時的。

ClickHouse沒有事務，每個更新操作是原子的。如果多個查詢同時更新相同的行，只有一個更新操作會被執行，其他操作會被忽略。

這個特性可能會導致某些問題，例如兩個客戶端同時更新同一行，結果只有一個客戶端的更新被執行。為了解決這個問題，ClickHouse提供了一個MATERIALIZED VIEW語句，可以通過該語句實現類似於觸發器的功能，對更新進行監視和控制。

示例代碼如下：

-- 創建一個表person
CREATE TABLE person
(
    id UInt32,
    name String,
    age Int16,
    address String
)
ENGINE = MergeTree()
ORDER BY id;

-- 創建MATERIALIZED VIEW，用於監控person表的更新
CREATE MATERIALIZED VIEW person_changes TO person (id, name, age, address)
AS SELECT id, name, age, address
FROM person;

-- 執行一條update語句，更新person表的age欄位
UPDATE person SET age = 18 WHERE id = 1;

-- 查詢person_changes表，觀察是否有更新
SELECT * FROM person_changes WHERE id = 1;

三、ClickHouse更新數據為空報錯

在ClickHouse中，如果更新數據時某個列為空，會報錯。這是因為ClickHouse使用NULL作為無效值的標誌，如果將NULL賦值給某個列，就會導致該列的值不再是有效值。這樣會影響查詢和執行數據變換操作的正確性。

要避免這種情況，可以在更新數據之前進行一些檢查。例如可以使用assert函數或if函數判斷數據是否合法，然後再進行更新操作。示例如下：

-- 創建一個表student
CREATE TABLE student
(
    id UInt32,
    name String,
    age Nullable(Int16),
    address Nullable(String)
)
ENGINE = MergeTree()
ORDER BY id;

-- 假設id為1的學生的age和address欄位為空
-- 使用if函數判斷age和address是否為空，如果為空則填充預設值
UPDATE student SET age = if(age IS NULL, 18, age),
address = if(address IS NULL, 'Beijing', address)
WHERE id = 1;

四、ClickHouse更新數據語句

ClickHouse的更新數據語句非常簡單，語法與傳統的SQL語句類似。下面是一些基本的示例：

-- 修改age欄位
UPDATE test_table SET age = 18 WHERE name = 'Tom';

-- 修改多個欄位
UPDATE test_table SET name = 'Lucy', age = 20 WHERE id = 1;

-- 使用計算表達式更新欄位
UPDATE test_table SET age = age + 1 WHERE id BETWEEN 1 AND 10;

可以使用WHERE子句來限制更新的行。如果沒有指定WHERE子句，則所有行都會被更新。

五、ClickHouse怎麼更新數據

更新數據的方式有多種，可以手動輸入UPDATE語句，也可以使用ETL工具或程序調用ClickHouse的API來進行更新。下面是一些常見的更新數據方式：

1. 使用ClickHouse的客戶端工具，例如clickhouse-client或Tabix，手動輸入UPDATE語句。

2. 使用ETL工具，例如Apache NiFi或Pentaho Data Integration，調用ClickHouse的JDBC驅動程序進行數據更新。

3. 使用程序調用ClickHouse的HTTP API介面，例如通過curl或Python requests庫發送HTTP POST請求進行更新。

示例代碼如下：

-- 使用clickhouse-client手動輸入UPDATE語句
$ clickhouse-client --query "UPDATE test_table SET age = 18 WHERE id = 1;"

-- 使用NiFi調用ClickHouse的JDBC驅動程序
# ExecuteScript processor中的Python腳本
import jaydebeapi
import os

jarFile = os.path.join('/path/to/clickhouse-jdbc-driver', 'clickhouse-jdbc-0.1.jar')

conn = jaydebeapi.connect(
    'ru.yandex.clickhouse.ClickHouseDriver',
    'jdbc:clickhouse://:8123/test',
    {'user': 'default', 'password': ''},
    jarFile)

cursor = conn.cursor()

sql = "UPDATE test_table SET age = 20 WHERE id = 1"

cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()

-- 使用Python的requests庫調用ClickHouse的HTTP API
import requests

data = {
    'query': 'UPDATE test_table SET age = 18 WHERE id = 1;'
}

response = requests.post('http://:8123/', data=data)

六、ClickHouse資料庫查詢

除了更新數據，ClickHouse還具有非常強大的查詢功能。可以使用SELECT語句對數據進行查詢和分析。ClickHouse的查詢速度非常快，在海量數據的情況下也具有出色的查詢性能。以下是一些基本的查詢示例：

-- 查詢test_table表中所有數據
SELECT * FROM test_table;

-- 查詢test_table表中id為1的數據
SELECT * FROM test_table WHERE id = 1;

-- 查詢test_table表中age大於等於18的數據
SELECT * FROM test_table WHERE age >= 18;

-- 查詢test_table表中按照age欄位降序排列的前10條數據
SELECT * FROM test_table ORDER BY age DESC LIMIT 10;

七、ClickHouse數據遷移

數據遷移是ClickHouse應用的一個重要部分，通常是將數據從一個ClickHouse集群或另一個資料庫遷移到ClickHouse。以下是一些實現數據遷移的方法：

1. 使用ClickHouse提供的copy命令，將數據從CSV或TSV文件導入到ClickHouse。

2. 使用ClickHouse提供的kafka引擎，將數據從Kafka主題導入到ClickHouse。

3. 使用ETL工具，例如Apache NiFi或Pentaho Data Integration，調用ClickHouse的JDBC驅動程序進行數據遷移。

4. 使用程序調用ClickHouse的HTTP API介面，例如通過curl或Python requests庫發送HTTP POST請求進行數據遷移。

以下是使用copy命令將數據從CSV文件導入到ClickHouse的示例：

-- 創建含有100萬條記錄的表test_table
CREATE TABLE test_table
(
    id UInt32,
    name String,
    age Int16,
    address String
)
ENGINE = MergeTree()
ORDER BY id;

-- 準備要導入的數據文件sample.csv
-- 文件格式為id,name,age,address，例如：1,Tom,20,Beijing
-- 使用copy命令將數據從sample.csv導入到test_table表
-- 欄位順序必須與表定義的一致，欄位數量和順序不能不同
COPY test_table (id, name, age, address) FROM 'sample.csv' FORMAT CSV;

-- 查詢test_table表中導入的數據
SELECT * FROM test_table;

八、ClickHouse刪除資料庫

如果不再需要某個ClickHouse資料庫，可以使用DROP DATABASE語句將其刪除。請注意，刪除資料庫將同時刪除其中所有的表和數據。

以下是刪除資料庫的示例代碼：

-- 刪除名為test的資料庫
DROP DATABASE test;

九、ClickHouse導出數據

可以使用ClickHouse提供的多種方式將數據導出到外部系統。例如使用copy命令將數據導出到CSV文件或使用HTTP API將數據以JSON格式導出到其他應用程序。以下是一些常見的導出數據方式：

1. 使用ClickHouse提供的copy命令，將數據從ClickHouse表導出到CSV或TSV文件。

2. 使用程序調用ClickHouse的HTTP API介面，例如通過curl或Python requests庫發送HTTP GET請求進行數據導出。

3. 使用ETL工具，例如Apache NiFi或Pentaho Data Integration，調用ClickHouse的JDBC驅動程序進行數據導出。

以下是使用copy命令將數據從ClickHouse表導出到CSV文件的示例：

-- 創建含有100萬條記錄的表test_table
CREATE TABLE test_table
(
    id UInt32,
    name String,
    age Int16,
    address String
)
ENGINE = MergeTree()
ORDER BY id;

-- 將test_table表中所有記錄導出到sample.csv文件
COPY test_table TO 'sample.csv' FORMAT CSV;

-- 將sample.csv文件的內容列印到終端
cat sample.csv;

總結

本文詳細介紹了ClickHouse更新數據的多個方面，包括性能、立即生效、空報錯、語句、數據遷移、表刪除和數據導出等。ClickHouse具有非常快的更新性能和查詢性能，同時支持多種格式的數據導入和導出。這些特性使得ClickHouse成為處理海量數據和實時分析場景的有力工具。

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/155460.html