大数据ClickHouse进阶（十）：ClickHouse的Array Join子句
【摘要】 ClickHouse的Array Join子句Array join 子句允许在数据表的内部，与数组类型的字段进行join操作，从而将一行数组展开为多行。首先我们创建一张 MergeTree引擎表并加入数据，操作如下：#创建表 mr_tblnode1 :) CREATE TABLE mr_tbl( `id` UInt8, `name` String, `age` Int, ...
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;
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）