2022 iThome 鐵人賽

DAY 23

Software Development

ClickHouse:時序資料庫建置與運行系列第 23 篇

day23-ClickHouse 客戶端連線介面方式(二)

14th鐵人賽

peter279k

2022-10-07 00:39:03

2846 瀏覽

分享至

前言

在本章節中，我們要來介紹的是有關於ClickHouse資料庫客戶端的其他連線方式。

gRPC模式連線介面

ClickHouse資料庫支援gRPC介面的方式進行連線，這是連線協定是一個開源的遠端程序呼叫系統並以HTTP/2通訊協定與協定緩衝（Protocol Buffers）作為基礎，該介面連線在ClickHouse資料庫支援的特性如下：

SSL（Secure Socket Layer，傳輸層安全性協定）
驗證機制（authentication）
連線機制（sessions）
資料壓縮機制（compression）
透過同個連線頻道（channel）執行平行的查詢
取消正在執行中的查詢
得到目前的進度與記錄
讀取外部的資料表機制

上述相關的介面連線規格都描述在此檔案：clickhouse_grpc.proto

gRPC介面連線設定

為了要使用gRPC介面連線來做相關的設定，可以在主要的資料庫伺服器設定之grpc_port進行設定，相關的設定選項如下：

<grpc_port>9100</grpc_port>
    <grpc>
        <enable_ssl>false</enable_ssl>

        <!-- 下列這兩個檔案的設定選項只用在當上述的SSL設定已經啟用並設定為true的情況 -->
        <ssl_cert_file>/path/to/ssl_cert_file</ssl_cert_file>
        <ssl_key_file>/path/to/ssl_key_file</ssl_key_file>

        <!-- 是否伺服器需要請求客戶端的憑證進行驗證 -->
        <ssl_require_client_auth>false</ssl_require_client_auth>

        <!-- 下列的檔案設定只用在當上述的伺服器請求客戶端的憑證設定為true的時候 -->
        <ssl_ca_cert_file>/path/to/ssl_ca_cert_file</ssl_ca_cert_file>

        <!-- 設定預設的壓縮方法，如果客戶端沒有指定的話，則會使用此設定的壓縮演算法進行資料壓縮
             支援的壓縮演算法有：none、deflate、gzip與stream_gzip -->
        <compression>deflate</compression>

        <!-- 設定預設壓縮演法法的等級，如果客戶端沒有指定的話，則會使用此設定的壓縮演法法等級
             支援的壓縮演法等級的設定有：none、low、medium與high -->
        <compression_level>medium</compression_level>

        <!-- 限制傳輸與接收訊息的大小，單位為bytes；若設定為-1則表示不限制 -->
        <max_send_message_size>-1</max_send_message_size>
        <max_receive_message_size>-1</max_receive_message_size>

        <!-- 啟用該設定可以得到更細節的記錄 -->
        <verbose_logs>false</verbose_logs>
    </grpc>

gRPC客戶端建置

我們可以使用任意的程式語言並使用ClickHouse資料庫提供的介面連線規格進行gRPC客戶端的實做。

我們以Python程式語言為例，來開發出相對應的客戶端，我們使用ClickHouse資料庫的原始碼專案中的clickhouse-grpc-client.py進行示範，而這個範例Python程式是需要grpcio與grpcio-tools這兩個Python模組函式庫。

相關執行的方式與指令如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ wget -O clickhouse-grpc-client.py https://raw.githubusercontent.com/ClickHouse/ClickHouse/master/utils/grpc-client/clickhouse-grpc-client.py
--2022-10-03 02:42:03--  https://github.com/ClickHouse/ClickHouse/blob/master/utils/grpc-client/clickhouse-grpc-client.py
Resolving github.com (github.com)... 20.205.243.166
Connecting to github.com (github.com)|20.205.243.166|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]
Saving to: ‘clickhouse-grpc-client.py’

clickhouse-grpc-client.py      [ <=>                                     ] 324.75K  --.-KB/s    in 0.02s

2022-10-03 02:42:03 (12.8 MB/s) - ‘clickhouse-grpc-client.py’ saved [332541]

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get install python3-pip
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  build-essential dh-python dpkg-dev fakeroot g++ g++-7 gcc gcc-7 libalgorithm-diff-perl
  libalgorithm-diff-xs-perl libalgorithm-merge-perl libasan4 libatomic1 libc-dev-bin libc6-dev libcc1-0
  libcilkrts5 libexpat1-dev libfakeroot libgcc-7-dev libitm1 liblsan0 libmpx2 libpython3-dev
  libpython3.6-dev libquadmath0 libstdc++-7-dev libtsan0 libubsan0 linux-libc-dev make manpages-dev
  python-pip-whl python3-crypto python3-dev python3-distutils python3-keyring python3-keyrings.alt
  python3-lib2to3 python3-secretstorage python3-setuptools python3-wheel python3-xdg python3.6-dev
Suggested packages:
........
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ pip3 install -U grpcio grpcio-tools
Collecting grpcio
  Downloading https://files.pythonhosted.org/packages/81/9a/6b33e8d15850356772f0ee6489bc8346a7aa90f0c86733283e139740865e/grpcio-1.48.2.tar.gz (22.0MB)
.......
Building wheels for collected packages: grpcio, grpcio-tools
  Running setup.py bdist_wheel for grpcio-tools ... done
  Stored in directory: /home/peter/.cache/pip/wheels/af/e7/67/b34a27691b71a0e9a3eb8723ce91e5474fff301de382bf9f74
Successfully built grpcio grpcio-tools
Installing collected packages: six, grpcio, protobuf, setuptools, grpcio-tools
Successfully installed grpcio-1.48.2 grpcio-tools-1.48.2 protobuf-3.19.6 setuptools-59.6.0 six-1.16.0

我們以Ubuntu 18.04的Linux發行的作業系統為例，從上述的執行與相關輸出的訊息可以知道，首先我們先使用wget指令將先前提到的clickhouse-grpc-client.py程式進行下載，接著再使用sudo apt-get install python3-pip指令將pip3指令安裝好，這個指令可以協助我們管理Python模組套件，安裝好該指令之後，接著執行pip3 install -U grpcio grpcio-tools指令將gRPC客戶端程式所需要的模組進行安裝，-U的參數指的是將該模組安裝到當前的使用者目錄中。

安裝上述的兩個套件需要等待一段時間，上述的套件安裝完成之後，我們可以來看clickhouse-grpc-client.py程式所支援的參數，相關的參數如下：

--help – 此參數可以用來輸出此程式如何使用與幫助的訊息，輸出上述的訊息之後即離開此程式。
--host HOST或是短參數-h HOST – 為指定伺服器的名稱，後面的HOST需要改成我們實際上的主機名稱值，預設的值為localhost，設定的值我們可以使用IPv4或IPv6位址。
--port PORT – 設定伺服器的埠號，這個埠號需要在ClickHouse資料庫伺服器設定檔中設定（可參考先前提到的grpc_port），預設的值為：9100。
--user USER_NAME與短參數-u USER_NAME – 指定使用者名稱，即後面的USER_NAME替換成使用者名稱，預設的值為：default。
--password PASSWORD – 指定資料庫伺服器的密碼，預設值為空字串。
--query QUERY與短參數-q QUERY – 在非互動模式下，指定一個SQL查詢語句讓資料庫伺服器進行執行與處理。
--database DATABASE與短參數-d DATABASE – 指定預設資料庫名稱，即DATABASE，若沒有指定的話，則預設連上伺服器的資料庫名稱會設定在伺服器的設定檔中。預設會是：default。
--format OUTPUT_FORMAT與短參數-f OUTPUT_FORMAT – 指定輸出查詢結果的格式，預設在互動模式下的輸出格式為：PrettyCompact。
--debug – 啟用顯示除錯相關的訊息。

若要以互動模式執行上述的clickhouse-grpc-client.py程式的話，不需要加入--query參數。

在執行批次模式下的SQL語句可以透過終端機的stdin管線給該clickhouse-grpc-client.py程式。

接下來要設定gRPC介面連線，相關設定的方式與執行指令所輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo vim /etc/clickhouse-server/config.d/grpc.xml
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo cat /etc/clickhouse-server/config.d/grpc.xml
[sudo] password for peter:
<clickhouse>
    <grpc_port>9100</grpc_port>
    <grpc>
        <enable_ssl>false</enable_ssl>
        <compression>deflate</compression>
        <compression_level>medium</compression_level>

        <max_send_message_size>-1</max_send_message_size>
        <max_receive_message_size>-1</max_receive_message_size>

        <verbose_logs>false</verbose_logs>
    </grpc>
</clickhouse>
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo systemctl restart clickhouse-server.service
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo systemctl status clickhouse-server.service
● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
   Loaded: loaded (/lib/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Mon 2022-10-03 04:08:38 UTC; 1s ago
 Main PID: 6967 (clckhouse-watch)
    Tasks: 210 (limit: 4915)
   CGroup: /system.slice/clickhouse-server.service
           ├─6967 clickhouse-watchdog --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid
           └─6982 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid

Oct 03 04:08:38 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[6967]: Logging trace to /var/log/clickhouse-server/clickhouse-server.log
Oct 03 04:08:38 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[6967]: Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
Oct 03 04:08:39 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[6967]: Processing configuration file '/etc/clickhouse-server/config.xml'.
Oct 03 04:08:39 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[6967]: Merging configuration file '/etc/clickhouse-server/config.d/grpc.xml'.
Oct 03 04:08:39 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[6967]: Merging configuration file '/etc/clickhouse-server/config.d/handler.xml'.
Oct 03 04:08:39 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[6967]: Merging configuration file '/etc/clickhouse-server/config.d/static.xml'.
Oct 03 04:08:39 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[6967]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/config.xml'.
Oct 03 04:08:39 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[6967]: Processing configuration file '/etc/clickhouse-server/users.xml'.
Oct 03 04:08:39 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[6967]: Merging configuration file '/etc/clickhouse-server/users.d/default-password.xml'.
Oct 03 04:08:39 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[6967]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/users.xml'.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get install net-tools
Reading package lists... Done
Building dependency tree
Reading state information... Done
net-tools is already the newest version (1.60+git20161116.90da8a0-1ubuntu1).
net-tools set to manually installed.
0 upgraded, 0 newly installed, 0 to remove and 3 not upgraded.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ netstat -ntlp | grep 9100
(No info could be read for "-p": geteuid()=1000 but you should be root.)
tcp6       0      0 ::1:9100                :::*                    LISTEN      -
tcp6       0      0 127.0.0.1:9100          :::*                    LISTEN      -
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ mkdir -p /home/peter/./protos
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ wget -O /home/peter/./protos/clickhouse_grpc.proto https://raw.githubusercontent.com/ClickHouse/ClickHouse/master/src/Server/grpc_protos/clickhouse_grpc.proto
--2022-10-03 06:00:07--  https://raw.githubusercontent.com/ClickHouse/ClickHouse/master/src/Server/grpc_protos/clickhouse_grpc.proto
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.110.133, 185.199.111.133, 185.199.108.133, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.110.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 8374 (8.2K) [text/plain]
Saving to: ‘/home/peter/./protos/clickhouse_grpc.proto’

/home/peter/./protos/click 100%[========================================>]   8.18K  --.-KB/s    in 0s

2022-10-03 06:00:07 (56.4 MB/s) - ‘/home/peter/./protos/clickhouse_grpc.proto’ saved [8374/8374]

從上述的執行指令與輸出的訊息可以知道，首先我們使用vim編輯器進行gRPC設定檔的編輯，並將此編輯的內容儲存到/etc/clickhouse-server/config.d/grpc.xml檔案路徑中。

接著使用cat指令來觀看編寫的設定是否是我們要的，編寫好設定之後，使用systemctl指令將ClickHouse資料庫伺服器服務進行重新啟動與觀看服務狀態是否有重新啟動成功。

接著執行安裝net-tools套件指令，來確認netstat指令可以使用並執行該指令確認9100之埠號已經有被監聽了，接著建立該程式需要讀取gRPC的protos檔案的目錄與檔案。

相關的客戶端程式執行範例與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ python3 ./clickhouse-grpc-client.py --password PASSWORD -q "CREATE TABLE grpc_example_table (id UInt32, text String) ENGINE = MergeTree() ORDER BY id;"

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ clickhouse-client --password
ClickHouse client version 22.8.4.7 (official build).
Password for user (default):
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.8.4 revision 54460.

Warnings:
 * Linux is not using a fast TSC clock source. Performance can be degraded. Check /sys/devices/system/clocksource/clocksource0/current_clocksource

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SHOW TABLES;

SHOW TABLES

Query id: bb64d163-20fb-4e53-80d6-2d755ff87777

┌─name────────────────────┐
│ describe_example        │
│ example_back            │
│ grpc_example_table      │
│ insert_select_testtable │
│ limit_by                │
│ simple_table            │
│ t1                      │
│ t2                      │
│ table_from_file         │
│ table_with_comment      │
│ table_with_ttl          │
│ temp                    │
│ ttt                     │
└─────────────────────────┘

13 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) exit
Bye.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

從上述執行的指令與輸出的訊息可以知道，以非互動模式執行了clickhouse-grpc-client.py程式來建立grpc_example_table資料表，接著再使用clickhouse-client指令去顯示資料表清單，可以驗證此資料表已經透過gRPC介面連線完成執行建立資料表的SQL語句了。

接著建立一個檔案叫做a.csv檔後，使用程式將此CSV檔案內容印出並透過管線方式將內容轉成輸入（stdin）轉給clickhouse-grpc-client.py執行，相關的指令如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ echo -e "0,Input data for\n1,gRPC protocol example" > a.csv
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ cat a.csv
0,Input data for
1,gRPC protocol example
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ cat a.csv | python3 ./clickhouse-grpc-client.py --password PASSWORD -q "INSERT INTO grpc_example_table FORMAT CSV"
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ clickhouse-client --password
ClickHouse client version 22.8.4.7 (official build).
Password for user (default):
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.8.4 revision 54460.

Warnings:
 * Linux is not using a fast TSC clock source. Performance can be degraded. Check /sys/devices/system/clocksource/clocksource0/current_clocksource

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) SELECT * FROM grpc_example_table;

SELECT *
FROM grpc_example_table

Query id: 753737df-108b-44a8-8f8f-46394d9daa48

┌─id─┬─text──────────────────┐
│  0 │ Input data for        │
│  1 │ gRPC protocol example │
└────┴───────────────────────┘

2 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) exit;
Bye.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

從上面的執行指令與輸出的訊息可以知道，執行了INSERT INTO之SQL語句將a.csv檔案內容寫入到grpc_example_table資料表中，接著可以用clickhouse-client指令來驗證先前執行的程式有順利的執行。

最後透過下列指令執行將grpc_example_table資料表所儲存的內容以PrettyCompact格式輸出，相關的執行指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ python3 ./clickhouse-grpc-client.py --password PASSWORD --format PrettyCompact -q "SELECT * FROM grpc_example_table;"
┌─id─┬─text──────────────────┐
│  0 │ Input data for        │
│  1 │ gRPC protocol example │
└────┴───────────────────────┘
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

MySQL模式連線介面

ClickHouse資料庫伺服器支援MySQL wire協定（MySQL wire protocol），這個設定可以透過伺服器設定檔案之mysql_port設定檔進行設定與啟用，相關的設定如下：

<mysql_port>9004</mysql_port>

啟用上述的設定與相關的執行指令所輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo vim /etc/clickhouse-server/config.d/mysql_port.xml
[sudo] password for peter:
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo cat /etc/clickhouse-server/config.d/mysql_port.xml
<clickhouse>
    <mysql_port>9004</mysql_port>
</clickhouse>
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo systemctl restart clickhouse-server.service
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo systemctl status clickhouse-server.service
● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
   Loaded: loaded (/lib/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Mon 2022-10-03 06:35:02 UTC; 13s ago
 Main PID: 16990 (clckhouse-watch)
    Tasks: 210 (limit: 4915)
   CGroup: /system.slice/clickhouse-server.service
           ├─16990 clickhouse-watchdog --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid
           └─17005 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid

Oct 03 06:35:02 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[16990]: Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
Oct 03 06:35:02 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[16990]: Processing configuration file '/etc/clickhouse-server/config.xml'.
Oct 03 06:35:02 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[16990]: Merging configuration file '/etc/clickhouse-server/config.d/grpc.xml'.
Oct 03 06:35:02 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[16990]: Merging configuration file '/etc/clickhouse-server/config.d/handler.xml'.
Oct 03 06:35:02 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[16990]: Merging configuration file '/etc/clickhouse-server/config.d/mysql_port.xml'.
Oct 03 06:35:02 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[16990]: Merging configuration file '/etc/clickhouse-server/config.d/static.xml'.
Oct 03 06:35:02 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[16990]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/config.xml'.
Oct 03 06:35:02 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[16990]: Processing configuration file '/etc/clickhouse-server/users.xml'.
Oct 03 06:35:02 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[16990]: Merging configuration file '/etc/clickhouse-server/users.d/default-password.xml'.
Oct 03 06:35:02 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[16990]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/users.xml'.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ netstat -ntlp | grep 9004
(No info could be read for "-p": geteuid()=1000 but you should be root.)
tcp        0      0 127.0.0.1:9004          0.0.0.0:*               LISTEN      -
tcp6       0      0 ::1:9004                :::*                    LISTEN      -
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

從上述執行與輸出的訊息可以知道，首先建立了mysql_port.xml設定檔並將相關的內容建立後，將設定檔建立到/etc/clickhouse-server/config.d/路徑中，並使用cat指令確認建立的設定檔內容。

建立設定檔內容之後，使用systemctl指令將資料庫伺服器之背景服務進行重新啟動並查看重新啟動服務後的狀態與相關的訊息，接著執行netstat指令去確認設定9004的埠號已經有監聽了。

接著執行下面的指令將mysql-client套件進行安裝來驗證可以使用mysql指令透過MySQL wire協定進行與ClickHouse資料庫之連線，相關的指令執行所輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get install mysql-client
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
  mysql-client
0 upgraded, 1 newly installed, 0 to remove and 3 not upgraded.
Need to get 9824 B of archives.
After this operation, 111 kB of additional disk space will be used.
Get:1 http://mirrors.digitalocean.com/ubuntu bionic-updates/main amd64 mysql-client all 5.7.39-0ubuntu0.18.04.2 [9824 B]
Fetched 9824 B in 0s (383 kB/s)
Selecting previously unselected package mysql-client.
(Reading database ... 135634 files and directories currently installed.)
Preparing to unpack .../mysql-client_5.7.39-0ubuntu0.18.04.2_all.deb ...
Unpacking mysql-client (5.7.39-0ubuntu0.18.04.2) ...
Setting up mysql-client (5.7.39-0ubuntu0.18.04.2) ...
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ mysql --version
mysql  Ver 14.14 Distrib 5.7.39, for Linux (x86_64) using  EditLine wrapper
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ mysql --protocol tcp -u default -P 9004 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 22.8.4.7-ClickHouse

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 22.8.4.7  |
+-----------+
1 row in set (0.00 sec)
Read 1 rows, 1.00 B in 0.000429275 sec., 2329 rows/sec., 2.27 KiB/sec.

mysql> SHOW TABLES;
+-------------------------+
| name                    |
+-------------------------+
| describe_example        |
| example_back            |
| grpc_example_table      |
| insert_select_testtable |
| limit_by                |
| simple_table            |
| t1                      |
| t2                      |
| table_from_file         |
| table_with_comment      |
| table_with_ttl          |
| temp                    |
| ttt                     |
+-------------------------+
13 rows in set (0.00 sec)
Read 13 rows, 472.00 B in 0.001066437 sec., 12190 rows/sec., 432.22 KiB/sec.

mysql>

從上述的執行指令就可以知道，已經成功透過mysql指令與MySQL wire協定連線上ClickHouse資料庫了。

為了要相容於所有的MySQL客戶端，ClickHouse資料庫官方建議指定在伺服器設定檔中的使用者密碼使用「double SHA1」之設定方式進行雜湊。

如果使用者密碼是指定使用SHA256之雜湊演算法，一些客戶端程式將不會進行驗證，例如mysqljs之Node.js套件、舊版MySQL與MariaDB之客戶端指令工具。

在MySQL 5.7版本以上來說，使用者密碼有支援SHA256的雜湊演算法，而MySQL 4.x版本僅支援double SHA1之雜湊演算法，可以透過上述的執行指令所輸出的訊息來驗證這件事情。

下列有幾個使用此方式連線到ClickHouse資料庫的限制情形：

不支援預先處理的查詢（prepared statement query）
一些資料型別會以字串的方式進行傳送。

若要對SQL查詢進行取消，則我們可以使用KILL QUERY connection_id語句來完成這件事情，相關的SQL語句範例如下：

mysql --protocol tcp -h mysql_server -P 9004 default -u default -p -e "KILL QUERY 123456;"

從上述的執行範例來看，其中SQL語句也可以寫成：KILL QUERY WHERE query_id = connection_id。

執行上述的範例與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ mysql --protocol tcp -u default -P 9004 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 22.8.4.7-ClickHouse

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT sleep(3);

從上述的執行指令所輸出的訊息可以知道，該連線id為4，我們透過mysql指令連線到ClickHouse資料庫，接著執行內建函式sleep讓此SQL語句停3秒，接著我們開啟以另一個終端機並執行下列的指令：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ mysql --protocol tcp -u default -P 9004 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 22.8.4.7-ClickHouse

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> KILL QUERY 4;
+-------------+----------------------------------------------+---------+-----------------+
| kill_status | query_id                                     | user    | query           |
+-------------+----------------------------------------------+---------+-----------------+
| waiting     | mysql:4:58391131-425a-4473-be53-9751c6b39ba9 | default | SELECT sleep(3) |
+-------------+----------------------------------------------+---------+-----------------+
1 row in set (0.00 sec)
Read 3 rows, 1.42 KiB in 0.001678632 sec., 1787 rows/sec., 847.63 KiB/sec.

mysql>

從上述輸出的訊息，可以知道，指定連線id為4的查詢進行取消，接著就會出現等待該查詢的回應結果，回到執行SELECT sleep(3)的SQL語句的終端機則會看到下列的輸出訊息：

mysql> SELECT sleep(3);
ERROR 394 (00000): Code: 394. DB::Exception: Query was cancelled. (QUERY_WAS_CANCELLED) (version 22.8.4.7 (official build))
mysql>

這樣就完成了取消指定的連id上正在執行的SQL查詢了。

PostgreSQL模式連線介面

ClickHouse資料庫支援了PostgreSQL wire協定，這可以用來允許我們使用PostgresSQL客戶端連線到ClickHouse資料庫伺服器。

ClickHouse資料庫也可以假裝成PostgreSQL實例（instance），即允許我們連線到PostgreSQL客戶端應用到ClickHouse資料庫伺服器，這樣方式已經有在ClickHouse資料庫中支援了。

舉例來說，Amazon Redshift就可以做到上述這件事情。

要啟用PostgreSQL wire協定，則可以加入postgresql_port設定到ClickHouse資料庫伺服器設定檔中，舉例來說，我們可以在config.d目錄下建立新的XML檔案來定義埠號：

<clickhouse>
    <postgresql_port>9005</postgresql_port>
</clickhouse>

相關進行上述的設定所執行的指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo vim /etc/clickhouse-server/config.d/psql_port.xml
[sudo] password for peter:
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo cat /etc/clickhouse-server/config.d/psql_port.xml
<clickhouse>
    <postgresql_port>9005</postgresql_port>
</clickhouse>
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo systemctl restart clickhouse-server.service
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo systemctl status clickhouse-server.service
● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
   Loaded: loaded (/lib/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Mon 2022-10-03 07:38:21 UTC; 16s ago
 Main PID: 21382 (clckhouse-watch)
    Tasks: 210 (limit: 4915)
   CGroup: /system.slice/clickhouse-server.service
           ├─21382 clickhouse-watchdog --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid
           └─21397 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid

Oct 03 07:38:21 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[21382]: Processing configuration file '/etc/clickhouse-server/config.xml'.
Oct 03 07:38:21 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[21382]: Merging configuration file '/etc/clickhouse-server/config.d/grpc.xml'.
Oct 03 07:38:21 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[21382]: Merging configuration file '/etc/clickhouse-server/config.d/handler.xml'.
Oct 03 07:38:21 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[21382]: Merging configuration file '/etc/clickhouse-server/config.d/mysql_port.xml'.
Oct 03 07:38:21 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[21382]: Merging configuration file '/etc/clickhouse-server/config.d/psql_port.xml'.
Oct 03 07:38:21 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[21382]: Merging configuration file '/etc/clickhouse-server/config.d/static.xml'.
Oct 03 07:38:21 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[21382]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/config.xml'.
Oct 03 07:38:21 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[21382]: Processing configuration file '/etc/clickhouse-server/users.xml'.
Oct 03 07:38:21 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[21382]: Merging configuration file '/etc/clickhouse-server/users.d/default-password.xml'.
Oct 03 07:38:21 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[21382]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/users.xml'.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ netstat -ntlp | grep 9005
(No info could be read for "-p": geteuid()=1000 but you should be root.)
tcp        0      0 127.0.0.1:9005          0.0.0.0:*               LISTEN      -
tcp6       0      0 ::1:9005                :::*                    LISTEN      -
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

從上述的執行指令所輸出的訊息可以知道，設定好PostgreSQL資料庫連線協定的埠號之後，利用cat指令將設定的XML檔案內容印出來驗證設定是正確的。

接著使用systemctl指令將運行在ClickHouse資料庫伺服器的背景服務進行重新啟動與檢查該服務的狀態，最後利用netstat指令驗證9005之埠號已經有成功的讓ClickHouse資料庫監聽了。

我們也可以從ClickHouse資料庫伺服器的log記錄來看到PostgreSQL資料庫連線的資訊，相關節錄的記錄資訊如下：

2022.10.03 07:38:21.970093 [ 21397 ] {} <Information> Application: Listening for PostgreSQL compatibility protocol: [::1]:9005
2022.10.03 07:38:21.971704 [ 21397 ] {} <Information> Application: Listening for PostgreSQL compatibility protocol: 127.0.0.1:9005

接下來我們透過下列的指令操作安裝PostgreSQL客戶端套件，讓我們可以使用psql指令進行ClickHouse資料庫的連線，相關執行指令所輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get install postgresql-client
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  libpq5 postgresql-client-10 postgresql-client-common
Suggested packages:
  postgresql-10 postgresql-doc-10
The following NEW packages will be installed:
  libpq5 postgresql-client postgresql-client-10 postgresql-client-common
.......
update-alternatives: using /usr/share/postgresql/10/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-client (10+190ubuntu0.1) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for libc-bin (2.27-3ubuntu1.6) ...
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

安裝好之後，接著可以執行下列的指令透過psql指令連線到ClickHouse資料庫，相關執行指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ psql -p 9005 -h 127.0.0.1 -U default default
psql: SSL SYSCALL error: Success
ERROR:  Authentication method is not supported
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

從上述的執行指令與輸出的訊息來說，透過psql連線到ClickHouse資料庫伺服器失敗了，原因是透過PostgreSQL客戶端連線到ClickHouse資料庫僅支援純文字密碼的模式，其他的方式都不支援。

相關的議題描述可以在這裡找到。

若要完成使用純明文密碼方式來使用psql指令進行ClickHouse資料庫連線，可以執行下列的指令，相關執行指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo vim /etc/clickhouse-server/users.d/default-password.xml
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo cat /etc/clickhouse-server/users.d/default-password.xml
<clickhouse>
    <users>
        <default>
            <password remove='1' />
            <password_sha256_hex>PASSWORD_SHA256_HEX</password_sha256_hex>
            <access_management>1</access_management>
        </default>
    </users>
</clickhouse>
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo systemctl restart clickhouse-server.service
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo systemctl status clickhouse-server.service
● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
   Loaded: loaded (/lib/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Mon 2022-10-03 08:04:08 UTC; 1min 45s ago
 Main PID: 25024 (clckhouse-watch)
    Tasks: 210 (limit: 4915)
   CGroup: /system.slice/clickhouse-server.service
           ├─25024 clickhouse-watchdog --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid
           └─25039 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid

Oct 03 08:04:09 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[25024]: Processing configuration file '/etc/clickhouse-server/config.xml'.
Oct 03 08:04:09 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[25024]: Merging configuration file '/etc/clickhouse-server/config.d/grpc.xml'.
Oct 03 08:04:09 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[25024]: Merging configuration file '/etc/clickhouse-server/config.d/handler.xml'.
Oct 03 08:04:09 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[25024]: Merging configuration file '/etc/clickhouse-server/config.d/mysql_port.xml'.
Oct 03 08:04:09 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[25024]: Merging configuration file '/etc/clickhouse-server/config.d/psql_port.xml'.
Oct 03 08:04:09 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[25024]: Merging configuration file '/etc/clickhouse-server/config.d/static.xml'.
Oct 03 08:04:09 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[25024]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/config.xml'.
Oct 03 08:04:09 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[25024]: Processing configuration file '/etc/clickhouse-server/users.xml'.
Oct 03 08:04:09 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[25024]: Merging configuration file '/etc/clickhouse-server/users.d/default-password.xml'.
Oct 03 08:04:09 ubuntu-s-4vcpu-8gb-amd-sgp1-01 clickhouse-server[25024]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/users.xml'.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ clickhouse-client --password
ClickHouse client version 22.8.4.7 (official build).
Password for user (default):
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.8.4 revision 54460.

Warnings:
 * Linux is not using a fast TSC clock source. Performance can be degraded. Check /sys/devices/system/clocksource/clocksource0/current_clocksource

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE USER peter HOST ANY IDENTIFIED WITH plaintext_password by 'password';

CREATE USER peter IDENTIFIED WITH plaintext_password BY 'password' HOST ANY

Query id: a791f378-84e1-4096-9ca5-a7673dbd43f5

Ok.

0 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) exit
Bye.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

從上述執行指令所輸出的訊息可以知道，首先先開啟default使用者的設定檔加入<access_management>1</access_management>設定，這樣才可以讓default使用者可以執行CREATE USER之SQL語句。

完成上述的設定之後，接著將ClickHouse資料庫伺服器之背景服務進行重啟並查看重啟後的服務狀態，再使用clickhouse-client指令進行連接並執行CREATE USER建立使用者叫做peter，並指定以明文的密碼設定為password。

設定好之後，離開clickhouse-client之互動介面，執行下列的psql指令來連線到ClickHouse資料庫伺服器，相關執行的指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ psql -p 9005 -h 127.0.0.1 -U peter default
Password for user peter:
psql (10.22 (Ubuntu 10.22-0ubuntu0.18.04.1), server 22.8.4.7)
WARNING: psql major version 10, server major version 22.
         Some psql features might not work.
Type "help" for help.
default=> SELECT version();
 version()
-----------
 22.8.4.7
(1 row)

default=> \q
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

從上述執行psql的指令與輸出的訊息可以知道，透過明文方式的密碼可以透過PostgreSQL wire協定連線到ClickHouse資料庫了，並執行一些指令來驗證，接著使用下列指令將此使用者刪除：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ clickhouse-client --password
ClickHouse client version 22.8.4.7 (official build).
Password for user (default):
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.8.4 revision 54460.

Warnings:
 * Linux is not using a fast TSC clock source. Performance can be degraded. Check /sys/devices/system/clocksource/clocksource0/current_clocksource

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) DROP USER peter;

DROP USER peter

Query id: 903abd12-df70-4fb7-a526-59dd0499124c

Ok.

0 rows in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) exit;
Bye.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

如果我們有設定SSL/TLS在ClickHouse資料庫的話，則postgresql_port將會使用同樣的設定（該埠號會分享給安全與非安全的客戶端）。

每個客戶端有自己的方法與機制來連線使用SSL方式進行資料庫的連線，下列的指令範例展示如何將憑證與金鑰給psql指令與ClickHouse資料庫伺服器進行安全的連線：

psql "port=9005 host=127.0.0.1 user=alice dbname=default sslcert=/path/to/certificate.pem sslkey=/path/to/key.pem sslrootcert=/path/to/rootcert.pem sslmode=verify-ca"

結論

從本章節中，可以知道如何使用gRPC、MySQL與PostgreSQL等客戶端連線介面進行與ClickHouse資料庫連線，唯讀要注意的是，PostgreSQL客戶端連線僅支援ClickHouse資料庫使用者密碼設定是以明文方式儲存的使用者才可以進行連線，這樣將不會是好的方法，原因是可能會導致有心人士與駭客進行嗅探攻擊的風險（sniffing attack）增加。

在下一章節中，將會介紹其他的客戶端連線方式，如JDBC、ODBC以及C++客戶端函式庫等。