数仓-StarRocks

[guanhui07]

数据分析 ,大数据统计,BI

大数据分析,很多公司会有BI (Business Intelligence)部门
数据清洗转换 ,数仓选型:
ElasticSearch 、Hapdoop 、Spark 、Clickhouse ,doris,Starrocks
数据开发

OLTP 和 OLAP

OLTP 和 OLAP 本身就是两个业务形态,数据分析就是费劲不讨好的业务

核心点 ：

• 数据清洗
• 大宽表，数据量大
• 数据准确性
• 复杂分组聚合
• 多表join
• 复杂嵌套子查询
• 复杂case when 条件统计
• 数据明细查阅，导出等

选型: StarRocks 与其他

数据分析 用starrocks ，性能比clickhouse 还好 而且还更兼容mysql 协议

查询性能优秀：查询延迟在亚秒级别，Join 性能优秀，能够满足 实时大数据分析的需求

使用方便：支持数据导入、导出等功能

数据模型丰富：支持明细模型、聚合模型、更新模型、主键模型，其中主键模型能够很好地满足 大数据的场景

运维成本低：支持高可用、在线扩缩容、数据分片自动均衡

分布式 PolarDB 和TiDb 不完全是 OLAP（联机分析处理）架构 而是 OLTP 架构

和朋友们的一些交流

朋友:大多数公司 mysql直接撸,前提数据量少
java系 可以hadoop hive离线计算
找大数据开发做 这是另外一套逻辑, binlog 同步数据给他们

朋友:看你们公司的规模和技术水平，不同的公司有不同的方案，Hadoop 技术栈并不适合小公司。
以下我列举从简单到复杂的各种方案供你参考,mysql  mongodb  Clickhouse  doris Starrocks  Spark  Hapdoop等
但是强烈建议一定要做好数据清洗 这一最关键.

我:专业的分析的数据库 专业的人做专业的事, 数据统计还是不推荐用Mysql 又不需要使用Innodb事务引擎

数据统计 看给谁看的:
给 骑手，给商户，还是给 公司内部，这个涉及数据的查询频率,需要实时高并发
数据在哪里，数据查询复杂程度怎么等等


朋友:假设你现在的原始数据在 mysql ， 一步到位的方式就是 把数据同步到 doris 等 这类数据库，
这种复杂查询很快的，就是 doris 集群的成本需要考虑


朋友:
假设你 2 张表都在同一个数据库，那么可以使用 MySQL 函数 UNION ALL 进行聚合然后分页。
如果你 2 张表在不同的数据库，那么现在根据条件查出 A 库 A 表放入一个指定集合，
再把 B 库 B 表根据条件查询出来放入指定集合。A 表和 B 表的集合必须是同一个泛型，
然后再根据这个汇总的集合进行内存分页即可


朋友: es 可以的,
实际上百万级我觉得 sql 数据库也完全能抗住... 毕竟数据量少
搞聚合查询不算是 es 专长，可以考虑 clickhouse 或者 greenplum，甚至 flink

我: es我们也没用,那千万 以上呢,前期量少,后期换架构 就坑了


年报统计 5 分钟刷一次？定这技术需求的人是不是从来没有做过 OLAP ？
月报年报根本不可能是实时报表，都是定期任务，明细数据输入截止后执行一晚上，
第二天就能看所有报表了，即使单表上千万也足够。
上亿数据就得用大数据计算框架，Spark/Flink 等都可以，但这得准备很多硬件资源


Spark/Flink 都可以做实时流式计算，需要配合 Kafka，场景是类似用户行为分析（商用），
交通流量分析（公共服务）这样，
短时间窗口内快速计算较多数据（例如 10 秒 1000 下单的分析），
几秒出结果。报表属于离线计算，针对每次几千万数亿或更多的数据量，
可能要 10 几分钟甚至几小时才出结果。建议厘清实际技术需求做些调研。

这种就只有用空间换时间，每天、每小时跑批， 根据所有需要使用的的维度（过滤条件）分组将相应指标定时写入报表即可。如果数据量大， 可以再做索引、分区存储、分表、缓存等优化。

clickhouse 和 Starrocks 是最佳选择，多条件，而且支持亿级查询，而且轻量运维小，统计上比 mysql 强多了，你可以 写个 定时任务把所有表拍平扔 ck ，如果担心用不好，就做好回退，当clickhouse 不可用的时候用 mysql 强查就是了

之前做过类似的需求，用的 apache druid ，也可以

单独建立一个计算表，精简一下字段，之类去掉，现在主要瓶颈应该是 IO ，如果精简一下+SSD ，感觉速度可以提升 30 倍  然后按最主要的条件分表，研究优化吧  (提高机器性能)

要不试试阿里云的 ADB 直接用 dts 同步源库 mysql 到 ADB 也是兼容的 mysql 写法 你的程序也不用改
ADB 号称比 mysql 快 10 倍 ADB 每个字段都自带索引的


还是不建议 ,建议用专业的数据库 专业的引擎 干专业的事,弄好宽表 ,统计字段指标 判断 核心要点 
前期量少,后期呢,后期公司可是要干上市的 做好千亿 万亿行的准备,况且现在成本这么低就能上好的架构.

实时链路

通过 写入到 队列 中，然后消费处理写入到 StarRocks 中进行实时表的查询。

脚本加定时任务

通过定时任务 脚本获取比如最近2个钟的 进行数据清洗刷进StarRocks
这个粒度 可以大 可以小 可控,比如凌晨0点统一执行脚本同步昨天一天的数据
每五分钟同步 最近10分钟的数据
每分钟同步 最近2分钟的数据

清洗数据做大宽表

宽表是把多个维度的数据，如用户基本信息、订单信息、产品信息等，
都整合在一张表中，使一行数据包含了丰富的信息，因此这张表的字段数量通常较多，
故而被称为宽表 。

宽表的特点

 数据冗余：
为了将不同维度的信息整合到一起，可能会存在大量的数据重复存储。例如，
在一个包含订单和客户信息的宽表中，每个订单记录都可能重复存储客户的基本信息，如姓名、地址等。
 结构复杂：
由于包含了众多不同类型的字段，其结构相对复杂。
这些字段可能涵盖了各种数据类型，如整数、字符串、日期等，且彼此之间的逻辑关系也较为复杂。
 信息丰富：
宽表的最大优势在于能够在一行数据中提供丰富的信息，
方便快速获取和分析与某个实体相关的多维度数据，
避免了多表关联查询的复杂性和性能开销。

宽表的使用场景

数据分析与报表：在数据分析和报表生成场景中，宽表非常有用。
它允许分析师直接从一张表中获取所需的各种数据，
无需复杂的多表连接操作，能大大提高数据分析的效率。
数据仓库：数据仓库通常会使用宽表来存储整合后的数据，
以便于进行全面的数据分析和决策支持。通过将多个数据源的数据整合到宽表中，
可以为企业提供一个统一的数据视图，方便管理层进行综合分析和决策。
快速查询与检索：对于一些需要快速查询和检索特定实体的详细信息的应用场景，
宽表能够满足需求。例如，在客户关系管理系统（CRM）中，
通过宽表可以快速获取某个客户的所有相关信息，包括基本信息、交易记录、
服务记录等，而无需从多个表中逐一查询。

假设有一个电商平台，其数据库中可能有用户表、订单表、商品表等多张表。
如果要创建一个宽表，可以将这些表中的相关字段整合在一起，如下表所示：

清洗数据做大宽表存入 starrocks，既然是付费那也就是付费订单的，表设置按充值日期分区，然后直接统计
有付费率的话，登录注册数据也得存到starrocks，方便统计
付费订单先存入redis list，定时推入队列处理，取出list数据，整理组合好数据（比如用户注册渠道日期等等）

数据同步等

数据同步通的 cloudcanal
可以看看 superset 写 sql 定时清洗数据存到分析数据库
同步数据可以试试 cloudcanal 5 个 task 免费
要设计数据指标公式 复杂的一批

建表 和 mysql postgresql 有不同

不支持 unsigned 无符号等

CREATE TABLE  `sc_payment_order`
(
	`id` int not null,
	`order_no` varchar(100) comment '订单号',
	`order_id` int    comment '订单表id',
	`created_at` datetime comment  '订单创建日期',
	`created_at_date` date comment '订单创建日',
	`updated_at` datetime comment  '订单更新日期',
	`from_channel` varchar(100) null comment  '渠道',
	`pay_from_type` varchar(100) null,
	`user_id` int   comment  '用户id',
	`to_user_id` int    comment  '导师uid',
	`amount` decimal  comment  '金额',
	`order_price` decimal comment  '金额',
	`refund_price` decimal comment  '退款',
	`discount_amount` decimal comment  '',
	`devide_amount` decimal comment  '',
	`order_ori_price` decimal comment  '',
	`type_id` int    comment  '支付类型ID',
	`business_id` bigint not null comment  '支付业务关联逻辑订单id',
	`service_id` int   not null comment  '服务id',
	`report_type` int   comment  '测算报告类型1 ai taro   2 ai测算报告   3 八字',
	`order_title` varchar(500) null,
	`h5_order_token` varchar(100) null comment  'h5临时凭证',
	`bussiess_id` int not  null,
	`status` tinyint not null comment  '订单支付状态：0.等待支付，1.支付完成，2.支付失败',
	`order_status` tinyint not null comment  '1已指派，2已完成',
	`is_delete` tinyint not null comment  '订单软删除',
	`active_time` int    comment  '激活时间',
	`type` varchar(100) null comment  '支付类型：wechat | alipay mycard_pay',
	`from_type` varchar(100) comment  '支付来源',
	`third_order_no` varchar(200) comment  '',
	`order_finish_time` datetime  comment  '订单结束时间',
	`email` varchar(200)  comment  '用户邮箱',
	`register_time` int  comment  '注册时间 user_search register_time',
	`register_date` date  comment  '注册日期',
	`is_new_day` tinyint   comment  '是否当天新注册 created_at 根据订单创建时间 判定是否新注册用户',
	`is_new` tinyint   comment  '是否三天内注册的人=is_new_day3',
	`is_new_day7` tinyint   comment  '是否7天内注册的人',
	`user_new_day` int   comment  ' 注册了多少天',
	`is_login` tinyint   comment  '有无登录  有则为激活',
	`completed_at` datetime comment  ' 支付时间 付费时间',
	`year_num` smallint comment  ' 当年 订单创建',
	`mouth_num` smallint ,
	`day_num` smallint ,
	`week_num` smallint ,
	`is_reg_cur_mouth` tinyint  comment  '是否注册当月的单',
	`is_reg_next_mouth` tinyint comment  '是否注册次月的单',
	`create_from` tinyint comment  '0=未知 1=站内 2=站外',
	`app_name` varchar(100) comment  ' app名',
	`app_system` varchar(100) comment  ' app系统',
	`from_version` varchar(100) ,
	`is_fly` tinyint comment  ' 是否自然流',
	`is_from_h5` tinyint comment  '是否h5付费',
	`is_read` tinyint comment  '是否导师已读订单',
	`ip` varchar(50) not null,
	`extends` varchar(3000) not null 
)
PRIMARY KEY (id)
DISTRIBUTED BY HASH (id)
PROPERTIES (
  "replication_num" = "1"
);

create table sc_user(
    id bigint(20)  NOT NULL AUTO_INCREMENT,
    from_channel varchar(100) DEFAULT NULL comment '渠道',
    year_num smallint(11)  comment '年',
    mouth_num tinyint(11)  comment '月',
    day_num smallint(11)  comment '日',
    week_num smallint(11)  comment '周',
    is_tutor tinyint(11)  comment '是否导师',
    mail varchar(100) DEFAULT '' comment '邮箱',
    birthday_new varchar(300) DEFAULT '' comment '',
    address varchar(300) DEFAULT '' comment '地址',
    last_login_time int   comment '最后登录时间 ',
    last_login_date date   comment '最后登录时间 date ',
    register_time int   comment '注册时间 ',
    register_date int   comment 'register_date ',
    sex  tinyint(11)  comment '1男2女',
    status  tinyint(11)  comment '用户状态：1正常 2禁用 3禁言 4注销',
    is_online  tinyint(11)  comment '用户是否在线',
    app_name varchar(100) DEFAULT '' comment 'app名',
    app_system varchar(100) DEFAULT '' comment 'app系统',
    from_type varchar(100) DEFAULT '' comment '来源google,facebook,email...',
    is_payment tinyint(11)  comment '是否支付过',
    is_fly tinyint(11)  comment '是否自然流',
    last_login_ip varchar(100) DEFAULT '' comment '最后登录ip',
    invite_user_id int(11)  comment '邀请人',
    destroy_time int(11)  comment '注销时间',
    destroy_date date null comment '注销时间',
    extends text comment ''
) 
PRIMARY KEY (id)
DISTRIBUTED BY HASH (id)
PROPERTIES (
  "replication_num" = "1"
);

//不支持unsigned 无符号
ALTER TABLE sc_payment_order  MODIFY COLUMN cnt int not null;
修改字段
ALTER TABLE sc_payment_order MODIFY COLUMN cnt INT ;
加字段
ALTER TABLE sc_payment_order add COLUMN bussiess_id INT  ;

 ALTER TABLE sc_payment_order MODIFY COLUMN order_id INT COMMENT '订单编号';

ALTER TABLE orders1 add COLUMN cnt2 INT  ;

ALTER TABLE orders1 drop COLUMN cnt2   ;   //删字段

较多重复 加索引
CREATE INDEX idx_created_at_date ON sc_payment_order (created_at_date) USING BITMAP;
删索引
DROP INDEX idx_created_at ON sc_payment_order;

一些坑

框架orm 增删改用不了,自己写pdo绑定 才可以弄进去 我就随便写了个pdo类，
复杂查询orm玩了好几个，一点问题都没,原生sql也试了

链接

阿里云 starRocks https://www.aliyun.com/product/bigdata/starrocks
https://help.aliyun.com/zh/emr/emr-on-ecs/user-guide/getting-started-1?spm=a2c4g.11186623.0.nextDoc.4cd17875sub9qw

https://docs.starrocks.io/zh/docs/cover_pages/query_acceleration/

https://juejin.cn/post/7109682148029759495 宽表

https://blog.csdn.net/weixin_45866849/article/details/127572538

https://docs.starrocks.io/zh/docs/integrations/loading_tools/SMT/ 数据同步

https://www.atbigapp.com/tool/createTableStarRocks?t=website_csdn 建表语句转换 工具

https://www.studywithgpt.com/zh-cn/tutorial/bdwqm4 数据同步

https://brightliao.com/2021/03/15/data-management-practice/ 数据平台,清洗和建模

https://help.aliyun.com/zh/emr/emr-on-ecs/user-guide/getting-started-1?spm=a2c4g.11186623.0.nextDoc.4cd17875sub9qw 基本操作

https://www.volcengine.com/docs/6491/1261823 分区表