应用场景

处理数据时，一列数据通常为字符串或数字等primitive类型的数据。在复杂的业务场景下，日志数据的某一列可能会是较为复杂的格式，例如数组（array）、对象（map）、JSON等格式。对这种特殊格式的日志字段进行查询分析，可以使用unnest语法。

例如以下日志：

__source__:  1.1.1.1
__tag__:__hostname__:  vm-req-170103232316569850-tianchi111932.tc
__topic__:  TestTopic_4
array_column:  [1,2,3]
double_column:  1.23
map_column:  {"a":1,"b":2}
text_column:  商品

其中array_column字段为数组类型。如果统计array_column中所有数值的汇总值，需要遍历每一行的数组中的每一个元素。

unnest语法结构

语法 说明
unnest( array) as table_alias(column_name) 表示把array类型展开成多行，行的名称为column_name。
unnest(map) as table(key_name, value_name) 表示把map类型展开成多行，key的名称为key_name，value的名称为value_name。
说明 注意，由于unnest接收的是array或者map类型的数据，如果您的输入为字符串类型，那么要先转化成JSON类型，然后再转化成array类型或map类型，转化的方式为cast(json_parse(array_column) as array(bigint))。

遍历数组每一个元素

使用SQL把array展开成多行：

* | select array_column, a from log, unnest(cast(json_parse(array_column) as array(bigint))) as t(a)

上述SQL把数组展开成多行数字，unnest( cast( json_parse(array_column) as array(bigint) ) ) as t(a)，unnest语法把数组展开，以t来命名新生成的表，使用a来引用展开后的列。

结果如下图：
图 1. 展开数组
展开数组
  • 统计数组中的每个元素的和： 
    * | select sum(a) from log, unnest(cast(json_parse(array_column) as array(bigint))) as t(a)
    图 2. 对数组进行sum计算
    sum计算
  • 按照数组中的每个元素进行group by计算： 
    * | select a, count(1) from log, unnest( cast( json_parse(array_column) as array(bigint))) as t(a) group by a
    图 3. 对数组进行group by计算
    group by计算

遍历Map

  • 遍历Map中的元素： 
    * | select map_column , a,b from log, unnest(cast(json_parse(map_column) as map(varchar, bigint))) as t(a,b)
    图 4. 遍历Map
    map遍历
  • 按照Map的key进行group by统计： 
    * | select key, sum(value) from log, unnest(cast(json_parse(map_column) as map(varchar, bigint))) as t(key,value) GROUP BY key
    图 5. 对Key进行group by统计
    key group by

格式化显示histogram,numeric_histogram的结果

  • histogram

    histogram函数类似于count group by 语法。语法请参考Map映射函数。

    通常情况下histogram的结果为一串JSON数据，无法配置视图展示，例如： 
    * | select histogram(method)
    图 6. 普通histogram结果
    histogram
    您可以通过unnest语法，把JSON展开成多行配置视图，例如： 
    * | select key , value from(select histogram(method) as his from log) , unnest(his ) as t(key,value)
    图 7. 展开JSON
    展开JSON
    接下来，可以配置可视化视图：
    图 8. 可视化视图1可视化1
  • numeric_histogram
    numeric_histogram语法是为了把数值列分配到多个桶中去，相当于对数值列进行group by，具体语法请参考估算函数。 
    * | select numeric_histogram(10,Latency)
    numeric_histogram的输出如下：
    图 9. numeric_histogram查询结果
    numeric_histogram
    您可以通过以下查询语句格式化展示该结果： 
    * |  select key,value from(select numeric_histogram(10,Latency) as his from log) , unnest(his) as t(key,value)
    结果如下：
    图 10. 查询结果
    查询结果
    同时配置柱状图的形式展示：
    图 11. 可视化视图2
    可视化2