laravel mysql查询 替换为 clickhouse查询

1. 类库

composer require the-tinderbox/clickhouse-builder=6.1

2. 语法&写法

(1). 查询表达式字段

// mysql
DB::raw($filed);

// clickhouse
raw();

(2). group by 以后使用 any 获取字段

 clickhouse 没有group by 使用 any，相当于mysql 没有group by 使用 count（类似），只会获取第一条数据

(3) . join写法 & 使用join以后出现相同的字段

1. join 以后的两表都有相同的字段，select * from a inner b on a.id = b.id, 则会出现重复的字段会自带表的前缀 例如
a.id, id 两列，这个时候则需要避免使用 * 或者 使用 a.id as a_id 别名来区别对应字段（如果不区分，可能在外层的子查询，会找不到对应的字段）

2. 子查询
// mysql
DB::table(DB::raw("{$query->toSql()} as a"))->addBinding($_query1->getBindings());

// clickhouse
ClickHouseModel::table(raw("{$query->toSql()} as a"));
# clickhouse 不需要 addBinding
3. join写法
	Table::from('table as t')
   ->join(function($join) {
    return $join->table('table2 as t2')
            ->on('t.id', '=', 't2.id')
            ->on('t.id2', '=', raw(1));
    }, null, 'left')
    
 参数1：使用匿名函数，join子查询同理，第一个on作为on的条件，第二个以及以后则为 and 连接，on中需要等于某个定值，则需要使用raw函数包裹，否则则认为为字段
 参数2：null，使用普通join，null即可，不使用left join,right join, 参数2,3不需要填写
 参数3：默认空为 inner join，left：left join,right: right join

(4). count 计数

clickhouse count 函数，除 null 以外的都会被认为有值，计为1，所以需要对应字段可能需要if判断是否为空，则为null 

(5) . 时间格式转换

1. datetime 时间最小为：1970-01-01 00:00:00， 所以即使 insert 一个1960-01-01年也会为最小时间
2. 转换时间字段为对应的年月日格式   
/**
* Desc : 获取clickhouse的日期函数
* @param $date_group
* @param $time_field
* @return string
*/
public function getClickHouseDateFunction($date_group, $time_field)
{
   $group = '';
   switch ($date_group) {
       case '%Y-%m-%d':
           $group = 'toDate';
           break;
       case '%Y-%m':
           return "formatDateTime($time_field, '%Y-%m', 'Asia/Shanghai')";
       case '%Y':
           $group = 'toYear';
           break;
       default:
           break;
   }
   return "{$group}({$time_field})";
}

(6) . 临时表

（1）$在使用临时表时，出现了上下文使用临时表插入以后，查询时，查询不到临时表$
原因：因为使用代码链接时使用的是https链接，clickhouse允许使用标识session_id来标识同一会话
在类库的 vendor/the-tinderbox/clickhouse-php-client/src/Query.php 文件中的getSettings() 函数需要设置 session_id
可以通过 Provider 来重写对应的方法，再实例化对应的客户端类

(2) . statement 函数不允许 多行执行 先删除后添加临时表

(7) . 封装调用方法

/**
* @param $args
* @return mixed
* @throws Exception
*/
public static function isCallClickHouse($args)
{
   $callStack = debug_backtrace();

   if (!empty($callStack[1]['class'])) {
       [$class, $function, $params] = [$callStack[1]['class'], $callStack[1]['function'], $args];

       $method = $function . 'ByClickHouse';

       if ((new ReflectionClass($class))->isAbstract()) {
           if (method_exists($class, $method)) {
               $class = $callStack[1]['object'];
               return call_user_func_array([$class, $method], $params);
           }
       }

       if (method_exists($class, $method)) {
           return call_user_func_array([new $class, $method], $params);
       }
   }

   throw new Exception('无法找到对应的clickhouse版本的方法: ' . ($function ?? 'unknow'));
}

8. 表引擎

使用到的项目用到了三种

1. ENGINE = MergeTree

大部分创建表引擎使用的 MergeTree

2. ENGINE = ReplacingMergeTree(version)

version 代表实际字段，表新增了 version DateTime DEFAULT now()；


该引擎与MergeTree的区别在于，它会删除排序键值相同的重复项。在select count() from 表名时，查询到的条数并非实际条数，需要使用final 关键词，select count() from 表名 final 才可以查询到实际条数

3. ENGINE = Memory()

内存表，通常使用临时表创建时使用

9. 其他

1. clickhouse版本问题
   在使用 22.8.5.27 版本时，在inner join 时，链表的字段需要是同一类型，比如 Int32 只能和 Int32 类型的字段进行关联

2. mysql表的id主键自增ID 在clickhouse无法使用
   在使用 22.8.5.29 版本时，业务需求需要临时表的id来计算排名，因为无法设置自增，所以在insert 数据时，使用了
   ROW_NUMBER() OVER () AS id 来根据已经排序好的数据进行设置按照顺序的id

3. 链接数据的软件
   使用了 dbeaver 来链接clickhouse，需要设置
   user_time_zone: Shanghai // 时区
   session_id: 1 // 需要在dbeaver中 使用临时表查询，则需要设置 session_id，随机数即可

4. 字符串拼接

// mysql
concat('我的世界', "已", "坠入爱河")

// clickhouse
('我的世界' || "已" || "坠入爱河")

5. Host [localhost] returned error: Code: 373. DB::Exception: Session is locked by a concurrent client. (SESSION_IS_LOCKED
   会话死锁，终止阻塞会话

// 根据查询出的信息，判断哪个是死锁的会话
SELECT query_id, user, query, client_hostname, client_name FROM system.processes;

// 根据query_id 杀掉会话
KILL QUERY WHERE query_id = '3ef7669f-41c7-4130-8c0b-0c3c0880f8eb';