数据库OLAP大数据后端【免费下载链接】druidApache Druid: a high performance real-time analytics database.项目地址https://gitcode.com/gh_mirrors/druid6/druid点击查看免费下载本文是 Apache Druid 数组展开的完整实战教程围绕 Druid 的unnest数据源展开你将学会如何把存储在数组列如dim3取值形如[a,b]中的数据拆解为独立的单值行并分别通过Druid SQL 的UNNEST子句与原生native查询的unnest数据源两种方式对展开后的数据进行扫描、过滤、分组、聚合、topN 与 JOIN 等操作。阅读完成后你可以在自己的 Druid 集群上从零加载含嵌套数组的数据并熟练编写可复用的展开查询。:::info 前置说明 本文聚焦于数组列的展开。如果你需要展开COMPLEXjson类型列中的嵌套数据请参阅 嵌套列文档。 :::展开数据时请牢记两点展开操作会大幅增加总行数一个数组列中的每个元素都会生成一行新记录数组中的数组无法展开即[[a,b],[c]]这类嵌套数组不会被递归展开内层数组会被当作一个整体元素输出详见后文 datasource 文档 中的示例表。你可以通过Druid 控制台web console或API执行展开操作。建议初学者优先使用 Druid 控制台便于直观对比展开前后的数据形态。前置条件你只需要一个可运行的 Druid 集群即可例如官方 快速入门集群。集群中不需要任何已存在的数据源datasource本教程会随步骤加载一个基础数据源。第一步加载含嵌套数组的数据本教程要加载的数据仅包含少量行形如t:2000-01-01, m1:1.0, m2:1.0, dim1:, dim2:[a], dim3:[a,b], dim4:[x,y], dim5:[a,b]其中dim2、dim3、dim4、dim5都是多值字符串维度multi-value dimensionMVD本教程的核心关注点是dim3中的嵌套数组值。你可以通过两种方式加载数据二者最终都会将数据写入名为nested_data的表方式一SQL 批量摄入MSQ在 Druid 控制台的 Query 视图中执行以下 SQL使用REPLACE INTO ... OVERWRITE ALL与EXTERN内联数据源按年PARTITIONED BY YEAR分区REPLACE INTO nested_data OVERWRITE ALL SELECT TIME_PARSE(t) as __time, dim1, dim2, dim3, dim4, dim5, m1, m2 FROM TABLE( EXTERN( {type:inline,data:{\t\:\2000-01-01\,\m1\:\1.0\,\m2\:\1.0\,\dim1\:\\,\dim2\:[\a\],\dim3\:[\a\,\b\],\dim4\:[\x\,\y\],\dim5\:[\a\,\b\]},\n{\t\:\2000-01-02\,\m1\:\2.0\,\m2\:\2.0\,\dim1\:\10.1\,\dim2\:[],\dim3\:[\c\,\d\],\dim4\:[\e\,\f\],\dim5\:[\a\,\b\,\c\,\d\]},\n{\t\:\2001-01-03\,\m1\:\6.0\,\m2\:\6.0\,\dim1\:\abc\,\dim2\:[\a\],\dim3\:[\k\,\l\]},\n{\t\:\2001-01-01\,\m1\:\4.0\,\m2\:\4.0\,\dim1\:\1\,\dim2\:[\a\],\dim3\:[\g\,\h\]},\n{\t\:\2001-01-02\,\m1\:\5.0\,\m2\:\5.0\,\dim1\:\def\,\dim2\:[\abc\],\dim3\:[\i\,\j\]},\n{\t\:\2001-01-03\,\m1\:\6.0\,\m2\:\6.0\,\dim1\:\abc\,\dim2\:[\a\],\dim3\:[\k\,\l\]},\n{\t\:\2001-01-02\,\m1\:\5.0\,\m2\:\5.0\,\dim1\:\def\,\dim2\:[\abc\],\dim3\:[\m\,\n\]}}, {type:json}, [{name:t,type:string},{name:dim1,type:string},{name:dim2,type:string},{name:dim3,type:string},{name:dim4,type:string},{name:dim5,type:string},{name:m1,type:float},{name:m2,type:double}] ) ) PARTITIONED BY YEAR内联数据共 7 行时间戳分布在 2000-01-01 至 2001-01-03 之间dim3的取值包括[a,b]、[c,d]、[k,l]、[g,h]、[i,j]、[m,n]其中[k,l]出现两次。注意到dim2还有一行空数组[]这在后续查询中不会产生任何展开输出。方式二JSON 批量摄入 spec如果你更习惯通过tasksAPI 或控制台 Load data 向导提交原生摄入任务也可以使用下面的index_parallel任务 spec同样的内联数据segmentGranularity为YEARrollup关闭{ type: index_parallel, spec: { ioConfig: { type: index_parallel, inputSource: { type: inline, data:{\t\:\2000-01-01\,\m1\:\1.0\,\m2\:\1.0\,\dim1\:\\,\dim2\:[\a\],\dim3\:[\a\,\b\],\dim4\:[\x\,\y\],\dim5\:[\a\,\b\]},\n{\t\:\2000-01-02\,\m1\:\2.0\,\m2\:\2.0\,\dim1\:\10.1\,\dim2\:[],\dim3\:[\c\,\d\],\dim4\:[\e\,\f\],\dim5\:[\a\,\b\,\c\,\d\]},\n{\t\:\2001-01-03\,\m1\:\6.0\,\m2\:\6.0\,\dim1\:\abc\,\dim2\:[\a\],\dim3\:[\k\,\l\]},\n{\t\:\2001-01-01\,\m1\:\4.0\,\m2\:\4.0\,\dim1\:\1\,\dim2\:[\a\],\dim3\:[\g\,\h\]},\n{\t\:\2001-01-02\,\m1\:\5.0\,\m2\:\5.0\,\dim1\:\def\,\dim2\:[\abc\],\dim3\:[\i\,\j\]},\n{\t\:\2001-01-03\,\m1\:\6.0\,\m2\:\6.0\,\dim1\:\abc\,\dim2\:[\a\],\dim3\:[\k\,\l\]},\n{\t\:\2001-01-02\,\m1\:\5.0\,\m2\:\5.0\,\dim1\:\def\,\dim2\:[\abc\],\dim3\:[\m\,\n\]} }, inputFormat: { type: json } }, tuningConfig: { type: index_parallel, partitionsSpec: { type: dynamic } }, dataSchema: { dataSource: nested_data, granularitySpec: { type: uniform, queryGranularity: NONE, rollup: false, segmentGranularity: YEAR }, timestampSpec: { column: t, format: auto }, dimensionsSpec: { dimensions: [ dim1, dim2, dim3, dim4, dim5 ] }, metricsSpec: [ { name: m1, type: floatSum, fieldName: m1 }, { name: m2, type: doubleSum, fieldName: m2 } ] } } }第二步查看原始数据数据加载完成后先运行一条最简单的查询确认数据形态SELECT * FROM nested_data在结果中你会看到dim3列的值是嵌套形式例如[a,b]。这正说明它是一个多值字符串维度。接下来的示例会展开dim3并对展开后的记录执行各类查询。根据你的查询方式分别选择 SQL 展开 或 原生查询展开 部分继续。展开操作的底层原理unnest 数据源与存储适配器在深入示例之前先了解一下展开操作在 Druid 内部的实现位置这有助于你理解行数膨胀、过滤下推等行为背后的原因。在 Druid 中UNNEST的 SQL 语义最终都会翻译为原生查询里的unnest数据源。其核心实现类是UnnestDataSource它由三个字段构成base被展开的底层数据源可以是表、查询、JOIN 结果甚至另一个unnest数据源virtualColumn一个虚拟列其输出名就是展开后新列的名字其表达式引用被展开的源列unnestFilter可空仅作用于展开后输出列的过滤条件。从源码可见UnnestDataSource.isCacheable()返回false即展开结果目前不会被查询缓存参见 UnnestDataSource.java。当查询真正执行时UnnestDataSource.createSegmentMapFunction()会为每个 segment 包装一个UnnestStorageAdapter。该类在创建游标Cursor时会根据源列是否字典编码分别创建UnnestDimensionCursor或UnnestColumnValueSelectorCursor来遍历展开后的行并在此过程中完成两类过滤的拆分作用于原始行的基础过滤base filter与作用于展开后输出列的过滤post-unnest filter。行数膨胀的量化结果可以在单元测试中看到在UnnestStorageAdapterTest的test_unnest_adapters_basic()中2 行各含 8 个元素的输入展开后得到 16 行Assert.assertEquals(count, 16)test_two_levels_of_unnest_adapters()则验证了两层unnest叠加的场景。这与本文示例中nested_data7 行原始数据展开后会显著增加行数的现象一致。使用 SQL 查询展开UNNESTDruid SQL 中UNNEST的通用语法如下完整定义见 sql.md 的 UNNEST 章节SELECT column_alias_name FROM datasource CROSS JOIN UNNEST(source_expression) AS table_alias_name(column_alias_name)要点说明datasource可以是任意 Druid 数据源一张表FROM a_table也可以是表的子集——基于子查询、过滤或 JOIN 得到的结果例如FROM (SELECT columnA,columnB,columnC FROM a_table)source_expression必须是数组类型可以来自任何表达式Druid 的 ARRAY 类型列可直接展开如果源列是多值 VARCHAR字符串维度必须先用MV_TO_ARRAY(dimension)把它转换成 ARRAY 类型详见 多值字符串函数。此外也可以对任意结果为 SQL 数组类型的表达式调用 UNNEST例如ARRAY[dim1,dim2]用两个维度拼成数组或ARRAY_CONCAT(dim1,dim2)拼接两个多值维度AS table_alias_name(column_alias_name)虽非强制但强烈建议它决定了展开结果输出到哪一列可以是已存在的列也可以是新列。如果不写Druid 会使用类似EXPR$0的不具描述性的名字CROSS JOIN在大多数情况下是必需的但展开内联数组数组本身就是数据源时不需要 CROSS JOIN查看 SQL UNNEST 的原生解释native explain时你会发现 Druid 内部使用名为j0.unnest的虚拟列执行展开每多一层 UNNEST 就多一个下划线前缀如_j0.unnest、__j0.unnestUNNEST保持源数组的元素顺序。展开单个源表达式下面的查询从nested_data返回一列d3其内容是源列dim3展开后的单值SELECT d3 FROM nested_data CROSS JOIN UNNEST(MV_TO_ARRAY(dim3)) AS example_table(d3)注意这里使用了MV_TO_ARRAY辅助函数由于dim3是多值字符串维度必须先转换成数组才能展开。如果你要展开的列本身就是 ARRAY 类型非字符串维度则不需要MV_TO_ARRAY。展开虚拟列多列合并UNNEST 支持把多个源列作为一个“虚拟列”来展开。下面这条查询返回两个源列dim4、dim5外加一个包含展开数据的虚拟列d45SELECT dim4,dim5,d45 FROM nested_data CROSS JOIN UNNEST(ARRAY[dim4,dim5]) AS example_table(d45)虚拟列d45是dim4与dim5两个源列的元素乘积笛卡尔积式展开。注意此时总行数已经明显增长——nested_data原始只有 7 行展开后行数会随每行的数组长度成倍增加。另一种构造虚拟列的方式是使用ARRAY_CONCAT把两个多值维度拼接成一个数组再展开SELECT dim4,dim5,d45 FROM nested_data CROSS JOIN UNNEST(ARRAY_CONCAT(dim4,dim5)) AS example_table(d45)ARRAY[...]两两组合与ARRAY_CONCAT(...)顺序拼接语义不同请根据实际业务目标选择。展开多个源表达式一条查询中可以包含多个UNNEST子句每个子句形如UNNEST(source_expression) AS table_alias_name(column_alias_name)每个 UNNEST 子句的table_alias_name和column_alias_name必须唯一。下面的示例同时完成两件事把dim3展开为d3把dim4与dim5组成的虚拟列展开为d45SELECT dim3,dim4,dim5,d3,d45 FROM nested_data CROSS JOIN UNNEST(MV_TO_ARRAY(dim3)) AS foo1(d3) CROSS JOIN UNNEST(ARRAY[dim4,dim5]) AS foo2(d45)展开表子集中的列UNNEST 的数据源可以是表的一个子集。下面这条查询先只取dim1、dim2、dim3三列作为数据源再展开其中的dim3为d3并返回SELECT d3 FROM (SELECT dim1, dim2, dim3 FROM nested_data) CROSS JOIN UNNEST(MV_TO_ARRAY(dim3)) AS example_table(d3)带过滤条件的展开有两种过滤方式先过滤再展开或展开后再过滤。先过滤再展开下面的查询先基于dim2过滤出匹配的行再展开这些行的dim3为d3SELECT d3 FROM (SELECT * FROM nested_data WHERE dim2 IN (abc)) CROSS JOIN UNNEST(MV_TO_ARRAY(dim3)) AS example_table(d3)展开后再过滤你也可以直接对 UNNEST 的输出做过滤。例如展开内联数组[1,2,3]但只保留1和2SELECT * FROM UNNEST(ARRAY[1,2,3]) AS example_table(d1) WHERE d1 IN (1,2)两者结合你可以写出同时约束“展开后值”与“原始行值”的查询。下面的查询要求dim3展开后的值d3匹配IN (b, d)且原始行中m1的值小于 2。SELECT * FROM nested_data CROSS JOIN UNNEST(MV_TO_ARRAY(dim3)) AS foo(d3) WHERE d3 IN (b, d) and m1 2由于原始数据中只有一个m11.0的行且其dim3[a,b]展开后有b命中该查询只返回一行。修改过滤条件可以直观地看到结果变化。从底层实现看这类过滤会由UnnestStorageAdapter拆分为 base filter 与 post-unnest filter 两部分尽可能把条件下推到原始行上以减少中间行数相关逻辑见 UnnestStorageAdapter.java以及测试 UnnestStorageAdapterTest.java 中大量test_pushdown_or_filters...、test_nested_filters...系列用例。展开后 GROUP BY 聚合展开之后可以继续分组。下面的查询把dim3展开为d3再按d3分组去重SELECT d3 FROM nested_data CROSS JOIN UNNEST(MV_TO_ARRAY(dim3)) AS example_table(d3) GROUP BY d3还可以继续追加变换例如ORDER BY d3 DESC或LIMIT子句来排序或限制结果数量。使用原生 unnest 数据源查询原生查询同样可以使用unnest数据源。以下示例都基于前面创建的nested_data表。unnest数据源在原生 JSON 中的语法结构如下完整说明见 datasource.md 的 unnest 章节dataSource: { type: unnest, base: { type: table, name: nested_data }, virtualColumn: { type: expression, name: output_column, expression: \column_reference\ }, unnestFilter: optional_filter }各字段含义dataSource.type固定为unnestdataSource.base定义要展开的数据源dataSource.base.type被展开数据源的类型例如tabledataSource.virtualColumn虚拟列引用要被展开的嵌套值。该虚拟列的输出名会复用为展开后结果列的名字你可以指定源列的名字来“就地替换”源列也可以指定一个新名字输出为新列输出为新列便于核对展开结果是否符合预期但并非必需unnestFilter仅作用于输出列展开后列的过滤器没有过滤需求时可省略或置为null。一个unnest数据源可以展开多列但务必谨慎这会带来非常庞大的新行数。Scan 查询展开下面的原生 Scan 查询通过unnest数据源展开dim3列查看查询{ queryType: scan, dataSource: { type: unnest, base: { type: table, name: nested_data }, virtualColumn: { type: expression, name: unnest-dim3, expression: \dim3\ } }, intervals: { type: intervals, intervals: [ -146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z ] }, limit: 100, columns: [ __time, dim1, dim2, dim3, m1, m2, unnest-dim3 ], granularity: { type: all }, context: { debug: true, useCache: false } }对比结果可以发现行数比展开前更多了并且多出一列unnest-dim3。它的取值与dim3相同只是不再嵌套——数组中的每个元素都成为一条独立记录。你还可以为 Scan 查询添加过滤器。例如把下面的filter加入查询仅保留dim2中值为a或abc的行filter: { type: in, dimension: dim2, values: [ a, abc, ] },groupBy 查询下面的 groupBy 查询把dim3展开为unnest-dim3列并按该列分组、降序返回查看查询{ queryType: groupBy, dataSource: { type: unnest, base: nested_data, virtualColumn: { type: expression, name: unnest-dim3, expression: \dim3\ } }, intervals: [-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z], granularity: all, dimensions: [ unnest-dim3 ], limitSpec: { type: default, columns: [ { dimension: unnest-dim3, direction: descending } ], limit: 1001 }, context: { debug: true } }topN 查询下面的 topN 查询把dim3展开为unnest-dim3并以展开后的列作为 topN 的维度。结果输出到名为topN-unnest-d3的列中按a0m1的最小值升序排列查看查询{ queryType: topN, dataSource: { type: unnest, base: { type: table, name: nested_data }, virtualColumn: { type: expression, name: unnest-dim3, expression: \dim3\ }, }, dimension: { type: default, dimension: unnest-dim3, outputName: topN-unnest-d3, outputType: STRING }, metric: { type: inverted, metric: { type: numeric, metric: a0 } }, threshold: 3, intervals: { type: intervals, intervals: [ -146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z ] }, granularity: { type: all }, aggregations: [ { type: floatMin, name: a0, fieldName: m1 } ], context: { debug: true } }在 JOIN 结果上展开unnest的base可以是 JOIN 的结果。下面的 Scan 查询把nested_data与自己 JOIN以m1 j0.v0为连接条件j0.v0是右侧子查询中m2的浮点虚拟列然后对 JOIN 结果展开dim3输出到新列unnest-dim3查看查询{ queryType: scan, dataSource: { type: unnest, base: { type: join, left: { type: table, name: nested_data }, right: { type: query, query: { queryType: scan, dataSource: { type: table, name: nested_data }, intervals: { type: intervals, intervals: [ -146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z ] }, virtualColumns: [ { type: expression, name: v0, expression: \m2\, outputType: FLOAT } ], resultFormat: compactedList, columns: [ __time, dim1, dim2, dim3, m1, m2, v0 ], context: { sqlOuterLimit: 1001, useNativeQueryExplain: true }, granularity: { type: all } } }, rightPrefix: j0., condition: (\m1\ \j0.v0\), joinType: INNER }, virtualColumn: { type: expression, name: unnest-dim3, expression: \dim3\ } }, intervals: { type: intervals, intervals: [ -146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z ] }, resultFormat: compactedList, limit: 1001, columns: [ __time, dim1, dim2, dim3, j0.__time, j0.dim1, j0.dim2, j0.dim3, j0.m1, j0.m2, m1, m2, unnest-dim3 ], context: { sqlOuterLimit: 1001, useNativeQueryExplain: true }, granularity: { type: all } }展开虚拟列原生方式unnest数据源同样支持展开虚拟列——即可以同时从多个源列取数构造的可查询复合列。下面的查询返回dim45和m1两列其中dim45是对由dim4、dim5构成的数组虚拟列展开后的结果使用array_concat函数并把outputType声明为ARRAYSTRING查看查询{ queryType: scan, dataSource:{ type: unnest, base: { type: table, name: nested_data }, virtualColumn: { type: expression, name: dim45, expression: array_concat(\dim4\,\dim5\), outputType: ARRAYSTRING }, } intervals: { type: intervals, intervals: [ -146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z ] }, resultFormat: compactedList, limit: 1001, columns: [ dim45, m1 ], granularity: { type: all }, context: { debug: true, useCache: false } }同时展开一个列和一个虚拟列由于unnest的base本身也可以是另一个unnest数据源因此可以串联实现“多列 多虚拟列”的展开。下面的 Scan 查询第一层把dim3展开为d3outputType: STRING第二层把dim4、dim5构成的数组虚拟列展开为d45outputType: ARRAYSTRING使用array(...)表达式最后返回源列与展开列查看查询{ queryType: scan, dataSource: { type: unnest, base: { type: unnest, base: { type: table, name: nested_data }, virtualColumn: { type: expression, name: d3, expression: \dim3\, outputType: STRING }, }, virtualColumn: { type: expression, name: d45, expression: array(\dim4\,\dim5\), outputType: ARRAYSTRING }, }, intervals: { type: intervals, intervals: [ -146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z ] }, resultFormat: compactedList, limit: 1001, columns: [ dim3, d3, dim4, dim5, d45 ], context: { queryId: 2618b9ce-6c0d-414e-b88d-16fb59b9c481, sqlOuterLimit: 1001, sqlQueryId: 2618b9ce-6c0d-414e-b88d-16fb59b9c481, useNativeQueryExplain: true }, granularity: { type: all } }注意每多一层unnest展开后的行数都会继续成倍放大请务必评估数据规模后再串联使用。UNNEST 的已知限制根据 sql.md 的 UNNEST 章节使用 UNNEST 时需要注意以下限制不去重、不剔除空值UNNEST 不会移除数组中的重复元素或 null。null 会被当作普通值处理如果数组中有多个 null则会为每个 null 分别生成一条记录不支持复杂 JSON 内的对象数组COMPLEXjson类型中嵌套的对象数组不在 UNNEST 支持范围内如需处理请参考 嵌套列文档数组中的数组无法展开内层数组会被当作单个元素原样输出见 datasource.md 中[e, [f,g]]展开为[f, g]一行的示例行数爆炸风险无论是 SQL 还是原生方式展开都会让总行数随数组长度增长多列/多层展开尤其明显unnest数据源目前也不参与查询结果缓存。延伸阅读Druid SQL 中的 UNNEST 子句完整语法与限制原生查询中unnest数据源的定义与字段说明多值字符串维度与MV_TO_ARRAY等转换函数、多值字符串函数参考数组类型与数组函数ARRAY、ARRAY_CONCAT等、SQL 数组函数虚拟列virtual column概念底层实现数据源定义 UnnestDataSource.java、展开执行器 UnnestStorageAdapter.java 及其行为验证测试 UnnestStorageAdapterTest.java赞分享数据库OLAP大数据后端【免费下载链接】druidApache Druid: a high performance real-time analytics database.项目地址https://gitcode.com/gh_mirrors/druid6/druid点击查看免费下载相关推荐StarRocks UNNEST 表函数完全指南数组/字符串/位图展开为多行的原理与实战StarRocks UNNEST 表函数完全指南数组/字符串/位图展开为多行的原理与实战 UNNEST 是 StarRocks 内置的表函数Table Fu数据库OLAP数据仓库大数据湖仓一体数据分析Daft 中 JSON 与嵌套数据处理完全指南jq 过滤、字段提取与 unnest 展开Daft 中 JSON 与嵌套数据处理完全指南jq 过滤、字段提取与 unnest 展开 Daft 为 JSON 字符串与嵌套Struct/List数据类大数据数据分析数据工程AI 应用Apache Druid Datasource 完全指南从 table 到 join/unnest 的八种查询数据源详解Apache Druid Datasource 完全指南从 table 到 join/unnest 的八种查询数据源详解 导读 在 Apache Druid数据库OLAP大数据后端创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考