如何使用 array join 提取和查询使用 map 键和值的可变属性
·4 分钟阅读
简单的示例，说明如何使用 array join 提取和查询使用 map 键和值的可变属性
问题
如果我在使用 map 类型的列中具有可变属性，如何提取它们并在查询中使用它们？
答案
这是一个从可变属性字段中提取键和值的基本示例。此方法将从源/原始表中的每一行创建看似重复项。但是，由于键和值被提取出来，它们可以放入主键或带有索引的辅助键中，例如布隆过滤器。
在此示例中，我们基本上有一个源，该源创建一个指标表，它在具有 map 的属性字段中具有可以应用的多个属性。如果存在始终存在于记录中的属性，则最好将这些属性提取到它们自己的列中并填充。
您应该能够直接复制和粘贴以查看输出结果以及物化视图在此实例中的作用。
创建一个示例数据库
create database db1;
创建将包含行和属性的初始表
create table db1.table1_metric_map
(
id UInt32,
timestamp DateTime,
metric_name String,
metric_value Int32,
attributes Map(String, String)
)
engine = MergeTree()
order by timestamp;
将示例行插入表中。示例大小有意设置得很小，以便在创建物化视图时，您可以看到每行的属性是如何倍增的。
insert into db1.table1_metric_map
VALUES
(1, '2023-09-20 00:01:00', 'ABC', 10, {'env':'prod','app':'app1','server':'server1'}),
(2, '2023-09-20 00:01:00', 'ABC', 20,{'env':'prod','app':'app2','server':'server1','dc':'dc1'}),
(3, '2023-09-20 00:01:00', 'ABC', 30,{'env':'qa','app':'app1','server':'server1'}),
(4, '2023-09-20 00:01:00', 'ABC', 40,{'env':'qa','app':'app2','server':'server1','dc':'dc1'}),
(5, '2023-09-20 00:01:00', 'DEF', 50,{'env':'prod','app':'app1','server':'server2'}),
(6, '2023-09-20 00:01:00', 'DEF', 60, {'env':'prod','app':'app2','server':'server1'}),
(7, '2023-09-20 00:01:00', 'DEF', 70,{'env':'qa','app':'app1','server':'server1'}),
(8, '2023-09-20 00:01:00', 'DEF', 80,{'env':'qa','app':'app2','server':'server1'}),
(9, '2023-09-20 00:02:00', 'ABC', 90,{'env':'prod','app':'app1','server':'server1'}),
(10, '2023-09-20 00:02:00', 'ABC', 100,{'env':'prod','app':'app1','server':'server2'}),
(11, '2023-09-20 00:02:00', 'ABC', 110,{'env':'qa','app':'app1','server':'server1'}),
(12, '2023-09-20 00:02:00', 'ABC', 120,{'env':'qa','app':'app1','server':'server1'}),
(13, '2023-09-20 00:02:00', 'DEF', 130,{'env':'prod','app':'app1','server':'server1'}),
(14, '2023-09-20 00:02:00', 'DEF', 140,{'env':'prod','app':'app2','server':'server1','dc':'dc1'}),
(15, '2023-09-20 00:02:00', 'DEF', 150,{'env':'qa','app':'app1','server':'server2'}),
(16, '2023-09-20 00:02:00', 'DEF', 160,{'env':'qa','app':'app1','server':'server1','dc':'dc1'}),
(17, '2023-09-20 00:03:00', 'ABC', 170,{'env':'prod','app':'app1','server':'server1'}),
(18, '2023-09-20 00:03:00', 'ABC', 180,{'env':'prod','app':'app1','server':'server1'}),
(19, '2023-09-20 00:03:00', 'ABC', 190,{'env':'qa','app':'app1','server':'server1'}),
(20, '2023-09-20 00:03:00', 'ABC', 200,{'env':'qa','app':'app1','server':'server2'}),
(21, '2023-09-20 00:03:00', 'DEF', 210,{'env':'prod','app':'app1','server':'server1'}),
(22, '2023-09-20 00:03:00', 'DEF', 220,{'env':'prod','app':'app1','server':'server1'}),
(23, '2023-09-20 00:03:00', 'DEF', 230,{'env':'qa','app':'app1','server':'server1'}),
(24, '2023-09-20 00:03:00', 'DEF', 240,{'env':'qa','app':'app1','server':'server1'});
然后，我们可以创建一个带有 array join 的物化视图，以便它可以将 map 属性提取到键和值列上。为了演示，在下面的示例中，它使用了隐式表（带有 POPULATE 命令和类似 .inner.{uuid}... 的后备表）。但是，推荐的最佳实践是使用显式表，您应该首先定义表，然后在顶部使用 TO 命令创建物化视图。
CREATE MATERIALIZED VIEW db1.table1_metric_map_mv
ORDER BY id
POPULATE AS
select
*,
attributes.keys as attribute_keys,
attributes.values as attribute_values
from db1.table1_metric_map
array join attributes
where notEmpty(attributes.keys);
新表将具有更多行，并将提取键，如下所示
SELECT *
FROM db1.table1_metric_map_mv
LIMIT 5
Query id: b7384381-53af-4e3e-bc54-871f61c033a6
┌─id─┬───────────timestamp─┬─metric_name─┬─metric_value─┬─attributes───────────┬─attribute_keys─┬─attribute_values─┐
│ 1 │ 2023-09-20 00:01:00 │ ABC │ 10 │ ('env','prod') │ env │ prod │
│ 1 │ 2023-09-20 00:01:00 │ ABC │ 10 │ ('app','app1') │ app │ app1 │
│ 1 │ 2023-09-20 00:01:00 │ ABC │ 10 │ ('server','server1') │ server │ server1 │
│ 2 │ 2023-09-20 00:01:00 │ ABC │ 20 │ ('env','prod') │ env │ prod │
│ 2 │ 2023-09-20 00:01:00 │ ABC │ 20 │ ('app','app2') │ app │ app2 │
└────┴─────────────────────┴─────────────┴──────────────┴──────────────────────┴────────────────┴──────────────────┘
从这里，为了查询需要某些属性的行，您可以执行类似这样的操作
SELECT
t1_app.id AS id,
timestamp,
metric_name,
metric_value
FROM
(
SELECT *
FROM db1.table1_metric_map_mv
WHERE (attribute_keys = 'app') AND (attribute_values = 'app1') AND (metric_name = 'ABC')
) AS t1_app
INNER JOIN
(
SELECT *
FROM db1.table1_metric_map_mv
WHERE (attribute_keys = 'server') AND (attribute_values = 'server1')
) AS t2_server ON t1_app.id = t2_server.id
Query id: 72ce7f19-b02a-4b6e-81e7-a955f257436d
┌─id─┬───────────timestamp─┬─metric_name─┬─metric_value─┐
│ 1 │ 2023-09-20 00:01:00 │ ABC │ 10 │
│ 3 │ 2023-09-20 00:01:00 │ ABC │ 30 │
│ 9 │ 2023-09-20 00:02:00 │ ABC │ 90 │
│ 11 │ 2023-09-20 00:02:00 │ ABC │ 110 │
│ 12 │ 2023-09-20 00:02:00 │ ABC │ 120 │
│ 17 │ 2023-09-20 00:03:00 │ ABC │ 170 │
│ 18 │ 2023-09-20 00:03:00 │ ABC │ 180 │
│ 19 │ 2023-09-20 00:03:00 │ ABC │ 190 │
└────┴─────────────────────┴─────────────┴──────────────┘