**php可用的clickhouse包**
地址:https://packagist.org/explore/?query=clickhouse<br><br>
**ClickHouse在Laravel9框架的應用**
項目使用的是bavix/laravel-clickhouse
php: ^7.2|^8.0
laravel/framework: ^6.0|^7.0|^8.0|^9.0|^10.0
lumen無法使用
Join方式與laravel不同
例：
連接方式
查詢方式如下
目前遇到的與mysql寫法不一致的地方
mysql寫法
clickhouse對一個字段的處理需要分兩次，否則會報錯
相同數據mysql與clickhouse查詢耗時對比，本地環境，本地數據庫，數據相同，全表掃
```
SELECT
link_book_id,
COUNT(DISTINCT CASE WHEN link_amount > 0 THEN user_id END ) as pay_uv,
COUNT(DISTINCT CASE WHEN read_chapter_num > 0 THEN user_id END ) as read_uv,
COUNT(DISTINCT CASE WHEN is_into_read =1 THEN user_id END ) as into_read_uv,
COUNT(DISTINCT CASE WHEN is_paywall =1 THEN user_id END ) as paywall_uv
FROM fq_link_user_behavior_records
group by link_book_id
```
**clickhouse的同步**
1：手動腳本同步，因為clickhouse的主鍵與mysql完全不同且不存在自增，所以腳本同步的過程中需要將mysql的主鍵id同步,根據主鍵之間的差異進行數據同步
2：binlog同步，目前使用的是Bifrox,文檔地址https://www.xbifrost.com/
自動建表的話數據庫會新增
**`bifrost_data_version`** **Int64**,
**`binlog_event_type`** **String**
字段,且insert,update,delete都會數據庫新增一條記錄，自動創建的表引擎是ReplacingMergeTree，屬于MergeTree引擎
自動建表同樣需要加這兩個字段，增刪改會對原數據進行操作，不會出現多余數據，該字段用于異步刪除
3：使用mysql引擎，建表時需要在數據庫上寫入mysql的連接方式并將引擎修改為mysql，不推薦
4:csv導入，navicate導出的csv文件日期格式為YYYY/MM/DD,導入clickhouse的DateTime類型時格式會出錯
Laravel腳本同步流程：
1：composer.json使用的包：bavix/laravel-clickhouse
2：以下為同步代碼片段
```
<?php
namespace App\Console\Commands\Clickhouse;
use App\Models\Clickhouse\ClickHouseRechargeMoneyAndNumsStatistic;
use App\Models\Recharge\RechargeMoneyAndNumsStatistic;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
class SyncClickhouseRechargeMoneyNumsCommand extends Command
{
/**
* The name and signature of the console command.
* php artisan SyncClickhouseRechargeMoneyNumsCommand
* @var string
*/
protected $signature = 'SyncClickhouseRechargeMoneyNumsCommand';
/**
* The console command description.
*
* @var string
*/
protected $description = '同步fq_user_recharge_money_and_nums表到clickhouse';
/**
* Execute the console command.
*
* @return int
*/
public function handle()
{
echo "開始執行同步\n";
$this->syncData();
echo "完成同步\n";
}
public function syncData()
{
// 同步fq_user_recharge_money_and_nums表到clickhouse
$maxMoneyAndNumsId = 0;
if (empty($isAll)) {
// 查詢最大id
$maxMoneyAndNumsId = ClickHouseRechargeMoneyAndNumsStatistic::query()->select("id")->orderByDesc("id")->first()->id ?? 0;
}
RechargeMoneyAndNumsStatistic::query()
->when(!empty($maxLinkUserBehaviorId), function ($q) use ($maxMoneyAndNumsId) {
$q->where("id", ">", $maxMoneyAndNumsId);
})->chunkById(1000, function ($result) {
$connection = DB::connection('bavix::clickhouse')->table('fq_user_recharge_money_and_nums');
$connection->getConnection()->unsetEventDispatcher();
$connection->insert($result->toArray());
});
}
}
```
同步代碼片段2：
```
<?php
namespace App\Console\Commands\Clickhouse;
use App\Models\Book;
use App\Models\Chapter;
use App\Models\ChapterRackDistinct;
use App\Models\Clickhouse\ClickhouseBook;
use App\Models\Clickhouse\ClickhouseChapterRackDistinct;
use App\Models\Clickhouse\ClickhouseChapters;
use App\Models\Clickhouse\ClickhouseExpenses;
use App\Models\Clickhouse\ClickhouseOrder;
use App\Models\Clickhouse\ClickhouseUser;
use App\Models\Clickhouse\ClickhouseUserBookReadTime;
use App\Models\Clickhouse\ClickhouseUserSearchLog;
use App\Models\Clickhouse\ClickhouseUserWelfares;
use App\Models\Expense;
use App\Models\Orders;
use App\Models\User\Users;
use App\Models\User\UserWelfare;
use App\Models\User\UserWelfareConfig;
use App\Models\UserBookReadTime;
use App\Models\UserSearchLog;
use Illuminate\Console\Command;
use App\Models\Clickhouse\ClickhousePageVisit;
use App\Models\PageVisit;
use Illuminate\Support\Facades\DB;
use Tinderbox\Clickhouse\Server as TinderboxServer;
use Tinderbox\Clickhouse\ServerProvider;
class SyncClickhousePageVisitCommand extends Command
{
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'SyncClickhousePageVisitCommand';
/**
* The console command description.
*
* @var string
*/
protected $description = '同步page_visit表到clickhouse';
/**
* Execute the console command.
*
* @return int
*/
public function handle()
{
$this->syncData();
}
public function syncData()
{
$date = date('Ymd',time());
// 同步book表數據
// 查詢page_visit當日最后一條數據id
$bookId = Book::query()->where("created_at","<=",date('Y-m-d 00:00:00',time()))->orderByDesc("id")->first()->id ?? 0;
$clickhouseBookId = ClickhouseBook::query()->select("id")->orderByDesc("id")->first()->id ?? 0;
$bookConnection = DB::connection('bavix::clickhouse')->table('fq_books');
Book::query()
->where("id",">",$clickhouseBookId)
->where("id","<=",$bookId)
->chunkById(1000,function ($result) use ($bookConnection) {
$bookConnection->getConnection()->unsetEventDispatcher();
$bookConnection->insert($result->toArray());
});
// user_welf_config需先截斷表
$table = 'fq_user_welfare_configs';
// 連接到 ClickHouse 數據庫
$server = new TinderboxServer(
env("CLICKHOUSE_DB_HOST"),
env("CLICKHOUSE_DB_PORT"),
env("CLICKHOUSE_DB_DATABASE"),
env("CLICKHOUSE_DB_USERNAME"),
env("CLICKHOUSE_DB_PASSWORD"));
$serverProvider = (new ServerProvider())->addServer($server);
$client = new \Tinderbox\Clickhouse\Client($serverProvider);
$client->writeOne("TRUNCATE TABLE $table");
$welfareConfigConnection = DB::connection('bavix::clickhouse')->table('fq_user_welfare_configs');
UserWelfareConfig::query()
->chunkById(1000,function ($result) use ($welfareConfigConnection) {
$welfareConfigConnection->getConnection()->unsetEventDispatcher();
$welfareConfigConnection->insert($result->toArray());
});
}
}
```
ClickHouse使用的模型代碼片段如下：
```
<?php
namespace App\Models\Clickhouse;
use Bavix\LaravelClickHouse\Database\Eloquent\Model;
/**
* Class ClickHouseRechargeMoneyAndNumsStatistic
* @package App\Models\Clickhouse
*/
class ClickHouseRechargeMoneyAndNumsStatistic extends Model
{
protected $table = 'fq_user_recharge_money_and_nums';
protected $guarded = ['id'];
}
```
