问题
如果我在使用映射类型的列中具有不同的属性，如何提取它们并在查询中使用它们？
答案
这是一个从可变属性字段提取键和值的简单示例。此方法将从源/原始表中的每一行创建看似重复的行。但是，由于提取了键和值，因此可以将它们放入主键或带有索引的辅助键中，例如布隆过滤器。
在这个示例中，我们基本上有一个创建指标表的源，它有多个属性可以应用于具有映射的属性字段。如果某些属性始终存在于记录中，最好将它们提取到自己的列中并填充。
您应该能够直接复制粘贴以查看输出结果以及此实例中物化视图的作用。
创建一个示例数据库
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'});
然后，我们可以使用数组连接创建一个物化视图，以便它可以将映射属性提取到键和值列中。为了演示，在下面的示例中，它使用隐式表（使用 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 │
└────┴─────────────────────┴─────────────┴──────────────┘