大数据ClickHouse进阶（十）：ClickHouse的Array Join子句

Lansonli • 2023-01-02 • 云技术社区 • 274 阅读

ClickHouse的Array Join子句

Array join 子句允许在数据表的内部，与数组类型的字段进行join操作，从而将一行数组展开为多行。

首先我们创建一张 MergeTree引擎表并加入数据，操作如下：

#创建表 mr_tbl
node1 :) CREATE TABLE mr_tbl
(
    `id` UInt8,
    `name` String,
    `age` Int,
    `local` Array(String)
)
ENGINE = MergeTree()
ORDER BY id
#向表mr_tbl中插入数据
node1 :) insert into table mr_tbl values (1,'zs',18,['beijing','shanghai']),(2,'ls',19,['guangzhou','hangzhou']),(3,'ww',20,[]);

┌─id─┬─name─┬─age─┬─local────────────────────┐
│  1 │ zs   │  18 │ ['beijing','shanghai']   │
│  2 │ ls   │  19 │ ['guangzhou','hangzhou'] │
│  3 │ ww   │  20 │ []                       │
└────┴──────┴─────┴──────────────────────────┘

我们可以使用array join针对以上表数组字段一条膨胀成多条数据，类似Hive中的explode函数，在ClickHouse中没有explode函数，可以使用array join 达到同样效果。

在使用Array join时，一条select语句中只能存在一个Array join（使用嵌套子查询除外），目前支持INNER和LEFT两种JOIN策略：

一、INNER ARRAY JOIN

Array join 默认使用的就是INNER JOIN 策略，使用如下：

node1 :) SELECT id,name,age,local FROM mr_tbl ARRAY JOIN local;
┌─id─┬─name─┬─age─┬─local─────┐
│  1 │ zs   │  18 │ beijing   │
│  1 │ zs   │  18 │ shanghai  │
│  2 │ ls   │  19 │ guangzhou │
│  2 │ ls   │  19 │ hangzhou  │
└────┴──────┴─────┴───────────┘

从以上查询结果来看，数据由原来的一行根据local列变成多行，并且排除掉了空数组对应的行。在使用Array Join时，如果我们在膨胀之后的数据结果中能够访问原有数组字段可以使用如下方式查询：

node1 :) SELECT id,name,age,local ,v FROM mr_tbl ARRAY JOIN local AS v;
┌─id─┬─name─┬─age─┬─local────────────────────┬─v─────────┐
│  1 │ zs   │  18 │ ['beijing','shanghai']   │ beijing   │
│  1 │ zs   │  18 │ ['beijing','shanghai']   │ shanghai  │
│  2 │ ls   │  19 │ ['guangzhou','hangzhou'] │ guangzhou │
│  2 │ ls   │  19 │ ['guangzhou','hangzhou'] │ hangzhou  │
└────┴──────┴─────┴──────────────────────────┴───────────┘

二、LEFT ARRAY JOIN

Array Join 子句支持LEFT连接策略，Left array join不会排除空数组，执行如下语句并查看结果。

node1 :) SELECT id,name,age,local FROM mr_tbl LEFT ARRAY JOIN local;
┌─id─┬─name─┬─age─┬─local─────┐
│  1 │ zs   │  18 │ beijing   │
│  1 │ zs   │  18 │ shanghai  │
│  2 │ ls   │  19 │ guangzhou │
│  2 │ ls   │  19 │ hangzhou  │
│  3 │ ww   │  20 │           │
└────┴──────┴─────┴───────────┘

当同时对多个数组字段进行Array join 操作时，array join 对应的多个字段的数组长度必须相等，查询的计算逻辑是按行合并并不是产生笛卡尔积，举例如下：

#创建表 mr_tbl2
node1 :) CREATE TABLE mr_tbl2
(
    `id` UInt8,
    `name` String,
    `age` Int,
`local` Array(String),
`score` Array(UInt32)
)
ENGINE = MergeTree()
ORDER BY id

#向表mr_tbl2中插入以下数据
node1 :) insert into table mr_tbl2 values (1,'zs',18,['beijing','shanghai'],[100,200]),(2,'ls',19,['guangzhou','hangzhou'],[300,400]),(3,'ww',20,[],[]);

┌─id─┬─name─┬─age─┬─local────────────────────┬─score─────┐
│  1 │ zs   │  18 │ ['beijing','shanghai']   │ [100,200] │
│  2 │ ls   │  19 │ ['guangzhou','hangzhou'] │ [300,400] │
│  3 │ ww   │  20 │ []                       │ []        │
└────┴──────┴─────┴──────────────────────────┴───────────┘

#执行array join 语句，将数组中的数据一变多行
node1 :) select id,name,age,local,local2,score,score2 from mr_tbl2 left array join local as local2 ,score as score2;