分散式 | 淺談 dble 引入 ClickHouse 的配置操作

愛可生雲資料庫發表於2022-07-13
分散式

作者：闕秀平

愛可生 dble 團隊測試成員，主要負責 dble 需求測試，自動化編寫和社群問題解答。熱衷rua雍正。

本文來源：原創投稿

*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

一、功能背景

我們知道, dble 是基於 MySQL 的⾼可擴充套件性的分散式中介軟體，而 MySQL 擅長的是聯機事務處理（OLTP），那麼面對越來越多的聯機分析(OLAP)需求，MySQL 就顯得有些捉襟見肘了。為了能夠提供良好的聯機分析(OLAP)能力，dble 在 3.22.01 版本提供瞭解決方案，可以在後端節點支援 ClickHouse ，藉助 ClickHouse 的能力，提供強大的聯機分析(OLAP)服務。

那什麼是 ClickHouse？為什麼選擇ClickHouse呢？ClickHouse是一個用於聯機分析(OLAP)的列式資料庫管理系統(DBMS)。相對於行式資料庫 MySQL ，對於大多數查詢而言，列式資料庫處理速度至少提高100倍。

那 dble 是怎麼實現的呢？下面來簡單操作一下。

二、環境準備

伺服器上部署四個 docker 容器：

1.部署兩個 5.7.25 版本的 MySQL 資料庫。

2.部署 3.22.01 版本 dble 環境，安裝可參考：【0.3 快速開始 · dble manual】（https://actiontech.github.io/...）

3.部署 22.6.1 版本 ClickHouse 資料庫，安裝可參考：【安裝 | ClickHouse Docs】（https://clickhouse.com/docs/z...），驗證 ClickHosue 可用。

# clickhouse-client -u test --password password -h 127.0.0.1
ClickHouse client version 22.6.1.1985 (official build).
Connecting to 127.0.0.1:9000 as user test.
Connected to ClickHouse server version 22.6.1 revision 54455.


clickhouse-1 :) show databases;

SHOW DATABASES

Query id: b2a417e7-7a76-4461-896d-961540eb60a1

┌─name─────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ system             │
└────────────┘

4 rows in set. Elapsed: 0.003 sec.

三、dble+ClickHouse

1.進入{install_dir}/dble/conf/user.xml ，新增使用者 analysisUser 。

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE dble:user SYSTEM "user.dtd">
<dble:user xmlns:dble="http://dble.cloud/">
     <managerUser name="root" password="password"/>
     <analysisUser name="ana1" password="password" dbGroup="ha_group3" />
</dble:user>

  • user.xml 配置注意：

    (1). 一個 analysisUser 僅對應一個 dbGroup 。

    (2). 多個 analysisUser 可引用同一個 dbGroup 。

2.進入{install_dir}/dble/conf/db.xml，新增 ha_group3

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE dble:db SYSTEM "db.dtd">
<dble:db xmlns:dble="http://dble.cloud/">
    <dbGroup rwSplitMode="0" name="ha_group3" delayThreshold="100" >
        <heartbeat>select user()</heartbeat>
        <dbInstance name="hostM3" password="password" url="172.100.9.13:9004" user="test" maxCon="1000" minCon="10" primary="true" databaseType="clickhouse"/>
    </dbGroup>
</dble:db>

  • db.xml配置注意：

    (1). heartbeat 中下發心跳的語句需要是 ClickHouse 支援的語法。

    (2). analysisUser 使用者所引用的 dbgroup 中配置的資料庫必須和 databaseType 統一。

    (3). ClickHosue 的埠需配置 9004 ，9004 提供 MySQL 的協議。

3.dble 啟動成功，使用 analysisUser 使用者登入。

# mysql -h127.0.0.1 -uana1 -ppassword -P8066
#下發show databases語句返回的結果可知，dble未使用分庫分表功能，僅展示後端 clickhouse 的資料庫資訊
mysql> show databases;
+--------------------+
| name               |
+--------------------+
| INFORMATION_SCHEMA |
| default            |
| information_schema |
| system             |
+--------------------+
4 rows in set (0.00 sec)

#進入default庫
mysql> use default;
Database changed, 1 warning

#建表
mysql>  CREATE TABLE t1 (x String) ENGINE = TinyLog;;
Query OK, 1 row affected (0.03 sec)

#檢查表是否正確
mysql> desc t1;
+------+--------+--------------+--------------------+---------+------------------+----------------+
| name | type   | default_type | default_expression | comment | codec_expression | ttl_expression |
+------+--------+--------------+--------------------+---------+------------------+----------------+
| x    | String |              |                    |         |                  |                |
+------+--------+--------------+--------------------+---------+------------------+----------------+
1 row in set (0.00 sec)

#插入資料
mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.00 sec)

#檢查資料是否正確插入
mysql>  SELECT x, toTypeName(x) FROM t1;
+------+---------------+
| x    | toTypeName(x) |
+------+---------------+
| 1    | String        |
+------+---------------+
1 row in set (0.00 sec)

進入 ClickHouse 中驗證。

# clickhouse-client -u test --password password -h 127.0.0.1
#進入預設庫
clickhouse-1 :) use default;
USE default
Query id: eac19b96-6da6-4d77-8258-e5a827c31685
Ok.
0 rows in set. Elapsed: 0.002 sec.
# 檢視錶是否建立成功
clickhouse-1 :) show tables;
SHOW TABLES
Query id: 02f5018d-4b7b-4348-be5a-89fdcdbc3aa5
┌─name─┐
│ t1   │
└──────┘
1 row in set. Elapsed: 0.003 sec.
#檢視錶是否正確
clickhouse-1 :) desc t1;
DESCRIBE TABLE t1
Query id: 6721fa63-c52c-4236-8c4a-27a1ffdcd059

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ x    │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
1 row in set. Elapsed: 0.002 sec.
# 檢視資料是否和dble查詢的一致
clickhouse-1 :) SELECT x, toTypeName(x) FROM t1;
SELECT
    x,
    toTypeName(x)
FROM t1

Query id: 548ea88e-b1a1-4362-912d-ffa770c7c1be
┌─x─┬─toTypeName(x)─┐
│ 1 │ String        │
└───┴───────────────┘
2 rows in set. Elapsed: 0.002 sec.

4.在 ClickHouse 匯入 4.4G 的資料，對比 ClickHouse 和 dble 的查詢結果。

#clickhouse容器安裝路徑下 下載資料，網速好的話五分鐘左右可下載完成
wget http://prod.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv

#clickhouse-client -u test --password password -h 127.0.0.1，登入預設庫建表
CREATE TABLE uk_price_paid
(
    price UInt32,
    date Date,
    postcode1 LowCardinality(String),
    postcode2 LowCardinality(String),
    type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
    is_new UInt8,
    duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
    addr1 String,
    addr2 String,
    street LowCardinality(String),
    locality LowCardinality(String),
    town LowCardinality(String),
    district LowCardinality(String),
    county LowCardinality(String),
    category UInt8
) ENGINE = MergeTree ORDER BY (postcode1, postcode2, addr1, addr2);

#clickhouse容器安裝路徑下 匯入資料，可能耗時40s左右
clickhouse-local --input-format CSV --structure '
    uuid String,
    price UInt32,
    time DateTime,
    postcode String,
    a String,
    b String,
    c String,
    addr1 String,
    addr2 String,
    street String,
    locality String,
    town String,
    district String,
    county String,
    d String,
    e String
' --query "
    WITH splitByChar(' ', postcode) AS p
    SELECT
        price,
        toDate(time) AS date,
        p[1] AS postcode1,
        p[2] AS postcode2,
        transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type,
        b = 'Y' AS is_new,
        transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration,
        addr1,
        addr2,
        street,
        locality,
        town,
        district,
        county,
        d = 'B' AS category
    FROM table" --date_time_input_format best_effort < pp-complete.csv | clickhouse-client --query "INSERT INTO uk_price_paid FORMAT TSV"

登入 dble 和 ClickHouse 檢視對比資料。

#ClickHouse側結果
clickhouse-1 :) use default;

0 rows in set. Elapsed: 0.001 sec.

clickhouse-1 :) SELECT count() FROM uk_price_paid;

┌──count()─┐
│ 27176256 │
└──────────┘

1 row in set. Elapsed: 0.003 sec.

clickhouse-1 :) SELECT formatReadableSize(total_bytes) FROM system.tables WHERE name = 'uk_price_paid';

┌─formatReadableSize(total_bytes)─┐
│ 235.39 MiB                      │
└─────────────────────────────────┘

1 row in set. Elapsed: 0.003 sec.

clickhouse-1 :) SELECT toYear(date) AS year, round(avg(price)) AS price, bar(price, 0, 1000000, 80) FROM uk_price_paid GROUP BY year ORDER BY year;

┌─year─┬──price─┬─bar(round(avg(price)), 0, 1000000, 80)─┐
│ 1995 │  67933 │ █████▍                                 │
│ 1996 │  71507 │ █████▋                                 │
│ 1997 │  78536 │ ██████▎                                │
│ 1998 │  85439 │ ██████▋                                │
│ 1999 │  96038 │ ███████▋                               │
│ 2000 │ 107486 │ ████████▌                              │
│ 2001 │ 118888 │ █████████▌                             │
│ 2002 │ 137945 │ ███████████                            │
│ 2003 │ 155893 │ ████████████▍                          │
│ 2004 │ 178887 │ ██████████████▎                        │
│ 2005 │ 189356 │ ███████████████▏                       │
│ 2006 │ 203530 │ ████████████████▎                      │
│ 2007 │ 219379 │ █████████████████▌                     │
│ 2008 │ 217054 │ █████████████████▎                     │
│ 2009 │ 213418 │ █████████████████                      │
│ 2010 │ 236107 │ ██████████████████▊                    │
│ 2011 │ 232803 │ ██████████████████▌                    │
│ 2012 │ 238381 │ ███████████████████                    │
│ 2013 │ 256923 │ ████████████████████▌                  │
│ 2014 │ 279984 │ ██████████████████████▍                │
│ 2015 │ 297263 │ ███████████████████████▋               │
│ 2016 │ 313470 │ █████████████████████████              │
│ 2017 │ 346297 │ ███████████████████████████▋           │
│ 2018 │ 350486 │ ████████████████████████████           │
│ 2019 │ 351985 │ ████████████████████████████▏          │
│ 2020 │ 375697 │ ██████████████████████████████         │
│ 2021 │ 379729 │ ██████████████████████████████▍        │
│ 2022 │ 370402 │ █████████████████████████████▋         │
└──────┴────────┴────────────────────────────────────────┘

28 rows in set. Elapsed: 0.064 sec. Processed 27.18 million rows, 163.06 MB (423.68 million rows/s., 2.54 GB/s.)


#dble側結果
mysql> SELECT count() FROM uk_price_paid;
+----------+
| count()  |
+----------+
| 27176256 |
+----------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT count() FROM uk_price_paid;
+----------+
| count()  |
+----------+
| 27176256 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT toYear(date) AS year, round(avg(price)) AS price, bar(price, 0, 1000000, 80) FROM uk_price_paid GROUP BY year ORDER BY year;
+------+--------+-----------------------------------------------------------------------------------------------+
| year | price  | bar(round(avg(price)), 0, 1000000, 80)                                                        |
+------+--------+-----------------------------------------------------------------------------------------------+
| 1995 |  67933 | █████▍                                                                                        |
| 1996 |  71507 | █████▋                                                                                        |
| 1997 |  78536 | ██████▎                                                                                       |
| 1998 |  85439 | ██████▋                                                                                       |
| 1999 |  96038 | ███████▋                                                                                      |
| 2000 | 107486 | ████████▌                                                                                     |
| 2001 | 118888 | █████████▌                                                                                    |
| 2002 | 137945 | ███████████                                                                                   |
| 2003 | 155893 | ████████████▍                                                                                 |
| 2004 | 178887 | ██████████████▎                                                                               |
| 2005 | 189356 | ███████████████▏                                                                              |
| 2006 | 203530 | ████████████████▎                                                                             |
| 2007 | 219379 | █████████████████▌                                                                            |
| 2008 | 217054 | █████████████████▎                                                                            |
| 2009 | 213418 | █████████████████                                                                             |
| 2010 | 236107 | ██████████████████▊                                                                           |
| 2011 | 232803 | ██████████████████▌                                                                           |
| 2012 | 238381 | ███████████████████                                                                           |
| 2013 | 256923 | ████████████████████▌                                                                         |
| 2014 | 279984 | ██████████████████████▍                                                                       |
| 2015 | 297263 | ███████████████████████▋                                                                      |
| 2016 | 313470 | █████████████████████████                                                                     |
| 2017 | 346297 | ███████████████████████████▋                                                                  |
| 2018 | 350486 | ████████████████████████████                                                                  |
| 2019 | 351985 | ████████████████████████████▏                                                                 |
| 2020 | 375697 | ██████████████████████████████                                                                |
| 2021 | 379729 | ██████████████████████████████▍                                                               |
| 2022 | 370402 | █████████████████████████████▋                                                                |
+------+--------+-----------------------------------------------------------------------------------------------+
28 rows in set (0.07 sec)

四、dble+MySQL+ClickHouse

1.修改 user.xml 的配置

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE dble:user SYSTEM "user.dtd">
<dble:user xmlns:dble="http://dble.cloud/">
   <managerUser name="root" password="password"/>
   <shardingUser name="test" password="password" schemas="schema1"/>
   <analysisUser name="ana1" password="password" dbGroup="ha_group3" />
</dble:user

2.修改 db.xml 的配置

<!DOCTYPE dble:db SYSTEM "db.dtd">
<dble:db xmlns:dble="http://dble.cloud/">
   <dbGroup rwSplitMode="0" name="ha_group1" delayThreshold="100" >
        <heartbeat>select user()</heartbeat>
        <dbInstance name="hostM1" password="password" url="172.100.9.5:3307" user="test" maxCon="1000" minCon="10" primary="true">
        </dbInstance>
    </dbGroup>

    <dbGroup rwSplitMode="0" name="ha_group2" delayThreshold="100" >
        <heartbeat>select user()</heartbeat>
        <dbInstance name="hostM2" password="password" url="172.100.9.6:3307" user="test" maxCon="1000" minCon="10" primary="true">
        </dbInstance>
    </dbGroup>
    
    <dbGroup rwSplitMode="0" name="ha_group3" delayThreshold="100" >
        <heartbeat>select user()</heartbeat>
        <dbInstance name="hostM3" password="password" url="172.100.9.13:9004" user="test" maxCon="1000" minCon="10" primary="true" databaseType="clickhouse"/>
    </dbGroup>
    
</dble:db>

  • db.xml 配置注意

    (1). shardingUser 使用者所引用的 dbgroup 中 databaseType 的引數值只能是 MySQL ，當 databaseType 未設定時，預設是 MySQL 。

    (2). shardingUser 引用的 dbGroup ，需要被配置的 schemas 對應的 sharding.xml 中的 shardingNode 所引用

3.修改 sharding.xml 配置

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE dble:sharding SYSTEM "sharding.dtd">
<dble:sharding xmlns:dble="http://dble.cloud/">
    <schema shardingNode="dn1" name="schema1" sqlMaxLimit="100">
        <shardingTable name="sharding_4_t1" shardingNode="dn1,dn2,dn3,dn4" function="hash-four" shardingColumn="id"/>
    </schema>
    <shardingNode dbGroup="ha_group1" database="db1" name="dn1" />
    <shardingNode dbGroup="ha_group2" database="db1" name="dn2" />
    <shardingNode dbGroup="ha_group1" database="db2" name="dn3" />
    <shardingNode dbGroup="ha_group2" database="db2" name="dn4" />
    <function class="Hash" name="hash-four">
      <property name="partitionCount">4</property>
      <property name="partitionLength">1</property>
   </function>
    
</dble:sharding>

4.dble 啟動成功，分別使用 shardingUser 使用者和 analysisUser 使用者登入。

#mysql -h127.0.0.1 -utest -ppassword -P8066 分庫分表使用者
mysql> show databases;
+----------+
| DATABASE |
+----------+
| schema1  |
+----------+
1 row in set (0.01 sec)


#mysql -h127.0.0.1 -uana1 -ppassword -P8066 analysisUser使用者
mysql> show databases;
+--------------------+
| name               |
+--------------------+
| INFORMATION_SCHEMA |
| default            |
| information_schema |
| system             |
+--------------------+
4 rows in set (0.00 sec)

一種可能的業務架構：

1.OLTP：client 端會傳送請求至 dble ，dble（shardingUser,rwSplitUser）會將語句傳送至 MySQL ，然後返回請求。

2.複製：dble 後端的 MySQL 的資料會同步至 MySQL 彙總資料庫，為了使用 ClickHouse 分析資料，使用工具把 MySQL 彙總資料庫的資料同步至 ClickHouse 。

3.OLAP：client 端會傳送請求至 dble ，dble（analysisUser）會將語句傳送至 ClickHouse ，然後返回請求。實現 dble+MySQL+ClickHouse 的資料分析。

相關文章