天天看點
傳回

Clickhouse入門

我們先擷取一些開源資料樣本集，我們将使用美國1987到2015年的民用航班資料，很難稱這個樣本為大資料（隻包含1億6千6百萬行資料，未壓縮時有63GB），但我們能用它很快地開幹。資料可以從這裡下載下傳，你也可以從原位址下載下傳，戳這裡.

首先，我們将在單台伺服器上部署Clickhouse，之後，我們再來搞搞如何部署到支援分片和複制的叢集上。

在Ubuntu和Debian上，Clickhouse可以通過包安裝。在其他Linux發行版上你得自己從源碼編譯安裝。

clickhouse-client這個包内含名為clickhouse-client的應用程式——一個互動式Clickhouse用戶端。clickhouse-server-base包含一個名為clickhouse-server的二進制檔案。clickhouse-server-commom包含clickhouse-server的配置檔案。

服務端配置檔案在

/etc/clickhouse-server/

下。在開幹之前要注意配置檔案中的path元素，path決定了資料存放的路徑。考慮到包更新的問題，直接編輯config.xml檔案不是很友善，建議重寫config.d目錄下配置檔案中的配置元素。另外，你也可能希望在開幹前設定通路權限。

clickhouse-server服務不會在安裝和更新之後自動啟動。啟動服務如下：

sudo service clickhouse-server start

伺服器日志檔案預設存放在

/var/log/clickhouse-server/

下。伺服器在記錄下“Ready for connections”後便可等待處理用戶端連接配接請求。

使用clickhouse-client來連接配接伺服器。

下面是clickhouse-client的一些用法小提示：

互動模式：

clickhouse-client
clickhouse-client --host=... --port=... --user=... --password=...

開啟多行查詢模式：

clickhouse-client -m
clickhouse-client --multiline

在批處理模式下執行查詢：

clickhouse-client --query='SELECT 1'
echo 'SELECT 1' | clickhouse-client

從指定格式的檔案中插入資料：

clickhouse-client --query='INSERT INTO table VALUES' < data.txt
clickhouse-client --query='INSERT INTO table FORMAT TabSeparated' < data.tsv

給樣本資料庫建立表

建立表的語句：

$ clickhouse-client --multiline
ClickHouse client version .
Connecting to localhost:
Connected to ClickHouse server version .

:) CREATE TABLE ontime
(
    Year UInt16,
    Quarter UInt8,
    Month UInt8,
    DayofMonth UInt8,
    DayOfWeek UInt8,
    FlightDate Date,
    UniqueCarrier FixedString(),
    AirlineID Int32,
    Carrier FixedString(),
    TailNum String,
    FlightNum String,
    OriginAirportID Int32,
    OriginAirportSeqID Int32,
    OriginCityMarketID Int32,
    Origin FixedString(),
    OriginCityName String,
    OriginState FixedString(),
    OriginStateFips String,
    OriginStateName String,
    OriginWac Int32,
    DestAirportID Int32,
    DestAirportSeqID Int32,
    DestCityMarketID Int32,
    Dest FixedString(),
    DestCityName String,
    DestState FixedString(),
    DestStateFips String,
    DestStateName String,
    DestWac Int32,
    CRSDepTime Int32,
    DepTime Int32,
    DepDelay Int32,
    DepDelayMinutes Int32,
    DepDel15 Int32,
    DepartureDelayGroups String,
    DepTimeBlk String,
    TaxiOut Int32,
    WheelsOff Int32,
    WheelsOn Int32,
    TaxiIn Int32,
    CRSArrTime Int32,
    ArrTime Int32,
    ArrDelay Int32,
    ArrDelayMinutes Int32,
    ArrDel15 Int32,
    ArrivalDelayGroups Int32,
    ArrTimeBlk String,
    Cancelled UInt8,
    CancellationCode FixedString(),
    Diverted UInt8,
    CRSElapsedTime Int32,
    ActualElapsedTime Int32,
    AirTime Int32,
    Flights Int32,
    Distance Int32,
    DistanceGroup UInt8,
    CarrierDelay Int32,
    WeatherDelay Int32,
    NASDelay Int32,
    SecurityDelay Int32,
    LateAircraftDelay Int32,
    FirstDepTime String,
    TotalAddGTime String,
    LongestAddGTime String,
    DivAirportLandings String,
    DivReachedDest String,
    DivActualElapsedTime String,
    DivArrDelay String,
    DivDistance String,
    Div1Airport String,
    Div1AirportID Int32,
    Div1AirportSeqID Int32,
    Div1WheelsOn String,
    Div1TotalGTime String,
    Div1LongestGTime String,
    Div1WheelsOff String,
    Div1TailNum String,
    Div2Airport String,
    Div2AirportID Int32,
    Div2AirportSeqID Int32,
    Div2WheelsOn String,
    Div2TotalGTime String,
    Div2LongestGTime String,
    Div2WheelsOff String,
    Div2TailNum String,
    Div3Airport String,
    Div3AirportID Int32,
    Div3AirportSeqID Int32,
    Div3WheelsOn String,
    Div3TotalGTime String,
    Div3LongestGTime String,
    Div3WheelsOff String,
    Div3TailNum String,
    Div4Airport String,
    Div4AirportID Int32,
    Div4AirportSeqID Int32,
    Div4WheelsOn String,
    Div4TotalGTime String,
    Div4LongestGTime String,
    Div4WheelsOff String,
    Div4TailNum String,
    Div5Airport String,
    Div5AirportID Int32,
    Div5AirportSeqID Int32,
    Div5WheelsOn String,
    Div5TotalGTime String,
    Div5LongestGTime String,
    Div5WheelsOff String,
    Div5TailNum String
)
ENGINE = MergeTree(FlightDate, (Year, FlightDate), );

現在我們有了一個MergeTree類型的表。推薦在生産環境中使用MergeTree類型的表。這種類型的表有一個用于增量排序的主鍵，允許通過主鍵快速執行查詢。

導入資料

Clickhouse的INSERT語句允許以任何支援的格式導入資料。資料導入隻需要O(1)的RAM消耗。INSERT語句可以接受任何大小資料量作為輸入。強烈建議不要以太小的資料塊插入資料。注意，以大小為

max_insert_block_size

（預設為1048576行）的塊進行插入是一個原子操作（即資料塊要麼完全插入，要麼完全不插入）。要是在插入操作的時候斷開了連接配接，你可能不清楚資料塊是否成功插入了。為了實作妥妥地一次性完工，Clickhouse支援複制表的幂等性。這意味着你可以重試插入相同的資料塊（可能在不同的副本上），但實際上這個資料塊隻插入了一次。由于本指南中是從本地導入資料，是以暫時不care資料塊生成和一次準确性問題。

用INSERT語句向MergeTree類型表中插資料的操作是非阻塞的，SELECT操作也是非阻塞式的，你可以在插入操作後即刻執行SELECT查詢。

我們的示例資料集有點不太理想，有兩點原因:

第一個原因是示例中的字元串（String）資料用枚舉（Enum）或者數值類型是最合适的。

當要插入的資料的可能類别已知并且比較短小（比如作業系統的名稱、浏覽器廠商等資訊）時，我們建議使用枚舉或數值類型可以提高性能。如果資料形式不确定（比如搜尋查詢、URL等），那就還是用字元串類型吧。

第二個原因是資料集包含了年、季度、月、本月第幾日、本周第幾日等等備援字段。實際上，一個航班日期（Flightdate）就夠了。這麼做可能是為了給那些處理日期函數不得勁兒的資料庫系統提高查詢性能而已。

Clickhouse中處理DateTime字段的函數是經過優化的，是以這種備援是不需要的。反正有了Clickhouse這種列式資料庫，媽媽再也不用擔心表内列數太多了，想要多少就要多少，大幾百列都是小case。（料理資料的家庭主婦們用過之後都說：得勁兒！）

查詢樣本資料集

下面是一些對測試資料的查詢示例。

  • 2015年最受歡迎的目的地 
SELECT
    OriginCityName,
    DestCityName,
    count(*) AS flights,
    bar(flights, , , )
FROM ontime WHERE Year =  GROUP BY OriginCityName, DestCityName ORDER BY flights DESC LIMIT
Clickhouse入門
  • 最受歡迎的出發城市 
SELECT OriginCityName, count(*) AS flights
FROM ontime GROUP BY OriginCityName ORDER BY flights DESC LIMIT
Clickhouse入門
  • 目的地最多的出發城市 
SELECT OriginCityName, uniq(Dest) AS u
FROM ontime GROUP BY OriginCityName ORDER BY u DESC LIMIT
Clickhouse入門
  • 周内各天的航班延誤 
SELECT DayOfWeek, count() AS c, avg(DepDelay >  ) AS delays
FROM ontime GROUP BY DayOfWeek ORDER BY DayOfWeek
Clickhouse入門
  • 最常延誤1小時及以上的出發城市 
SELECT OriginCityName, count() AS c, avg(DepDelay >  ) AS delays
FROM ontime
GROUP BY OriginCityName
HAVING c >  
ORDER BY delays DESC
LIMIT
Clickhouse入門
  • 最長飛行時間 
SELECT OriginCityName, DestCityName, count(*) AS flights, avg(AirTime) AS duration
FROM ontime
GROUP BY OriginCityName, DestCityName
ORDER BY duration DESC
LIMIT
Clickhouse入門
  • 按航空公司進行劃分的到達時間延遲分布 
SELECT Carrier, count() AS c, round(quantileTDigest()(DepDelay), ) AS q
FROM ontime GROUP BY Carrier ORDER BY q DESC
Clickhouse入門
  • 停止航班營運的航空公司 
SELECT Carrier, min(Year), max(Year), count()
FROM ontime GROUP BY Carrier HAVING max(Year) <  ORDER BY count() DESC
Clickhouse入門
  • 2015年最具趨向目的地城市 
SELECT
    DestCityName,
    sum(Year = ) AS c2014,
    sum(Year = ) AS c2015,
    c2015 / c2014 AS diff
FROM ontime
WHERE Year IN (, )
GROUP BY DestCityName
HAVING c2014 >   AND c2015 >   AND diff >  
ORDER BY diff DESC
Clickhouse入門
  • 最受歡迎的季節性旅遊目的地城市 
SELECT
    DestCityName,
    any(total),
    avg(abs(monthly *  - total) / total) AS avg_month_diff
FROM
(
    SELECT DestCityName, count() AS total
    FROM ontime GROUP BY DestCityName HAVING total > 
)
ALL INNER JOIN
(
    SELECT DestCityName, Month, count() AS monthly
    FROM ontime GROUP BY DestCityName, Month HAVING monthly > 
)
USING DestCityName
GROUP BY DestCityName
ORDER BY avg_month_diff DESC
LIMIT
Clickhouse入門

Clickhouse的叢集部署

Clickhouse叢集是一個同質化（homogenous）叢集，建構步驟如下：

  1. 在叢集每一台機器上都安裝上Clickhouse伺服器
  2. 設定叢集配置檔案
  3. 在每個執行個體上建立本地表（local tables）
  4. 建立一個分布式表

分布式表實際上是Clickhouse叢集本地表的一種“視圖”。對分布式表的SELECT查詢，會利用叢集所有分片資源進行執行。你可以配置多個叢集，并建立多個分布式表，給不同的叢集提供視圖。

如下是有三個分片組成一個叢集的配置檔案，每個分片單獨存儲一個資料副本

<remote_servers>
    <perftest_3shards_1replicas>
        <shard>
            <replica>
                <host>example-perftest01j.yandex.ru</host>
                <port>9000</port>
            </replica>
        </shard>
        <shard>
            <replica>
                <host>example-perftest02j.yandex.ru</host>
                <port>9000</port>
            </replica>
        </shard>
        <shard>
            <replica>
                <host>example-perftest03j.yandex.ru</host>
                <port>9000</port>
            </replica>
        </shard>
    </perftest_3shards_1replicas>
</remote_servers>

建立一個本地表：

CREATE TABLE ontime_local (...) ENGINE = MergeTree(FlightDate, (Year, FlightDate), );

建立一個分布式表，提供到叢集本地表的視圖：

CREATE TABLE ontime_all AS ontime_local
    ENGINE = Distributed(perftest_3shards_1replicas, default, ontime_local, rand());

你可以在叢集的所有機器上建立分布式表。這将允許在任何機器上運作分布式查詢。除了分布式表外，還可以使用“remote”表函數。

我們來運作INSERT SELECT語句，往分布式表中插入資料，将表擴充到多個伺服器。

值得注意的是，上面介紹的方法不适合那些大表分片。請使用内置的分片特性。

如你所料，重量級查詢在3台伺服器上的執行速度比在1台機器上快N倍：

Clickhouse入門

這個例子中我們使用了包含3個分片的叢集，每個分片包含了一個副本。

在生産環境中為了提供資料恢複能力，我們建議每個分片最好有2-3個副本分布到多個資料中心之間。Clickhouse支援無限數量的副本。

下面是一個分片包含三個副本的叢集配置：

<remote_servers>
    ...
    <perftest_1shards_3replicas>
        <shard>
            <replica>
                <host>example-perftest01j.yandex.ru</host>
                <port>9000</port>
             </replica>
             <replica>
                <host>example-perftest02j.yandex.ru</host>
                <port>9000</port>
             </replica>
             <replica>
                <host>example-perftest03j.yandex.ru</host>
                <port>9000</port>
             </replica>
        </shard>
    </perftest_1shards_3replicas>
</remote_servers>

要實作資料複制，ZooKeeper必不可少。Clickhouse會照顧到所有副本資料的一緻性，在運作故障發生後能自動恢複程式。建議吧ZooKeeper叢集部署到獨立的伺服器上。

簡單起見，你可能自己寫程式代碼将資料寫到所有副本中來完成資料複制，那麼這就不需要ZooKeeper了。這種做法我們不推薦，因為這種情況下Clickhouse不能保證所有副本的資料一緻性。看你啰！搞砸了我不負責！

在配置檔案中設定ZooKeeper的位置：

<zookeeper-servers>
    <node>
        <host>zoo01.yandex.ru</host>
        <port>2181</port>
    </node>
    <node>
        <host>zoo02.yandex.ru</host>
        <port>2181</port>
    </node>
    <node>
        <host>zoo03.yandex.ru</host>
        <port>2181</port>
    </node>
</zookeeper-servers>

我們還需要設定确定分片和副本的宏，這在建立表的時候會用到。

<macros>
    <shard>01</shard>
    <replica>01</replica>
</macros>

如果複制表在建立的時候沒有副本，首個新的副本将會被執行個體化。如果已經有過副本，新的副本将從已存在的副本中克隆。你可以先建立所有複制表，再向其中插入資料；也可以建立若幹副本，再在資料插入期間或之後加入其它副本。

CREATE TABLE ontime_replica (...)
ENGINE = ReplicatedMergeTree(
    '/clickhouse_perftest/tables/{shard}/ontime',
    '{replica}',
    FlightDate,
    (Year, FlightDate),
    );

這裡我們使用的是ReplicatedMergeTree表類型。在參數中我們指定Zookeeper的路徑，其中包含了分片和副本标示符。

複制發生在多主（multi-master）模式下，資料可以加載到任何副本中，它将自動與其他執行個體同步。複制過程是異步的，是以在給定的時刻，并非所有副本都可能包含最近插入的資料。為了允許資料插入，至少建立一個副本。一旦其他副本再次可用，将會同步資料并修複一緻性。請注意，這種方案仍存在使剛剛添加的資料丢失的可能性。

資料庫 olap 列式資料庫 ClickHouse
上一篇: 分享 | 真正輕量級的 Linux 記事本
下一篇: Clickhouse 入門

繼續閱讀