clickhouse中使用Join表引擎

2022-03-19 张子阳分类: 大数据处理

在上一篇文章 clickhouse选取group后的首末行中，我们介绍了在Clickhouse中获取分组后的首/末行的几种方式。常见的使用场景，例如：针对用户订单表，查询：用户的首次付款金额、首次订单号、用户最后一次付款金额、最后一次订单号。这篇文章介绍另一种方式：利用Join表的特性，来获取首行记录，实现和上一篇中获取首行类似的效果。在生产环境中，可以根据情况灵活选用。

准备工作

和前一章类似，我们先做一些数据准备，创建一张user_order用户订单表：

drop table if exists user_order;
create table user_order
(    
    user_id String,         // 用户ID
    event_date String,      // 付款日期
    order_no String,        // 订单号
    amount Int32            // 金额
) ENGINE = MergeTree()
ORDER BY (user_id, event_date)

创建基于Join引擎的物化视图

Join表引擎和Set表引擎共用了大部分的处理逻辑，继承自Set的重要特性就是：所有元素都是基于关联字段唯一的。这样，当我们需要首行数据时，就可以利用Join表的这个特性，创建一个基于Join引擎的物化视图。

drop view if exists user_order_userid_j;
CREATE MATERIALIZED VIEW user_order_userid_j
ENGINE = Join(ANY, INNER, user_id)
POPULATE
AS
select user_id, event_date, order_no, amount
from user_order

需要注意表引擎的声明语句 ENGINE = Join(ANY, INNER, user_id)；第1个参数为连接精度(join_strictness)，当第一个取值为ANY时，会去除重复键；第2个参数，连接类型(join_type)，决定了左右两个数据集合的策略，目前支持INNER、OUTER和CROSS；第3个参数，决定了用哪个字段进行关联，可以是1个或者多个。更加详细的介绍，可以参看官方文档：Join Table Engine

需要说明的是：Join表引擎更加通常的用途，是用于Join连接查询的右侧表。且Join表的数据是首先被写至内存，然后才被同步到磁盘文件上。这意味着两件事：1.Join表的查询速度很快，因为它的存在本来就是为了优化连接查询的速度；2.Join表不适合存放千万级以上的大表，否则会占用过多的服务器内存，它更适合存放需要经常查询的小表，且通常为join语句的右侧表。

数据测试

向基础的明细表中插入数据：

insert into user_order(user_id, event_date, order_no, amount)
values
('user1', '2022-01-01', 'B', 4),
('user1', '2022-01-01', 'C', 8),
('user1', '2022-01-01', 'A', 2),
('user2', '2022-01-02', 'E', 3),
('user2', '2022-01-02', 'D', 7),
('user1', '2022-01-02', 'X', 6),
('user1', '2022-01-02', 'Y', 9)

再对 user_order_userid_j 进行查询：

select * from user_order_userid_j

可以看到，user_order_userid_j 中仅保留了不同user_id 的首行数据：

('user1', '2022-01-01', 'B', 4)
('user2', '2022-01-02', 'E', 3)

基于多个字段分组

在上面的例子中，相当于基于用户ID分组后取首行。如果需要更多列进行分组，则需要重新创建一个新的Join表引擎，比如基于user_id、event_date进行分组。那么按照官方文档，可以像下面这样创建物化视图：

drop view if exists user_order_userid_eventdate_j;
CREATE MATERIALIZED VIEW user_order_userid_eventdate_j
ENGINE = Join(ANY, INNER, user_id, event_date)
POPULATE
AS
select user_id, event_date, order_no, amount
from user_order

像上面的方式，在创建时并不会发生报错，但是在执行查询时，select * from user_order_userid_eventdate_j，却会报下面的错误：Unsupported JOIN keys in StorageJoin. Type: 11: While executing Join. (UNSUPPORTED_JOIN_KEYS) (version 21.9.2.17 (official build))

因为我的创建方式并无问题，所以猜想可能是clickhouse尚不完善的原因，在未来的版本中可能会解决这个问题。目前如果要完成这样的操作，只能将两个字段合并成一个，再用这个字段作为连接字段：

drop view if exists user_order_userid_eventdate_j;
CREATE MATERIALIZED VIEW user_order_userid_eventdate_j
ENGINE = Join(ANY, INNER, id)
POPULATE
AS
select concat(user_id, event_date) id, user_id, event_date, order_no, amount
from user_order

注意到上面的POPULATE关键字，在创建这个物化视图的时候，会自动填充数据。接下来我们对它进行一下查询：

select * from user_order_userid_eventdate_j

可以看到已经返回了预期的结果：

总结

在这篇文章中，我们利用Join表引擎的特性：基于关联列自动去重，通过创建Join表引擎的物化视图，实现了分组后取首行记录的需求。可以作为上一篇文章的补充。

感谢阅读，希望这篇文章能给你带来帮助！