**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'];
}
```
