ClickHouse實戰

哥不是小蘿莉發表於2021-10-31

1.概述

最近有被留言關於ClickHouse的使用問題，今天筆者將為大家分享一下ClickHouse的安裝細節和使用方法。

2.內容

首先安裝環境如下所示：

Linux：CentOS7
ClickHouse：21.8.10.19

2.1 ClickHouse是什麼？

ClickHouse是一個真正的列式資料庫管理系統，在ClickHouse中，資料始終是按照列來進行儲存的，包括向量（向量或者列塊）執行的過程。只要有可能，操作都是基於向量進行分派的，而不是單個的值，這被稱為向量化查詢執行，它有利於降低實際的資料處理開銷。

這個想法並不新鮮，向量程式設計被大量用於科學資料處理中。即使在關係型資料庫中，這個想法也不是什麼新的東西。通常有兩種不同的加速查詢處理的方法：向量化查詢執行和執行時程式碼生成。在後者中，動態的為每一類查詢生成程式碼，消除了間接分派和動態分派。這兩種方法中，並沒有哪一種嚴格地比另一種好。執行時程式碼生成可以更好的將多個操作融合在一起，從而充分利用CPU執行單元和流水線。向量化查詢執行不是特別使用，因為它涉及必須寫到快取並讀回的臨時向量。如果L2快取容納不下臨時資料，那麼這將成為一個問題，但是向量化查詢執行更加容易利用CPU。

2.2 列

要表示記憶體中的列（實際上是列塊），需使用 IColumn 介面。該介面提供了用於實現各種關係操作符的輔助方法。幾乎所有的操作都是不可變的：這些操作不會更改原始列，但是會建立一個新的修改後的列。比如，IColumn::filter 方法接受過濾位元組掩碼，用於 WHERE 和 HAVING 關係操作符中。另外的例子：IColumn::permute 方法支援 ORDER BY 實現，IColumn::cut 方法支援 LIMIT 實現等等。
不同的 IColumn 實現（ColumnUInt8、ColumnString 等）負責不同的列記憶體佈局。記憶體佈局通常是一個連續的陣列。對於資料型別為整型的列，只是一個連續的陣列，比如 std::vector。對於 String 列和 Array 列，則由兩個向量組成：其中一個向量連續儲存所有的 String 或陣列元素，另一個儲存每一個 String 或 Array 的起始元素在第一個向量中的偏移。而 ColumnConst 則僅在記憶體中儲存一個值，但是看起來像一個列。

2.3 欄位

儘管如此，有時候也可能需要處理單個值。表示單個值，可以使用 Field。Field 是 UInt64、Int64、Float64、String 和 Array 組成的聯合。IColumn 擁有 operator[] 方法來獲取第 n 個值成為一個 Field，同時也擁有 insert 方法將一個 Field 追加到一個列的末尾。這些方法並不高效，因為它們需要處理表示單一值的臨時 Field 物件，但是有更高效的方法比如 insertFrom 和 insertRangeFrom 等。
Field 中並沒有足夠的關於一個表（table）的特定資料型別的資訊。比如，UInt8、UInt16、UInt32 和 UInt64 在 Field 中均表示為 UInt64。

2.4 資料型別

IDataType 負責序列化和反序列化：讀寫二進位制或文字形式的列或單個值構成的塊。IDataType 直接與表的資料型別相對應。比如，有 DataTypeUInt32、DataTypeDateTime、DataTypeString 等資料型別。
IDataType 與 IColumn 之間的關聯並不大。不同的資料型別在記憶體中能夠用相同的 IColumn 實現來表示。比如，DataTypeUInt32 和 DataTypeDateTime 都是用 ColumnUInt32 或 ColumnConstUInt32 來表示的。另外，相同的資料型別也可以用不同的 IColumn 實現來表示。比如，DataTypeUInt8 既可以使用 ColumnUInt8 來表示，也可以使用過 ColumnConstUInt8 來表示。
IDataType 僅儲存後設資料。比如，DataTypeUInt8 不儲存任何東西（除了 vptr）；DataTypeFixedString 僅儲存 N（固定長度字串的串長度）。
IDataType 具有針對各種資料格式的輔助函式。比如如下一些輔助函式：序列化一個值並加上可能的引號；序列化一個值用於 JSON 格式；序列化一個值作為 XML 格式的一部分。輔助函式與資料格式並沒有直接的對應。比如，兩種不同的資料格式 Pretty 和 TabSeparated 均可以使用 IDataType 介面提供的 serializeTextEscaped 這一輔助函式。

2.5 塊

Block 是表示記憶體中表的子集（chunk）的容器，是由三元組：(IColumn, IDataType, 列名) 構成的集合。在查詢執行期間，資料是按 Block 進行處理的。如果我們有一個 Block，那麼就有了資料（在 IColumn 物件中），有了資料的型別資訊告訴我們如何處理該列，同時也有了列名（來自表的原始列名，或人為指定的用於臨時計算結果的名字）。
當我們遍歷一個塊中的列進行某些函式計算時，會把結果列加入到塊中，但不會更改函式引數中的列，因為操作是不可變的。之後，不需要的列可以從塊中刪除，但不是修改。這對於消除公共子表示式非常方便。
Block 用於處理資料塊。注意，對於相同型別的計算，列名和型別對不同的塊保持相同，僅列資料不同。最好把塊資料（block data）和塊頭（block header）分離開來，因為小塊大小會因複製共享指標和列名而帶來很高的臨時字串開銷。

3.安裝ClickHouse

在Github上搜尋ClickHouse，並下載二進位制安裝包，下載內容如下：

下載地址如下：

https://github.com/ClickHouse/ClickHouse/releases

安裝步驟如下：

## 第1步
# 解壓common包
tar -zxvf clickhouse-common-static-21.8.10.19.tgz
tar -zxvf clickhouse-common-static-dbg-21.8.10.19.tgz

# 執行指令碼安裝
sudo doinst.sh

## 第2步
# 解壓server包
tar -zxvf clickhouse-server-21.8.10.19.tgz

# 修改config.xml儲存路徑和監聽埠
<!-- 實際資料儲存路徑，建議用一個磁碟空間較大的目錄 -->
<path>/appcom/clickhouse/data/</path>
<tmp_path>/appcom/clickhouse/data/tmp/</tmp_path>

<!-- 開啟遠端訪問 -->
<listen_host>::</listen_host>

# 執行指令碼安裝
sudo doinst.sh

## 第3步
# 解壓client包
tar -zxvf clickhouse-client-21.8.10.19.tgz

# 執行指令碼安裝
sudo doinst.sh

## 第4步
# 啟動ClickHouse
/etc/init.d/clickhouse-server start

## 第5步
# 啟動Client
clickhouse-client -m -h 127.0.0.1 --user default --port 9000 --password ckxxx

4.實戰

4.1 準備實戰資料

在Linux控制檯中執行如下命令，獲取表資料。命令如下所示：

# 獲取表資料
curl https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz | unxz --threads=`nproc` > hits_v1.tsv

curl https://datasets.clickhouse.com/visits/tsv/visits_v1.tsv.xz | unxz --threads=`nproc` > visits_v1.tsv

4.2 建立資料庫和表

如果是使用ClickHouse Client執行建表語句，啟動Client時，需要指定 -m 的引數，具體命令如下：

-- 建立資料庫
CREATE DATABASE IF NOT EXISTS tutorial;

-- 建立表（hits_v1）所有使用者在服務所涵蓋的所有網站上完成的每個操作
CREATE TABLE tutorial.hits_v1
(
    `WatchID` UInt64,
    `JavaEnable` UInt8,
    `Title` String,
    `GoodEvent` Int16,
    `EventTime` DateTime,
    `EventDate` Date,
    `CounterID` UInt32,
    `ClientIP` UInt32,
    `ClientIP6` FixedString(16),
    `RegionID` UInt32,
    `UserID` UInt64,
    `CounterClass` Int8,
    `OS` UInt8,
    `UserAgent` UInt8,
    `URL` String,
    `Referer` String,
    `URLDomain` String,
    `RefererDomain` String,
    `Refresh` UInt8,
    `IsRobot` UInt8,
    `RefererCategories` Array(UInt16),
    `URLCategories` Array(UInt16),
    `URLRegions` Array(UInt32),
    `RefererRegions` Array(UInt32),
    `ResolutionWidth` UInt16,
    `ResolutionHeight` UInt16,
    `ResolutionDepth` UInt8,
    `FlashMajor` UInt8,
    `FlashMinor` UInt8,
    `FlashMinor2` String,
    `NetMajor` UInt8,
    `NetMinor` UInt8,
    `UserAgentMajor` UInt16,
    `UserAgentMinor` FixedString(2),
    `CookieEnable` UInt8,
    `JavascriptEnable` UInt8,
    `IsMobile` UInt8,
    `MobilePhone` UInt8,
    `MobilePhoneModel` String,
    `Params` String,
    `IPNetworkID` UInt32,
    `TraficSourceID` Int8,
    `SearchEngineID` UInt16,
    `SearchPhrase` String,
    `AdvEngineID` UInt8,
    `IsArtifical` UInt8,
    `WindowClientWidth` UInt16,
    `WindowClientHeight` UInt16,
    `ClientTimeZone` Int16,
    `ClientEventTime` DateTime,
    `SilverlightVersion1` UInt8,
    `SilverlightVersion2` UInt8,
    `SilverlightVersion3` UInt32,
    `SilverlightVersion4` UInt16,
    `PageCharset` String,
    `CodeVersion` UInt32,
    `IsLink` UInt8,
    `IsDownload` UInt8,
    `IsNotBounce` UInt8,
    `FUniqID` UInt64,
    `HID` UInt32,
    `IsOldCounter` UInt8,
    `IsEvent` UInt8,
    `IsParameter` UInt8,
    `DontCountHits` UInt8,
    `WithHash` UInt8,
    `HitColor` FixedString(1),
    `UTCEventTime` DateTime,
    `Age` UInt8,
    `Sex` UInt8,
    `Income` UInt8,
    `Interests` UInt16,
    `Robotness` UInt8,
    `GeneralInterests` Array(UInt16),
    `RemoteIP` UInt32,
    `RemoteIP6` FixedString(16),
    `WindowName` Int32,
    `OpenerName` Int32,
    `HistoryLength` Int16,
    `BrowserLanguage` FixedString(2),
    `BrowserCountry` FixedString(2),
    `SocialNetwork` String,
    `SocialAction` String,
    `HTTPError` UInt16,
    `SendTiming` Int32,
    `DNSTiming` Int32,
    `ConnectTiming` Int32,
    `ResponseStartTiming` Int32,
    `ResponseEndTiming` Int32,
    `FetchTiming` Int32,
    `RedirectTiming` Int32,
    `DOMInteractiveTiming` Int32,
    `DOMContentLoadedTiming` Int32,
    `DOMCompleteTiming` Int32,
    `LoadEventStartTiming` Int32,
    `LoadEventEndTiming` Int32,
    `NSToDOMContentLoadedTiming` Int32,
    `FirstPaintTiming` Int32,
    `RedirectCount` Int8,
    `SocialSourceNetworkID` UInt8,
    `SocialSourcePage` String,
    `ParamPrice` Int64,
    `ParamOrderID` String,
    `ParamCurrency` FixedString(3),
    `ParamCurrencyID` UInt16,
    `GoalsReached` Array(UInt32),
    `OpenstatServiceName` String,
    `OpenstatCampaignID` String,
    `OpenstatAdID` String,
    `OpenstatSourceID` String,
    `UTMSource` String,
    `UTMMedium` String,
    `UTMCampaign` String,
    `UTMContent` String,
    `UTMTerm` String,
    `FromTag` String,
    `HasGCLID` UInt8,
    `RefererHash` UInt64,
    `URLHash` UInt64,
    `CLID` UInt32,
    `YCLID` UInt64,
    `ShareService` String,
    `ShareURL` String,
    `ShareTitle` String,
    `ParsedParams` Nested(
        Key1 String,
        Key2 String,
        Key3 String,
        Key4 String,
        Key5 String,
        ValueDouble Float64),
    `IslandID` FixedString(16),
    `RequestNum` UInt32,
    `RequestTry` UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID);

-- 建立表（visits_v1）預先構建的會話，而不是單個操作
CREATE TABLE tutorial.visits_v1
(
    `CounterID` UInt32,
    `StartDate` Date,
    `Sign` Int8,
    `IsNew` UInt8,
    `VisitID` UInt64,
    `UserID` UInt64,
    `StartTime` DateTime,
    `Duration` UInt32,
    `UTCStartTime` DateTime,
    `PageViews` Int32,
    `Hits` Int32,
    `IsBounce` UInt8,
    `Referer` String,
    `StartURL` String,
    `RefererDomain` String,
    `StartURLDomain` String,
    `EndURL` String,
    `LinkURL` String,
    `IsDownload` UInt8,
    `TraficSourceID` Int8,
    `SearchEngineID` UInt16,
    `SearchPhrase` String,
    `AdvEngineID` UInt8,
    `PlaceID` Int32,
    `RefererCategories` Array(UInt16),
    `URLCategories` Array(UInt16),
    `URLRegions` Array(UInt32),
    `RefererRegions` Array(UInt32),
    `IsYandex` UInt8,
    `GoalReachesDepth` Int32,
    `GoalReachesURL` Int32,
    `GoalReachesAny` Int32,
    `SocialSourceNetworkID` UInt8,
    `SocialSourcePage` String,
    `MobilePhoneModel` String,
    `ClientEventTime` DateTime,
    `RegionID` UInt32,
    `ClientIP` UInt32,
    `ClientIP6` FixedString(16),
    `RemoteIP` UInt32,
    `RemoteIP6` FixedString(16),
    `IPNetworkID` UInt32,
    `SilverlightVersion3` UInt32,
    `CodeVersion` UInt32,
    `ResolutionWidth` UInt16,
    `ResolutionHeight` UInt16,
    `UserAgentMajor` UInt16,
    `UserAgentMinor` UInt16,
    `WindowClientWidth` UInt16,
    `WindowClientHeight` UInt16,
    `SilverlightVersion2` UInt8,
    `SilverlightVersion4` UInt16,
    `FlashVersion3` UInt16,
    `FlashVersion4` UInt16,
    `ClientTimeZone` Int16,
    `OS` UInt8,
    `UserAgent` UInt8,
    `ResolutionDepth` UInt8,
    `FlashMajor` UInt8,
    `FlashMinor` UInt8,
    `NetMajor` UInt8,
    `NetMinor` UInt8,
    `MobilePhone` UInt8,
    `SilverlightVersion1` UInt8,
    `Age` UInt8,
    `Sex` UInt8,
    `Income` UInt8,
    `JavaEnable` UInt8,
    `CookieEnable` UInt8,
    `JavascriptEnable` UInt8,
    `IsMobile` UInt8,
    `BrowserLanguage` UInt16,
    `BrowserCountry` UInt16,
    `Interests` UInt16,
    `Robotness` UInt8,
    `GeneralInterests` Array(UInt16),
    `Params` Array(String),
    `Goals` Nested(
        ID UInt32,
        Serial UInt32,
        EventTime DateTime,
        Price Int64,
        OrderID String,
        CurrencyID UInt32),
    `WatchIDs` Array(UInt64),
    `ParamSumPrice` Int64,
    `ParamCurrency` FixedString(3),
    `ParamCurrencyID` UInt16,
    `ClickLogID` UInt64,
    `ClickEventID` Int32,
    `ClickGoodEvent` Int32,
    `ClickEventTime` DateTime,
    `ClickPriorityID` Int32,
    `ClickPhraseID` Int32,
    `ClickPageID` Int32,
    `ClickPlaceID` Int32,
    `ClickTypeID` Int32,
    `ClickResourceID` Int32,
    `ClickCost` UInt32,
    `ClickClientIP` UInt32,
    `ClickDomainID` UInt32,
    `ClickURL` String,
    `ClickAttempt` UInt8,
    `ClickOrderID` UInt32,
    `ClickBannerID` UInt32,
    `ClickMarketCategoryID` UInt32,
    `ClickMarketPP` UInt32,
    `ClickMarketCategoryName` String,
    `ClickMarketPPName` String,
    `ClickAWAPSCampaignName` String,
    `ClickPageName` String,
    `ClickTargetType` UInt16,
    `ClickTargetPhraseID` UInt64,
    `ClickContextType` UInt8,
    `ClickSelectType` Int8,
    `ClickOptions` String,
    `ClickGroupBannerID` Int32,
    `OpenstatServiceName` String,
    `OpenstatCampaignID` String,
    `OpenstatAdID` String,
    `OpenstatSourceID` String,
    `UTMSource` String,
    `UTMMedium` String,
    `UTMCampaign` String,
    `UTMContent` String,
    `UTMTerm` String,
    `FromTag` String,
    `HasGCLID` UInt8,
    `FirstVisit` DateTime,
    `PredLastVisit` Date,
    `LastVisit` Date,
    `TotalVisits` UInt32,
    `TraficSource` Nested(
        ID Int8,
        SearchEngineID UInt16,
        AdvEngineID UInt8,
        PlaceID UInt16,
        SocialSourceNetworkID UInt8,
        Domain String,
        SearchPhrase String,
        SocialSourcePage String),
    `Attendance` FixedString(16),
    `CLID` UInt32,
    `YCLID` UInt64,
    `NormalizedRefererHash` UInt64,
    `SearchPhraseHash` UInt64,
    `RefererDomainHash` UInt64,
    `NormalizedStartURLHash` UInt64,
    `StartURLDomainHash` UInt64,
    `NormalizedEndURLHash` UInt64,
    `TopLevelDomain` UInt64,
    `URLScheme` UInt64,
    `OpenstatServiceNameHash` UInt64,
    `OpenstatCampaignIDHash` UInt64,
    `OpenstatAdIDHash` UInt64,
    `OpenstatSourceIDHash` UInt64,
    `UTMSourceHash` UInt64,
    `UTMMediumHash` UInt64,
    `UTMCampaignHash` UInt64,
    `UTMContentHash` UInt64,
    `UTMTermHash` UInt64,
    `FromHash` UInt64,
    `WebVisorEnabled` UInt8,
    `WebVisorActivity` UInt32,
    `ParsedParams` Nested(
        Key1 String,
        Key2 String,
        Key3 String,
        Key4 String,
        Key5 String,
        ValueDouble Float64),
    `Market` Nested(
        Type UInt8,
        GoalID UInt32,
        OrderID String,
        OrderPrice Int64,
        PP UInt32,
        DirectPlaceID UInt32,
        DirectOrderID UInt32,
        DirectBannerID UInt32,
        GoodID String,
        GoodName String,
        GoodQuantity Int32,
        GoodPrice Int64),
    `IslandID` FixedString(16)
)
ENGINE = CollapsingMergeTree(Sign)
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
SAMPLE BY intHash32(UserID);

4.3 載入資料

資料匯入到ClickHouse是通過INSERT INTO方式完成的，查詢類似許多SQL資料庫。然而，資料通常是在一個提供支援序列化格式而不是VALUES子句（也支援）。執行命令如下所示：

# 匯入hits_v1表資料
clickhouse-client --password ckxxx --query "INSERT INTO tutorial.hits_v1 FORMAT TSV" --max_insert_block_size=100000 < hits_v1.tsv

# 匯入visits_v1表資料
clickhouse-client --password ckxxx --query "INSERT INTO tutorial.visits_v1 FORMAT TSV" --max_insert_block_size=100000 < visits_v1.tsv

4.4 查詢

這裡查詢，我們使用Hue來作為視覺化介面，這裡推薦大家使用JDBC的方式來連線ClickHouse，由於Hue的Python庫sqlalchemy-clickhouse對ClickHouse的相容很差，如果使用sqlalchemy的方式來連線ClickHouse，會出現很多不相容的情況，同時，在使用JDBC的方式時，也推薦大家使用clickhouse4j-1.4.4.jar的驅動包，而不是clickhouse-jdbc驅動包，即使用cc.blynk.clickhouse.ClickHouseDriver來替代ru.yandex.clickhouse.ClickHouseDriver。Hue系統檔案hue.ini的配置內容如下所示：

 [[[clickhouse]]]
       name=ClickHouse
       interface=jdbc
    #   ## Specific options for connecting to the ClickHouse server.
    #   ## The JDBC driver clickhouse-jdbc.jar and its related jars need to be in the CLASSPATH environment variable.
       options='{"url": "jdbc:clickhouse://localhost:8123", "driver": "cc.blynk.clickhouse.ClickHouseDriver", "user": "default", "password": "ckxxx"}'

同時，在~/.bash_profile中配置clickhouse4j-1.4.4.jar的環境變數，內容如下：

export CLASSPATH=/appcom/clickhouse/app/clickhouse4j-1.4.4.jar

執行source ~/.bash_profile使環境變數立即生效。

4.4.1 AVG查詢

SELECT
    StartURL AS URL,
    AVG(Duration) AS AvgDuration
FROM tutorial.visits_v1
WHERE StartDate BETWEEN '2014-03-23' AND '2014-03-30'
GROUP BY URL
ORDER BY AvgDuration DESC
LIMIT 10

預覽截圖如下：

4.4.2 SUM查詢

SELECT
    sum(Sign) AS visits,
    sumIf(Sign, has(Goals.ID, 1105530)) AS goal_visits,
    (100. * goal_visits) / visits AS goal_percent
FROM tutorial.visits_v1
WHERE (CounterID = 912887) AND (toYYYYMM(StartDate) = 201403) AND (domain(StartURL) = 'yandex.ru')