Clickhouse的MySQL外部表，坑貨！

這兩天的主要精力一直在思考，如何把外部的MySQL、SQLServer資料來源高效、優雅的匯入到Clickhouse(下稱CK)中。

要說高效，其實用CK自帶的外部表+物化檢視的方式一定是最快的、最省事的。

因為之前用過kafka的外部表+物化檢視的方式，就可以很方便把外部資料給匯入到CK內部，確實很好用，可是這一次，我們把kafka換成MySQL之後，到底還能不能好使呢？

那麼這篇文章，我們就來看看，CK支援的mysql外部表，到底能不能行。

0. MySQL外部表種類

針對MySQL的外部表，官方文件說明中至少有3種玩法，前兩種是專門針對MySQL的 MySQL 引擎表和 MaterializedMySQL 引擎庫，還有第三種，則是基於JDBC的外部引擎表。

那麼針對這兩種方式的外部表如何玩，這其中到底有沒有坑，接下來我們一起來實地考察一番。

1. MySQL引擎表

先來看CK專門針對MySQL資料庫而提供的外部表功能，相比後面的JDBC方式，這是一種目前CK提供的，跟MySQL資料庫建立關聯最簡單的方式。

具體玩法，就跟建普通的CK本地表一樣，只不過，需要把表的引擎給換成MySQL，比如像這樣：

CREATE TABLE mysql_data01
(
    `id` int,
    `name` String
)
ENGINE = MySQL('192.168.211.106', 'test', 'test01', 'xxx', 'xxxxx')

括號裡面的引數依次是：MySQL資料庫的ip，庫名，表名，使用者名稱，密碼。

建成之後，此時的CK命令列介面，就好比MySQL的一個客戶端，你就可以直接透過這個表名，來查詢到MySQL對應的test01這張表的資料了。

但是，你知道，這肯定不是我的目的，因為既然是客戶端，那麼就說明該表的資料僅僅只是拉到我當前CK例項的記憶體裡，而並沒有寫入我的CK儲存中。

這個時候，我就需要在CK建立一張真實的物理表，然後透過某種方式把這個MySQL的資料給灌到這個真實的物理表中。

那麼這個「某種方式」，官方最推薦的做法就是建立一個物化檢視，它可以把MySQL表中的資料，根據寫入變化(物化檢視目前只支援insert)，實時同步到CK的物理表中，類似給它們之間架起了一座資料傳輸管道。

於是，我就建立了CK本地表：

再又建立MySQL(test01)表跟CK本地表(mysql2ck_local01)之間的物化檢視：

按理說，這個時候我只要往MySQL表裡寫資料，就可以從這張CK的本地表或者物化檢視裡查到對應的資料。

可遺憾的是，當我往MySQL表寫了幾條資料之後，並沒有得到我期待的結果：

MySQL對映表有資料

物化檢視沒有資料

本地表亦沒有資料

所以我宣佈，用MySQL外部表+物化檢視的方式來同步MySQL表資料失敗。

PS：後面我又嘗試用了物化檢視的POPULATE方式，雖然可以把mysql的歷史資料全部匯入到CK本地表，但後續mysql增加的資料，就匯入不了了，所以還是不行。

2. MaterializedMySQL引擎庫

順著官方文件往後翻，發現它還提供了一個支援MySQL全庫同步的方式，那就是建立一個 MaterializedMySQL 引擎庫(注意是庫，而非表)，這樣就可以把MySQL特定庫下的所有表都同步到CK裡。

只不過，這是CK推出的一個實驗性功能，雖然如此，但我還是決定一試。

大概查了一下原理，這種玩法，其實是利用了MySQL的binlog功能，透過日誌變化來同步資料的變化。

所以這種玩法也就意味這需要對原本的MySQL資料庫做一些配置的改變(比如需要開啟原本預設關閉的binlog功能)。

只是不幸的是，我當前的MySQL為5.5版本(centos7自帶的版本)，好像沒辦法識別官方文件給的那些需要額外新增的MySQL配置。

需要新增的配置

當前mysql無法識別

既然這樣，只好作罷，說明這一招並不是在所有的MySQL版本上都奏效，下次我再換個高版本的再試試。

所以，果然是實驗性的，不靠譜。

3. JDBC引擎表

透過官方文件，現在貌似只剩最後一招了，那就是試試透過JDBC引擎表+物化檢視的方式，看看得不得行。

之所以把這部分放到最後來寫，原因在於想要使用JDBC引擎，相比上面的2個引擎操作，要更麻煩一些，它需要額外啟動一個JDBC-Bride服務。

執行原理大致是這樣的：

官網copy過來的圖

也就是如果想要在CK中查詢支援JDBC協議的外部表，必須要透過JDBC-Bride這麼個中間服務才可以。

具體的部署方式呢，其實有3種(詳見: )：

1. docker方式部署;

2. RPM包安裝；

3. Java程式包執行。

官方文件用的第3種，而我為了更方便，用的第2種部署方式：

rpm包部署方式

用rpm包部署最大的好處在於，相關的配置檔案和目錄在部署的時候就已經給你自動生成好了，不需要再額外去建立。

下面這個目錄，就是用rpm包部署後自動生成的配置目錄：

現在我們最關心的，就是如何來使用這個jdbc-bride。

既然是透過它來連線資料庫，那是不是就必須得有對應資料庫的配置才行，跟上面直接在建表語句中配置資料來源資訊不一樣的是，這個jdbc-bridge的玩法，需要在專門的配置檔案中。

在哪呢？擱著藏著呢(官方文件其實沒有說明，被我摸索找到的)：

這個目錄下，對應的json檔案，就是你目標資料庫的配置，比如我的這個資料來源就是MySQL(版本為5.5)，對應配置檔案為mysql5.5.json(取什麼名字不重要，你能區別就行)。

對應配置內容如下：

{
  "mysql5.5": {
  "driverUrls": [
    "
  ],
  "jdbcUrl": "jdbc:mysql://192.168.211.106:3306",
  "username": "***",
  "password": "***"
  }
}

這個配置中，「mysql5.5」代表的資料來源的名稱，這個在後面查詢語句裡需要用到，而這個 driverUrls 則可以改為本地的jar包路徑。

這種不同的資料庫源，用不同的配置檔案來分別表示，最大的好處在於，可以隨著資料庫版本的不一樣，設定不同版本需要的jar包，避免了用同一種連線方式面對不同版本資料庫時，無法相容的情況。

啟動jdbc-bridge服務：

資料庫源的配置檔案設定好之後，接下來就是啟動該服務，很簡單，直接在命令列敲如下命令即可(這就是RPM部署的好處之一)：

從啟動的輸出中可以看出來，剛才配置的mysql5.5資料來源已經被載入了。

如何用呢？

服務跑起來了，那麼現在就可以在CK的客戶端直接透過JDBC的方式查詢到mysql5.5資料來源的資料了。

這種是直接利用jdbc函式，透過指定資料來源名稱、庫名、表名引數的方式就能查詢到對應表的資料。

還有一招，那就是透過建立JDBC引擎表來查資料：

建立JDBC引擎表

透過JDBC引擎表查詢

至此，這個JDBC引擎的初步功能算是已經實現了。

但是不知道你有沒有發現一個問題，那就是這個jdbc-bridge服務，當前我是部署在跟CK同一臺機器上的，那如果把它部署到其他地方，是不是我的CK就找不到它了(預設會在本機找)。

那這個時候，就需要在CK的主配置檔案/etc/clickhouse-server/config.xml中，新增(修改)如下配置：

如何導資料到CK表？

上面提到的查詢，還都只是CK充當一個jdbc的客戶端，並沒有把mysql的資料給匯入到CK中來(可以透過檢視實際資料儲存的目錄來判斷)，那怎麼樣才能把它給倒騰過來呢？

我能想到的就是再建立一張CK本地表，然後再透過建立一個物化檢視，把這個資料給引過來。

先建本地表：

再建物化檢視：

然後，我再往mysql的表(mysql_data02)寫入一些資料後發現。

物化檢視居然查不到(本地表也一樣)：

就讓人很生氣。

隨後，我又順著官方文件往後翻，又發現了CK一些其他的騷操作，比如像什麼動態檢視(Live view)、視窗檢視(Window view)等。

我都替大家一一試了一遍，雖然效果確實比較花，但在實現上還是隻能停留在CK作為客戶端這個基礎上，我個人會膚淺的認為，這玩意並不實用，至少不能解決我眼下的問題。

最後

這篇文章的初衷呢，是想利用mysql的外部表+物化檢視的方式，看能不能順利把mysql原表的資料給匯入到CK的(模仿之前kafka的玩法)，但是幾經折騰後發現，不行(當然，也有可能是我水平不行)。

核心問題是，這種方式不能同步mysql源表的增量資料(歷史資料用 POPULATE 方式是可以的)，所以也就失去了作為mysql資料來源同步方案之一的資格。

那咋整？

正如上篇文章寫到的，我暫時用DataX。