本章節中，將會介紹一些ClickHouse資料庫官方並未以此程式語言實做的第三方資料庫客戶端連線的函式庫。
第三方的客戶端連線介面即代表ClickHouse資料庫的官方並不會進行維護，是由私人團隊或是個人進行維護的函式庫專案，就可以稱為第三方客戶端介面函式庫定義。
從ClickHouse官網列出的第三方函式庫中，關於以PHP程式語言實做的有下列這幾個：
從上面列出的PHP第三方函式庫列表，排除需要較為複雜的安裝PHP擴展（extension）專案，再進一步整理出還在活躍的專案：
在使用上述的兩個PHP套件之前，需要先將目標的作業系統進行PHP的執行環境安裝，這邊以Ubuntu 18.04的Linux發行版本作業系統為例，相關執行指令與輸出的訊息如下：
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo add-apt-repository ppa:ondrej/php -y
[sudo] password for peter:
Get:1 http://mirrors.digitalocean.com/ubuntu bionic InRelease [242 kB]
Hit:2 http://mirrors.digitalocean.com/ubuntu bionic-updates InRelease
Hit:3 http://mirrors.digitalocean.com/ubuntu bionic-backports InRelease
Hit:4 https://packages.clickhouse.com/deb stable InRelease
Get:5 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]
Hit:6 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease
......
Fetched 499 kB in 4s (138 kB/s)
Reading package lists... Done
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get install php8.0-cli php8.0-curl php8.0-xml php8.0-zip php8.0-mbstring
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libargon2-1 libpcre2-8-0 libzip4 php-common php8.0-common php8.0-opcache php8.0-readline
Suggested packages:
php-pear
The following NEW packages will be installed:
libargon2-1 libpcre2-8-0 libzip4 php-common php8.0-cli php8.0-common php8.0-curl php8.0-opcache
php8.0-readline php8.0-xml php8.0-zip
0 upgraded, 11 newly installed, 0 to remove and 27 not upgraded.
Need to get 3029 kB of archives.
After this operation, 16.1 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
......
Creating config file /etc/php/8.0/cli/php.ini with new version
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for libc-bin (2.27-3ubuntu1.6) ...
Processing triggers for php8.0-cli (1:8.0.24-1+ubuntu18.04.1+deb.sury.org+2) ...
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ php -v
PHP 8.0.24 (cli) (built: Sep 29 2022 22:26:26) ( NTS )
Copyright (c) The PHP Group
Zend Engine v4.0.24, Copyright (c) Zend Technologies
with Zend OPcache v8.0.24, Copyright (c), by Zend Technologies
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ curl -sS https://getcomposer.org/installer | php
All settings correct for using Composer
Downloading...
Composer (version 2.4.2) successfully installed to: /home/peter/composer.phar
Use it: php composer.phar
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ php ./composer.phar --version
Composer version 2.4.2 2022-09-14 16:11:15
從上述的輸出訊息可以知道，先引入外部的執行PHP程式語言環境相關套件的儲存庫，安裝的PHP版本是8.0，接著再安裝需要用到的擴展，安裝完成之後使用curl指令將Composer（PHP套件管理器）下載回來。
此套件是透過HTTP介面進行資料庫的連線，執行下列的指令將此套件進行安裝，相關執行指令與輸出的訊息如下：
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ php ~/composer.phar require smi2/phpClickHouse
Info from https://repo.packagist.org: #StandWithUkraine
Using version ^1.4 for smi2/phpclickhouse
./composer.json has been updated
Running composer update smi2/phpclickhouse
Loading composer repositories with package information
Updating dependencies
Lock file operations: 1 install, 0 updates, 0 removals
- Locking smi2/phpclickhouse (1.4.6)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 1 install, 0 updates, 0 removals
- Downloading smi2/phpclickhouse (1.4.6)
- Installing smi2/phpclickhouse (1.4.6): Extracting archive
Generating autoload files
10 packages you are using are looking for funding.
Use the `composer fund` command to find out more!
No security vulnerability advisories found
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$
安裝完成之後，使用vim文字編輯器進行client.php檔案的編輯，執行下列指令與輸出的訊息如下：
vim client.php
填入的PHP程式碼內容如下：
<?php
require_once __DIR__ . '/vendor/autoload.php';
// 設定資料庫主機、埠號、此用者名稱以及密碼
$config = [
'host' => '192.168.1.1',
'port' => '8123',
'username' => 'default',
'password' => 'password'
];
$db = new ClickHouseDB\Client($config);
$db->database('default'); // 設定預設連上哪個資料庫的名稱
$db->setTimeout(10); // 設定發送請求語句逾時時間，這邊設定為10秒
$db->setConnectTimeOut(5); // 設定連上資料庫逾時時間，這邊設定為5秒
$db->ping(true); // 如果無法成功連上資料庫則丟出例外的訊息
$result = $db->select('SELECT number FROM numbers(10)'); // 執行輸出0~9數字的SQL語句
// 走訪$result變數將資料依序的輸出
foreach ($result as $key => $value) {
echo $value['number'], PHP_EOL;
}
執行上述的client.php程式之後，輸出的訊息如下：
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ php client.php
0
1
2
3
4
5
6
7
8
9
更多的範例可以在該PHP套件原始碼的README.md檔案中找到：https://github.com/smi2/phpClickHouse/blob/master/README.md
此套是使用HTTP介面進行資料庫連線，執行下列的指令將此套件進行安裝，相關執行指令與輸出的訊息如下：
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ php ~/composer.phar require simpod/clickhouse-client
Info from https://repo.packagist.org: #StandWithUkraine
Cannot use simpod/clickhouse-client's latest version 0.6.3 as it requires php ^8.1 which is not satisfied by your platform.
Using version ^0.6.2 for simpod/clickhouse-client
......
- Installing simpod/clickhouse-client (0.6.2): Extracting archive
6 package suggestions were added by new dependencies, use `composer suggest` to see details.
Generating autoload files
6 packages you are using are looking for funding.
Use the `composer fund` command to find out more!
No security vulnerability advisories found
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ php ~/composer.phar require nyholm/psr7
Info from https://repo.packagist.org: #StandWithUkraine
Using version ^1.5 for nyholm/psr7
./composer.json has been updated
Running composer update nyholm/psr7
Loading composer repositories with package information
Updating dependencies
Lock file operations: 1 install, 0 updates, 0 removals
- Locking nyholm/psr7 (1.5.1)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 1 install, 0 updates, 0 removals
- Downloading nyholm/psr7 (1.5.1)
- Installing nyholm/psr7 (1.5.1): Extracting archive
Generating autoload files
7 packages you are using are looking for funding.
Use the `composer fund` command to find out more!
No security vulnerability advisories found
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ php ~/composer.phar require symfony/http-client
Cannot use symfony/http-client's latest version v6.1.5 as it requires php >=8.1 which is not satisfied by your platform.
Using version ^6.0 for symfony/http-client
./composer.json has been updated
Running composer update symfony/http-client
Loading composer repositories with package information
Updating dependencies
Lock file operations: 4 installs, 0 updates, 0 removals
- Locking psr/container (2.0.2)
- Locking symfony/http-client (v6.0.13)
- Locking symfony/http-client-contracts (v3.0.2)
- Locking symfony/service-contracts (v3.0.2)
......
10 packages you are using are looking for funding.
Use the `composer fund` command to find out more!
No security vulnerability advisories found
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$
從上述指令可以知道，除了安裝主要的simpod/clickhouse-client套件之外，還需要額外安裝symfony/http-client與nyholm/psr7，在建立連線的時候需要用到。
安裝完成之後，使用vim文字編輯器編輯client2.php檔案，相關的指令如下：
vim client2.php
相關的PHP程式碼如下：
<?php
require_once __DIR__ . '/vendor/autoload.php';
use Http\Client\Curl\Client;
use Nyholm\Psr7\Factory\Psr17Factory;
use SimPod\ClickHouseClient\Format\Json;
use SimPod\ClickHouseClient\Format\JsonCompact;
use Symfony\Component\HttpClient\Psr18Client;
use Symfony\Component\HttpClient\CurlHttpClient;
use SimPod\ClickHouseClient\Client\PsrClickHouseClient;
use SimPod\ClickHouseClient\Client\Http\RequestFactory;
$psr17Factory = new Psr17Factory;
$endpoint = 'http://127.0.0.1:8123';
$headers = [
'X-ClickHouse-User' => 'default',
'X-ClickHouse-Key' => 'password',
];
$database = 'default';
$clickHouseClient = new PsrClickHouseClient(
new Psr18Client(
new CurlHttpClient([
'base_uri' => $endpoint,
'headers' => $headers,
'query' => ['database' => $database],
])
),
new RequestFactory(
$psr17Factory,
$psr17Factory
),
);
$outputJson = $clickHouseClient->select('SELECT number FROM numbers(10)', new Json());
$outputJsonCompact = $clickHouseClient->select('SELECT number FROM numbers(10)', new JsonCompact());
var_dump($outputJson->data);
var_dump($outputJsonCompact->data);
從上述的程式碼可以知道，首先建立相容PSR-18標準的HTTP客戶端的類別實例，接著執行SELECT number FROM numbers(10)語句將輸出的結果分別轉成JSON與JsonCompact的格式，最後以var_dump輸出。
需要注意的是，password需要改成使用資料庫的密碼，執行上述的程式碼之後，相關的內容輸出如下：
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ php client2.php
array(10) {
[0]=>
array(1) {
["number"]=>
string(1) "0"
}
[1]=>
array(1) {
["number"]=>
string(1) "1"
}
[2]=>
array(1) {
["number"]=>
string(1) "2"
}
[3]=>
array(1) {
["number"]=>
string(1) "3"
}
[4]=>
array(1) {
["number"]=>
string(1) "4"
}
[5]=>
array(1) {
["number"]=>
string(1) "5"
}
[6]=>
array(1) {
["number"]=>
string(1) "6"
}
[7]=>
array(1) {
["number"]=>
string(1) "7"
}
[8]=>
array(1) {
["number"]=>
string(1) "8"
}
[9]=>
array(1) {
["number"]=>
string(1) "9"
}
}
array(10) {
[0]=>
array(1) {
[0]=>
string(1) "0"
}
[1]=>
array(1) {
[0]=>
string(1) "1"
}
[2]=>
array(1) {
[0]=>
string(1) "2"
}
[3]=>
array(1) {
[0]=>
string(1) "3"
}
[4]=>
array(1) {
[0]=>
string(1) "4"
}
[5]=>
array(1) {
[0]=>
string(1) "5"
}
[6]=>
array(1) {
[0]=>
string(1) "6"
}
[7]=>
array(1) {
[0]=>
string(1) "7"
}
[8]=>
array(1) {
[0]=>
string(1) "8"
}
[9]=>
array(1) {
[0]=>
string(1) "9"
}
}
更多的範例程式碼可以在下列的測試程式碼的連結中找到：
在ClickHouse官方中提供的是TypeScript的JS版本並以HTTP介面連線方式進行存取資料庫，第三方有直接使用後端JS（即Node.js）的方式實做的函式庫，相關的列表如下：
篩選掉已經很久沒有維護與TypeScript的專案後，剩下的專案如下：
假設已經安裝好可以執行Node.js的環境，若沒有的話，需要先參考先前章節進行Node.js環境的安裝與建置，相關執行指令與輸出的訊息如下：
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ npm i --save @depyronick/clickhouse-client
added 7 packages, and audited 29 packages in 13s
1 package is looking for funding
run `npm fund` for details
found 0 vulnerabilities
安裝完成之後，使用vim文字編輯器編輯client.js的檔案，相關檔案內容如下：
const { ClickHouseClient } = require('@depyronick/clickhouse-client');
const analyticsServer = new ClickHouseClient({
host: '127.0.0.1',
port: 8123,
username: 'default',
password: 'password',
database: 'default',
});
analyticsServer.query('SELECT number FROM numbers(10)').subscribe({
error: (err) => {
console.log(err);
},
next: (row) => {
console.log(row);
},
complete: () => {
}
});
從上述的程式碼可以知道，要注意設定password為資料庫的使用者密碼，完成上述的程式碼之後，接著執行client.js程式，執行程式所輸出的訊息如下：
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ node client.js
{ number: '0' }
{ number: '1' }
{ number: '2' }
{ number: '3' }
{ number: '4' }
{ number: '5' }
{ number: '6' }
{ number: '7' }
{ number: '8' }
{ number: '9' }
更多的範例可以參考專案原始碼的README.md：https://github.com/depyronick/clickhouse-client/blob/main/README.md
目前R程式語言只有一個第三方的專案：https://github.com/IMSMWU/RClickHouse
要執行R程式語言之前，需要先建置好R程式語言的環境，這邊以Ubuntu 18.04的Linux發行版本的作業系統為例進行建置，相關建置所需要執行的指令與輸出的訊息如下：
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys E298A3A825C0D65DFD57CBB651716619E084DAB9
[sudo] password for peter:
Executing: /tmp/apt-key-gpghome.UNIco6D1kT/gpg.1.sh --keyserver keyserver.ubuntu.com --recv-keys E298A3A825C0D65DFD57CBB651716619E084DAB9
gpg: key 51716619E084DAB9: public key "Michael Rutter <marutter@gmail.com>" imported
gpg: Total number processed: 1
gpg: imported: 1
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo add-apt-repository 'deb https://cloud.r-project.org/bin/linux/ubuntu bionic-cran35/'
Get:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran35/ InRelease [3626 B]
Get:2 http://mirrors.digitalocean.com/ubuntu bionic InRelease [242 kB]
......
Fetched 469 kB in 2s (218 kB/s)
Reading package lists... Done
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get install r-base -y
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
......
Processing triggers for install-info (6.5.0.dfsg.1-2) ...
Processing triggers for libc-bin (2.27-3ubuntu1.6) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ R --version
R version 3.6.3 (2020-02-29) -- "Holding the Windsock"
Copyright (C) 2020 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under the terms of the
GNU General Public License versions 2 or 3.
For more information about these matters see
https://www.gnu.org/licenses/.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$
因為這個R套件是以ClickHouse資料庫官方的C++客戶端函式庫為基礎的所開發出來的，因此需要在安裝此R套件時進行編譯，因此需要有C與C++的編譯器，這可以在之前的章節中進行編譯的環境建置。
在Ubuntu 18.04版本中，則會自動在安裝r-base的時候把編譯需要的環境同時也安裝起來。
安裝好R程式語言執行的環境之後，接著可以執行下列的指令將此R套件安裝起來，相關執行的指令與輸出的訊息如下：
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ R
R version 3.6.3 (2020-02-29) -- "Holding the Windsock"
Copyright (C) 2020 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.
R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.
Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
> install.packages("RClickhouse")
Installing package into ‘/usr/local/lib/R/site-library’
(as ‘lib’ is unspecified)
Warning in install.packages("RClickhouse") :
'lib = "/usr/local/lib/R/site-library"' is not writable
Would you like to use a personal library instead? (yes/No/cancel) yes
Would you like to create a personal library
‘~/R/x86_64-pc-linux-gnu-library/3.6’
to install packages into? (yes/No/cancel) yes
also installing the dependencies ‘fansi’, ‘pkgconfig’, ‘ellipsis’, ‘utf8’, ‘generics’, ‘glue’, ‘lifecycle’, ‘magrittr’, ‘R6’, ‘rlang’, ‘tibble’, ‘tidyselect’, ‘vctrs’, ‘pillar’, ‘assertthat’, ‘blob’, ‘purrr’, ‘withr’, ‘bit’, ‘dplyr’, ‘dbplyr’, ‘DBI’, ‘Rcpp’, ‘bit64’, ‘cli’
......
** byte-compile and prepare package for lazy loading
** help
*** installing help indices
** building package indices
** testing if installed package can be loaded from temporary location
** checking absolute paths in shared objects and dynamic libraries
** testing if installed package can be loaded from final location
** testing if installed package keeps a record of temporary installation path
* DONE (RClickhouse)
The downloaded source packages are in
‘/tmp/RtmpkkvUdR/downloaded_packages’
>
接著使用vim文字編輯器開啟client.R，並編輯下列R之程式碼：
con <- DBI::dbConnect(
RClickhouse::clickhouse(),
host="127.0.0.1",
port=9000,
user="default",
password="password"
)
res <- DBI::dbGetQuery(con, "SELECT number FROM numbers(10)")
print(res)
DBI::dbDisconnect(con)
從上述的程式碼可以知道，這個R套件是透過ClickHouse資料庫的原生TCP方式連線的，因此預設的埠號為9000，而要注意的是使用者資料庫密碼需要改為自己確切的使用者密碼。
編輯好程式碼之後，執行該R程式並會輸出下面的訊息：
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ Rscript client.R
number
1 0
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
更多有關於此R套件的範例可以從專案原始碼的Readme.md中進行參考：https://github.com/IMSMWU/RClickhouse/blob/master/Readme.md
從本章節中可以知道，其他第三方資料庫客戶端連線的函式庫並用不同的程式語言實做，我們挑選了幾個官方沒有用到的程式語言實做的函式庫作為範例建置與教學的重點。
iThome鐵人賽
看影片追技術