ClickHouse嵌套类型使用案例：使用Array和Tuple（或Nested）表示交通干线指标场景

坚持是一种态度

已于 2023-05-11 23:40:53 修改

阅读量578

点赞数

分类专栏： ClickHouse 大数据开发文章标签： clickhouse 大数据交通评价嵌套类型Array Tuple与Nested

于 2023-05-11 23:37:48 首次发布

本文链接：https://blog.csdn.net/u010882234/article/details/130632497

版权

ClickHouse 同时被 2 个专栏收录

24 篇文章 1 订阅

订阅专栏

大数据开发

24 篇文章 2 订阅

订阅专栏

文章目录

业务场景

我们目前在做信控平台，其中有一部分业务功能，需要基于一些计算好的交通指标
以我们现在做的交通干线评价为例，在一条交通主干道上，对某一段路段做交通评价。这段干线经过某创新产业园区，包含七八个路口，为交通流量较大的干线
把这条干线作为1个评价主体，假设它有相连的8个路口组成，8个路口间又形成了7个小路段
在做干线评价时，要计算这条干线的数据指标，例如平均速度、旅行时间、交通流量、拥堵时间等
数据来源有路口的雷达、卡口相机等，可以给出经过各个路口的车辆状态，从路口推断路段信息
对于拥堵指数、拥堵里程等，无法通过已有数据计算的部分，借助互联网数据接口获取
对于拥有8个路口的该干线，没法一下子计算出它的交通指标，要基于8个路口和7个路段去计算
展示干线指标时，也要展示各个路段的指标，使用列表表格和折线图做展示，用以查看具体在哪个路段发生拥堵，产生了交通瓶颈
从交通干线数据，得到路口和路段，数据展示时也要按照道路上实际的路口顺序在图表上展示
假设只做15分钟一次的统计，该如何设置数据结构呢

解决思路

这1条干线，由7个路段组成，先计算路段，再计算干线。15分钟统计一次，可以使用定时任务。
雷达、卡口数据，实时存入ClickHouse数据库，会有实时过车轨迹数据，包含车牌、速度、车型等
雷达也会周期性计算上报一些路口指标，如路口的流量、停车次数、平均延误、旅行时间等
有车牌号，就可以根据路口过车数据，去匹配车牌，推算一些路段指标，如旅行时间、平均速度
在设计交通干线实体类时，其中一个字段表示该路段的路口ids，按照道路实际顺序存储。展示需求要求，接口返回时，必须是和存储路口id顺序一致
有了路口指标数据、路段指标数据、互联网接口数据，对7个路段进行加权计算，得到该条干线的交通指标
考虑上以上需求和分析，决定使用嵌套数据结构，而ClickHouse的嵌套类型完美符合要求，既能嵌套，又保证有序，列表查询返回也很方便

数据结构

最终数据结构如下，时间戳、干线id，加上干线指标，再加一个road_section_index数组
road_section_index数组存储该干线的所有路段的指标
数组内是一个Tuple(使用Nested也是一样的)，包括路段id和路段指标
建表SQL（部分）：

-- 干线指标表，包括干线指标和干线里各路段指标
-- radar.traffic_artery_index definition
DROP table if exists radar.traffic_artery_index;
CREATE TABLE radar.traffic_artery_index
(

    `time_stamp` DateTime COMMENT '时间戳',
    
    `traffic_artery_id` Int32 COMMENT '干线id',

    `traffic_flow` Int32 COMMENT '交通流量',

    `avg_speed` Float64 COMMENT '平均速度:单位：Km/h',

    `travel_time` Float64 COMMENT '行程时间:单位：s',

    `road_section_index` Array(
                     Tuple(
                         `road_section_id` Int32,
                         
                         `traffic_flow` Int32,
                         
                         `avg_speed` Float64,
                         
                         `travel_time` Float64
                         )
                     )
)
    ENGINE = MergeTree
        PARTITION BY toYYYYMM(time_stamp)
        PRIMARY KEY time_stamp
        ORDER BY time_stamp
        SETTINGS max_suspicious_broken_parts = 1000;