使用场景
- 我们的平台需要采集和存储一些交通大数据,例如过车数据、违法数据、雷达/信号机/卡口实时数据等
- 之前有一些小项目,几十个路口,设备数量也不多,客户提供的服务器及配置也比较有限,我们使用单点clickhouse存储这些数据
- 现在要在郑州港区实施一个比较大的项目,有两百多个路口,还有大量的电警卡口设备,单点clickhouse已经不能满足我们的需求,考虑到一些数据更新和联表查询需求,我们更换为使用doris集群
- 之前在clickhouse里,根据数据需求,有的表字段有使用了数组类型array,转成doris后,同样需要使用array
- clickhouse的数组类型array的使用,已经写了几篇博客,有兴趣的可以看一下
- 这里再介绍一下doris的数组数据类型
数据类型
- 官方文档地址:主页 > SQL 手册 > SQL 手册 > 数据类型 > ARRAY
- ARRAY:由T类型元素组成的数组,不能作为key列使用。目前支持在Duplicate模型的表中使用
- 2.0 版本之后支持在Unique模型的表中非key列使用
- T支持的类型有:
BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE,
DATEV2, DATETIME, DATETIMEV2, CHAR, VARCHAR, STRING
- 常用的数据类型基本上都包含了,根据需要创建字段即可
数组函数
- 官方文档地址:主页 > SQL 手册 > SQL 函数 > 数组函数 > ARRAY
- 这里面有很多array相关的函数,可以根据需要使用,下面举例几个,剩下的大家可以去官方文档查看,按需要使用
- array():根据参数构造并返回array, 参数可以是多列或者常量
- array_max():返回数组中最大的元素,数组中的
NULL
值会被跳过。空数组以及元素全为NULL
值的数组,结果返回NULL
值。 - array_min():返回数组中最小的元素,数组中的
NULL
值会被跳过。空数组以及元素全为NULL
值的数组,结果返回NULL
值。 - array_avg():返回数组中所有元素的平均值,数组中的
NULL
值会被跳过。空数组以及元素全为NULL
值的数组,结果返回NULL
值。 - array_sum():返回数组中所有元素之和,数组中的
NULL
值会被跳过。空数组以及元素全为NULL
值的数组,结果返回NULL
值。 - array_size():返回数组中元素数量,如果输入数组为
NULL
,则返回NULL
- element_at(ARRAY arr, BIGINT position):返回数组中位置为
position
的元素。如果该位置上元素不存在,返回NULL
。position
从1
开始,并且支持负数。正数表示从前往后,负数表示从后往前,都从1
、-1
开始。但是,仅支持向量化引擎中使用
使用示例
- 安全指标表,记录整个路口的安全指标,和各个进口的部分指标
- 使用的数据模型为Duplicate模型,数据完全按照导入文件中的数据进行存储,不会有任何聚合。即使两行数据完全相同,也都会保留。没有聚合需求和唯一性需求的普通数据表,都可以使用Duplicate模型。
- 建表语句SQL如下:
DROP TABLE IF EXISTS signal.dwd_signal_securityindex_ri;
CREATE TABLE IF NOT EXISTS signal.dwd_signal_securityindex_ri
(
`time_stamp` DATETIME NOT NULL COMMENT '周期开始时间',
`region_id` INT NOT NULL COMMENT '路口所属区域编号',
`intersection_number` INT NOT NULL COMMENT "路口ID",
`analysis_period` TINYINT comment '分析时段,1-早高峰,2-白天平峰,3-晚高峰,4-夜间平峰',
`safety_factor` FLOAT DEFAULT "0" COMMENT '安全系数(根据下面4个安全评价参数加权计算,只计算整个路口的)',
`phase_clearance_rate` FLOAT DEFAULT "0" COMMENT '相位清空率(路口)',
`pedestrian_time_guarantee_rate` FLOAT DEFAULT "0" COMMENT '行人过街时间保障率(路口)',
`pedestrian_illegal_rate` FLOAT DEFAULT "0" COMMENT '行人闯红灯违法率(路口)',
`traffic_conflict` SMALLINT COMMENT '交通冲突(车道/方向)',
`approach_index` ARRAY<VARCHAR(32)> COMMENT "进口-行人过街时间保障率(进口)-行人闯红灯违法率(进口)-交通冲突次数(进口)"
)
DUPLICATE KEY(`time_stamp`, `region_id`, `intersection_number`,`analysis_period`)
COMMENT "路口安全指标表"
DISTRIBUTED BY HASH(`region_id`,`intersection_number`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
- 数据插入时,可以拼接为数组的字符串
-- 类似这样
insert into array_type_table values (0, []), (1, [NULL]), (2, [1, 2, 3]), (3, [1, NULL, 3]);
- Java代码,一样拼接即可
// 数据转换,存入数据库
SignalSecurityIndex securityIndex = new SignalSecurityIndex();
BeanUtils.copyProperties(securityVO, securityIndex);
securityIndex.setAnalysisPeriod(analysisPeriod.byteValue());
securityIndex.setTimeStamp(cycleStartTime);
securityIndex.setRegionId(regionId.intValue());
securityIndex.setTrafficConflict(securityVO.getTrafficConflict().shortValue());
// 不同进口存储
String north = IntersectionApproachEnum.NB.getValue() + "-" + rateNorth + "-" + northIllegalRate + "-" + countNorth;
String south = IntersectionApproachEnum.SB.getValue() + "-" + rateSouth + "-" + southPedestrianIllegalRate + "-" + countSouth;
String east = IntersectionApproachEnum.EB.getValue() + "-" + rateEast + "-" + eastPedestrianIllegalRate + "-" + countEast;
String west = IntersectionApproachEnum.WB.getValue() + "-" + rateWest + "-" + westPedestrianIllegalRate + "-" + countWest;
String approachIndex = "[" + north + "," + south + ","+ east + ","+ west + "]";
securityIndex.setApproachIndex(approachIndex);
securityIndexMapper.insertSelective(securityIndex);
- 查询时,正常查询即可,会返回数组
- 对于clickhouse里的array类型字段,我们使用了
arrayJoin
实现列转行,doris并没有这个函数,需要使用其他方法实现,详情参考我的这篇博客:Doris:使用表函数explode实现array字段列转行