1.Doris 简介
1.1 Doris 概述
Apache Doris 由百度大数据部研发（之前叫百度 Palo，2018 年贡献到 Apache 社区后，
更名为 Doris ），在百度内部，有超过 200 个产品线在使用，部署机器超过 1000 台，单一
业务最大可达到上百 TB。
Apache Doris 是一个现代化的 MPP（Massively Parallel Processing，即大规模并行处理）
分析型（OLAP）数据库产品。仅需亚秒级响应时间即可获得查询结果，有效地支持实时数据分析。
Apache Doris 的分布式架构非常简洁，易于运维，并且可以支持 10PB 以上的超大数据集。
Apache Doris 可以满足多种数据分析需求，例如固定历史报表，实时数据分析，交互式数据分析和探索式数据分析等。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Hvj6IaqO-1677043665246)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130351225.png)]
1.2OLAP和OLTP
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6ICur7Tt-1677043665247)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221140509218.png)]
- 联机事务处理OLTP(On-Line Transaction Processing)
- 公司业务系统使用数据库的场景，针对业务系统数据库有大量随机的增删改查
- 高并发
- 速度要快
- 支持事务
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pV8Ef3NE-1677043665247)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130420630.png)]
- 联机分析处理OLAP(On-Line Analytical Processing)
- 公司的数据分析使用数据库的场景，对已经生成好的数据进行统计分析
- 一次操作都是针对的整个数据集，
- 只有查这个动作，不会去增删改
- 查询的响应速度相对慢点也能接受
- 并发量要求不是太高
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QGK1jH3t-1677043665248)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130520084.png)]
OLAP和OLTP比较
| OLTP | OLAP | |
|---|---|---|
| 数据源 | 仅包含当前运行日常业务数据 | 整合来自多个来源的数据，包括OLTP和外部来源 |
| 目的 | 面向应用,面向业务,支撑事务 | 面向主题，面向分析，支持分析决策 |
| 焦点 | 当下 | 主要面向过去，面向历史(实时数仓除外) |
| 任务 | 增删改查 | 主要是用于读，select查询，写操作很少 |
| 响应时间 | 毫秒 | 秒，分钟，小时，天，这些取决于数据量和查询的复杂程度 |
| 数据量 | 小数据,MB,GB | 大数据，TP,PB |
常见的开源OLAP引擎
| 开源OLAP引擎 | 优点 | 缺点 | 技术融合成本 | 易用性 | 使用场景 | 运维成本 | 引擎类型 |
|---|---|---|---|---|---|---|---|
| ClickHouse | 列式存储单极性彪悍保留明细数据 | 分布式集群在线扩展支持不佳运维成本极高 | 高 | 非标协议接口 | 全面 | 高 | 纯列存OLAP |
| Druid | 实时数据摄入列式存储和位图索引多租户和高并发 | OLAP性能分场景表现差异大使用门槛高仅支持聚合查询 | 高 | 非标协议接口 | 局限 | 高 | MOLAP |
| TiDB | HTAP混合数据库同时支持明细和聚合查询高度兼容mysql | 非列式存储OLAP能力不足 | 低 | SQL标准 | 全面 | 低 | 纯列存OLAP |
| Kylin | 与计算引擎，可以对数据一次聚合多次查询支持数据规模超大易用性强，支持标准sql性能强，查询数据快 | 需要依赖hadoop生态仅支持聚合查·询不支持adhoc查询不支持join和对数据的更新 | 高 | SQL标准 | 局限 | 高 | MOLAP |
| Doris | GooleMesa+Apache Impa+ORCFile/Parquet主键更新支持Rollup Table高并发和高通图的Ad-hoc查询支持聚合+明细数据查询无外部系统依赖 | 成熟度不够 | 低 | 兼容mysql访问协议 | 全面 | 低 | HOLAP |
1.3使用场景
- 报表分析
- 实时看板 （Dashboards） ==》rolap中我给过他一个sql，亚秒级的响应我想要的结果
- 面向企业内部分析师和管理者的报表
- 面向用户或者客户的高并发报表分析（Customer Facing Analytics）。比如面向网站主的站点分析、面向广告主的广告报表，并发通常要求成千上万的 QPS ，查询延时要求毫秒级响应。著名的电商公司京东在广告报表中使用 Apache Doris ，每天写入 100 亿行数据，查询并发 QPS 上万，99 分位的查询延时 150ms。
- 即席查询（Ad-hoc Query）：面向分析师的自助分析，查询模式不固定，要求较高的吞吐。小米公司基于 Doris 构建了增长分析平台（Growing Analytics，GA），利用用户行为数据对业务进行增长分析，平均查询延时 10s，95 分位的查询延时 30s 以内，每天的 SQL 查询量为数万条。
- 统一数仓构建 ：一个平台满足统一的数据仓库建设需求，简化繁琐的大数据软件栈。海底捞基于 Doris 构建的统一数仓，替换了原来由 Spark、Hive、Hbase、Phoenix 组成的旧架构，架构大大简化。
- 数据湖联邦查询：通过外表的方式联邦分析位于 Hive、Hudi 中的数据，在避免数据拷贝的前提下，查询性能大幅提升
1.4优势
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MIJKSzRq-1677043665248)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130543966.png)]
1.5架构
Doris 的架构很简洁，只设 FE(Frontend)前端进程、BE(Backend)后端进程两种角色、两个后台的服务进程，不依赖于外部组件，方便部署和运维，FE、BE 都可在线性扩展。
- FE（Frontend）：存储、维护集群元数据；负责接收、解析查询请求，规划查询计划,调度查询执行，返回查询结果。主要有三个角色：
- Leader 和 Follower：主要是用来达到元数据的高可用，保证单节点宕机的情况下,元数据能够实时地在线恢复，而不影响整个服务。
- Observer：用来扩展查询节点，同时起到元数据备份的作用。如果在发现集群压力非常大的情况下，需要去扩展整个查询的能力，那么可以加 observer 的节点。observer 不参与任何的写入，只参与读取。
- BE（Backend）：负责物理数据的存储和计算；依据 FE 生成的物理计划，分布式地执行查询。数据的可靠性由 BE 保证，BE 会对整个数据存储多副本或者是三副本。副本数可根据需求动态调整。
- MySQL Client:Doris 借助 MySQL 协议，用户使用任意 MySQL 的 ODBC/JDBC 以及 MySQL 的客户端，都可以直接访问 Doris。
- Broker:一个独立的无状态进程。封装了文件系统接口，提供 Doris 读取远端存储系统中文件的能力，包括 HDFS，S3，BOS 等。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7hWTvpeO-1677043665249)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221140545443.png)]
1.6默认端口
| 实例名称 | 端口名称 | 默认端口 | 通讯方向 | 说明 |
|---|---|---|---|---|
| BE | be_port | 9060 | FE–>BE | BE 上 thrift server 的端口,用于接收来自 FE 的请求 |
| BE | webserver_port | 8040 | BE<–>FE | BE 上的 http server 端口 |
| BE | heartbeat_service_port | 9050 | FE–>BE | BE 上心跳服务端口,用于接收来自 FE 的心跳 |
| BE | brpc_prot* | 8060 | FE<–>BE,BE<–>BE | BE 上的 brpc 端口,用于 BE 之间通信 |
| FE | http_port | 8030 | FE<–>FE ,用户<–> FE | FE 上的 http_server 端口 |
| FE | rpc_port | 9020 | BE–>FE ,FE<–>FE | FE 上 thirft server 端口 |
| FE | query_port | 9030 | 用户<–> FE | FE 上的 mysql server 端口 |
| FE | edit_log_port | 9010 | FE<–>FE | FE 上 bdbje 之间通信用的端口 |
| Broker | broker_ipc_port | 8000 | FE–>BROKER,BE–>BROKER | Broker 上的 thrift server,用于接收请求 |
2.安装
2.1安装前准备
- Linux 操作系统版本需求
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jk01spBH-1677043665249)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221140613923.png)]
- 软件需求
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VfIho23O-1677043665250)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221140623838.png)]
- 测试环境硬件配置需求
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rYGT0dNF-1677043665250)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221140637392.png)]
- 生产环境硬件配置需求
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zz32SAqm-1677043665250)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221140648508.png)]
- 操作系统环境要求
设置系统最大文件打开句柄数 ==>启动一个程序的时候，打开文件的数量就是句柄数
1.打开文件
security /sɪˈkjʊərəti/
vi /etc/security/limits.conf
2.在文件最后添加下面几行信息(注意* 也要赋值进去)
* soft nofile 65535
* hard nofile 65535
* soft nproc 65535
* hard nproc 65535
ulimit -n 65535 临时生效
修改完文件后需要重新启动虚拟机
重启永久生效，也可以用 。
如果不修改这个句柄数大于等于60000，回头启动doris的be节点的时候就会报如下的错
File descriptor number is less than 60000. Please use (ulimit -n) to set a value equal or greater than 60000
W1120 18:14:20.934705 3437 storage_engine.cpp:188] check fd number failed, error: Internal error: file descriptors limit is too small
W1120 18:14:20.934713 3437 storage_engine.cpp:102] open engine failed, error: Internal error: file descriptors limit is too small
F1120 18:14:20.935087 3437 doris_main.cpp:404] fail to open StorageEngine, res=file descriptors limit is too small
时钟同步
Doris 的元数据要求时间精度要小于5000ms，所以所有集群所有机器要进行时钟同步，避免因为时钟问题引发的元数据不一致导致服务出现异常。
如何时间同步？？
首先安装 ntpdate
# ntpdate是一个向互联网上的时间服务器进行时间同步的软件
[root@zuomm01 doris]# yum install ntpdate -y
然后开始三台机器自己同步时间
[root@node01 ~]# ntpdate ntp.sjtu.edu.cn
美国标准技术院时间服务器：time.nist.gov（192.43.244.18）
上海交通大学网络中心NTP服务器地址：ntp.sjtu.edu.cn（202.120.2.101）
中国国家授时中心服务器地址：cn.pool.ntp.org（210.72.145.44）
# 将当前时间写入bios，这样才能永久生效不变，不然reboot后还会恢复到原来的时间
clock -w
关闭交换分区（swap）
交换分区是linux用来当做虚拟内存用的磁盘分区；
linux可以把一块磁盘分区当做内存来使用（虚拟内存、交换分区）；
Linux使用交换分区会给Doris带来很严重的性能问题，建议在安装之前禁用交换分区；
1、查看 Linux 当前 Swap 分区
free -m
2、关闭 Swap 分区
swapoff -a
[root@zuomm01 app]# free -m
total used free shared buff/cache available
Mem: 5840 997 4176 9 666 4604
Swap: 6015 0 6015
[root@zuomm01 app]# swapoff -a
3.验证是否关闭成功
[root@zuomm01 app]# free -m
total used free shared buff/cache available
Mem: 5840 933 4235 9 671 4667
Swap: 0 0 0
注意事项：
- FE 的磁盘空间主要用于存储元数据，包括日志和 image。通常从几百 MB 到几个GB 不等。
- BE 的磁盘空间主要用于存放用户数据，总磁盘空间按用户总数据量* 3（3 副本）计算，然后再预留额外 40%的空间用作后台 compaction 以及一些中间数据的存放。
- 一台机器上可以部署多个 BE 实例，但是只能部署一个 FE。如果需要 3 副本数 据，那么至少需要 3 台机器各部署一个 BE 实例（而不是 1 台机器部署 3 个 BE 实例）。多 个 FE 所在服务器的时钟必须保持一致（允许最多 5 秒的时钟偏差）
- 测试环境也可以仅适用一个 BE 进行测试。实际生产环境，BE 实例数量直接决定了整体查询延迟。
- 所有部署节点关闭 Swap。
- FE 节点数据至少为 1（1 个 Follower）。当部署 1 个 Follower 和 1 个 Observer 时,可以实现读高可用。当部署 3 个 Follower 时，可以实现读写高可用（HA）。
- Follower 的数量必须为奇数，Observer 数量随意。
- 根据以往经验，当集群可用性要求很高时（比如提供在线业务），可以部署 3 个Follower 和 1-3 个 Observer。如果是离线业务，建议部署 1 个 Follower 和 1-3 个 Observer。
- Broker 是用于访问外部数据源（如 HDFS）的进程。通常，在每台机器上部署一个 broker 实例即可。
2.2安装FE
- 去官网下载源码包，官网地址：https://doris.apache.org
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-m0ZhIPKg-1677043665251)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130612649.png)]
根据自己的配置选择性点击下载
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s3EwSo0A-1677043665251)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130630963.png)]
当然你也可以选择历史版本下载
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4mjfYR2U-1677043665251)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130648280.png)]
- 上传到linux
- 解压
- 修改配置文件
-- 去自己的路劲中找到fe.conf文件
vi /opt/app/doris/fe/conf/fe.conf
#配置文件中指定元数据路径： 注意这个文件夹要自己创建
meta_dir = /opt/data/dorisdata/doris-meta
#修改绑定 ip（每台机器修改成自己的 ip）
priority_networks = 192.168.17.0/24
- 分发集群
[root@zuomm01 app]# for i in 2 3
> do
> scp /et/profile linux0$i:/etc/profile
> scp -r /opt/app/doris/ linux0$i:/opt/app/
> done
- 启动
进入到fe的bin目录下执行
[root@zuomm01 bin]# ./start_fe.sh --daemon
生产环境强烈建议单独指定目录不要放在 Doris 安装目录下，最好是单独的磁盘（如果有 SSD 最好）。 如果机器有多个 ip, 比如内网外网, 虚拟机 docker 等, 需要进行 ip 绑定，才能正确识别。 JAVA_OPTS 默认 java 最大堆内存为 4GB，建议生产环境调整至 8G 以上。
2.3安装BE
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fDaswJGj-1677043665252)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130705950.png)]
进入到be的conf目录下修改配置文件
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dJYuSEmU-1677043665252)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130720558.png)]
vi be.conf
#配置文件中指定数据存放路径：
storage_root_path = /opt/data/dorisdata/bedata
#修改绑定 ip（每台机器修改成自己的 ip）
priority_networks = 192.168.17.0/24
注意事项：
storage_root_path 默认在 be/storage 下，需要手动创建该目录。多个路径之间使用英文状
态的分号;分隔（最后一个目录后不要加）。
可以通过路径区别存储目录的介质，HDD 或 SSD。可以添加容量限制在每个路径的末
尾，通过英文状态逗号，隔开，如：
storage_root_path=/home/disk1/doris.HDD,50;/home/disk2/doris.SSD,10;/home/disk2/doris
说明：
/home/disk1/doris.HDD,50，表示存储限制为 50GB，HDD;
/home/disk2/doris.SSD,10，存储限制为 10GB，SSD；
/home/disk2/doris，存储限制为磁盘最大容量，默认为 HDD
这样就好了嘛？不是哦
因为FE和BE两个都是单独的个体，所以他俩相互间还不认识，就需要我们通过mysql的客户端将他们建立起联系
如果没有装mysql的家伙，记得先装mysql
-- 安装yum
0 yum list
-- 安装wget
1 yum -y install wget
-- 让wget直接去网页上安装mysql的安装包，可以解决一些依赖问题
2 wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
-- 安装刚才下载的mysql安装包
3 yum -y install mysql57-community-release-el7-10.noarch.rpm
4-- 安装mysql的服务
yum -y install mysql-community-server
这边有时候会报错
Failing package is: mysql-community-server-5.7.37-1.el7.x86_64
GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
解决办法 在yum install 版本后面加上 --nogpgcheck，即可绕过GPG验证成功安装。
比如yum install mysql-community-server --nogpgcheck
亲测没啥问题，安装成功
-- 启动mysql
5 service mysqld start
-- 查看mysql的3306端口，确认下mysql的服务是否打开
6 netstat -nltp | grep 3306
-- 去mysql的log日志中查找他的初始密码
7 grep "password" /var/log/mysqld.log 查看原始密码
grep 'password' /var/log/mysqld.log
2020-06-24T07:21:25.731630Z 1 [Note] A temporary password is generated for root@localhost: Apd>;WYEc2Ir
-- 注释:在密码不是以空格开头的
-- 如果登录不进去的情况下,可以尝试 mysql -u root -p 然后回车在将密码复制进去
2020-06-24T07:21:48.097350Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)
-- 登录mysql
8 登录 mysql -uroot -pWYEc2Ir
-- 修改mysql的密码，首先设置两个参数，这样密码就可以设置成123456这种简单的密码
9 修改密码
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1; 这个两个设置以后 密码很简单不会报错
-- 修改mysql的密码
alter user user() identified by "XXXXXX"; xxxx 就是你的新密码
使用 MySQL Client 连接 FE
mysql -h zuomm01 -P 9030 -uroot
这个只是用了mysql的客户端去连接doris的fe，不是启动的mysql哦！！！并且第一次进去的话，是不需要密码的
解释：
-h 连接地址
-P 端口号
-u 账号
-p 密码
--这个可以设置可以不设置啦，正常生产过程中都会设置一个相对比较复杂的密码，学习的时候就无所谓了
--如果想设置，下面的命令就可以
SET PASSWORD FOR 'root' = PASSWORD('123456');
fe启动完成后可以查看fe的运行状态
SHOW PROC '/frontends'\G;
添加 BE 节点
ALTER SYSTEM ADD BACKEND "zuomm01:9050";
ALTER SYSTEM ADD BACKEND "zuomm02:9050";
ALTER SYSTEM ADD BACKEND "zuomm03:9050";
查看 BE 状态
SHOW PROC '/backends';
Alive 为 false 表示该 BE 节点还是死的
添加环境变量
#doris_fe
export DORIS_FE_HOME=/opt/app/doris1.1.4/fe
export PATH=$PATH:$DORIS_FE_HOME/bin
#doris_be
export DORIS_BE_HOME=/opt/app/doris1.1.4/be
export PATH=$PATH:$DORIS_BE_HOME/bin
启动BE
启动 BE（每个节点）
/opt/app/doris/be/bin/start_be.sh --daemon
启动后再次查看BE的节点
mysql -h zuomm01 -P 9030 -uroot -p 123456
SHOW PROC '/backends';
Alive 为 true 表示该 BE 节点存活。
2.4部署 FS_Broker（可选）
Broker 以插件的形式，独立于 Doris 部署。如果需要从第三方存储系统导入数据，需要部署相应的 Broker，默认提供了读取 HDFS、百度云 BOS 及 Amazon S3 的 fs_broker。fs_broker 是无状态的，建议每一个 FE 和 BE 节点都部署一个 Broker。
直接启动就可以啦！！！没想到吧骚年
启动 Broker
/opt/app/doris/fe/apache_hdfs_broker/bin/start_broker.sh --daemon
使用 mysql-client 连接启动的 FE，执行以下命令：
mysql -h zuomm01 -P 9030 -uroot -p 123456
ALTER SYSTEM ADD BROKER broker_name "zuomm01:8000","zuomm02:8000","zuomm03:8000";
当然你也可以一个个的加，并且 broker_name 这只是一个名字，可以自己取
查看 Broker 状态
使用 mysql-client 连接任一已启动的 FE，执行以下命令查看 Broker 状态：
SHOW PROC "/brokers";
2.5扩容和缩容
2.5.1 FE 扩容和缩容
可以通过将 FE 扩容至 3 个以上节点来实现 FE 的高可用。
使用 MySQL 登录客户端后，可以使用 sql 命令查看 FE 状态，目前就一台 FE
mysql -h zuomm01 -P 9030 -uroot -p
mysql> SHOW PROC '/frontends'\G;
*************************** 1. row ***************************
Name: 192.168.17.3_9010_1661510658077
IP: 192.168.17.3
HostName: zuomm01
EditLogPort: 9010
HttpPort: 8030
QueryPort: 9030
RpcPort: 9020
Role: FOLLOWER
IsMaster: true
ClusterId: 1133836578
Join: true
Alive: true
ReplayedJournalId: 2472
LastHeartbeat: 2022-08-26 13:07:47
IsHelper: true
ErrMsg:
Version: 1.1.1-rc03-2dbd70bf9
CurrentConnected: Yes
1 row in set (0.03 sec)
添加FE的新节点：
FE 分为 Leader，Follower 和 Observer 三种角色。 默认一个集群，只能有一个 Leader,可以有多个 Follower 和 Observer。其中 Leader 和 Follower 组成一个 Paxos 选择组，如果Leader 宕机，则剩下的 Follower 会自动选出新的 Leader，保证写入高可用。Observer 同步 Leader 的数据，但是不参加选举。
如果只部署一个 FE，则 FE 默认就是 Leader。在此基础上，可以添加若干 Follower 和 Observer。
ALTER SYSTEM ADD FOLLOWER "zuomm02:9010";
ALTER SYSTEM ADD OBSERVER "zuomm03:9010";
在zuomm02和zuomm03上分别启动FE节点
/opt/app/doris/fe/bin/start_fe.sh --helper zuomm01:9010 --daemon
记住哦，如果是第一次添加的话，一定要加这两个参数 --helper zuomm01:9010
此时你再去查看FE的状态就发现有3台啦
mysql> SHOW PROC '/frontends'\G;
*************************** 1. row ***************************
Name: 192.168.17.4_9010_1661490723344
IP: 192.168.17.4
HostName: zuomm02
EditLogPort: 9010
HttpPort: 8030
QueryPort: 0
RpcPort: 0
Role: FOLLOWER
IsMaster: false
ClusterId: 1133836578
Join: false
Alive: false
ReplayedJournalId: 0
LastHeartbeat: NULL
IsHelper: true
ErrMsg: java.net.ConnectException: Connection refused (Connection refused)
Version: NULL
CurrentConnected: No
*************************** 2. row ***************************
Name: 192.168.17.5_9010_1661490727316
IP: 192.168.17.5
HostName: zuomm03
EditLogPort: 9010
HttpPort: 8030
QueryPort: 0
RpcPort: 0
Role: OBSERVER
IsMaster: false
ClusterId: 1133836578
Join: false
Alive: false
ReplayedJournalId: 0
LastHeartbeat: NULL
IsHelper: false
ErrMsg: java.net.ConnectException: Connection refused (Connection refused)
Version: NULL
CurrentConnected: No
*************************** 3. row ***************************
Name: 192.168.17.3_9010_1661510658077
IP: 192.168.17.3
HostName: zuomm01
EditLogPort: 9010
HttpPort: 8030
QueryPort: 9030
RpcPort: 9020
Role: FOLLOWER
IsMaster: true
ClusterId: 1133836578
Join: true
Alive: true
ReplayedJournalId: 2577
LastHeartbeat: 2022-08-26 13:13:33
IsHelper: true
ErrMsg:
Version: 1.1.1-rc03-2dbd70bf9
CurrentConnected: Yes
3 rows in set (0.04 sec)
删除FE节点命令
ALTER SYSTEM DROP FOLLOWER[OBSERVER] "fe_host:edit_log_port";
ALTER SYSTEM DROP FOLLOWER "zuomm01:9010";
注意：删除 Follower FE 时，确保最终剩余的 Follower（包括 Leader）节点最好为奇数。
2.5.2 BE 扩容和缩容
增加 BE 节点
在 MySQL 客户端，通过
ALTER SYSTEM ADD BACKEND 命令增加 BE 节点。
ALTER SYSTEM ADD BACKEND "zuomm01:9050";
DROP 方式删除 BE 节点（不推荐）
ALTER SYSTEM DROP BACKEND "be_host:be_heartbeat_service_port";
ALTER SYSTEM DROP BACKEND "zuomm01:9050";
注意：DROP BACKEND 会直接删除该 BE，并且其上的数据将不能再恢复！！！所以我们强烈不推荐使用 DROP BACKEND 这种方式删除 BE 节点。当你使用这个语句时,会有对应的防误操作提示。
DECOMMISSION 方式删除 BE 节点（推荐）
ALTER SYSTEM DECOMMISSION BACKEND "be_host:be_heartbeat_service_port";
ALTER SYSTEM DECOMMISSION BACKEND "zuomm01:9050";
- 该命令用于安全删除 BE 节点。命令下发后，Doris 会尝试将该 BE 上的数据向其 他 BE 节点迁移，当所有数据都迁移完成后，Doris 会自动删除该节点。
- 该命令是一个异步操作。执行后，可以通过 SHOW PROC ‘/backends’; 看到该 BE节点的 isDecommission 状态为 true。表示该节点正在进行下线。
- 该命令不一定执行成功。比如剩余 BE 存储空间不足以容纳下线 BE 上的数据，或者剩余机器数量不满足最小副本数时，该命令都无法完成，并且 BE 会一直处于isDecommission 为 true 的状态。
- DECOMMISSION 的进度，可以通过 SHOW PROC ‘/backends’; 中的 TabletNum 查看，如果正在进行，TabletNum 将不断减少。
- 该操作可以通过如下命令取消：CANCEL DECOMMISSION BACKEND “be_host:be_heartbeat_service_port”; 取消0后，该 BE 上的数据将维持当前剩余的数据量。后续 Doris 重新进行负载均衡。
2.5.3 Broker 扩容缩容
Broker 实例的数量没有硬性要求。通常每台物理机部署一个即可。Broker 的添加和删除可以通过以下命令完成：
ALTER SYSTEM ADD BROKER broker_name "broker_host:broker_ipc_port";
ALTER SYSTEM DROP BROKER broker_name "broker_host:broker_ipc_port";
ALTER SYSTEM DROP ALL BROKER broker_name;
Broker 是无状态的进程，可以随意启停。当然，停止后，正在其上运行的作业会失败，重试即可。
第 3 章 数据表设计
3.1字段类型
| TINYINT | 1 字节 | 范围：-2^7 + 1 ~ 2^7 - 1 |
|---|---|---|
| SMALLINT | 2 字节 | 范围：-2^15 + 1 ~ 2^15 - 1 |
| INT | 4 字节 | 范围：-2^31 + 1 ~ 2^31 - 1 |
| BIGINT | 8 字节 | 范围：-2^63 + 1 ~ 2^63 - 1 |
| LARGEINT | 16 字节 | 范围：-2^127 + 1 ~ 2^127 - 1 |
| FLOAT | 4 字节 | 支持科学计数法 |
| DOUBLE | 12 字节 | 支持科学计数法 |
| DECIMAL[(precision, scale)] | 16 字节 | 保证精度的小数类型。默认是DECIMAL(10, 0) ，precision: 1 ~ 27 ，scale: 0 ~ 9，其中整数部分为 1 ~ 18，不支持科学计数法 |
| DATE | 3 字节 | 范围：0000-01-01 ~ 9999-12-31 |
| DATETIME | 8 字节 | 范围：0000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
| CHAR[(length)] | 定长字符串。长度范围：1 ~ 255。默认为 1 | |
| VARCHAR[(length)] | 变长字符串。长度范围：1 ~ 65533 | |
| BOOLEAN | 与 TINYINT 一样，0 代表 false，1 代表 true | |
| HLL | 1~16385 个字节 | hll 列类型，不需要指定长度和默认值,长度根据数据的聚合程度系统内控制，并且 HLL 列只能通过 配套的hll_union_agg、Hll_cardinality、hll_hash 进行查询或使用 |
| BITMAP | bitmap 列类型，不需要指定长度和默认值。表示整型的集合，元素最大支持到 2^64 - 1 | |
| STRING | 变长字符串，0.15 版本支持，最大支持 2147483643 字节（2GB-4），长度还受 be 配置string_type_soft_limit, 实际能存储的最大长度取两者最小值。只能用在 value 列，不能用在 key列和分区、分桶列 |
3.2 表的基本概念
3.2.1 Row & Column
一张表包括行（Row）和列（Column）；
Row 即用户的一行数据。Column 用于描述一行数据中不同的字段。
doris中的列分为两类：key列和value列
key列在doris中有两种作用：
聚合表模型中，key是聚合和排序的依据
其他表模型中，key是排序依据
3.2.2 分区与分桶
- partition**（分区）：是在逻辑上****将一张表按行(横向)划分**
- tablet**（又叫bucket，分桶）：在物理上****对一个分区再按行(横向)划分**
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AC4btOmP-1677043665253)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130759507.png)]
3.2.2.1 Partition
- Partition 列可以指定一列或多列，在聚合模型中，分区列必须为 KEY 列。
- 不论分区列是什么类型，在写分区值时，都需要加双引号。
- 分区数量理论上没有上限。
- 当不使用 Partition 建表时，系统会自动生成一个和表名同名的，全值范围的 Partition。该 Partition 对用户不可见，并且不可删改。
- 创建分区时不可添加范围重叠的分区。
**1）**Range 分区
range分区创建语法
-- Range Partition
drop table if exists test.expamle_range_tbl;
CREATE TABLE IF NOT EXISTS test.expamle_range_tbl
(
`user_id` LARGEINT NOT NULL COMMENT "用户id",
`date` DATE NOT NULL COMMENT "数据灌入日期时间",
`timestamp` DATETIME NOT NULL COMMENT "数据灌入的时间戳",
`city` VARCHAR(20) COMMENT "用户所在城市",
`age` SMALLINT COMMENT "用户年龄",
`sex` TINYINT COMMENT "用户性别"
)
ENGINE=OLAP
DUPLICATE KEY(`user_id`, `date`) -- 表模型
-- 分区的语法
PARTITION BY RANGE(`date`) -- 指定分区类型和分区列
(
-- 指定分区名称，分区的上界 前闭后开
PARTITION `p201701` VALUES LESS THAN ("2017-02-01"),
PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
PARTITION `p201703` VALUES LESS THAN ("2017-04-01")
)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aP5QiJlg-1677043665253)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221133835273.png)]
- 分区列通常为时间列，以方便的管理新旧数据。
- Partition 支持通过 VALUES LESS THAN (…) 仅指定上界，系统会将前一个分区的上界作为该分区的下界，生成一个左闭右开的区间。同时，也支持通过 VALUES […) 指定上下界，生成一个左闭右开的区间。
- 通过 VALUES […) 同时指定上下界比较容易理解。这里举例说明，当使用 VALUES LESS THAN (…) 语句进行分区的增删操作时，分区范围的变化情况：
如上 expamle_range_tbl 得建表语句中可以看到，当建表完成后，会自动生成如下3个分区：
-- 查看表中分区得情况
SHOW PARTITIONS FROM test.expamle_range_tbl \G;
mysql> SHOW PARTITIONS FROM test.expamle_range_tbl \G;
*************************** 1. row ***************************
PartitionId: 12020
PartitionName: p201701
VisibleVersion: 1
VisibleVersionTime: 2022-08-30 21:57:36
State: NORMAL
PartitionKey: date
Range: [types: [DATE]; keys: [0000-01-01]; ..types: [DATE]; keys: [2017-02-01]; )
DistributionKey: user_id
Buckets: 1
ReplicationNum: 3
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: 0.000
IsInMemory: false
ReplicaAllocation: tag.location.default: 3
*************************** 2. row ***************************
PartitionId: 12021
PartitionName: p201702
VisibleVersion: 1
VisibleVersionTime: 2022-08-30 21:57:36
State: NORMAL
PartitionKey: date
Range: [types: [DATE]; keys: [2017-02-01]; ..types: [DATE]; keys: [2017-03-01]; )
DistributionKey: user_id
Buckets: 1
ReplicationNum: 3
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: 0.000
IsInMemory: false
ReplicaAllocation: tag.location.default: 3
*************************** 3. row ***************************
PartitionId: 12022
PartitionName: p201703
VisibleVersion: 1
VisibleVersionTime: 2022-08-30 21:57:35
State: NORMAL
PartitionKey: date
Range: [types: [DATE]; keys: [2017-03-01]; ..types: [DATE]; keys: [2017-04-01]; )
DistributionKey: user_id
Buckets: 1
ReplicationNum: 3
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: 0.000
IsInMemory: false
ReplicaAllocation: tag.location.default: 3
3 rows in set (0.00 sec)
这是他生成得三个分区：
p201701: [MIN_VALUE, 2017-02-01)
p201702: [2017-02-01, 2017-03-01)
p201703: [2017-03-01, 2017-04-01)
当我们增加一个分区 p201705 VALUES LESS THAN (“2017-06-01”)，分区结果如下：
ALTER TABLE test.expamle_range_tbl ADD PARTITION p201705 VALUES LESS THAN ("2017-06-01");
p201701: [MIN_VALUE, 2017-02-01)
p201702: [2017-02-01, 2017-03-01)
p201703: [2017-03-01, 2017-04-01)
p201705: [2017-04-01, 2017-06-01)
此时我们删除分区 p201703，则分区结果如下：
ALTER TABLE test.expamle_range_tbl DROP PARTITION p201703;
p201701: [MIN_VALUE, 2017-02-01)
p201702: [2017-02-01, 2017-03-01)
p201705: [2017-04-01, 2017-06-01)
注意到 p201702 和 p201705 的分区范围并没有发生变化，而这两个分区之间，出现了一个空洞：[2017-03-01, 2017-04-01)。即如果导入的数据范围在这个空洞范围内，是无法导入的。
继续删除分区 p201702，分区结果如下：
p201701: [MIN_VALUE, 2017-02-01)
p201705: [2017-04-01, 2017-06-01)
空洞范围变为：[2017-02-01, 2017-04-01)
现在增加一个分区 p201702new VALUES LESS THAN (“2017-03-01”)，分区结果如下：
p201701: [MIN_VALUE, 2017-02-01)
p201702new: [2017-02-01, 2017-03-01)
p201705: [2017-04-01, 2017-06-01)
可以看到空洞范围缩小为：[2017-03-01, 2017-04-01)
现在删除分区 p201701，并添加分区 p201612 VALUES LESS THAN (“2017-01-01”)，分区结果如下：
p201612: [MIN_VALUE, 2017-01-01)
p201702new: [2017-02-01, 2017-03-01)
p201705: [2017-04-01, 2017-06-01)
即出现了一个新的空洞：[2017-01-01, 2017-02-01)
综上，分区的删除不会改变已存在分区的范围。删除分区可能出现空洞。通过 VALUES LESS THAN 语句增加分区时，分区的下界紧接上一个分区的上界。
Range分区除了上述我们看到的单列分区，也支持多列分区，示例如下：
PARTITION BY RANGE(`date`, `id`) 前闭后开
(
PARTITION `p201701_1000` VALUES LESS THAN ("2017-02-01", "1000"),
PARTITION `p201702_2000` VALUES LESS THAN ("2017-03-01", "2000"),
PARTITION `p201703_all` VALUES LESS THAN ("2017-04-01")-- 默认采用id类型的最小值
)
在以上示例中，我们指定 date(DATE 类型) 和 id(INT 类型) 作为分区列。以上示例最终得到的分区如下：
* p201701_1000: [(MIN_VALUE, MIN_VALUE), ("2017-02-01", "1000") )
* p201702_2000: [("2017-02-01", "1000"), ("2017-03-01", "2000") )
* p201703_all: [("2017-03-01", "2000"), ("2017-04-01", MIN_VALUE))
注意，最后一个分区用户缺失，只指定了 date 列的分区值，所以 id 列的分区值会默认填充 MIN_VALUE。当用户插入数据时，分区列值会按照顺序依次比较，最终得到对应的分区。举例如下：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n3G2UiWJ-1677043665253)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221133924628.png)]
**2）**List 分区
- 分区列支持 BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR 数据类型，分区值为枚举值。只有当数据为目标分区枚举值其中之一时，才可以命中分区。
- Partition 支持通过 VALUES IN (…) 来指定每个分区包含的枚举值。
- 下面通过示例说明，进行分区的增删操作时，分区的变化。
List分区创建语法
-- List Partition
CREATE TABLE IF NOT EXISTS test.expamle_list_tbl
(
`user_id` LARGEINT NOT NULL COMMENT "用户id",
`date` DATE NOT NULL COMMENT "数据灌入日期时间",
`timestamp` DATETIME NOT NULL COMMENT "数据灌入的时间戳",
`city` VARCHAR(20) NOT NULL COMMENT "用户所在城市",
`age` SMALLINT NOT NULL COMMENT "用户年龄",
`sex` TINYINT NOT NULL COMMENT "用户性别",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
`cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
`max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
ENGINE=olap
AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
PARTITION BY LIST(`city`)
(
PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"),
PARTITION `p_usa` VALUES IN ("New York", "San Francisco"),
PARTITION `p_jp` VALUES IN ("Tokyo")
)
-- 指定分桶的语法
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES
(
"replication_num" = "3"
);
如上 example_list_tbl 示例，当建表完成后，会自动生成如下3个分区：
p_cn: ("Beijing", "Shanghai", "Hong Kong")
p_usa: ("New York", "San Francisco")
p_jp: ("Tokyo")
当我们增加一个分区 p_uk VALUES IN (“London”)，分区结果如下：
p_cn: ("Beijing", "Shanghai", "Hong Kong")
p_usa: ("New York", "San Francisco")
p_jp: ("Tokyo")
p_uk: ("London")
当我们删除分区 p_jp，分区结果如下：
p_cn: ("Beijing", "Shanghai", "Hong Kong")
p_usa: ("New York", "San Francisco")
p_uk: ("London")
List分区也支持多列分区，示例如下
PARTITION BY LIST(`id`, `city`)
(
PARTITION `p1_city` VALUES IN (("1", "Beijing"), ("1", "Shanghai")),
PARTITION `p2_city` VALUES IN (("2", "Beijing"), ("2", "Shanghai")),
PARTITION `p3_city` VALUES IN (("3", "Beijing"), ("3", "Shanghai"))
)
在以上示例中，我们指定 id(INT 类型) 和 city(VARCHAR 类型) 作为分区列。以上示例最终得到的分区如下：
* p1_city: [("1", "Beijing"), ("1", "Shanghai")]
* p2_city: [("2", "Beijing"), ("2", "Shanghai")]
* p3_city: [("3", "Beijing"), ("3", "Shanghai")]
当用户插入数据时，分区列值会按照顺序依次比较，最终得到对应的分区。举例如下：
* 数据 ---> 分区
* 1, Beijing ---> p1_city
* 1, Shanghai ---> p1_city
* 2, Shanghai ---> p2_city
* 3, Beijing ---> p3_city
* 1, Tianjin ---> 无法导入
* 4, Beijing ---> 无法导入
3.2.2.2 Bucket
- 如果使用了 Partition，则 DISTRIBUTED … 语句描述的是数据在各个分区内的划分规则。如果不使用 Partition，则描述的是对整个表的数据的划分规则。
- 分桶列可以是多列，但必须为 Key 列。分桶列可以和 Partition 列相同或不同。
- 分桶列的选择，是在 查询吞吐 和 查询****并发 之间的一种权衡：
- 如果选择多个分桶列，则数据分布更均匀。如果一个查询条件不包含所有分桶列的等值条件，那么该查询会触发所有分桶同时扫描，这样查询的吞吐会增加，单个查询的延迟随之降低。这个方式适合大吞吐低并发的查询场景。
- 如果仅选择一个或少数分桶列，则对应的点查询可以仅触发一个分桶扫描。此时，当多个点查询并发时，这些查询有较大的概率分别触发不同的分桶扫描，各个查询之间的IO影响较小（尤其当不同桶分布在不同磁盘上时），所以这种方式适合高并发的点查询场景。
- 分桶的数量理论上没有上限
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zrm3fybh-1677043665254)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134018543.png)]
关于 Partition 和 Bucket的数量和数据量的建议。
- 一个表的 Tablet 总数量等于 (Partition num * Bucket num)。
- 一个表的 Tablet 数量，在不考虑扩容的情况下，推荐略多于整个集群的磁盘数量。
- 单个 Tablet 的数据量理论上没有上下界，但建议在 1G - 10G 的范围内。如果单个 Tablet 数据量过小，则数据的聚合效果不佳，且元数据管理压力大。如果数据量过大，则不利于副本的迁移、补齐，且会增加 Schema Change 或者 Rollup 操作失败重试的代价（这些操作失败重试的粒度是 Tablet）。分桶应该控制桶内数据量 ，不易过大或者过小
- 当 Tablet 的数据量原则和数量原则冲突时，建议优先考虑数据量原则****。
- 在建表时，每个分区的 Bucket 数量统一指定。但是在动态增加分区时（ADD PARTITION），可以单独指定新分区的 Bucket 数量。可以利用这个功能方便的应对数据缩小或膨胀。
- 一个 Partition 的 Bucket 数量一旦指定，不可更改。所以在确定 Bucket 数量时，需要预先考虑集群扩容的情况。比如当前只有 3 台 host，每台 host 有 1 块盘。如果 Bucket 的数量只设置为 3 或更小，那么后期即使再增加机器，也不能提高并发度。
小例子：
假设在有10台BE，每台BE一块磁盘的情况下。
如果一个表总大小为 500MB，则可以考虑4-8个分片。
5GB：8-16个分片。
50GB：32个分片。
500GB：建议分区，每个分区大小在 50GB 左右，每个分区16-32个分片。
5TB：建议分区，每个分区大小在 500GB 左右，每个分区16-32个分片。
注：表的数据量可以通过 SHOW DATA命令查看，结果除以副本数，即表的数据量。
进阶：复合分区与单分区的选择
复合分区
- 第一级称为 Partition，即分区。用户可以指定某一维度列作为分区列（当前只支持整型和时间类型的列），并指定每个分区的取值范围。
- 第二级称为 Distribution，即分桶。用户可以指定一个或多个维度列以及桶数对数据进行 HASH 分布。
以下场景推荐使用复合分区
- 有时间维度或类似带有有序值的维度，可以以这类维度列作为分区列。分区粒度可以根据导入频次、分区数据量等进行评估。地域、时间
- 历史数据删除需求：如有删除历史数据的需求（比如仅保留最近N 天的数据）。使用复合分区，可以通过删除历史分区来达到目的。也可以通过在指定分区内发送 DELETE 语句进行数据删除。
- 解决数据倾斜问题：每个分区可以单独指定分桶数量。如按天分区，当每天的数据量差异很大时，可以通过指定分区的分桶数，合理划分不同分区的数据,分桶列建议选择区分度大的列。
用户也可以不使用复合分区，即使用单分区。则数据只做 HASH 分布。
小练习：建表指定分区和分桶数
需求：现在有如下数据需要插入到表中，请创建一个表，要求按照月份分区，2个分桶 ==》
（用哪一个，几个列作为分桶字段）
-- 数据
uid name age gender province birthday
1 zss 18 male jiangsu 2022-11-01
2 lss 18 male zhejiang 2022-11-10
3 ww 18 male jiangsu 2022-12-01
4 zll 18 female zhejiang 2022-09-11
5 tqq 18 female jiangsu 2022-09-02
6 aa 18 female jiangsu 2022-10-11
7 bb 18 male zhejiang 2022-11-08
CREATE TABLE IF NOT EXISTS test.user_info
(
uid int,
name varchar(50),
age int,
gender varchar(20),
province varchar(100),
birthday date
)
ENGINE=olap
duplicate KEY(uid,name)
PARTITION BY range(`birthday`)
(
partition `p202209` values less than ('2022-10-01'),
partition `p202210` values less than ('2022-11-01'),
partition `p202211` values less than ('2022-12-01'),
partition `p202212` values less than ('2023-01-01')
)
-- 指定分桶的语法
DISTRIBUTED BY HASH(uid) BUCKETS 1
PROPERTIES(
"replication_num" = "2",
""
);
3.2.3 PROPERTIES
在建表语句的最后，可以用 PROPERTIES 关键字来设置一些表的属性参数（参数有很多）
PROPERTIES(
"参数名" = "参数值"
)
下文挑选了3个比较重要的参数进行示例；
3.2.3.1 分片副本数
- replication_num
每个 Tablet 的副本数量。默认为 3，建议保持默认即可。在建表语句中，所有 Partition中的 Tablet 副本数量统一指定。而在增加新分区时，可以单独指定新分区中 Tablet 的副本数量。
副本数量可以在运行时修改。强烈建议保持奇数。
最大副本数量取决于集群中独立 IP 的数量（注意不是 BE 数量）。Doris 中副本分布的原则是，不允许同一个 Tablet 的副本分布在同一台物理机上，而识别物理机即通过 IP。所以，即使在同一台物理机上部署了 3 个或更多 BE 实例，如果这些 BE 的 IP 相同，则依然只能设置副本数为 1。对于一些小，并且更新不频繁的维度表，可以考虑设置更多的副本数。这样在 Join 查询时，可以有更大的概率进行本地数据 Join。
3.2.3.2 存储介质 和 热数据冷却时间
- storage_medium
- storage_cooldown_time datetime
建表时，可以统一指定所有 Partition 初始存储的介质及热数据的冷却时间，如：
"storage_medium" = "SSD"
"storage_cooldown_time" = "2022-11-30 00:00:00"
默认初始存储介质可通过 fe 的配置文件 fe.conf 中指定 default_storage_medium=xxx,如果没有指定，则默认为 HDD。如果指定为 SSD，则数据初始存放在 SSD 上。没设storage_cooldown_time，则默认 30 天后，数据会从 SSD 自动迁移到 HDD上。如果指定了 storage_cooldown_time，则在到达 storage_cooldown_time 时间后，数据才会迁移。
注意，当指定 storage_medium 时，如果 FE 参数 enable_strict_storage_medium_check 为False 该参数只是一个“尽力而为”的设置。即使集群内没有设置 SSD 存储介质，也不会报错，而是自动存储在可用的数据目录中。 同样，如果 SSD 介质不可访问、空间不足，都可能导致数据初始直接存储在其他可用介质上。而数据到期迁移到 HDD 时，如果 HDD 介质不 可 访 问 、 空 间 不 足 ， 也 可 能 迁 移 失 败 （ 但 是 会 不 断 尝 试 ） 。 如 果 FE 参 数enable_strict_storage_medium_check 为 True 则当集群内没有设置 SSD 存储介质时，会报错Failed to find enough host in all backends with storage medium is SSD。
小练习:建表时加上属性
需求：创建一个表，并为该表添加****如下属性
- 指定分区中的副本个数为2
- 指定数据初始存储位置是ssd
- 指定冷却时间到2022-12-20 00:00:00
CREATE TABLE IF NOT EXISTS test.user_info
(
`uid` LARGEINT ,
`name` varchar(50),
`age` SMALLINT,
`gender` VARCHAR(20),
`province` varchar(50),
`birthday` date
)
ENGINE=OLAP
duplicate KEY(`uid`,`name`)
PARTITION BY RANGE(`birthday`)
(
PARTITION `p202209` VALUES LESS THAN ("2022-10-01"),
PARTITION `p202210` VALUES LESS THAN ("2022-11-01"),
PARTITION `p202211` VALUES LESS THAN ("2022-12-01"),
PARTITION `p202212` VALUES LESS THAN ("2023-01-01")
)
DISTRIBUTED BY HASH(`uid`) BUCKETS 2
PROPERTIES
(
"replication_num" = "2",
"storage_medium" = "SSD",
"storage_cooldown_time" = "2022-11-30 00:00:00" -- 时间要大于当前时间
);
3.3 数据表模型
Doris 的数据模型主要分为3类:
- Aggregate 聚合模型
- Unique 唯一模型
- Duplicate 明细模型
3.3.1 Aggregate 模型
是相同key的数据进行自动聚合的表模型。表中的列按照是否设置了 AggregationType，分为 Key（维度列）和 Value（指标列），没有设置 AggregationType 的称为 Key，设置了 AggregationType 的称为 Value。当我们导入数据时，对于 Key 列相同的行会聚合成一行，而 Value 列会按照设置的AggregationType 进行聚合。AggregationType 目前有以下四种聚合方式：
- SUM：求和，多行的 Value 进行累加。
- REPLACE：替代，下一批数据中的 Value 会替换之前导入过的行中的 Value。
- REPLACE_IF_NOT_NULL ：当遇到 null 值则不更新。
- MAX：保留最大值。
- MIN：保留最小值。
有如下场景：需要创建一个表，来记录公司每个用户的每一次消费行为信息，有如下字段
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dcAsLvLQ-1677043665254)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134225557.png)]
而且，公司对这份数据，特别关心一个报表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KuBCbAr7-1677043665254)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134239242.png)]
每次要看这个报表，都需要在“明细表”上运行一个统计sql
Select
user_id,data,city,age,gender,
max(visit_data) as last_visit_data,
sum(cost) as cost,
max(dwell_time) as max_dwell_time,
min(dwell_time) as min_dwell_time
From t
Group by user_id,data,city,age,gender -- 对应的是聚合模型型key
聚合模型
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0MECPO2s-1677043665255)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134254367.png)]
sql示例：
-- 这是一个用户消费和行为记录的数据表
CREATE TABLE IF NOT EXISTS test.ex_user
(
`user_id` LARGEINT NOT NULL COMMENT "用户 id",
`date` DATE NOT NULL COMMENT "数据灌入日期时间",
`city` VARCHAR(20) COMMENT "用户所在城市",
`age` SMALLINT COMMENT "用户年龄",
`sex` TINYINT COMMENT "用户性别",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
`cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
`max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
ENGINE=olap
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
-- 分区
-- 分桶
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;
向表中插入部分数据
insert into test.ex_user values\
(10000,'2017-10-01','北京',20,0,'2017-10-01 06:00:00',20,10,10),\
(10000,'2017-10-01','北京',20,0,'2017-10-01 07:00:00',15,2,2),\
(10001,'2017-10-01','北京',30,1,'2017-10-01 17:05:45',2,22,22),\
(10002,'2017-10-02','上海',20,1,'2017-10-02 12:59:12',200,5,5),\
(10003,'2017-10-02','广州',32,0,'2017-10-02 11:20:00',30,11,11),\
(10004,'2017-10-01','深圳',35,0,'2017-10-01 10:00:15',100,3,3),\
(10004,'2017-10-03','深圳',35,0,'2017-10-03 10:20:22',11,6,6);
查看数据的时候发现，数据只剩下6条了，就是因为再key相同的时候，将后面的结果聚合了
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-22091Ulx-1677043665255)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130909595.png)]
想一想：聚合模型有时候不是我们想要的，我就不想让他们聚合，怎么办呢？
练一练
-- 数据
订单id,userId，商品id，购买件数，支付的金额，订单日期
1,u01,p01,2,20,2022-12-01
1,u01,p02,1,10,2022-12-01
1,u01,p01,1,10,2022-12-01
2,u02,p03,2,40,2022-12-01
需求：
创建一个doris的聚合模型的表，插入上述明细数据后，自动聚合出如下结果：
订单id，userId，商品id，购买得总件数，支付总额，订单日期
要求：
按天分区（每天一个分区）
每个分区要划分成2个桶
表的数据需要保存2个副本
表的数据初始存储介质指定为HDD
-- 建表语句
-- 插入数据
insert into order_info values
('2022-12-01',1,'u01','p01',2,20),
('2022-12-01',1,'u01','p02',1,10),
('2022-12-01',1,'u01','p01',1,10),
('2022-12-01',2,'u02','p03',2,40);
我可以这样不？一个人不能同时干两件事情，所以我加一个字段，让这个数据灌入的时间精确到时分秒，确保他组合起来的key都是唯一的，是不是就能搞定了？？
建表语句：
CREATE TABLE IF NOT EXISTS test.ex_user2
(
`user_id` LARGEINT NOT NULL COMMENT "用户 id",
`date` DATE NOT NULL COMMENT "数据灌入日期时间",
`timestamp` DATETIME COMMENT "数据灌入时间，精确到秒",
`city` VARCHAR(20) COMMENT "用户所在城市",
`age` SMALLINT COMMENT "用户年龄",
`sex` TINYINT COMMENT "用户性别",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
`cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
`max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时
间" )
AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;
插入部分数据
insert into test.ex_user2 values \
(10000,'2017-10-01','2017-10-01 08:00:05','北京',20,0,'2017-10-01 06:00:00',20,10,10),\
(10000,'2017-10-01','2017-10-01 09:00:05','北京',20,0,'2017-10-01 07:00:00',15,2,2),\
(10001,'2017-10-01','2017-10-01 18:12:10','北京',30,1,'2017-10-01 17:05:45',2,22,22),\
(10002,'2017-10-02','2017-10-02 13:10:00','上海',20,1,'2017-10-02 12:59:12',200,5,5),\
(10003,'2017-10-02','2017-10-02 13:15:00','广州',32,0,'2017-10-02 11:20:00',30,11,11),\
(10004,'2017-10-01','2017-10-01 12:12:48','深圳',35,0,'2017-10-01 10:00:15',100,3,3),\
(10004,'2017-10-03','2017-10-03 12:38:20','深圳',35,0,'2017-10-03 10:20:22',11,6,6);
再去select * 的时候就不会出现聚合的情况了
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eu98yBDJ-1677043665255)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130931815.png)]
数据的聚合，在 Doris 中有如下三个阶段发生：
- 每一批次数据导入的 ETL 阶段。该阶段会在每一批次导入的数据内部进行聚合。
- 底层 BE 进行数据 Compaction 的阶段。BE 会对已导入的不同批次的数据进行进一步的聚合。
- 数据查询阶段。在数据查询时，对于查询涉及到的数据，会进行对应的聚合。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6gWlgij1-1677043665256)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134354256.png)]
3.3.2 UNIQUE 模型
是相同key的数据进行自动去重的表模型。在某些多维分析场景下，用户更关注的是如何保证 Key 的唯一性，即如何获得 Primary Key 唯一性约束。因此，引入了 Uniq 的数据模型。该模型本质上是聚合模型的一个特例，也是一种简化的表结构表示方式。
建表示例：
drop table if exists test.user;
CREATE TABLE IF NOT EXISTS test.user
(
`user_id` LARGEINT NOT NULL COMMENT "用户 id",
`username` VARCHAR(50) NOT NULL COMMENT "用户昵称",
`city` VARCHAR(20) COMMENT "用户所在城市",
`age` SMALLINT COMMENT "用户年龄",
`sex` TINYINT COMMENT "用户性别",
`phone` LARGEINT COMMENT "用户电话",
`address` VARCHAR(500) COMMENT "用户地址",
`register_time` DATETIME COMMENT "用户注册时间" )
UNIQUE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;
插入语句
insert into test.user values\
(10000,'zss','北京',18,0,12345678910,'北京朝阳区 ','2017-10-01 07:00:00'),\
(10000,'zss','北京',19,0,12345678910,'北京顺义区 ','2018-10-01 07:00:00'),\
(10000,'lss','北京',20,0,12345678910,'北京海淀区','2017-11-15 06:10:20');
insert into test.user1 values\
(10000,'zss','北京',18,0,12345678910,'北京朝阳区 ','2017-10-01 07:00:00'),\
(10000,'zss','北京',19,0,12345678910,'北京顺义区 ','2018-10-01 07:00:00'),\
(10000,'lss','北京',20,0,12345678910,'北京海淀区','2017-11-15 06:10:20');
查询结果后发现，相同的数据就会被替换掉
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vl8AzlAJ-1677043665256)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222130954872.png)]
Uniq 模型完全可以用聚合模型中的 REPLACE 方式替代。其内部的实现方式和数据存储方式也完全一样。
3.3.3 Duplicate 模型
就是存明细数据的表模型，既不做聚合也不做去重。在某些多维分析场景下，数据既没有主键，也没有聚合需求。Duplicate 数据模型可以满足这类需求。数据完全按照导入文件中的数据进行存储，不会有任何聚合。即使两行数据完全相同，也都会保留。 而在建表语句中指定的 DUPLICATE KEY，只是用来指明底层数据按照那些列进行排序。
建表语句：
CREATE TABLE IF NOT EXISTS test.log_detail
(
`timestamp` DATETIME NOT NULL COMMENT "日志时间",
`type` INT NOT NULL COMMENT "日志类型",
`error_code` INT COMMENT "错误码",
`error_msg` VARCHAR(1024) COMMENT "错误详细信息",
`op_id` BIGINT COMMENT "负责人 id",
`op_time` DATETIME COMMENT "处理时间" )
DUPLICATE KEY(`timestamp`, `type`)
DISTRIBUTED BY HASH(`timestamp`) BUCKETS 1;
插入部分数据
insert into test.log_detail values\
('2017-10-01 08:00:05',1,404,'not found page', 101, '201e7-10-01 08:00:05'),\
('2017-10-01 08:00:05',1,404,'not found page', 101, '2017-10-01 08:00:05'),\
('2017-10-01 08:00:05',2,404,'not found page', 101, '2017-10-01 08:00:06'),\
('2017-10-01 08:00:06',2,404,'not found page', 101, '2017-10-01 08:00:07');
查询结果后发现，插入的数据全部会被保留，即使两条数据一模一样，也会保留，正常可以操作用户行为日志数据这种
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZTuE18VM-1677043665256)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131011348.png)]
3.3.4 数据模型的选择
数据模型在建表时就已经确定，且**无法修改****；**所以，选择一个合适的数据模型非常重要。
- Aggregate 模型可以通过预聚合，极大地降低聚合查询时所需扫描的数据量和查询的计算量，非常适合有固定模式的报表类查询场景。
- Uniq 模型针对需要唯一主键约束的场景，可以保证主键唯一性约束。但是无法利用 ROLLUP 等预聚合带来的查询优势（因为本质是 REPLACE，没有 SUM 这种聚合方式）。
- Duplicate 适合任意维度的查询。虽然同样无法利用预聚合的特性，但是不受聚合模型的约束，可以发挥列存模型的优势（只读取相关列，而不需要读取所有 Key 列）
模型建表练习
业务数据：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bCPvBeTz-1677043665257)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134445236.png)]
-- 需求：针对业务数据选择合适的表模型建表
-- 要求：
-- 需要保留明细数据
-- 场景：经常会根据用户和订单取查询该用户一个订单的总金额
-- 建表的时候建什么模型比较合适？？？？ 要建明细模型
我就想用聚合模型，就不用明细模型，但是我还想要明细数据==》保证key列是唯一的
3.4 索引
索引用于帮助快速过滤或查找数据。
目前 Doris 主要支持两类索引：
- 内建的智能索引：包括前缀索引和 ZoneMap 索引。
- 用户创建的二级索引：包括 Bloom Filter 索引 和 Bitmap倒排索引。
其中 ZoneMap 索引是在列存格式上，对每一列自动维护的索引信息，包括 Min/Max，Null 值个数等等。这种索引对用户透明。
3.4.1 前缀索引
doris中，对于前缀索引有如下约束：
- 他的索引键最大长度是36个字节
- 当他遇到了varchar数据类型的时候，即使没有超过36个字节，也会自动截断
- 示例1:以下表中我们定义了: user_id,age,message作为表的key ；
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hGJFFngM-1677043665257)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134607505.png)]
那么，doris为这个表创建前缀索引时，它生成的索引键如下：
user_id(8 Bytes) + age(4 Bytes) + message(prefix 24 Bytes）
- 示例2:以下表中我们定义了：age，user_name，message作为表的key
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aJfTiGVK-1677043665258)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134623340.png)]
那么，doris为这个表创建前缀索引时，它生成的索引键如下：
age(4 Bytes) +user_name(20 Bytes) 指定key的时候
为什么是这个结果呢？
虽然还没有超过36个字节，但是已经遇到了一个varchar字段，它自动截断，不会再往后面取了
当我们的查询条件，是前缀索引的前缀时，可以极大的加快查询速度。比如在第一个例子中，我们执行如下查询：
SELECT * FROM table WHERE user_id=1829239 and age=20
该查询的效率会远高于以下查询：
SELECT * FROM table WHERE age=20；
所以在建表时，正确的选择列顺序，能够极大地提高查询效率。
3.4.2 Bloom Filter 索引
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5psJ83KZ-1677043665258)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134653978.png)]
小总结：
- Bloom Filter 本质上是一种位图结构，用于判断一个值是否存在
- 会产生小概率的误判，因为hash算法天生的碰撞
- 在doris中是以tablet为粒度创建的，给每一个tablet创建一个布隆过滤器索引
如何创建BloomFilter索引?
- 建表的时候指定
PROPERTIES (
"bloom_filter_columns"="name,age"
)
- alter修改表的时候指定
ALTER TABLE sale_detail_bloom SET ("bloom_filter_columns" = "k1,k3");
ALTER TABLE sale_detail_bloom SET ("bloom_filter_columns" = "k1,k4");
建表示例：
CREATE TABLE IF NOT EXISTS sale_detail_bloom (
sale_date date NOT NULL COMMENT "销售时间",
customer_id int NOT NULL COMMENT "客户编号",
saler_id int NOT NULL COMMENT "销售员",
sku_id int NOT NULL COMMENT "商品编号",
category_id int NOT NULL COMMENT "商品分类",
sale_count int NOT NULL COMMENT "销售数量",
sale_price DECIMAL(12,2) NOT NULL COMMENT "单价",
sale_amt DECIMAL(20,2) COMMENT "销售总金额"
)
Duplicate KEY(sale_date, customer_id,saler_id,sku_id,category_id)
PARTITION BY RANGE(sale_date)
(
PARTITION P_202111 VALUES [('2021-11-01'), ('2021-12-01'))
)
DISTRIBUTED BY HASH(saler_id) BUCKETS 1
PROPERTIES (
"replication_num" = "1",
"bloom_filter_columns"="saler_id,category_id",
"storage_medium" = "SSD"
);
查看BloomFilter索引
mysql> SHOW CREATE TABLE sale_detail_bloom \G;
*************************** 1. row ***************************
Table: sale_detail_bloom
Create Table: CREATE TABLE `sale_detail_bloom` (
`sale_date` date NOT NULL COMMENT "销售时间",
`customer_id` int(11) NOT NULL COMMENT "客户编号",
`saler_id` int(11) NOT NULL COMMENT "销售员",
`sku_id` int(11) NOT NULL COMMENT "商品编号",
`category_id` int(11) NOT NULL COMMENT "商品分类",
`sale_count` int(11) NOT NULL COMMENT "销售数量",
`sale_price` decimal(12, 2) NOT NULL COMMENT "单价",
`sale_amt` decimal(20, 2) NULL COMMENT "销售总金额"
) ENGINE=OLAP
DUPLICATE KEY(`sale_date`, `customer_id`, `saler_id`, `sku_id`, `category_id`)
COMMENT "OLAP"
PARTITION BY RANGE(`sale_date`)
(PARTITION P_202111 VALUES [('2021-11-01'), ('2021-12-01')),
PARTITION P_202208 VALUES [('2022-08-01'), ('2022-09-01')),
PARTITION P_202209 VALUES [('2022-09-01'), ('2022-10-01')),
PARTITION P_202210 VALUES [('2022-10-01'), ('2022-11-01')))
DISTRIBUTED BY HASH(`saler_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"bloom_filter_columns" = "category_id, saler_id"
)
1 row in set (0.00 sec)
修改/删除BloomFilter索引
ALTER TABLE sale_detail_bloom SET ("bloom_filter_columns" = "");
Doris BloomFilter适用场景
满足以下几个条件时可以考虑对某列建立Bloom Filter 索引：
- BloomFilter是在无法利用前缀索引的查询场景中，来加快查询速度的。
- 查询会根据该列高频过滤，而且查询条件大多是 in 和 = 过滤。
- 不同于Bitmap, BloomFilter适用于高基数列。比如UserID。因为如果创建在低基数的列上，比如 “性别” 列，则每个Block几乎都会包含所有取值，导致BloomFilter索引失去意义。字段随机
Doris BloomFilter使用注意事项
- 不支持对Tinyint、Float、Double 类型的列建Bloom Filter索引。
- Bloom Filter索引只对 in 和 = 过滤查询有加速效果。
- 可以通过explain来查看命中了哪种索引 --没办法查看
3.4.3 Bitmap 索引
用户可以通过创建bitmap index 加速查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KPxR2qbn-1677043665259)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134725055.png)]
创建索引
在table1 上为siteid 创建bitmap 索引
CREATE INDEX [IF NOT EXISTS] index_name ON table1 (siteid) USING BITMAP COMMENT 'balabala';
create index user_id_bitmap on sale_detail_bloom(sku_id) USING BITMAP COMMENT '使用user_id创建的bitmap索引';
查看索引
SHOW INDEX FROM example_db.table_name;
删除索引
DROP INDEX [IF EXISTS] index_name ON [db_name.]table_name;
注意事项
- bitmap 索引仅在单列上创建。
- bitmap 索引能够应用在 Duplicate、Uniq 数据模型的所有列和 Aggregate模型的key列上。
- bitmap 索引支持的数据类型如下:(老版本只支持bitmap类型)
- TINYINT，SMALLINT，INT，BIGINT，CHAR，VARCHAR，DATE，DATETIME，LARGEINT，DECIMAL，BOOL
- bitmap索引仅在 Segment V2 下生效(Segment V2是升级版本的文件格式)。当创建 index 时，表的存储格式将默认转换为 V2 格式
小练习：建表，并且指定索引练习
-- 数据
uid name age gender province term
1 zss 18 male jiangsu 1
2 lss 16 male zhejiang 2
3 ww 19 male jiangsu 1
4 zll 18 female zhejiang 3
5 tqq 17 female jiangsu 2
6 aa 18 female jiangsu 2
7 bb 17 male zhejiang 3
提要求：
这张表，以后需要经常按照如下条件查询
-- 前缀索引 key ==》 term province
where term =??
where term =?? and province = ??
-- 布隆过滤器索引
where name=??
-- bitmap索引
where uid=??
SET GLOBAL enable_profile=true;
--主要是索引怎么建
create table stu(
term int,
province varchar(100),
uid int,
name varchar(30),
age int,
gender varchar(30)
)
engine = olap
duplicate key(term ,province)
distributed by hash(uid) buckets 2
properties(
"bloom_filter_columns"="name"
);
3.5 Rollup
ROLLUP 在多维分析中是“上卷”的意思，即将数据按某种指定的粒度进行进一步聚合。
之前的聚合模型：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tnMUycfh-1677043665259)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134752483.png)]
1.求每个城市的每个用户的每天的总销售额
select user_id,city,date， sum(sum_cost) as sum_cost from t group by user_id,city,date -- user_id date city sum_cost 10000 2017/10/2 北京 195 10000 2017/10/1 上海 100 10000 2017/10/2 上海 30 10000 2017/10/3 上海 55 10000 2017/10/4 上海 65 10001 2017/10/1 上海 30 10001 2017/10/2 上海 10 10001 2017/10/2 天津 18 10001 2017/10/1 天津 46 10002 2017/10/1 天津 55 10002 2017/10/3 北京 55 10002 2017/10/2 天津 20 10002 2017/10/2 北京 352.求每个用户、每个城市的总消费额
select user_id,city， sum(sum_cost) as sum_cost from t group by user_id,city user_id city sum_cost 10000 北京 195 10000 上海 100 10001 上海 40 10001 天津 64 10002 天津 75 10002 北京 903.求每个用户的总消费额
select user_id， sum(sum_cost) as sum_cost from t group by user_id user_id sum_cost 10000 295 10001 104 10002 165
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TCFKH3Pz-1677043665260)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221134817607.png)]
3.5.1基本概念
通过建表语句创建出来的表称为 Base 表（Base Table,基表）
在 Base 表之上，我们可以创建任意多个 ROLLUP 表。这些 ROLLUP 的数据是基于 Base 表产生的，并且在物理上是独立存储的。
Rollup表的好处：
- 和基表共用一个表名，doris会根据具体的查询逻辑选择合适的数据源(合适的表)来计算结果
- 对于基表中数据的增删改，rollup表会自动更新同步
3.5.2 Aggregate 模型中的 ROLLUP
查看下之前建得一张表：
mysql> desc ex_user all;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EnaolYHZ-1677043665260)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131050734.png)]
示例1：查看某个用户的总消费
添加一个roll up
alter table ex_user add rollup rollup_ucd_cost(user_id,city,date,cost);
alter table ex_user add rollup rollup_u_cost(user_id,cost);
alter table ex_user add rollup rollup_cd_cost(city,date,cost);
alter table ex_user drop rollup rollup_u_cost;
alter table ex_user drop rollup rollup_cd_cost;
--如果是replace聚合类型得value，需要指定所有得key
-- alter table ex_user add rollup rollup_cd_visit(city,date,last_visit_date);
-- ERROR 1105 (HY000): errCode = 2, detailMessage = Rollup should contains
-- all keys if there is a REPLACE value
--添加完成之后可以show一下，看看底层得rollup有没有执行完成
SHOW ALTER TABLE ROLLUP;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZU8wNNgl-1677043665260)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131108877.png)]
再次查看该表得详细信息后发现，多了一个IndexName为rollup_cost_userid（这是我们自己取得roll
Up 名字）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bC4jXCRO-1677043665261)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131125683.png)]
Doris 会自动命中这个 ROLLUP 表，从而只需扫描极少的数据量，即可完成这次聚合查询。
explain SELECT user_id, sum(cost) FROM ex_user GROUP BY user_id;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Eb4sWESo-1677043665261)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131146417.png)]
示例 2：获得不同城市，不同年龄段用户的总消费、最长和最短页面驻留时间
alter table ex_user add rollup rollup_city(city,age,cost,max_dwell_time,min_dwell_time);
-- 当创建好了立即去查看得时候就会发现，他还没有开始
SHOW ALTER TABLE ROLLUP;
然后过会再去查询得时候，他就完成了，看他的状态即可
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TtDF4s56-1677043665261)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131206108.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RGgvXvNb-1677043665262)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131218668.png)]
分别执行下面得三条语句，看看有什么不一样的？？
explain SELECT city, age, sum(cost), max(max_dwell_time), min(min_dwell_time) FROM ex_user GROUP BY city, age;
explain SELECT city, sum(cost), max(max_dwell_time), min(min_dwell_time) FROM ex_user GROUP BY city;
explain SELECT city, age, sum(cost), min(min_dwell_time) FROM ex_user GROUP BY city, age;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ol6ikudS-1677043665262)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131236502.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oKgr552V-1677043665262)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131249834.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AfYerFGa-1677043665263)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131303344.png)]
很显然得发现，维度是city，或者age，或者他们组合得时候，都是可以命中这个rollup得，相对来说效率会高很多
3.5.3 Unique 模型中的 ROLLUP 没有什么意义
unique模型示例表
drop table if exists test.user;
CREATE TABLE IF NOT EXISTS test.user
(
`user_id` LARGEINT NOT NULL COMMENT "用户 id",
`username` VARCHAR(50) NOT NULL COMMENT "用户昵称",
`city` VARCHAR(20) COMMENT "用户所在城市",
`age` SMALLINT COMMENT "用户年龄",
`sex` TINYINT COMMENT "用户性别",
`phone` LARGEINT COMMENT "用户电话",
`address` VARCHAR(500) COMMENT "用户地址",
`register_time` DATETIME COMMENT "用户注册时间" )
UNIQUE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;
插入语句
insert into test.user values\
(10000,'zss','北京',18,0,12345678910,'北京朝阳区 ','2017-10-01 07:00:00'),\
(10000,'zss','北京',19,0,12345678910,'北京朝阳区 ','2017-10-01 07:00:00'),\
(10000,'lss','北京',20,0,12345678910,'北京海淀区','2017-11-15 06:10:20');
很显然，里面的数据是这样的
mysql> select * from user;
+---------+----------+--------+------+------+-------------+------------------+---------------------+
| user_id | username | city | age | sex | phone | address | register_time |
+---------+----------+--------+------+------+-------------+------------------+---------------------+
| 10000 | lss | 北京 | 20 | 0 | 12345678910 | 北京海淀区 | 2017-11-15 06:10:20 |
| 10000 | zss | 北京 | 19 | 0 | 12345678910 | 北京朝阳区 | 2017-10-01 07:00:00 |
+---------+----------+--------+------+------+-------------+------------------+---------------------+
在unique模型中做rollup表，rollup的key必须**延用base表中所有的key，**不同的是value可以随意指定
alter table user add rollup rollup_username_id(username,user_id,age);
所以说，unique模型中建立rollup表没有什么太多的意义
试想一下：
如果不沿用base表中所有的key，只针对上面的user_id进行rollup，那么他的age值取20还是19呢？好像也就不确定了，毕竟底层的aggregationType 用的是replace，到底谁替换谁就不确定了
3.5.4 Duplicate 模型中的 ROLLUP
因为 Duplicate 模型没有聚合的语意。所以该模型中的 ROLLUP，已经失去了“上卷” 这一层含义。而仅仅是作为调整列顺序，以命中前缀索引的作用。下面详细介绍前缀索引，以及如何使用 ROLLUP 改变前缀索引，以获得更好的查询效率。
ROLLUP 调整前缀索引（新增一套前缀索引）
因为建表时已经指定了列顺序，所以一个表只有一种前缀索引。这对于使用其他不能命中前缀索引的列作为条件进行的查询来说，效率上可能无法满足需求。因此，我们可以通过创建 ROLLUP 来人为的调整列顺序。
Base 表结构如下：
ColumnNameTypeuser_idBIGINTageINTmessageVARCHAR(100)max_dwell_timeDATETIMEmin_dwell_timeDATETIME
我们可以在此基础上创建一个 ROLLUP 表：
ColumnNameTypeageINTuser_idBIGINTmessageVARCHAR(100)max_dwell_timeDATETIMEmin_dwell_timeDATETIME
可以看到，ROLLUP 和 Base 表的列完全一样，只是将 user_id 和 age 的顺序调换了。那么当我们进行如下查询时：
SELECT * FROM table where age=20 and message LIKE "%error%";
会优先选择 ROLLUP 表，因为 ROLLUP 的前缀索引匹配度更高。
示例：针对上面的log_detail这张基表添加两个rollup表
按照type 和error_code 进行建前缀索引
alter table log_detail add rollup rollup_tec(type,error_code,timestamp,error_msg,op_id,op_time);
alter table log_detail drop rolluprollup_tec
按照op_id和error_code 进行建前缀索引
alter table log_detail add rollup rollup_oec(op_id,error_code,timestamp,type,error_msg,op_time);
查看表中基表和rollup表
mysql> desc log_detail all;
+------------+---------------+------------+---------------+------+-------+---------+-------+---------+
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra | Visible |
+------------+---------------+------------+---------------+------+-------+---------+-------+---------+
| log_detail | DUP_KEYS | timestamp | DATETIME | No | true | NULL | | true |
| | | type | INT | No | true | NULL | | true |
| | | error_code | INT | Yes | false | NULL | NONE | true |
| | | error_msg | VARCHAR(1024) | Yes | false | NULL | NONE | true |
| | | op_id | BIGINT | Yes | false | NULL | NONE | true |
| | | op_time | DATETIME | Yes | false | NULL | NONE | true |
| | | | | | | | | |
| rollup_oec | DUP_KEYS | op_id | BIGINT | Yes | true | NULL | | true |
| | | error_code | INT | Yes | true | NULL | | true |
| | | timestamp | DATETIME | No | true | NULL | | true |
| | | type | INT | No | false | NULL | NONE | true |
| | | error_msg | VARCHAR(1024) | Yes | false | NULL | NONE | true |
| | | op_time | DATETIME | Yes | false | NULL | NONE | true |
| | | | | | | | | |
| rollup_tec | DUP_KEYS | type | INT | No | true | NULL | | true |
| | | error_code | INT | Yes | true | NULL | | true |
| | | timestamp | DATETIME | No | true | NULL | | true |
| | | error_msg | VARCHAR(1024) | Yes | false | NULL | NONE | true |
| | | op_id | BIGINT | Yes | false | NULL | NONE | true |
| | | op_time | DATETIME | Yes | false | NULL | NONE | true |
+------------+---------------+------------+---------------+------+-------+---------+-------+---------+
示例：看如下sql会命中哪一张表
explain select * from log_detail where type = 1;
explain select * from log_detail where type = 1 and error_code = 404;
explain select * from log_detail where op_id = 101 ;
explain select * from log_detail where op_id = 101 and error_code = 404;
explain select * from log_detail where timestamp = '2017-10-01 08:00:05' ;
explain select * from log_detail where timestamp = '2017-10-01 08:00:05' and type = 1;
ROLLUP使用说明
- ROLLUP 是附属于 Base 表的,用户可以在 Base 表的基础上，创建或删除 ROLLUP，但是不能在查询中显式的指定查询某 ROLLUP。是否命中 ROLLUP 完全由 Doris 系统自动决定。
- ROLLUP 的数据是独立物理存储的。因此，创建的 ROLLUP 越多，占用的磁盘空间也就越大。同时对导入速度也会有影响，但是不会降低查询效率（只会更好）。
- ROLLUP 的数据更新与 Base 表是完全同步的。用户无需关心这个问题。
- 在聚合模型中，ROLLUP 中列的聚合方式，与 Base 表完全相同。在创建 ROLLUP 无需指定，也不能修改。
- 可以通过 EXPLAIN your_sql; 命令获得查询执行计划，在执行计划中，查看是否命中 ROLLUP。
- 可以通过 DESC tbl_name ALL; 语句显示 Base 表和所有已创建完成的 ROLLUP
3.6 物化视图
就是查询结果预先存储起来的特殊的表。物化视图的出现主要是为了满足用户，既能对原始明细数据的任意维度分析，也能快速的对固定维度进行分析查询
3.6**.1** 优势
- 可以复用预计算的结果来提高查询效率
- 自动实时的维护物化视图表中的结果数据，无需额外人工成本(自动维护会有计算资源的开销)
- 查询时，会自动选择最优物化视图
3.6**.2** 物化视图 VS Rollup
- 明细模型表下，rollup和物化视图的差别：
物化视图：都可以实现预聚合，新增一套前缀索引
rollup：对于明细模型，新增一套前缀索引
- 聚合模型下，功能一致
3.6**.3** 创建物化视图
语法：
CREATE MATERIALIZED VIEW [MV name] as
[query] -- sql逻辑
--[MV name]：雾化视图的名称
--[query]：查询条件，基于base表创建雾化视图的逻辑
物化视图创建成功后，用户的查询不需要发生任何改变，也就是还是查询的 base 表。Doris 会根据当前查询的语句去自动选择一个最优的物化视图，从物化视图中读取数据并计算。
用户可以通过 EXPLAIN 命令来检查当前查询是否使用了物化视图。
3.6**.4** 案例演示
创建一个 Base 表：
用户有一张销售记录明细表，存储了每个交易的交易id，销售员，售卖门店，销售时间，以及金额
create table sales_records(
record_id int,
seller_id int,
store_id int,
sale_date date,
sale_amt bigint)
duplicate key (record_id,seller_id,store_id,sale_date)
distributed by hash(record_id) buckets 2
properties("replication_num" = "1");
-- 插入数据
insert into sales_records values \
(1,1,1,'2022-02-02',100),\
(2,2,1,'2022-02-02',200),\
(3,3,2,'2022-02-02',300),\
(4,3,2,'2022-02-02',200),\
(5,2,1,'2022-02-02',100),\
(6,4,2,'2022-02-02',200),\
(7,7,3,'2022-02-02',300),\
(8,2,1,'2022-02-02',400),\
(9,9,4,'2022-02-02',100);
如果用户需要经常对不同门店的销售量进行统计
第一步：创建一个物化视图
-- 不同门店，看总销售额的一个场景
select store_id, sum(sale_amt)
from sales_records
group by store_id;
CREATE MATERIALIZED VIEW store_id_sale_amonut as
select store_id, sum(sale_amt)
from sales_records
group by store_id;
CREATE MATERIALIZED VIEW store_amt as
select store_id, sum(sale_amt) as sum_amount
from sales_records
group by store_id;
--针对上述场景做一个物化视图
create materialized view store_amt as
select store_id, sum(sale_amt) as sum_amount
from sales_records
group by store_id;
第二步：检查物化视图是否构建完成(物化视图的创建是个异步的过程)
show alter table materialized view from 库名 order by CreateTime desc limit 1;
show alter table materialized view from test order by CreateTime desc limit 1;
+-------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| JobId | TableName | CreateTime | FinishTime | BaseIndexName | RollupIndexName | RollupId | TransactionId | State | Msg | Progress | Timeout |
+-------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| 15093 | sales_records | 2022-11-25 10:32:33 | 2022-11-25 10:32:59 | sales_records | store_amt | 15094 | 3008 | FINISHED | | NULL | 86400 |
+-------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
查看 Base 表的所有物化视图
desc sales_records all;
+---------------+---------------+-----------+--------+------+-------+---------+-------+---------+
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra | Visible |
+---------------+---------------+-----------+--------+------+-------+---------+-------+---------+
| sales_records | DUP_KEYS | record_id | INT | Yes | true | NULL | | true |
| | | seller_id | INT | Yes | true | NULL | | true |
| | | store_id | INT | Yes | true | NULL | | true |
| | | sale_date | DATE | Yes | true | NULL | | true |
| | | sale_amt | BIGINT | Yes | false | NULL | NONE | true |
| | | | | | | | | |
| store_amt | AGG_KEYS | store_id | INT | Yes | true | NULL | | true |
| | | sale_amt | BIGINT | Yes | false | NULL | SUM | true |
+---------------+---------------+-----------+--------+------+-------+---------+-------+---------+
第三步：查询
看是否命中刚才我们建的物化视图
EXPLAIN SELECT store_id, sum(sale_amt) FROM sales_records GROUP BY store_id;
+------------------------------------------------------------------------------------+
| Explain String |
+------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:<slot 2> `store_id` | <slot 3> sum(`sale_amt`) |
| PARTITION: UNPARTITIONED |
| |
| VRESULT SINK |
| |
| 4:VEXCHANGE |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: <slot 2> `store_id` |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:VAGGREGATE (merge finalize) |
| | output: sum(<slot 3> sum(`sale_amt`)) |
| | group by: <slot 2> `store_id` |
| | cardinality=-1 |
| | |
| 2:VEXCHANGE |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: HASH_PARTITIONED: `default_cluster:study`.`sales_records`.`record_id` |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: <slot 2> `store_id` |
| |
| 1:VAGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(`sale_amt`) |
| | group by: `store_id` |
| | cardinality=-1 |
| | |
| 0:VOlapScanNode |
| TABLE: sales_records(store_amt), PREAGGREGATION: ON |
| partitions=1/1, tablets=10/10, tabletList=15095,15097,15099 ... |
| cardinality=7, avgRowSize=1560.0, numNodes=3 |
+------------------------------------------------------------------------------------+
删除物化视图语法
-- 语法：
DROP MATERIALIZED VIEW 物化视图名 on base_table_name;
--示例：
drop materialized view store_amt on sales_records;
案例一：计算广告的 pv、uv
用户有一张点击广告的明细数据表
需求：针对用户点击计广告明细数据的表，算每天，每个页面，每个渠道的 pv,uv
pv:page view，页面浏览量或点击量
uv:unique view，通过互联网访问、浏览这个网页的自然人
drop table if exists ad_view_record;
create table ad_view_record(
dt date,
ad_page varchar(10),
channel varchar(10),
refer_page varchar(10),
user_id int
)
distributed by hash(dt)
properties("replication_num" = "1");
select
dt,ad_page,channel,
count(refer_page) as pv,
count(distinct user_id ) as uv
from ad_view_record
group by dt,ad_page,channel
插入数据
insert into ad_view_record values \
('2020-02-02','a','app','/home',1),\
('2020-02-02','a','web','/home',1),\
('2020-02-02','a','app','/addbag',2),\
('2020-02-02','b','app','/home',1),\
('2020-02-02','b','web','/home',1),\
('2020-02-02','b','app','/addbag',2),\
('2020-02-02','b','app','/home',3),\
('2020-02-02','b','web','/home',3),\
('2020-02-02','c','app','/order',1),\
('2020-02-02','c','app','/home',1),\
('2020-02-03','c','web','/home',1),\
('2020-02-03','c','app','/order',4),\
('2020-02-03','c','app','/home',5),\
('2020-02-03','c','web','/home',6),\
('2020-02-03','d','app','/addbag',2),\
('2020-02-03','d','app','/home',2),\
('2020-02-03','d','web','/home',3),\
('2020-02-03','d','app','/addbag',4),\
('2020-02-03','d','app','/home',5),\
('2020-02-03','d','web','/addbag',6),\
('2020-02-03','d','app','/home',5),\
('2020-02-03','d','web','/home',4);
创建物化视图
-- 怎么去计算pv，uv
select
dt,ad_page,channel,
count(ad_page) as pv,
count(distinct user_id) as uv
from ad_view_record
group by dt,ad_page,channel;
-- 1.物化视图中，不能够使用两个相同的字段
-- 2.在增量聚合里面，不能够使用count(distinct) ==> bitmap_union
-- 3.count(字段)
create materialized view dpc_pv_uv as
select
dt,ad_page,channel,
-- refer_page 没有null的情况
count(refer_page) as pv,
-- doris的物化视图中，不支持count(distint) ==> bitmap_union
-- count(distinct user_id) as uv
bitmap_union(to_bitmap(user_id)) uv_bitmap
from ad_view_record
group by dt,ad_page,channel;
create materialized view tpc_pv_uv as
select
dt,ad_page,channel,
count(refer_page) as pv,
-- refer_page 不能为null
-- count(user_id) as pv
-- count(1) as pv,
bitmap_union(to_bitmap(user_id)) as uv_bitmap
--count(distinct user_id) as uv
from ad_view_record
group by dt,ad_page,channel;
--结论：在doris的物化视图中，一个字段不能用两次,并且聚合函数后面必须跟字段名称
在 Doris 中，count(distinct) 聚合的结果和 bitmap_union_count 聚合的结果是完全一致的。而 bitmap_union_count 等于 bitmap_union 的结果求 count，所以如果查询中涉及到count(distinct) 则通过创建带 bitmap_union 聚合的物化视图方可加快查询。因为本身 user_id 是一个 INT 类型，所以在 Doris 中需要先将字段通过函数 to_bitmap 转换为 bitmap 类型然后才可以进行 bitmap_union 聚合。
查询自动匹配
explain
select
dt,ad_page,channel,
count(refer_page) as pv,
count(distinct user_id) as uv
from ad_view_record
group by dt,ad_page,channel;
会自动转换成。
explain
select
dt,ad_page,channel,
count(1) as pv,
bitmap_union_count(to_bitmap(user_id)) as uv
from ad_view_record
group by dt,ad_page,channel;
这个sql用的是哪张表呢？
explain
select
dt,ad_page,
count(refer_page) as pv,
count(distinct user_id) as uv
from ad_view_record
group by dt,ad_page;
TABLE: ad_view_record_1(tpc_pv_uv), PREAGGREGATION: ON
-- 很显然命中的是tpc_pv_uv 这个物化视图
当然，我们还可以根据日期和页面的维度再去创建一张物化视图
create materialized view tp_pv_uv as
select
dt,ad_page,
count(refer_page) as pv,
bitmap_union(to_bitmap(user_id)) as uv
from ad_view_record
group by dt,ad_page;
再去执行上面的sql，显然命中的就是tp_pv_uv这个物化视图
explain
select
dt,ad_page,
count(refer_page) as pv,
count(distinct user_id) as uv
from ad_view_record
group by dt,ad_page;
-- TABLE: ad_view_record_1(tp_pv_uv), PREAGGREGATION: ON
explain
select
dt,
count(refer_page) as pv,
count(distinct user_id) as uv
from ad_view_record
group by dt;
总结：
- 在创建doris的物化视图中，同一个字段不能被使用两次,并且聚合函数后面必须跟字段名称(不能使用count(1)这样的聚合逻辑)
- doris在选择使用哪一个物化视图表的时候，按照维度上卷的原则，选距离查询维度最接近，并且指标可以复用的物化视图
- 一张基表可以创建多个物化视图(计算资源占用比较多)
案例二：调整前缀索引
场景:用户的原始表有（k1, k2, k3）三列。其中 k1, k2 为前缀索引列。这时候如果用户查询条件中包含 where k1=1 and k2=2 就能通过索引加速查询。
但是有些情况下，用户的过滤条件无法匹配到前缀索引，比如 where k3=3。则无法通过索引提升查询速度。
解决方法：
创建以 k3 作为第一列的物化视图就可以解决这个问题。
查询
desc sales_records all;
+---------------+---------------+-----------+--------+------+-------+---------+-------+---------+
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra | Visible |
+---------------+---------------+-----------+--------+------+-------+---------+-------+---------+
| sales_records | DUP_KEYS | record_id | INT | Yes | true | NULL | | true |
| | | seller_id | INT | Yes | true | NULL | | true |
| | | store_id | INT | Yes | true | NULL | | true |
| | | sale_date | DATE | Yes | true | NULL | | true |
| | | sale_amt | BIGINT | Yes | false | NULL | NONE | true |
+---------------+---------------+-----------+--------+------+-------+---------+-------+---------+
5 rows in set (0.00 sec)
--针对上面的前缀索引情况，执行下面的sql是无法利用前缀索引的
explain
select record_id,seller_id,store_id from sales_records
where store_id=3;
创建物化视图
create materialized view sto_rec_sell as
select
store_id,
record_id,
seller_id,
sale_date,
sale_amt
from sales_records;
通过上面语法创建完成后，物化视图中既保留了完整的明细数据，且物化视图的前缀索
引为 store_id 列。
3）查看表结构
desc sales_records all;
+---------------+---------------+-----------+--------+------+-------+---------+-------+---------+
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra | Visible |
+---------------+---------------+-----------+--------+------+-------+---------+-------+---------+
| sales_records | DUP_KEYS | record_id | INT | Yes | true | NULL | | true |
| | | seller_id | INT | Yes | true | NULL | | true |
| | | store_id | INT | Yes | true | NULL | | true |
| | | sale_date | DATE | Yes | true | NULL | | true |
| | | sale_amt | BIGINT | Yes | false | NULL | NONE | true |
| | | | | | | | | |
| sto_rec_sell | DUP_KEYS | store_id | INT | Yes | true | NULL | | true |
| | | record_id | INT | Yes | true | NULL | | true |
| | | seller_id | INT | Yes | true | NULL | | true |
| | | sale_date | DATE | Yes | false | NULL | NONE | true |
| | | sale_amt | BIGINT | Yes | false | NULL | NONE | true |
+---------------+---------------+-----------+--------+------+-------+---------+-------+---------+
查询匹配
explain select record_id,seller_id,store_id from sales_records where store_id=3;
+------------------------------------------------------------------------------------+
| Explain String |
+------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:`record_id` | `seller_id` | `store_id` |
| PARTITION: UNPARTITIONED |
| |
| VRESULT SINK |
| |
| 1:VEXCHANGE |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: `default_cluster:study`.`sales_records`.`record_id` |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 01 |
| UNPARTITIONED |
| |
| 0:VOlapScanNode |
| TABLE: sales_records(sto_rec_sell), PREAGGREGATION: ON |
| PREDICATES: `store_id` = 3 |
| partitions=1/1, tablets=10/10, tabletList=15300,15302,15304 ... |
| cardinality=0, avgRowSize=12.0, numNodes=1 |
+------------------------------------------------------------------------------------+
这时候查询就会直接从刚才创建的sto_rec_sell物化视图中读取数据。物化视图对 store_id是存在前缀索引的，查询效率也会提升。
4.数据的导入导出
按照使用场景划分
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J9t7181D-1677043665263)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221135105219.png)]
使用 Insert 方式同步数据 - Apache Doris
4.1使用 Insert 方式同步数据
用户可以通过 MySQL 协议，使用 INSERT 语句进行数据导入。
INSERT 语句的使用方式和 MySQL 等数据库中 INSERT 语句的使用方式类似。 INSERT 语句支持以下两种语法：
* INSERT INTO table SELECT ...
* INSERT INTO table VALUES(...)
对于 Doris 来说，一个 INSERT 命令就是一个完整的导入事务。
因此不论是导入一条数据，还是多条数据，我们都不建议在生产环境使用这种方式进行数据导入。高频次的 INSERT 操作会导致在存储层产生大量的小文件，会严重影响系统性能。
该方式仅用于线下简单测试或低频少量的操作。
或者可以使用以下方式进行批量的插入操作：
INSERT INTO example_tbl VALUES
(1000, "baidu1", 3.25)
(2000, "baidu2", 4.25)
(3000, "baidu3", 5.25);
4.2导入本地数据
Stream Load 用于将本地文件导入到doris中。Stream Load 是通过 HTTP 协议与 Doris 进行连接交互的。
该方式中涉及 HOST:PORT 都是对应的HTTP 协议端口。
- BE 的 HTTP 协议端口，默认为 8040。
- FE 的 HTTP 协议端口，默认为 8030。
但须保证客户端所在机器网络能够联通FE, BE 所在机器。
基本原理：
| |
| | 1. User submit load to FE 1.提交导入请求
| |
| +--v-----------+
| | FE | 生成导入计划
4. Return result to user | +--+-----------+
| |
| | 2. Redirect to BE 下发给每一个BE节点
| |
| +--v-----------+
+---+Coordinator BE| 1B. User submit load to BE
+-+-----+----+-+
| | |
+-----+ | +-----+
| | | 3. Distrbute data 分发数据并导入
| | |
+-v-+ +-v-+ +-v-+
|BE | |BE | |BE |
+---+ +---+ +---+
- 创建一张表
建表语句：
drop table if exists load_local_file_test;
CREATE TABLE IF NOT EXISTS load_local_file_test
(
id INT,
name VARCHAR(50),
age TINYINT
)
unique key(id)
DISTRIBUTED BY HASH(id) BUCKETS 3;
1,zss,28
2,lss,28
3,ww,88
- 导入数据
执行 curl 命令导入本地文件（这个命令不是在mysql端执行的哦）：
# 语法示例
curl \
-u user:passwd \ # 账号密码
-H "label:load_local_file_test" \ # 本次任务的唯一标识
-T 文件地址 \
http://主机名:端口号/api/库名/表名/_stream_load
curl \
-u root:123456 \
-H "label:load_local_file" \
-H "column_separator:," \
-T /root/data/loadfile.txt \
http://zuomm01:8040/api/test/load_local_file_test/_stream_load
- user:passwd 为在 Doris 中创建的用户。初始用户为 admin / root，密码初始状态下为空。
- host:port 为 BE 的 HTTP 协议端口，默认是 8040，可以在 Doris 集群 WEB UI页面查看。
- label: 可以在 Header 中指定 Label 唯一标识这个导入任务。
- 等待导入结果
-- 这是失败的
[root@zuomm01 data]# curl \
> -u root:123456 \
> -H "label:load_local_file" \
> -T /root/data/loadfile.txt \
> http://zuomm01:8040/api/test/load_local_file_test/_stream_load
{
"TxnId": 1004,
"Label": "load_local_file",
"TwoPhaseCommit": "false",
"Status": "Fail",
"Message": "too many filtered rows",
"NumberTotalRows": 4,
"NumberLoadedRows": 0,
"NumberFilteredRows": 4,
"NumberUnselectedRows": 0,
"LoadBytes": 36,
"LoadTimeMs": 82,
"BeginTxnTimeMs": 13,
"StreamLoadPutTimeMs": 56,
"ReadDataTimeMs": 0,
"WriteDataTimeMs": 9,
"CommitAndPublishTimeMs": 0,
"ErrorURL": "http://192.168.17.3:8040/api/_load_error_log?file=__shard_0/error_log_insert_stmt_cf4aa4d10e8d5fc5-458f16b70f0f2e87_cf4aa4d10e8d5fc5_458f16b70f0f2e87"
}
-- 这是成功的
[root@zuomm01 data]# curl \
> -u root:123456 \
> -H "label:load_local_file" \
> -H "column_separator:," \
> -T /root/data/loadfile.txt \
> http://zuomm01:8040/api/test/load_local_file_test/_stream_load
{
"TxnId": 1005,
"Label": "load_local_file",
"TwoPhaseCommit": "false",
"Status": "Success",
"Message": "OK",
"NumberTotalRows": 4,
"NumberLoadedRows": 4,
"NumberFilteredRows": 0,
"NumberUnselectedRows": 0,
"LoadBytes": 36,
"LoadTimeMs": 54,
"BeginTxnTimeMs": 0,
"StreamLoadPutTimeMs": 2,
"ReadDataTimeMs": 0,
"WriteDataTimeMs": 14,
"CommitAndPublishTimeMs": 36
}
如果遇到错误了，可以根据他给定的错误的"ErrorURL"去查看错误的细节
SHOW LOAD WARNINGS ON "http://192.168.17.3:8040/api/_load_error_log?file=__shard_0/error_log_insert_stmt_cf4aa4d10e8d5fc5-458f16b70f0f2e87_cf4aa4d10e8d5fc5_458f16b70f0f2e87"
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zGpfA91a-1677043665264)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131420644.png)]
4.2.1curl的一些可配置的参数：
- label: 导入任务的标签，相同标签的数据无法多次导入。（标签默认保留30分钟）
- column_separator：用于指定导入文件中的列分隔符，默认为\t。
- line_delimiter：用于指定导入文件中的换行符，默认为\n。
- columns：用于指定文件中的列和table中列的对应关系，默认一一对应
例1: 表中有3个列“c1, c2, c3”，源文件中的三个列一次对应的是"c3,c2,c1"; 那么需要指定-H “columns: c3, c2, c1”
例2: 表中有3个列“c1, c2, c3", 源文件中前三列依次对应，但是有多余1列；那么需要指定-H “columns: c1, c2, c3, xxx”;最后一个列随意指定个名称占位即可
例3: 表中有3个列“year, month, day"三个列，源文件中只有一个时间列，为”2018-06-01 01:02:03“格式；那么可以指定
-H "columns: col, year = year(col), month=month(col), day=day(col)"完成导入
- where: 用来过滤导入文件中的数据
例1: 只导入大于k1列等于20180601的数据，那么可以在导入时候指定-H “where: k1 = 20180601”
- max_filter_ratio：最大容忍可过滤（数据不规范等原因）的数据比例。默认零容忍。数据不规范不包括通过 where 条件过滤掉的行。
- partitions: 用于指定这次导入所设计的partition。如果用户能够确定数据对应的partition，推荐指定该项。不满足这些分区的数据将被过滤掉。
比如指定导入到p1, p2分区，
-H “partitions: p1, p2”
- timeout: 指定导入的超时时间。单位秒。默认是 600 秒。可设置范围为 1 秒 ~ 259200 秒。
- timezone: 指定本次导入所使用的时区。默认为东八区。该参数会影响所有导入涉及的和时区有关的函数结果。
- exec_mem_limit: 导入内存限制。默认为 2GB。单位为字节。
- format: 指定导入数据格式，默认是csv，支持json格式。
- read_json_by_line: 布尔类型，为true表示支持每行读取一个json对象，默认值为false。
- merge_type: 数据的合并类型，一共支持三种类型APPEND、DELETE、MERGE 其中，APPEND是默认值，表示这批数据全部需要追加到现有数据中，DELETE 表示删除与这批数据key相同的所有行，MERGE 语义 需要与delete 条件联合使用，表示满足delete 条件的数据按照DELETE 语义处理其余的按照APPEND 语义处理， 示例：-H “merge_type: MERGE” -H “delete: flag=1”
- delete: 仅在 MERGE下有意义， 表示数据的删除条件 function_column.sequence_col: 只适用于UNIQUE_KEYS,相同key列下，保证value列按照source_sequence列进行REPLACE, source_sequence可以是数据源中的列，也可以是表结构中的一列。
-- 准备数据
{"id":1,"name":"liuyan","age":18}
{"id":2,"name":"tangyan","age":18}
{"id":3,"name":"jinlian","age":18}
{"id":4,"name":"dalang","age":18}
{"id":5,"name":"qingqing","age":18}
curl \
-u root: \
-H "label:load_local_file_json_20221126" \
-H "columns:id,name,age" \
-H "max_filter_ratio:0.1" \
-H "timeout:1000" \
-H "exec_mem_limit:1G" \
-H "where:id>1" \
-H "format:json" \
-H "read_json_by_line:true" \
-H "merge_type:delete" \
-T /root/data/json.txt \
http://zuomm01:8040/api/test/load_local_file_test/_stream_load
-H "merge_type:append" \
# 会把id = 3 的这条数据删除
-H "merge_type:MERGE" \
-H "delete:id=3"
导入建议
- Stream Load 只能导入本地文件。
- 建议一个导入请求的数据量控制在 1 - 2 GB 以内。如果有大量本地文件，可以分批并发提交。
4.3外部存储数据导入(hdfs)
4.3.1 适用场景
- 源数据在 Broker 可以访问的存储系统中，如 HDFS。
- 数据量在几十到百 GB 级别。
4.3.2 基本原理
- 创建提交导入的任务
- FE生成执行计划并将执行计划分发到多个BE节点上(每个BE节点都导入一部分数据)
- BE收到执行计划后开始执行，从broker上拉取数据到自己的节点上
- 所有BE都完成后，FE决定是否导入成功，返回结果给客户端
+
| 1. user create broker load
v
+----+----+
| |
| FE | 生成导入计划
| |
+----+----+
|
| 2. BE etl and load the data
+--------------------------+
| | |
+---v---+ +--v----+ +---v---+
| | | | | |
| BE | | BE | | BE |
| | | | | |
+---+-^-+ +---+-^-+ +--+-^--+
| | | | | |
| | | | | | 3. pull data from broker
+---v-+-+ +---v-+-+ +--v-+--+
| | | | | |
|Broker | |Broker | |Broker |
| | | | | |
+---+-^-+ +---+-^-+ +---+-^-+
| | | | | |
+---v-+-----------v-+----------v-+-+
| HDFS/BOS/AFS cluster |
+----------------------------------+
新建一张表
drop table if exists load_hdfs_file_test;
CREATE TABLE IF NOT EXISTS load_hdfs_file_test
(
id INT,
name VARCHAR(50),
age TINYINT
)
unique key(id)
DISTRIBUTED BY HASH(id) BUCKETS 3;
将本地的数据导入到hdfs上面
hadoop fs -put ./loadfile.txt hdfs://zuomm01:8020/
hadoop fs -ls hdfs://zuomm01:8020/
导入格式：
语法示例：
LOAD LABEL test.label_202204(
[MERGE|APPEND|DELETE] -- 不写就是append
DATA INFILE
(
"file_path1"[, file_path2, ...] -- 描述数据的路径 这边可以写多个 ，以逗号分割
)
[NEGATIVE] -- 负增长
INTO TABLE `table_name` -- 导入的表名字
[PARTITION (p1, p2, ...)] -- 导入到哪些分区，不符合这些分区的就会被过滤掉
[COLUMNS TERMINATED BY "column_separator"] -- 指定分隔符
[FORMAT AS "file_type"] -- 指定存储的文件类型
[(column_list)] -- 指定导入哪些列
[COLUMNS FROM PATH AS (c1, c2, ...)] -- 从路劲中抽取的部分列
[SET (column_mapping)] -- 对于列可以做一些映射，写一些函数
-- 这个参数要写在要写在set的后面
[PRECEDING FILTER predicate] -- 做一些过滤
[WHERE predicate] -- 做一些过滤 比如id>10
[DELETE ON expr] --根据字段去做一些抵消消除的策略 需要配合MERGE
[ORDER BY source_sequence] -- 导入数据的时候保证数据顺序
[PROPERTIES ("key1"="value1", ...)] -- 一些配置参数
将hdfs上的数据load到表中
LOAD LABEL test.label_20221125
(
DATA INFILE("hdfs://zuomm01:8020/test.txt")
INTO TABLE `load_hdfs_file_test`
COLUMNS TERMINATED BY ","
(id,name,age)
)
with HDFS (
"fs.defaultFS"="hdfs://zuomm01:8020",
"hadoop.username"="root"
)
PROPERTIES
(
"timeout"="1200",
"max_filter_ratio"="0.1"
);
这是一个异步的操作，所以需要去查看下执行的状态
show load order by createtime desc limit 1\G;
*************************** 1. row ***************************
JobId: 12143
Label: label_20220402
State: FINISHED
Progress: ETL:100%; LOAD:100%
Type: BROKER
EtlInfo: unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=4
TaskInfo: cluster:N/A; timeout(s):1200; max_filter_ratio:0.1
ErrorMsg: NULL
CreateTime: 2022-08-31 01:36:01
EtlStartTime: 2022-08-31 01:36:03
EtlFinishTime: 2022-08-31 01:36:03
LoadStartTime: 2022-08-31 01:36:03
LoadFinishTime: 2022-08-31 01:36:03
URL: NULL
JobDetails: {"Unfinished backends":{"702bc3732d804f60-aa4593551c6e577a":[]},"ScannedRows":4,"TaskNumber":1,"LoadBytes":139,"All backends":{"702bc3732d804f60-aa4593551c6e577a":[10004]},"FileNumber":1,"FileSize":36}
TransactionId: 1007
ErrorTablets: {}
1 row in set (0.00 sec)
--失败的案例：会有详细的错误信息，可以参考参考
mysql> show load order by createtime desc limit 1\G;
*************************** 1. row ***************************
JobId: 12139
Label: label_20220402
State: CANCELLED
Progress: ETL:N/A; LOAD:N/A
Type: BROKER
EtlInfo: NULL
TaskInfo: cluster:N/A; timeout(s):1200; max_filter_ratio:0.1
ErrorMsg: type:LOAD_RUN_FAIL; msg:errCode = 2, detailMessage = connect failed. hdfs://zuomm01
CreateTime: 2022-08-31 01:32:16
EtlStartTime: 2022-08-31 01:32:19
EtlFinishTime: 2022-08-31 01:32:19
LoadStartTime: 2022-08-31 01:32:19
LoadFinishTime: 2022-08-31 01:32:19
URL: NULL
JobDetails: {"Unfinished backends":{"4bd307c0bd564c45-b7df986d26569ffa":[]},"ScannedRows":0,"TaskNumber":1,"LoadBytes":0,"All backends":{"4bd307c0bd564c45-b7df986d26569ffa":[10004]},"FileNumber":1,"FileSize":36}
TransactionId: 1006
ErrorTablets: {}
1 row in set (0.01 sec)
4.3.3 参数的说明
- load_label：导入任务的唯一 Label
- [MERGE|APPEND|DELETE]：数据合并类型。默认为 APPEND，表示本次导入是普通的追加写操作。MERGE 和 DELETE 类型仅适用于 Unique Key 模型表。其中 MERGE 类型需要配合 [DELETE ON] 语句使用，以标注 Delete Flag 列。而 DELETE 类型则表示本次导入的所有数据皆为删除数据
- DATA INFILE：被导入文件的路径，可以为多个。
- NEGTIVE：该关键词用于表示本次导入为一批”负“导入。这种方式仅针对具有整型 SUM 聚合类型的聚合数据表。该方式会将导入数据中，SUM 聚合列对应的整型数值取反。主要用于冲抵之前导入错误的数据。
- PARTITION(p1, p2, …)：可以指定仅导入表的某些分区。不再分区范围内的数据将被忽略。
- COLUMNS TERMINATED BY：指定列分隔符
- FORMAT AS：指定要导入文件的类型，支持 CSV、PARQUET 和 ORC 格式。默认为 CSV。
- column list：用于指定原始文件中的列顺序。
- COLUMNS FROM PATH AS：指定从导入文件路径中抽取的列。
- PRECEDING FILTER：前置过滤条件。数据首先根据 column list 和 COLUMNS FROM PATH AS 按顺序拼接成原始数据行。然后按照前置过滤条件进行过滤。
- SET (column_mapping)：指定列的转换函数。
- WHERE predicate：根据条件对导入的数据进行过滤。
- DELETE ON expr：需配合 MEREGE 导入模式一起使用，仅针对 Unique Key 模型的表。用于指定导入数据中表示 Delete Flag 的列和计算关系。
- load_properties:指定导入的相关参数。目前支持以下参数：
- timeout:导入超时时间。默认为 4 小时。单位秒。
- max_filter_ratio:最大容忍可过滤（数据不规范等原因）的数据比例。默认零容忍。取值范围为0到1。
- exec_mem_limit:导入内存限制。默认为 2GB。单位为字节。
- strict_mode:是否对数据进行严格限制。默认为 false。
- timezone:指定某些受时区影响的函数的时区，如 strftime/alignment_timestamp/from_unixtime 等等，具体请查阅 时区 文档。如果不指定，则使用 “Asia/Shanghai” 时区
4.3.4 导入命令的进阶参数示例
从 HDFS 导入数据，使用通配符匹配两批两批文件。分别导入到两个表中
LOAD LABEL example_db.label2
(
DATA INFILE("hdfs://hdfs_host:hdfs_port/input/file-10*")
INTO TABLE `my_table1`
PARTITION (p1)
COLUMNS TERMINATED BY ","
FORMAT AS "parquet"
(id, tmp_salary, tmp_score)
SET (
salary= tmp_salary + 1000,
score = tmp_score + 10
),
DATA INFILE("hdfs://hdfs_host:hdfs_port/input/file-20*")
INTO TABLE `my_table2`
COLUMNS TERMINATED BY ","
(k1, k2, k3)
)
with HDFS (
"fs.defaultFS"="hdfs://zuomm01:8020",
"hadoop.username"="root"
)
导入数据，并提取文件路径中的分区字段
LOAD LABEL example_db.label10
(
DATA INFILE("hdfs://hdfs_host:hdfs_port/user/hive/warehouse/table_name/dt=20221125/*")
INTO TABLE `my_table`
FORMAT AS "csv"
(k1, k2, k3)
COLUMNS FROM PATH AS (dt)
)
WITH BROKER hdfs
(
"username"="hdfs_user",
"password"="hdfs_password"
);
对待导入数据进行过滤。
LOAD LABEL example_db.label6
(
DATA INFILE("hdfs://host:port/input/file")
INTO TABLE `my_table`
(k1, k2, k3)
SET (
k2 = k2 + 1
)
PRECEDING FILTER k1 = 1 ==》前置过滤
WHERE k1 > k2 ==》 后置过滤
)
WITH BROKER hdfs
(
"username"="user",
"password"="pass"
);
只有原始数据中，k1 = 1，并且转换后，k1 > k2 的行才会被导入。
4.3.5 取消导入任务
当 Broker load 作业状态不为 CANCELLED 或 FINISHED 时，可以被用户手动取消。
取消时需要指定待取消导入任务的 Label 。取消导入命令语法可执行 HELP CANCEL LOAD 查看。
CANCEL LOAD [FROM db_name] WHERE LABEL="load_label";
4.4通过外部表同步数据
Doris 可以创建外部表。创建完成后，可以通过 SELECT 语句直接查询外部表的数据，也可以通过 INSERT INTO SELECT 的方式导入外部表的数据。
Doris 外部表目前支持的数据源包括：MySQL，Oracle，Hive，PostgreSQL，SQLServer，Iceberg，ElasticSearch
4.4.1 整体语法
CREATE [EXTERNAL] TABLE table_name (
col_name col_type [NULL | NOT NULL] [COMMENT "comment"]
) ENGINE=HIVE
[COMMENT "comment"]
PROPERTIES (
-- 我要映射的hive表在哪个库里面
-- 映射的表名是哪一张
-- hive的元数据服务地址
'property_name'='property_value',
...
);
参数说明：
- 外表列
- 列名要与 Hive 表一一对应
- 列的顺序需要与 Hive 表一致
- 必须包含 Hive 表中的全部列
- Hive 表分区列无需指定，与普通列一样定义即可。
- ENGINE 需要指定为 HIVE
- PROPERTIES 属性：
- hive.metastore.uris：Hive Metastore 服务地址
- database：挂载 Hive 对应的数据库名
- table：挂载 Hive 对应的表名
4.4.2 使用示例
完成在 Doris 中建立 Hive 外表后，除了无法使用 Doris 中的数据模型(rollup、预聚合、物化视图等)外，与普通的 Doris OLAP 表并无区别
- 在Hive 中创建一个测试用****表：
CREATE TABLE `user_info` (
`id` int,
`name` string,
`age` int
) stored as orc;
insert into user_info values (1,'zss',18);
insert into user_info values (2,'lss',20);
insert into user_info values (3,'ww',25);
- Doris 中创建外部表
CREATE EXTERNAL TABLE `hive_user_info` (
`id` int,
`name` varchar(10),
`age` int
) ENGINE=HIVE
PROPERTIES (
'hive.metastore.uris' = 'thrift://linux01:9083',
'database' = 'db1',
'table' = 'user_info'
);
- 直接查询外部****表
外部表创建好后，就可以直接在doris中对这个外部表进行查询了
直接查询外部表，无法利用到doris自身的各种查询优化机制！
select * from hive_user_info;
- 将数据从外部表导入内部表
数据从外部表导入内部表后，就可以利用doris自身的查询优势了！
假设要导入的目标内部表为： doris_user_info (需要提前创建好哦！！)
-- 就是用sql查询，从外部表中select出数据后，insert到内部表即可
insert into doris_user_info
select
*
from hive_user_info;
注意：
Hive 表 Schema 变更不会自动同步，需要在 Doris 中重建 Hive 外表。
当前 Hive 的存储格式仅支持 Text，Parquet 和 ORC 类型
4.5 Binlog Load
Binlog Load提供了一种使Doris增量同步用户在Mysql数据库中对数据更新操作的CDC(Change Data Capture)功能。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h5PJzSBB-1677043665264)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221135400995.png)]
4.5.1 适用场景
- INSERT/UPDATE/DELETE支持
- 过滤Query
- 暂不兼容DDL语句
4.5.2 基本原理
当前版本设计中，Binlog Load需要依赖canal作为中间媒介，让canal伪造成一个从节点去获取Mysql主节点上的Binlog并解析，再由Doris去获取Canal上解析好的数据，主要涉及Mysql端、Canal端以及Doris端，总体数据流向如下：
+---------------------------------------------+
| Mysql |
+----------------------+----------------------+
| Binlog
+----------------------v----------------------+
| Canal Server 数据解析 |
+-------------------+-----^-------------------+
Get | | Ack
+-------------------|-----|-------------------+
| FE | | |
| +-----------------|-----|----------------+ |
| | Sync Job | | | |
| | +------------v-----+-----------+ | |
| | | Canal Client | | |
| | | +-----------------------+ | | |
| | | | Receiver | | | |
| | | +-----------------------+ | | |
| | | +-----------------------+ | | |
| | | | Consumer | | | |
| | | +-----------------------+ | | |
| | +------------------------------+ | |
| +----+---------------+--------------+----+ |
| | | | |
| +----v-----+ +-----v----+ +-----v----+ |
| | Channel1 | | Channel2 | | Channel3 | |
| | [Table1] | | [Table2] | | [Table3] | |
| +----+-----+ +-----+----+ +-----+----+ |
| | | | |
| +--|-------+ +---|------+ +---|------+|
| +---v------+| +----v-----+| +----v-----+||
| +----------+|+ +----------+|+ +----------+|+|
| | Task |+ | Task |+ | Task |+ |
| +----------+ +----------+ +----------+ |
+----------------------+----------------------+
| | |
+----v-----------------v------------------v---+
| Coordinator |
| BE |
+----+-----------------+------------------+---+
| | |
+----v---+ +---v----+ +----v---+
| BE | | BE | | BE |
+--------+ +--------+ +--------+
如上图，用户向FE提交一个数据同步作业。
- FE会为每个数据同步作业启动一个canal client，来向canal server端订阅并获取数据。
- client中的receiver将负责通过Get命令接收数据，每获取到一个数据batch，都会由consumer根据对应表分发到不同的channel，每个channel都会为此数据batch产生一个发送数据的子任务Task。
- 在FE上，一个Task是channel向BE发送数据的子任务，里面包含分发到当前channel的同一个batch的数据。
- channel控制着单个表事务的开始、提交、终止。一个事务周期内，一般会从consumer获取到多个batch的数据，因此会产生多个向BE发送数据的子任务Task，在提交事务成功前，这些Task不会实际生效。
- 满足一定条件时（比如超过一定时间、达到提交最大数据大小），consumer将会阻塞并通知各个channel提交事务。
- 当且仅当所有channel都提交成功，才会通过Ack命令通知canal并继续获取并消费数据。
- 如果有任意channel提交失败，将会重新从上一次消费成功的位置获取数据并再次提交（已提交成功的channel不会再次提交以保证幂等性）。
- 整个数据同步作业中，FE通过以上流程不断的从canal获取数据并提交到BE，来完成数据同步。
4.5.3 配置Mysql端
在Mysql Cluster模式的主从同步中，二进制日志文件(Binlog)记录了主节点上的所有数据变化，数据在Cluster的多个节点间同步、备份都要通过Binlog日志进行，从而提高集群的可用性。架构通常由一个主节点(负责写)和一个或多个从节点(负责读)构成,所有在主节点上发生的数据变更将会复制给从节点。
注意：目前必须要使用Mysql 5.7及以上的版本才能支持Binlog Load功能。
- 打开mysql的二进制binlog日志功能，则需要编辑my.cnf配置文件设置一下。
my.cnf怎么找？
[root@zuomm01 sbin]# find / -name my.cnf
/etc/my.cnf
修改mysqld中的一些配置文件
[mysqld]
server_id = 1
log-bin = mysql-bin
binlog-format = ROW
#binlog-format 的三种模式
#ROW 记录每一行数据的信息
#Statement 记录sql语句
#Mixed 上面两种的混合
- 重启 MySQL 使配置生效
systemctl restart mysqld
- 创建用户并授权
-- 设置这些参数可以使得mysql的密码简单化
set global validate_password_length=4;
set global validate_password_policy=0;
-- 新增一个canal的用户，让他监听所有库中的所有表，并且设置密码为canal
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' IDENTIFIED BY 'canal' ;
-- 刷新一下权限
FLUSH PRIVILEGES;
- 准备测试表
CREATE TABLE `user_doris` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
4.5.4 配置 Canal 端
Canal 是属于阿里巴巴 otter 项目下的一个子项目，主要用途是基于 MySQL 数据库增量日志解析，提供增量数据订阅和消费，用于解决跨机房同步的业务场景，建议使用 canal 1.1.5及以上版本。
下载地址：https://github.com/alibaba/canal/releases
上传并解压 canal deployer压缩包
mkdir /opt/app/canal
tar -zxvf canal.deployer-1.1.5.tar.gz -C /opt/app/canal
在 conf 文件夹下新建目录并重命名
一个 canal 服务中可以有多个 instance，conf/下的每一个目录即是一个实例，每个实例下面都有独立的配置文件
mkdir /opt/app/canel/conf/doris
拷贝配置文件模板
cp /opt/app/canal/conf/example/instance.properties /opt/app/canal/conf/doris/
修改 conf/canal.properties 的配置
vi canal.properties
进入找到canal.destinations = example
将其修改为 我们自己配置的目录
canal.destinations = doris-load
修改 instance 配置文件
vi instance.properties
修改：
canal.instance.master.address=zuomm01:3306
启动
sh bin/startup.sh
验证启动成功
cat logs/doris-load/doris-load.log
注意：canal client 和 canal instance 是一一对应的，Binlog Load 已限制多个数据同步作
业不能连接到同一个 destination。
4.5.5 配置目标表
Doris 创建与 Mysql 对应的目标表
CREATE TABLE `binlog_mysql` (
`id` int(11) NOT NULL COMMENT "",
`name` VARCHAR(50) NOT NULL COMMENT "",
`age` int(11) NOT NULL COMMENT "" ,
`gender` VARCHAR(50) NOT NULL COMMENT ""
) ENGINE=OLAP
UNIQUE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 1;
基本语法：
CREATE SYNC [db.]job_name
(
channel_desc,
channel_desc
...
)
binlog_desc
参数说明：
- job_name：是数据同步作业在当前数据库内的唯一标识
- channel_desc ：用来定义任务下的数据通道，可表示 MySQL 源表到 doris 目标表的映射关系。在设置此项时，如果存在多个映射关系，必须满足 MySQL 源表应该与 doris 目标表是一一对应关系，其他的任何映射关系（如一对多关系），检查语法时都被视为不合法。
- column_mapping：主要指MySQL源表和doris目标表的列之间的映射关系，如果不指定，FE 会默认源表和目标表的列按顺序一一对应。但是我们依然建议显式的指定列的映射关系，这样当目标表的结构发生变化（比如增加一个 nullable 的列），数据同步作业依然可以进行。否则，当发生上述变动后，因为列映射关系不再一一对应，导入将报错。
- binlog_desc：定义了对接远端 Binlog 地址的一些必要信息，目前可支持的对接类型只有 canal 方式，所有的配置项前都需要加上 canal 前缀。
- canal.server.ip: canal server 的地址
- canal.server.port: canal server 的端口
- canal.destination: 前文提到的 instance 的字符串标识
- canal.batchSize: 每批从 canal server 处获取的 batch 大小的最大值，默认 8192
- canal.username: instance 的用户名
- canal.password: instance 的密码
- canal.debug: 设置为 true 时，会将 batch 和每一行数据的详细信息都打印出来，会影响性能。
示例：
CREATE SYNC test.job20221228
(
FROM test.binlog_test INTO binlog_test
)
FROM BINLOG
(
"type" = "canal",
"canal.server.ip" = "zuomm01",
"canal.server.port" = "11111",
"canal.destination" = "doris",
"canal.username" = "canal",
"canal.password" = "canal"
);
查看作业状态
展示当前数据库的所有数据同步作业状态。
SHOW SYNC JOB;
展示数据库 `test_db` 下的所有数据同步作业状态。
SHOW SYNC JOB FROM `test`;
返回结果集的参数意义如下：
State：作业当前所处的阶段。作业状态之间的转换如下图所示：
+-------------+
create job | PENDING | resume job
+-----------+ <-------------+
| +-------------+ |
+----v-------+ +-------+----+
| RUNNING | pause job | PAUSED |
| +-----------------------> |
+----+-------+ run error +-------+----+
| +-------------+ |
| | CANCELLED | |
+-----------> <-------------+
stop job +-------------+ stop job
system error
作业提交之后状态为PENDING，由FE调度执行启动canal client后状态变成RUNNING，用户可以通过 STOP/PAUSE/RESUME 三个命令来控制作业的停止，暂停和恢复，操作后作业状态分别为CANCELLED/PAUSED/RUNNING。
作业的最终阶段只有一个CANCELLED，当作业状态变为CANCELLED后，将无法再次恢复。当作业发生了错误时，若错误是不可恢复的，状态会变成CANCELLED，否则会变成PAUSED。
Channel：作业所有源表到目标表的映射关系。 Status：当前binlog的消费位置(若设置了GTID模式，会显示GTID)，以及doris端执行时间相比mysql端的延迟时间。 JobConfig：对接的远端服务器信息，如canal server的地址与连接instance的destination
**控制作业：**用户可以通过 STOP/PAUSE/RESUME 三个命令来控制作业的停止，暂停和恢复
停止名称为 `job_name` 的数据同步作业
STOP SYNC JOB [db.]job_name
暂停名称为 `job_name` 的数据同步作业
PAUSE SYNC JOB [db.]job_name
恢复名称为 `job_name` 的数据同步作业
RESUME SYNC JOB `job_name`
4.6导出数据
数据导出（Export）是 Doris 提供的一种将数据导出的功能。该功能可以将用户指定的表或分区的数据，以文本的格式，通过 Broker 进程导出到远端存储上，如 HDFS / 对象存储（支持S3协议） 等。
4.6.1 原理
+--------+
| Client |
+---+----+
| 1. Submit Job
|
+---v--------------------+
| FE |
| |
| +-------------------+ |
| | ExportPendingTask | |
| +-------------------+ |
| | 2. Generate Tasks
| +--------------------+ |
| | ExportExporingTask | |
| +--------------------+ |
| |
| +-----------+ | +----+ +------+ +---------+
| | QueryPlan +----------------> BE +--->Broker+---> |
| +-----------+ | +----+ +------+ | Remote |
| +-----------+ | +----+ +------+ | Storage |
| | QueryPlan +----------------> BE +--->Broker+---> |
| +-----------+ | +----+ +------+ +---------+
+------------------------+ 3. Execute Tasks
- 用户提交一个 Export 作业到 FE。
- FE 的 Export 调度器会通过两阶段来执行一个 Export 作业：
- PENDING：FE 生成 ExportPendingTask，向 BE 发送 snapshot 命令，对所有涉及到的 Tablet 做一个快照。并生成多个查询计划。
- EXPORTING：FE 生成 ExportExportingTask，开始执行查询计划。
4.6.2 查询计划拆分
Export 作业会生成多个查询计划，每个查询计划负责扫描一部分 Tablet。每个查询计划扫描的 Tablet 个数由 FE 配置参数 export_tablet_num_per_task 指定，默认为 5。即假设一共 100 个 Tablet，则会生成 20 个查询计划。用户也可以在提交作业时，通过作业属性 tablet_num_per_task 指定这个数值。
一个作业的多个查询计划顺序执行
4.6.3 查询计划执行
一个查询计划扫描多个分片，将读取的数据以行的形式组织，每 1024 行为一个 batch，调用 Broker 写入到远端存储上。
查询计划遇到错误会整体自动重试 3 次。如果一个查询计划重试 3 次依然失败，则整个作业失败。
Doris 会首先在指定的远端存储的路径中，建立一个名为 __doris_export_tmp_12345 的临时目录（其中 12345 为作业 id）。导出的数据首先会写入这个临时目录。每个查询计划会生成一个文件，文件名示例：
export-data-c69fcf2b6db5420f-a96b94c1ff8bccef-1561453713822
其中 c69fcf2b6db5420f-a96b94c1ff8bccef 为查询计划的 query id。1561453713822 为文件生成的时间戳。当所有数据都导出后，Doris 会将这些文件 rename 到用户指定的路径中
示例：导出到hdfs
EXPORT TABLE test.event_info_log1 -- 库名.表名
to "hdfs://linux01:8020/event_info_log1" -- 导出到那里去
PROPERTIES
(
"label" = "event_info_log1",
"column_separator"=",",
"exec_mem_limit"="2147483648",
"timeout" = "3600"
)
WITH BROKER "broker_name"
(
"username" = "root",
"password" = ""
);
- label：本次导出作业的标识。后续可以使用这个标识查看作业状态。
- column_separator：列分隔符。默认为 \t。支持不可见字符，比如 ‘\x07’。
- columns：要导出的列，使用英文状态逗号隔开，如果不填这个参数默认是导出表的所有列。
- line_delimiter：行分隔符。默认为 \n。支持不可见字符，比如 ‘\x07’。
- exec_mem_limit： 表示 Export 作业中，一个查询计划在单个 BE 上的内存使用限制。默认 2GB。单位字节。
- timeout：作业超时时间。默认 2小时。单位秒。
- tablet_num_per_task：每个查询计划分配的最大分片数。默认为 5。
4.6.4 查看导出状态
mysql> show EXPORT \G;
*************************** 1. row ***************************
JobId: 14008
State: FINISHED
Progress: 100%
TaskInfo: {"partitions":["*"],"exec mem limit":2147483648,"column separator":",","line delimiter":"\n","tablet num":1,"broker":"hdfs","coord num":1,"db":"default_cluster:db1","tbl":"tbl3"}
Path: bos://bj-test-cmy/export/
CreateTime: 2019-06-25 17:08:24
StartTime: 2019-06-25 17:08:28
FinishTime: 2019-06-25 17:08:34
Timeout: 3600
ErrorMsg: NULL
1 row in set (0.01 sec)
- JobId：作业的唯一 ID
- State：作业状态：
- PENDING：作业待调度
- EXPORTING：数据导出中
- FINISHED：作业成功
- CANCELLED：作业失败
- Progress：作业进度。该进度以查询计划为单位。假设一共 10 个查询计划，当前已完成 3 个，则进度为 30%。
- TaskInfo：以 Json 格式展示的作业信息：
- db：数据库名
- tbl：表名
- partitions：指定导出的分区。
*表示所有分区。 - exec mem limit：查询计划内存使用限制。单位字节。
- column separator：导出文件的列分隔符。
- line delimiter：导出文件的行分隔符。
- tablet num：涉及的总 Tablet 数量。
- broker：使用的 broker 的名称。
- coord num：查询计划的个数。
- Path：远端存储上的导出路径。
- CreateTime/StartTime/FinishTime：作业的创建时间、开始调度时间和结束时间。
- Timeout：作业超时时间。单位是秒。该时间从 CreateTime 开始计算。
- ErrorMsg：如果作业出现错误，这里会显示错误原因。
注意事项
- 不建议一次性导出大量数据。一个 Export 作业建议的导出数据量最大在几十 GB。过大的导出会导致更多的垃圾文件和更高的重试成本。
- 如果表数据量过大，建议按照分区导出。
- 在 Export 作业运行过程中，如果 FE 发生重启或切主，则 Export 作业会失败，需要用户重新提交。
- 如果 Export 作业运行失败，在远端存储中产生的 __doris_export_tmp_xxx 临时目录，以及已经生成的文件不会被删除，需要用户手动删除。
- 如果 Export 作业运行成功，在远端存储中产生的 __doris_export_tmp_xxx 目录，根据远端存储的文件系统语义，可能会保留，也可能会被清除。比如在百度对象存储（BOS）中，通过 rename 操作将一个目录中的最后一个文件移走后，该目录也会被删除。如果该目录没有被清除，用户可以手动清除
- 当 Export 运行完成后（成功或失败），FE 发生重启或切主，则 SHOW EXPORT展示的作业的部分信息会丢失，无法查看。
- Export 作业只会导出 Base 表的数据，不会导出 Rollup Index 的数据。
- Export 作业会扫描数据，占用 IO 资源，可能会影响系统的查询延迟
4.7 查询结果导出
SELECT INTO OUTFILE 语句可以将查询结果导出到文件中。目前支持通过 Broker进程, 通过 S3 协议, 或直接通过 HDFS 协议，导出到远端存储，如 HDFS，S3，BOS，COS （腾讯云）上。
语法：
query_stmt -- 查询语句
INTO OUTFILE "file_path" --导出文件的路劲
[format_as] -- 指定文件存储的格式
[properties] -- 一些配置文件
file_path:指向文件存储的路径以及文件前缀。如 hdfs://path/to/my_file_.最终的文件名将由 my_file_，文件序号以及文件格式后缀组成。其中文件序号由 0 开始，数量为文件被分割的数量
如：
my_file_abcdefg_0.csv
my_file_abcdefg_1.csv
my_file_abcdegf_2.csv
[format_as]：指定导出格式。默认为 CSV
[properties]：指定相关属性。目前支持通过 Broker 进程，hdfs协议等
Broker 相关属性需加前缀 broker.
HDFS 相关属性需加前缀 hdfs. 其中hdfs.fs.defaultFS 用于填写 namenode地址和端口,属于必填项。
如：
("broker.prop_key" = "broker.prop_val", ...)
or
("hdfs.fs.defaultFS" = "xxx", "hdfs.hdfs_user" = "xxx")
其他属性：
- column_separator：列分隔符，仅对 CSV 格式适用。默认为 \t。
- line_delimiter：行分隔符，仅对 CSV 格式适用。默认为 \n。
- max_file_size：单个文件的最大大小。默认为 1GB。取值范围在 5MB 到 2GB 之间。超过这个大小的文件将会被切分。
- schema：PARQUET 文件 schema 信息。仅对 PARQUET 格式适用。导出文件格式为 PARQUET 时，必须指定 schema。
示例1：使用 broker 方式，将简单查询结果导出
select * from log_detail where id >2
INTO OUTFILE "hdfs://zuomm01:8020/doris-out/broker_a_"
FORMAT AS CSV
PROPERTIES
(
"broker.name" = "broker_name",
"column_separator" = ",",
"line_delimiter" = "\n",
"max_file_size" = "100MB"
);
示例2：使用 HDFS 方式导出
EXPLAIN SELECT * FROM log_detail
INTO OUTFILE "hdfs://doris-out/hdfs_"
FORMAT AS CSV
PROPERTIES
(
"fs.defaultFS" = "hdfs://zuomm01:8020",
"hadoop.username" = "root",
"column_separator" = ","
);
5.doris的查询语法
5.0 查询语法整体结构
SELECT
[ALL | DISTINCT | DISTINCTROW ] -- 对查询字段的结果是否需要去重，还是全部保留等参数
select_expr [, select_expr ...] -- select的查询字段
[FROM table_references
[PARTITION partition_list] -- from 哪个库里面的那张表甚至哪一个(几个)分区
[WHERE where_condition] -- WHERE 查询
[GROUP BY {col_name | expr | position} -- group by 聚合
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition] -- having 针对聚合函数的再一次过滤
[ORDER BY {col_name | expr | position} -- 对结果数据按照字段进行排序
[ASC | DESC], ...] -- 排序规则
[LIMIT {[offset,] row_count | row_count OFFSET offset}] -- 限制输出多少行内容
[INTO OUTFILE 'file_name'] -- 将查询的结果导出到文件中
5.1 doris内置函数
5.1.1条件函数
5.1.1.1 if函数
语法示例：
if(boolean condition, type valueTrue, type valueFalseOrNull)
--如果表达式 condition 成立，返回结果 valueTrue；否则，返回结果 valueFalseOrNull
--返回值类型：valueTrue 表达式结果的类型
示例：
mysql> select user_id, if(user_id = 1, "true", "false") as test_if from test;
+---------+---------+
| user_id | test_if |
+---------+---------+
| 1 | true |
| 2 | false |
+---------+---------+
5.1.1.2 ifnull,nvl,coalesce,nullif函数
语法示例：
ifnull(expr1, expr2)
--如果 expr1 的值不为 NULL 则返回 expr1，否则返回 expr2
nvl(expr1, expr2)
--如果 expr1 的值不为 NULL 则返回 expr1，否则返回 expr2
coalesce(expr1, expr2, ...., expr_n))
--返回参数中的第一个非空表达式（从左向右）
nullif(expr1, expr2)
-- 如果两个参数相等，则返回NULL。否则返回第一个参数的值
示例：
mysql> select ifnull(1,0);
+--------------+
| ifnull(1, 0) |
+--------------+
| 1 |
+--------------+
mysql> select nvl(null,10);
+------------------+
| nvl(null,10) |
+------------------+
| 10 |
+------------------+
mysql> select coalesce(NULL, '1111', '0000');
+--------------------------------+
| coalesce(NULL, '1111', '0000') |
+--------------------------------+
| 1111 |
+--------------------------------+
mysql> select coalesce(NULL, NULL,NULL,'0000', NULL);
+----------------------------------------+
| coalesce(NULL, NULL,NULL,'0000', NULL) |
+----------------------------------------+
| 0000 |
+----------------------------------------+
mysql> select nullif(1,1);
+--------------+
| nullif(1, 1) |
+--------------+
| NULL |
+--------------+
mysql> select nullif(1,0);
+--------------+
| nullif(1, 0) |
+--------------+
| 1 |
+--------------+
5.1.1.3 case
语法示例：
-- 方式一
CASE expression
WHEN condition1 THEN result1
[WHEN condition2 THEN result2]
...
[WHEN conditionN THEN resultN]
[ELSE result]
END
-- 方式二
CASE WHEN condition1 THEN result1
[WHEN condition2 THEN result2]
...
[WHEN conditionN THEN resultN]
[ELSE result]
END
-- 将表达式和多个可能的值进行比较，当匹配时返回相应的结果
示例：
mysql> select user_id,
case user_id
when 1 then 'user_id = 1'
when 2 then 'user_id = 2'
else 'user_id not exist'
end as test_case
from test;
+---------+-------------+
| user_id | test_case |
+---------+-------------+
| 1 | user_id = 1 |
| 2 | user_id = 2 |
| 3 | 'user_id not exist' |
+---------+-------------+
mysql> select user_id,
case
when user_id = 1 then 'user_id = 1'
when user_id = 2 then 'user_id = 2'
else 'user_id not exist'
end as test_case
from test;
+---------+-------------+
| user_id | test_case |
+---------+-------------+
| 1 | user_id = 1 |
| 2 | user_id = 2 |
+---------+-------------+
5.1.2聚合函数
5.1.2.1 min,max,sum,avg,count
5.1.2.2 min_by和max_by
MAX_BY(expr1, expr2)
返回expr2最大值所在行的 expr1 （求分组top1的简介函数）
语法示例：
MySQL > select * from tbl;
+------+------+------+------+
| k1 | k2 | k3 | k4 |
+------+------+------+------+
| 0 | 3 | 2 | 100 |
| 1 | 2 | 3 | 4 |
| 4 | 3 | 2 | 2 |
| 3 | 4 | 2 | 1 |
+------+------+------+------+
MySQL > select max_by(k1, k4) from tbl;
select max_by(k1, k4) from tbl;
--取k4这个列中的最大值对应的k1这个列的值
+--------------------+
| max_by(`k1`, `k4`) |
+--------------------+
| 0 |
+--------------------+
练习：
name subject score
zss,chinese,99
zss,math,89
zss,English,79
lss,chinese,88
lss,math,88
lss,English,22
www,chinese,99
www,math,45
zll,chinese,23
zll,math,88
zll,English,80
www,English,94
-- 建表语句
create table score
(
name varchar(50),
subject varchar(50),
score double
)
DUPLICATE KEY(name)
DISTRIBUTED BY HASH(name) BUCKETS 1;
-- 通过本地文件的方式导入数据
curl \
-u root: \
-H "label:salary" \
-H "column_separator:," \
-T /root/data/salary.txt \
http://zuomm01:8040/api/test/salary/_stream_load
-- 求每门课程成绩最高分的那个人
select
subject,max_by(name,score) as name
from score
group by subject
+---------+------+
| subject | name |
+---------+------+
| English | www |
| math | lss |
| chinese | www |
+---------+------+
5.1.2.3 group_concat
求：每一个人有考试成绩的所有科目
select name， group_concat(subject,‘,’) as all_subject from score group by name
语法示例：
VARCHAR GROUP_CONCAT([DISTINCT] VARCHAR 列名[, VARCHAR sep]
该函数是类似于 sum() 的聚合函数，group_concat 将结果集中的多行结果连接成一个字符串
-- group_concat对于收集的字段只能是string，varchar，char类型
--当不指定分隔符的时候，默认使用 ','
VARCHAR ：代表GROUP_CONCAT函数返回值类型
[DISTINCT]：可选参数，针对需要拼接的列的值进行去重
[, VARCHAR sep]：拼接成字符串的分隔符，默认是 ','
示例：
--建表
create table example(
id int,
name varchar(50),
age int,
gender string,
is_marry boolean,
marry_date date,
marry_datetime datetime
)engine = olap
distributed by hash(id) buckets 3;
--插入数据
insert into example values \
(1,'zss',18,'male',0,null,null),\
(2,'lss',28,'female',1,'2022-01-01','2022-01-01 11:11:11'),\
(3,'ww',38,'male',1,'2022-02-01','2022-02-01 11:11:11'),\
(4,'zl',48,'female',0,null,null),\
(5,'tq',58,'male',1,'2022-03-01','2022-03-01 11:11:11'),\
(6,'mly',18,'male',1,'2022-04-01','2022-04-01 11:11:11'),\
(7,null,18,'male',1,'2022-05-01','2022-05-01 11:11:11');
--当收集的那一列，有值为null时，他会自动将null的值过滤掉
select
gender,
group_concat(name,',') as gc_name
from example
group by gender;
+--------+---------------+
| gender | gc_name |
+--------+---------------+
| female | zl,lss |
| male | zss,ww,tq,mly |
+--------+---------------+
select
gender,
group_concat(DISTINCT cast(age as string)) as gc_age
from example
group by gender;
+--------+------------+
| gender | gc_age |
+--------+------------+
| female | 48, 28 |
| male | 58, 38, 18 |
+--------+------------+
5.1.2.4 collect_list，collect_set (1.2版本上线)
语法示例：
ARRAY<T> collect_list(expr)
--返回一个包含 expr 中所有元素(不包括NULL)的数组，数组中元素顺序是不确定的。
ARRAY<T> collect_set(expr)
--返回一个包含 expr 中所有去重后元素(不包括NULL)的数组，数组中元素顺序是不确定的。
5.1.3日期函数
5.1.3.1 获取当前时间
curdate,current_date，now，curtime,current_time，current_timestamp
示例：
select current_date();
+----------------+
| current_date() |
+----------------+
| 2022-11-25 |
+----------------+
select curdate();
+------------+
| curdate() |
+------------+
| 2022-11-25 |
+------------+
select now();
+---------------------+
| now() |
+---------------------+
| 2022-11-25 00:55:15 |
+---------------------+
select curtime();
+-----------+
| curtime() |
+-----------+
| 00:42:13 |
+-----------+
select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2022-11-25 00:42:30 |
+---------------------+
5.1.3.2last_day(1.2版本上线)
语法：
DATE last_day(DATETIME date)
-- 返回输入日期中月份的最后一天；
--'28'(非闰年的二月份),
--'29'(闰年的二月份),
--'30'(四月，六月，九月，十一月),
--'31'(一月，三月，五月，七月，八月，十月，十二月)
select last_day('2000-03-03 01:00:00'); -- 给我返回这个月份中的最后一天的日期 年月日
ERROR 1105 (HY000): errCode = 2, detailMessage = No matching function with signature: last_day(varchar(-1)).
5.1.3.3from_unixtime
语法：
DATETIME FROM_UNIXTIME(INT unix_timestamp[, VARCHAR string_format])
-- 将 unix 时间戳转化为对应的 time 格式，返回的格式由 string_format 指定
--支持date_format中的format格式，默认为 %Y-%m-%d %H:%i:%s
-- 正常使用的三种格式
yyyyMMdd
yyyy-MM-dd
yyyy-MM-dd HH:mm:ss
示例：
mysql> select from_unixtime(1196440219);
+---------------------------+
| from_unixtime(1196440219) |
+---------------------------+
| 2007-12-01 00:30:19 |
+---------------------------+
mysql> select from_unixtime(1196440219, 'yyyy-MM-dd HH:mm:ss');
+--------------------------------------------------+
| from_unixtime(1196440219, 'yyyy-MM-dd HH:mm:ss') |
+--------------------------------------------------+
| 2007-12-01 00:30:19 |
+--------------------------------------------------+
mysql> select from_unixtime(1196440219, '%Y-%m-%d');
+-----------------------------------------+
| from_unixtime(1196440219, '%Y-%m-%d') |
+-----------------------------------------+
| 2007-12-01 |
+-----------------------------------------+
5.1.2.4unix_timestamp
语法：
UNIX_TIMESTAMP(),
UNIX_TIMESTAMP(DATETIME date),
UNIX_TIMESTAMP(DATETIME date, STRING fmt) -- 给一个日期，指定这个日期的格式
-- 将日期转换成时间戳，返回值是一个int类型
示例：
-- 获取当前日期的时间戳
select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1669309722 |
+------------------+
-- 获取指定日期的时间戳
select unix_timestamp('2022-11-26 01:09:01');
+---------------------------------------+
| unix_timestamp('2022-11-26 01:09:01') |
+---------------------------------------+
| 1669396141 |
+---------------------------------------+
-- 给定一个特殊日期格式的时间戳，指定格式
select unix_timestamp('2022-11-26 01:09-01', '%Y-%m-%d %H:%i-%s');
+------------------------------------------------------------+
| unix_timestamp('2022-11-26 01:09-01', '%Y-%m-%d %H:%i-%s') |
+------------------------------------------------------------+
| 1669396141 |
+------------------------------------------------------------+
5.1.3.5to_date
语法：
DATE TO_DATE(DATETIME)
--返回 DATETIME 类型中的日期部分。
示例：
select to_date("2022-11-20 00:00:00");
+--------------------------------+
| to_date('2022-11-20 00:00:00') |
+--------------------------------+
| 2022-11-20 |
+--------------------------------+
5.1.3.6extract
语法：
extract(unit FROM DATETIME) --抽取
-- 提取DATETIME某个指定单位的值。
--unit单位可以为year, month, day, hour, minute或者second
示例：
select
extract(year from '2022-09-22 17:01:30') as year,
extract(month from '2022-09-22 17:01:30') as month,
extract(day from '2022-09-22 17:01:30') as day,
extract(hour from '2022-09-22 17:01:30') as hour,
extract(minute from '2022-09-22 17:01:30') as minute,
extract(second from '2022-09-22 17:01:30') as second;
+------+-------+------+------+--------+--------+
| year | month | day | hour | minute | second |
+------+-------+------+------+--------+--------+
| 2022 | 9 | 22 | 17 | 1 | 30 |
+------+-------+------+------+--------+--------+
5.1.3.7date_add，date_sub,datediff
语法：
DATE_ADD(DATETIME date,INTERVAL expr type)
DATE_SUB(DATETIME date,INTERVAL expr type)
DATEDIFF(DATETIME expr1,DATETIME expr2)
-- 计算两个日期相差多少天，结果精确到天。
-- 向日期添加指定的时间间隔。
-- date 参数是合法的日期表达式。
-- expr 参数是您希望添加的时间间隔。
-- type 参数可以是下列值：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
示例：
select date_add('2010-11-30 23:59:59', INTERVAL 2 DAY);
+-------------------------------------------------+
| date_add('2010-11-30 23:59:59', INTERVAL 2 DAY) |
+-------------------------------------------------+
| 2010-12-02 23:59:59 |
+-------------------------------------------------+
--传一个负数进去也就等同于date_sub
select date_add('2010-11-30 23:59:59', INTERVAL -2 DAY);
+--------------------------------------------------+
| date_add('2010-11-30 23:59:59', INTERVAL -2 DAY) |
+--------------------------------------------------+
| 2010-11-28 23:59:59 |
+--------------------------------------------------+
mysql> select datediff('2022-11-27 22:51:56','2022-11-24 22:50:56');
+--------------------------------------------------------+
| datediff('2022-11-27 22:51:56', '2022-11-24 22:50:56') |
+--------------------------------------------------------+
| 3 |
+--------------------------------------------------------+
5.1.3.8date_format
语法：
VARCHAR DATE_FORMAT(DATETIME date, VARCHAR format)
--将日期类型按照format的类型转化为字符串
示例：
select date_format('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| date_format('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00 |
+------------------------------------------------+
select date_format('2007-10-04 22:23:00', 'yyyy-MM-dd');
+------------------------------------------------+
| date_format('2007-10-04 22:23:00', '%Y-%m-%d') |
+------------------------------------------------+
| 2007-10-04 |
+------------------------------------------------+
5.1.4 字符串函数
5.1.4.1 length，lower，upper，reverse
示例：
获取到字符串的长度，对字符串转大小写和字符串的反转
5.1.4.2 lpad，rpad
语法：
VARCHAR rpad(VARCHAR str, INT len, VARCHAR pad)
VARCHAR lpad(VARCHAR str, INT len, VARCHAR pad)
-- 返回 str 中长度为 len（从首字母开始算起）的字符串。
--如果 len 大于 str 的长度，则在 str 的后面不断补充 pad 字符，
--直到该字符串的长度达到 len 为止。如果 len 小于 str 的长度，
--该函数相当于截断 str 字符串，只返回长度为 len 的字符串。
--len 指的是字符长度而不是字节长度。
示例：
-- 向左边补齐
SELECT lpad("1", 5, "0");
+---------------------+
| lpad("1", 5, "0") |
+---------------------+
| 00001 |
+---------------------+
-- 向右边补齐
SELECT rpad('11', 5, '0');
+---------------------+
| rpad('11', 5, '0') |
+---------------------+
| 11000 |
+---------------------+
5.1.4.3 concat，concat_ws
语法：
select concat("a", "b");
+------------------+
| concat('a', 'b') |
+------------------+
| ab |
+------------------+
select concat("a", "b", "c");
+-----------------------+
| concat('a', 'b', 'c') |
+-----------------------+
| abc |
+-----------------------+
-- concat中，如果有一个值为null，那么得到的结果就是null
mysql> select concat("a", null, "c");
+------------------------+
| concat('a', NULL, 'c') |
+------------------------+
| NULL |
+------------------------+
--使用第一个参数 sep 作为连接符
--将第二个参数以及后续所有参数(或ARRAY中的所有字符串)拼接成一个字符串。
-- 如果分隔符是 NULL，返回 NULL。 concat_ws函数不会跳过空字符串，会跳过 NULL 值。
mysql> select concat_ws("_", "a", "b");
+----------------------------+
| concat_ws("_", "a", "b") |
+----------------------------+
| a_b |
+----------------------------+
mysql> select concat_ws(NULL, "d", "is");
+----------------------------+
| concat_ws(NULL, 'd', 'is') |
+----------------------------+
| NULL |
+----------------------------+
5.1.4.4 substr
语法：
--求子字符串，返回第一个参数描述的字符串中从start开始长度为len的部分字符串。
--首字母的下标为1。
mysql> select substr("Hello doris", 2, 1);
+-----------------------------+
| substr('Hello doris', 2, 1) |
+-----------------------------+
| e |
+-----------------------------+
mysql> select substr("Hello doris", 1, 2);
+-----------------------------+
| substr('Hello doris', 1, 2) |
+-----------------------------+
| He |
+-----------------------------+
5.1.4.5 ends_with，starts_with
语法：
BOOLEAN ENDS_WITH (VARCHAR str, VARCHAR suffix)
--如果字符串以指定后缀结尾，返回true。否则，返回false。
--任意参数为NULL，返回NULL。
BOOLEAN STARTS_WITH (VARCHAR str, VARCHAR prefix)
--如果字符串以指定前缀开头，返回true。否则，返回false。
--任意参数为NULL，返回NULL。
示例：
mysql> select ends_with("Hello doris", "doris");
+-----------------------------------+
| ends_with('Hello doris', 'doris') |
+-----------------------------------+
| 1 |
+-----------------------------------+
mysql> select ends_with("Hello doris", "Hello");
+-----------------------------------+
| ends_with('Hello doris', 'Hello') |
+-----------------------------------+
| 0 |
+-----------------------------------+
MySQL [(none)]> select starts_with("hello world","hello");
+-------------------------------------+
| starts_with('hello world', 'hello') |
+-------------------------------------+
| 1 |
+-------------------------------------+
MySQL [(none)]> select starts_with("hello world","world");
+-------------------------------------+
| starts_with('hello world', 'world') |
+-------------------------------------+
| 0 |
+-------------------------------------+
5.1.4.6 trim，ltrim，rtrim
语法：
VARCHAR trim(VARCHAR str)
-- 将参数 str 中左侧和右侧开始部分连续出现的空格去掉
mysql> SELECT trim(' ab d ') str;
+------+
| str |
+------+
| ab d |
+------+
VARCHAR ltrim(VARCHAR str)
-- 将参数 str 中从左侧部分开始部分连续出现的空格去掉
mysql> SELECT ltrim(' ab d') str;
+------+
| str |
+------+
| ab d |
+------+
VARCHAR rtrim(VARCHAR str)
--将参数 str 中从右侧部分开始部分连续出现的空格去掉
mysql> SELECT rtrim('ab d ') str;
+------+
| str |
+------+
| ab d |
+------+
5.1.4.7 null_or_empty，not_null_or_empty
BOOLEAN NULL_OR_EMPTY (VARCHAR str)
-- 如果字符串为空字符串或者NULL，返回true。否则，返回false。
MySQL [(none)]> select null_or_empty(null);
+---------------------+
| null_or_empty(NULL) |
+---------------------+
| 1 |
+---------------------+
MySQL [(none)]> select null_or_empty("");
+-------------------+
| null_or_empty('') |
+-------------------+
| 1 |
+-------------------+
MySQL [(none)]> select null_or_empty("a");
+--------------------+
| null_or_empty('a') |
+--------------------+
| 0 |
+--------------------+
BOOLEAN NOT_NULL_OR_EMPTY (VARCHAR str)
如果字符串为空字符串或者NULL，返回false。否则，返回true。
MySQL [(none)]> select not_null_or_empty(null);
+-------------------------+
| not_null_or_empty(NULL) |
+-------------------------+
| 0 |
+-------------------------+
MySQL [(none)]> select not_null_or_empty("");
+-----------------------+
| not_null_or_empty('') |
+-----------------------+
| 0 |
+-----------------------+
MySQL [(none)]> select not_null_or_empty("a");
+------------------------+
| not_null_or_empty('a') |
+------------------------+
| 1 |
+------------------------+
5.1.4.8 replace
VARCHAR REPLACE (VARCHAR str, VARCHAR old, VARCHAR new)
-- 将str字符串中的old子串全部替换为new串
mysql> select replace("http://www.baidu.com:9090", "9090", "");
+------------------------------------------------------+
| replace('http://www.baidu.com:9090', '9090', '') |
+------------------------------------------------------+
| http://www.baidu.com: |
+------------------------------------------------------+
5.1.4.9 split_part
VARCHAR split_part(VARCHAR content, VARCHAR delimiter, INT field)
-- 根据分割符拆分字符串, 返回指定的分割部分(从一开始计数)。
mysql> select split_part("hello world", " ", 1);
+----------------------------------+
| split_part('hello world', ' ', 1) |
+----------------------------------+
| hello |
+----------------------------------+
mysql> select split_part("hello world", " ", 2);
+----------------------------------+
| split_part('hello world', ' ', 2) |
+----------------------------------+
| world |
+----------------------------------+
mysql> select split_part("2019年7月8号", "月", 1);
+-----------------------------------------+
| split_part('2019年7月8号', '月', 1) |
+-----------------------------------------+
| 2019年7 |
+-----------------------------------------+
mysql> select split_part("abca", "a", 1);
+----------------------------+
| split_part('abca', 'a', 1) |
+----------------------------+
| |
+----------------------------+
5.1.4.10 money_format
VARCHAR money_format(Number)
-- 将数字按照货币格式输出，整数部分每隔3位用逗号分隔，小数部分保留2位
mysql> select money_format(17014116);
+------------------------+
| money_format(17014116) |
+------------------------+
| 17,014,116.00 |
+------------------------+
mysql> select money_format(1123.456);
+------------------------+
| money_format(1123.456) |
+------------------------+
| 1,123.46 |
+------------------------+
mysql> select money_format(1123.4);
+----------------------+
| money_format(1123.4) |
+----------------------+
| 1,123.40 |
+----------------------+
5.1.5数学函数
5.1.5.1ceil和floor
BIGINT ceil(DOUBLE x)
-- 返回大于或等于x的最小整数值.
mysql> select ceil(1);
+-----------+
| ceil(1.0) |
+-----------+
| 1 |
+-----------+
mysql> select ceil(2.4);
+-----------+
| ceil(2.4) |
+-----------+
| 3 |
+-----------+
mysql> select ceil(-10.3);
+-------------+
| ceil(-10.3) |
+-------------+
| -10 |
+-------------+
BIGINT floor(DOUBLE x)
-- 返回小于或等于x的最大整数值.
mysql> select floor(1);
+------------+
| floor(1.0) |
+------------+
| 1 |
+------------+
mysql> select floor(2.4);
+------------+
| floor(2.4) |
+------------+
| 2 |
+------------+
mysql> select floor(-10.3);
+--------------+
| floor(-10.3) |
+--------------+
| -11 |
+--------------+
5.1.5.2round
round(x), round(x, d)
-- 将x四舍五入后保留d位小数，d默认为0。
-- 如果d为负数，则小数点左边d位为0。如果x或d为null，返回null。
mysql> select round(2.4);
+------------+
| round(2.4) |
+------------+
| 2 |
+------------+
mysql> select round(2.5);
+------------+
| round(2.5) |
+------------+
| 3 |
+------------+
mysql> select round(-3.4);
+-------------+
| round(-3.4) |
+-------------+
| -3 |
+-------------+
mysql> select round(-3.5);
+-------------+
| round(-3.5) |
+-------------+
| -4 |
+-------------+
mysql> select round(1667.2725, 2);
+---------------------+
| round(1667.2725, 2) |
+---------------------+
| 1667.27 |
+---------------------+
mysql> select round(1667.2725, -2);
+----------------------+
| round(1667.2725, -2) |
+----------------------+
| 1700 |
+----------------------+
5.1.5.3truncate
DOUBLE truncate(DOUBLE x, INT d)
-- 按照保留小数的位数d对x进行数值截取。
-- 规则如下：
-- 当d > 0时：保留x的d位小数
-- 当d = 0时：将x的小数部分去除，只保留整数部分
-- 当d < 0时：将x的小数部分去除，整数部分按照 d所指定的位数，采用数字0进行替换
mysql> select truncate(124.3867, 2);
+-----------------------+
| truncate(124.3867, 2) |
+-----------------------+
| 124.38 |
+-----------------------+
mysql> select truncate(124.3867, 0);
+-----------------------+
| truncate(124.3867, 0) |
+-----------------------+
| 124 |
+-----------------------+
mysql> select truncate(-124.3867, -2);
+-------------------------+
| truncate(-124.3867, -2) |
+-------------------------+
| -100 |
+-------------------------+
5.1.5.4abs
数值类型 abs(数值类型 x)
-- 返回x的绝对值.
mysql> select abs(-2);
+---------+
| abs(-2) |
+---------+
| 2 |
+---------+
mysql> select abs(3.254655654);
+------------------+
| abs(3.254655654) |
+------------------+
| 3.254655654 |
+------------------+
mysql> select abs(-3254654236547654354654767);
+---------------------------------+
| abs(-3254654236547654354654767) |
+---------------------------------+
| 3254654236547654354654767 |
+---------------------------------+
5.1.5.5pow
DOUBLE pow(DOUBLE a, DOUBLE b)
-- 求幂次：返回a的b次方.
mysql> select pow(2,0);
+---------------+
| pow(2.0, 0.0) |
+---------------+
| 1 |
+---------------+
mysql> select pow(2,3);
+---------------+
| pow(2.0, 3.0) |
+---------------+
| 8 |
+---------------+
mysql> select round(pow(3,2.4),2);
+--------------------+
| pow(3.0, 2.4) |
+--------------------+
| 13.966610165238235 |
+--------------------+
5.1.5.6greatest和 least
greatest(col_a, col_b, …, col_n)
-- 返回一行中 n个column的最大值.若column中有NULL，则返回NULL.
least(col_a, col_b, …, col_n)
-- 返回一行中 n个column的最小值.若column中有NULL，则返回NULL.
mysql> select greatest(-1, 0, 5, 8);
+-----------------------+
| greatest(-1, 0, 5, 8) |
+-----------------------+
| 8 |
+-----------------------+
mysql> select greatest(-1, 0, 5, NULL);
+--------------------------+
| greatest(-1, 0, 5, NULL) |
+--------------------------+
| NULL |
+--------------------------+
mysql> select greatest(6.3, 4.29, 7.6876);
+-----------------------------+
| greatest(6.3, 4.29, 7.6876) |
+-----------------------------+
| 7.6876 |
+-----------------------------+
mysql> select greatest("2022-02-26 20:02:11","2020-01-23 20:02:11","2020-06-22 20:02:11");
+-------------------------------------------------------------------------------+
| greatest('2022-02-26 20:02:11', '2020-01-23 20:02:11', '2020-06-22 20:02:11') |
+-------------------------------------------------------------------------------+
| 2022-02-26 20:02:11 |
+-------------------------------------------------------------------------------+
小练习：
需求：求每个人工资组成部分中占比最高的工资
-- 准备数据
name user_id jbgz jjgz tcgz
zss,1,2000,3000,5000
lss,2,1000,4000,1000
www,3,5000,1000,5000
tqq,4,4000,300,7000
name user_id jbgz jjgz tcgz
zss,1,2000,3000,5000
lss,2,1000,4000,1000
www,3,5000,1000,5000
tqq,4,4000,300,7000
-- 建表语句
create table salary
(
name varchar(50),
user_id int,
jbgz double,
jjgz double,
tcgz double
)
DUPLICATE KEY(name)
DISTRIBUTED BY HASH(name) BUCKETS 1;
-- 通过本地文件的方式导入数据
curl \
-u root: \
-H "label:salary" \
-H "column_separator:," \
-T /root/data/salary.txt \
http://zuomm01:8040/api/test/salary/_stream_load
select * from salary;
+------+---------+------+------+------+
| name | user_id | jbgz | jjgz | tcgz |
+------+---------+------+------+------+
| lss | 2 | 1000 | 4000 | 1000 |
| tqq | 4 | 4000 | 300 | 7000 |
| www | 3 | 5000 | 1000 | 5000 |
| zss | 1 | 2000 | 3000 | 5000 |
+------+---------+------+------+------+
逻辑分析：最高工资比较好求，直接用greatest函数
greatest用法：greatest（字段一，字段二，字段三。。。） 求多个字段中的最大值
工资类型怎么办？只能用这种等值匹配的方式去处理，让表自关联，然后最大值和三种工资匹配
+------+---------+------+------+------+--------+---------+
| name | user_id | jbgz | jjgz | tcgz | max_gz | gz_type |
+------+---------+------+------+------+--------+---------+
| lss | 2 | 1000 | 4000 | 1000 | 4000 | jjgz |
| tqq | 4 | 4000 | 300 | 7000 | 7000 | tcgz |
| www | 3 | 5000 | 1000 | 5000 | 5000 | jbgz |
| zss | 1 | 2000 | 3000 | 5000 | 5000 | tcgz |
+------+---------+------+------+------+--------+---------+
5.1.6数组函数（1.2版本正式添加）
Only supported in vectorized engine
仅支持向量化引擎中使用
5.1.6.1array()
ARRAY<T> array(T, ...)
-- 把多个字段构造成一个数组
mysql> set enable_vectorized_engine=true;
mysql> select array("1", 2, 1.1);
+----------------------+
| array('1', 2, '1.1') |
+----------------------+
| ['1', '2', '1.1'] |
+----------------------+
1 row in set (0.00 sec)
mysql> select array(null, 1);
+----------------+
| array(NULL, 1) |
+----------------+
| [NULL, 1] |
+----------------+
1 row in set (0.00 sec)
mysql> select array(1, 2, 3);
+----------------+
| array(1, 2, 3) |
+----------------+
| [1, 2, 3] |
+----------------+
1 row in set (0.00 sec)
5.1.6.2array_min，array_max，array_avg，array_sum，array_size
求数组中的最小值，最大值，平均值，数组中所有元素的和，数组的长度
-- 数组中的NULL值会被跳过。空数组以及元素全为NULL值的数组，结果返回NULL值。
5.1.6.3array_remove
ARRAY<T> array_remove(ARRAY<T> arr, T val)
-- 返回移除所有的指定元素后的数组，如果输入参数为NULL，则返回NULL
mysql> set enable_vectorized_engine=true;
mysql> select array_remove(['test', NULL, 'value'], 'value');
+-----------------------------------------------------+
| array_remove(ARRAY('test', NULL, 'value'), 'value') |
+-----------------------------------------------------+
| [test, NULL] |
+-----------------------------------------------------+
mysql> select k1, k2, array_remove(k2, 1) from array_type_table_1;
+------+--------------------+-----------------------+
| k1 | k2 | array_remove(`k2`, 1) |
+------+--------------------+-----------------------+
| 1 | [1, 2, 3] | [2, 3] |
| 2 | [1, 3] | [3] |
| 3 | NULL | NULL |
| 4 | [1, 3] | [3] |
| 5 | [NULL, 1, NULL, 2] | [NULL, NULL, 2] |
+------+--------------------+-----------------------+
mysql> select k1, k2, array_remove(k2, k1) from array_type_table_1;
+------+--------------------+--------------------------+
| k1 | k2 | array_remove(`k2`, `k1`) |
+------+--------------------+--------------------------+
| 1 | [1, 2, 3] | [2, 3] |
| 2 | [1, 3] | [1, 3] |
| 3 | NULL | NULL |
| 4 | [1, 3] | [1, 3] |
| 5 | [NULL, 1, NULL, 2] | [NULL, 1, NULL, 2] |
+------+--------------------+--------------------------+
5.1.6.4array_sort
ARRAY<T> array_sort(ARRAY<T> arr)
-- 返回按升序排列后的数组，如果输入数组为NULL，则返回NULL。
-- 如果数组元素包含NULL, 则输出的排序数组会将NULL放在最前面。
mysql> set enable_vectorized_engine=true;
mysql> select k1, k2, array_sort(k2) array_test;
+------+-----------------------------+-----------------------------+
| k1 | k2 | array_sort(`k2`) |
+------+-----------------------------+-----------------------------+
| 1 | [1, 2, 3, 4, 5] | [1, 2, 3, 4, 5] |
| 2 | [6, 7, 8] | [6, 7, 8] |
| 3 | [] | [] |
| 4 | NULL | NULL |
| 5 | [1, 2, 3, 4, 5, 4, 3, 2, 1] | [1, 1, 2, 2, 3, 3, 4, 4, 5] |
| 6 | [1, 2, 3, NULL] | [NULL, 1, 2, 3] |
| 7 | [1, 2, 3, NULL, NULL] | [NULL, NULL, 1, 2, 3] |
| 8 | [1, 1, 2, NULL, NULL] | [NULL, NULL, 1, 1, 2] |
| 9 | [1, NULL, 1, 2, NULL, NULL] | [NULL, NULL, NULL, 1, 1, 2] |
+------+-----------------------------+-----------------------------+
5.1.6.5array_contains
BOOLEAN array_contains(ARRAY<T> arr, T value)
-- 判断数组中是否包含value。返回结果如下：
-- 1 - value在数组arr中存在；
-- 0 - value不存在数组arr中；
-- NULL - arr为NULL时。
mysql> set enable_vectorized_engine=true;
mysql> SELECT id,c_array,array_contains(c_array, 5) FROM `array_test`;
+------+-----------------+------------------------------+
| id | c_array | array_contains(`c_array`, 5) |
+------+-----------------+------------------------------+
| 1 | [1, 2, 3, 4, 5] | 1 |
| 2 | [6, 7, 8] | 0 |
| 3 | [] | 0 |
| 4 | NULL | NULL |
+------+-----------------+------------------------------+
5.1.6.6array_except
ARRAY<T> array_except(ARRAY<T> array1, ARRAY<T> array2)
-- 返回一个数组，包含所有在array1内但不在array2内的元素，会对返回的结果数组去重
-- 类似于取差集，将返回的差集结果数组去重
mysql> set enable_vectorized_engine=true;
mysql> select k1,k2,k3,array_except(k2,k3) from array_type_table;
+------+-----------------+--------------+--------------------------+
| k1 | k2 | k3 | array_except(`k2`, `k3`) |
+------+-----------------+--------------+--------------------------+
| 1 | [1, 2, 3] | [2, 4, 5] | [1, 3] |
| 2 | [2, 3] | [1, 5] | [2, 3] |
| 3 | [1, 1, 1] | [2, 2, 2] | [1] |
+------+-----------------+--------------+--------------------------+
5.1.6.7array_intersect
ARRAY<T> array_intersect(ARRAY<T> array1, ARRAY<T> array2)
-- 返回一个数组，包含array1和array2的交集中的所有元素，不包含重复项
-- 两个数组去交集后。将返回的结果去重
mysql> set enable_vectorized_engine=true;
mysql> select k1,k2,k3,array_intersect(k2,k3) from array_type_table;
+------+-----------------+--------------+-----------------------------+
| k1 | k2 | k3 | array_intersect(`k2`, `k3`) |
+------+-----------------+--------------+-----------------------------+
| 1 | [1, 2, 3] | [2, 4, 5] | [2] |
| 2 | [2, 3] | [1, 5] | [] |
| 3 | [1, 1, 1] | [2, 2, 2] | [] |
+------+-----------------+--------------+-----------------------------+
mysql> select k1,k2,k3,array_intersect(k2,k3) from array_type_table_nullable;
+------+-----------------+--------------+-----------------------------+
| k1 | k2 | k3 | array_intersect(`k2`, `k3`) |
+------+-----------------+--------------+-----------------------------+
| 1 | [1, NULL, 3] | [1, 3, 5] | [1, 3] |
| 2 | [NULL, NULL, 2] | [2, NULL, 4] | [NULL, 2] |
| 3 | NULL | [1, 2, 3] | NULL |
+------+-----------------+--------------+-----------------------------+
5.1.6.8array_union
ARRAY<T> array_union(ARRAY<T> array1, ARRAY<T> array2)
-- 返回一个数组，包含array1和array2的并集中的所有元素，不包含重复项
-- 取两个数组的并集，将返回的结果去重
mysql> set enable_vectorized_engine=true;
mysql> select k1,k2,k3,array_union(k2,k3) from array_type_table;
+------+-----------------+--------------+-------------------------+
| k1 | k2 | k3 | array_union(`k2`, `k3`) |
+------+-----------------+--------------+-------------------------+
| 1 | [1, 2, 3] | [2, 4, 5] | [1, 2, 3, 4, 5] |
| 2 | [2, 3] | [1, 5] | [2, 3, 1, 5] |
| 3 | [1, 1, 1] | [2, 2, 2] | [1, 2] |
+------+-----------------+--------------+-------------------------+
5.1.6.9array_distinct
ARRAY<T> array_distinct(ARRAY<T> arr)
-- 返回去除了重复元素的数组，如果输入数组为NULL，则返回NULL。
mysql> set enable_vectorized_engine=true;
mysql> select k1, k2, array_distinct(k2) from array_test;
+------+-----------------------------+---------------------------+
| k1 | k2 | array_distinct(k2) |
+------+-----------------------------+---------------------------+
| 1 | [1, 2, 3, 4, 5] | [1, 2, 3, 4, 5] |
| 2 | [6, 7, 8] | [6, 7, 8] |
| 3 | [] | [] |
| 4 | NULL | NULL |
| 5 | [1, 2, 3, 4, 5, 4, 3, 2, 1] | [1, 2, 3, 4, 5] |
| 6 | [1, 2, 3, NULL] | [1, 2, 3, NULL] |
| 7 | [1, 2, 3, NULL, NULL] | [1, 2, 3, NULL] |
+------+-----------------------------+---------------------------+
5.1.7JSON函数
建表，导入测试数据
CREATE TABLE test_json (
id INT,
json_string String
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 3
PROPERTIES("replication_num" = "1");
--测试数据
{"k1":"v31", "k2": 300, "a1": [{"k1":"v41", "k2": 400}, 1, "a", 3.14]}
{"k1":"v32", "k2": 400, "a1": [{"k1":"v41", "k2": 400}, 2, "a", 4.14],"a2":{"k3":"v33", "k4": 200,"a2": [{"k1":"v41", "k2": 400}, 2, "a", 4.14]}}
{"k1":"v33", "k2": 500, "a1": [{"k1":"v41", "k2": 400}, 3, "a", 5.14],"a2":{"k3":"v33", "k4": 200,"a2": [{"k5":"v42", "k6": 600}]}}
{"k1":"v31"}
{"k1":"v31", "k2": 300}
{"k1":"v31", "k2": 200 "a1": []}
--json是一种里面存着一对对key，value类型的结构
--针对值类型的不同：
1.简单值："k1":"v31"
2.数组：[{"k1":"v41", "k2": 400}, 1, "a", 3.14]
3.对象："a2":{"k3":"v33", "k4": 200,"a2": [{"k5":"v42", "k6": 600}]}
取值的时候，指定的'$.k1'==>这样的东西我们称之为json path ，json的路劲
-- 通过本地文件的方式导入
curl \
-u root: \
-H "label:load_local_file1" \
-H "column_separator:_" \
-T /root/data/json.txt \
http://zuomm01:8040/api/test/test_json/_stream_load
-- 用insert into 的方式导入一条
INSERT INTO test_json VALUES(7, '{"k1":"v1", "k2": 200}');
5.1.7.1get_json_double，get_json_int，get_json_string
语法：
DOUBLE get_json_int(VARCHAR json_str, VARCHAR json_path)
INT get_json_int(VARCHAR json_str, VARCHAR json_path)
VARCHAR get_json_string(VARCHAR json_str, VARCHAR json_path)
-- 解析并获取 json 字符串内指定路径的double，int，string 类型的内容。
-- 其中 json_path 必须以 $ 符号作为开头，使用 . 作为路径分割符。
-- 如果路径中包含 . ，则可以使用双引号包围。
-- 使用 [ ] 表示数组下标，从 0 开始。
-- path 的内容不能包含 ", [ 和 ]。
-- 如果 json_string 格式不对，或 json_path 格式不对，或无法找到匹配项，则返回 NULL。
--1.获取到k1对应的value的值
mysql> select id, get_json_string(json_string,'$.k1') as k1 from test_json;
+------+------+
| id | k1 |
+------+------+
| 2 | v32 |
| 4 | v31 |
| 5 | v31 |
| 6 | v31 |
| 1 | v31 |
| 3 | v33 |
+------+------+
--2.获取到key 为a1 里面的数组
mysql> select id, get_json_string(json_string,'$.a1') as arr from test_json;
+------+------------------------------------+
| id | arr |
+------+------------------------------------+
| 1 | [{"k1":"v41","k2":400},1,"a",3.14] |
| 3 | [{"k1":"v41","k2":400},3,"a",5.14] |
| 2 | [{"k1":"v41","k2":400},2,"a",4.14] |
| 4 | NULL |
| 5 | NULL |
| 6 | [] |
+------+------------------------------------+
--3.获取到key 为a1 里面的数组中第一个元素的值
mysql> select id, get_json_string(json_string,'$.a1[0]') as arr from test_json;
+------+-----------------------+
| id | arr |
+------+-----------------------+
| 2 | {"k1":"v41","k2":400} |
| 1 | {"k1":"v41","k2":400} |
| 3 | {"k1":"v41","k2":400} |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+------+-----------------------+
--4.获取到key 为a1 里面的数组中第一个元素的值（这个值是一个json串，再次获取到这个字符串中）
select id, get_json_string(get_json_string(json_string,'$.a1[0]'),'$.k1') as arr from test_json;
+------+------+
| id | arr |
+------+------+
| 2 | v41 |
| 1 | v41 |
| 3 | v41 |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+------+------+
6 rows in set (0.02 sec)
5.1.7.2json_object
VARCHAR json_object(VARCHAR,...)
-- 生成一个包含指定Key-Value对的json object,
-- 传入的参数是key,value对，且key不能为null
87
MySQL> select json_object('time',curtime());
+--------------------------------+
| json_object('time', curtime()) |
+--------------------------------+
| {"time": "10:49:18"} |
+--------------------------------+
MySQL> SELECT json_object('id', 87, 'name', 'carrot');
+-----------------------------------------+
| json_object('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"} |
+-----------------------------------------+
json_object('id', 87, 'name', 'carrot');
MySQL> select json_object('username',null);
+---------------------------------+
| json_object('username', 'NULL') |
+---------------------------------+
| {"username": NULL} |
+---------------------------------+
5.1.8窗口函数
doris中的窗口函数和hive中的窗口函数的用法一样
5.1.8.1ROW_NUMBER()，DENSE_RANK()，RANK()
-- 测试rank打行号，名次相同会并列排名，比如两个第一名，就是1 1 然后第二名会显示3
select x, y, rank() over(partition by x order by y) as rank from int_t;
| x | y | rank |
|----|------|----------|
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 2 | 2 |
| 2 | 1 | 1 |
| 2 | 2 | 2 |
| 2 | 3 | 3 |
| 3 | 1 | 1 |
| 3 | 1 | 1 |
| 3 | 2 | 3 |
-- 测试dense_rank(),名词相同会并列排名，比如两个第一名，就是1 1 然后第二名会显示2
select x, y, dense_rank() over(partition by x order by y) as rank from int_t;
| x | y | rank |
|----|------|----------|
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 2 | 2 |
| 2 | 1 | 1 |
| 2 | 2 | 2 |
| 2 | 3 | 3 |
| 3 | 1 | 1 |
| 3 | 1 | 1 |
| 3 | 2 | 2 |
-- 测试ROW_NUMBER() 按照分组排序要求，返回的编号依次底层，1 2 3 4 5 ，
-- 不会有重复值，也不会有空缺值，就是连续递增的整数，从1 开始
select x, y, row_number() over(partition by x order by y) as rank from int_t;
| x | y | rank |
|---|------|----------|
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 2 | 3 |
| 2 | 1 | 1 |
| 2 | 2 | 2 |
| 2 | 3 | 3 |
| 3 | 1 | 1 |
| 3 | 1 | 2 |
| 3 | 2 | 3 |
小练习：
-- 案例数据
孙悟空,语文,87
孙悟空,数学,95
娜娜,英语,84
宋宋,语文,64
孙悟空,英语,68
宋宋,英语,84
婷婷,语文,65
娜娜,语文,94
宋宋,数学,86
婷婷,数学,85
娜娜,数学,56
婷婷,英语,78
-- 建表语句
create table stu
(
name varchar(50),
subject varchar(50),
score double
)
DUPLICATE KEY(name)
DISTRIBUTED BY HASH(name) BUCKETS 1;
-- 通过本地文件的方式导入数据
curl \
-u root: \
-H "label:num_test" \
-H "column_separator:," \
-T /root/data/stu.txt \
http://zuomm01:8040/api/test/stu/_stream_load
需求：
【相同分数并列(假设第一名有两个，排名就是并列第一，然后第三名从2开始)】
1.按照分数降序排序，求每个学科中每个人的名次
2.按照每个人的总分进行升序排列，得到每个人总分名次的名次
【相同分数并列(假设第一名有两个，排名就是并列第一，然后第三名从3开始)】
3.按照学科进行升序排列，得到每个人的每个学科的名次
4.按照每个人的总分进行升序排列，得到每个人总分名次的名次
【相同分数并列
(假设第一名有两个，排名就是并列第一，
就再单独比语文的成绩，然后数学，最后英语，
分数全部一样，按照学生名字的字典顺序，在前的为第一)】
5.按照每个人的总分进行升序排列，得到每个人总分名次的名次
sql：
-- 1.按照学科进行升序排列，得到每个人的每个学科的名次
select
name,subject,score,
dense_rank() over(partition by subject order by score desc) as rank
from stu
+-----------+---------+-------+------+
| name | subject | score | rank |
+-----------+---------+-------+------+
| 孙悟空 | 数学 | 95 | 1 |
| 宋宋 | 数学 | 86 | 2 |
| 婷婷 | 数学 | 85 | 3 |
| 娜娜 | 数学 | 56 | 4 |
| 娜娜 | 英语 | 84 | 1 |
| 宋宋 | 英语 | 84 | 1 |
| 婷婷 | 英语 | 78 | 2 |
| 孙悟空 | 英语 | 68 | 3 |
| 娜娜 | 语文 | 94 | 1 |
| 孙悟空 | 语文 | 87 | 2 |
| 婷婷 | 语文 | 65 | 3 |
| 宋宋 | 语文 | 64 | 4 |
+-----------+---------+-------+------+
-- 2.按照每个人的总分进行升序排列，得到每个人总分名次的名次
select
name,sum_score,
-- 因为是整体按照学生的总分进行求名次，所有学生为1组，就不需要分组了
dense_rank() over(order by sum_score desc) as rank
from
(
select
name,sum(score) as sum_score
from stu
group by name
) as t ;
+-----------+-----------+------+
| name | sum_score | rank |
+-----------+-----------+------+
| 孙悟空 | 250 | 1 |
| 宋宋 | 234 | 2 |
| 娜娜 | 234 | 2 |
| 婷婷 | 228 | 3 |
+-----------+-----------+------+
【相同分数并列(假设第一名有两个，排名就是并列第一，然后第三名从3开始)】
-- 3.按照学科进行升序排列，得到每个人的每个学科的名次
select
name,subject,score,
rank() over(partition by subject order by score desc) as rank
from stu
+-----------+---------+-------+------+
| name | subject | score | rank |
+-----------+---------+-------+------+
| 孙悟空 | 数学 | 95 | 1 |
| 宋宋 | 数学 | 86 | 2 |
| 婷婷 | 数学 | 85 | 3 |
| 娜娜 | 数学 | 56 | 4 |
| 娜娜 | 英语 | 84 | 1 |
| 宋宋 | 英语 | 84 | 1 |
| 婷婷 | 英语 | 78 | 3 |
| 孙悟空 | 英语 | 68 | 4 |
| 娜娜 | 语文 | 94 | 1 |
| 孙悟空 | 语文 | 87 | 2 |
| 婷婷 | 语文 | 65 | 3 |
| 宋宋 | 语文 | 64 | 4 |
+-----------+---------+-------+------+
-- 4.按照每个人的总分进行升序排列，得到每个人总分名次的名次
select
name,sum_score,
-- 因为是整体按照学生的总分进行求名次，所有学生为1组，就不需要分组了
rank() over(order by sum_score desc) as rank
from
(
select
name,sum(score) as sum_score
from stu
group by name
) as t ;
+-----------+-----------+------+
| name | sum_score | rank |
+-----------+-----------+------+
| 孙悟空 | 250 | 1 |
| 宋宋 | 234 | 2 |
| 娜娜 | 234 | 2 |
| 婷婷 | 228 | 4 |
+-----------+-----------+------+
【相同分数并列
(假设第一名有两个，排名就是并列第一，
就再单独比语文的成绩，然后数学，最后英语，
分数全部一样，按照学生名字的字典顺序，在前的为第一)】
-- 5.按照每个人的总分进行升序排列，得到每个人总分名次的名次
--方案1：利用窗口函数来列转行
select
name,subject,score as math_score,english_score,chinese_score,sum_score,
row_number()over(order by sum_score desc ,chinese_score desc ,score desc ,english_score desc,name asc) as num
from
(
select
name,subject,score,
lead(score,1,0)over(partition by name order by subject) as english_score,
lead(score,2,0)over(partition by name order by subject) as chinese_score,
sum(score)over(partition by name) as sum_score,
row_number()over(partition by name) as num
from stu
) as tmp
where num = 1
-- 方案2：利用if判断来列转行
select
name,chinese_score,match_score,english_score,sum_score,
row_number()over(order by sum_score desc ,chinese_score desc ,match_score desc ,english_score desc,name asc) as num
from
(
select
name,
sum(chinese_score) as chinese_score,
sum(match_score) as match_score,
sum(english_score) as english_score,
sum(chinese_score) + sum(match_score) + sum(english_score) as sum_score
from
(
select name,subject,
if(subject = '语文',score,0) as chinese_score,
if(subject = '数学',score,0) as match_score,
if(subject = '英语',score,0) as english_score
from stu
)as t
group by name
) as t1
+-----------+---------+------------+---------------+---------------+-----------+------+
| name | subject | math_score | english_score | chinese_score | sum_score | num |
+-----------+---------+------------+---------------+---------------+-----------+------+
| 孙悟空 | 数学 | 95 | 68 | 87 | 250 | 1 |
| 娜娜 | 数学 | 56 | 84 | 94 | 234 | 2 |
| 宋宋 | 数学 | 86 | 84 | 64 | 234 | 3 |
| 婷婷 | 数学 | 85 | 78 | 65 | 228 | 4 |
+-----------+---------+------------+---------------+---------------+-----------+------+
5.1.8.2 min，max，sum，avg，count
min(x)over() -- 取窗口中x列的最小值
max(x)over() -- 取窗口中x列的最大值
sum(x)over() -- 取窗口中x列的数据总和
avg(x)over() -- 取窗口中x列的数据平均值
count(x)over() -- 取窗口中x列有多少行
unbounded preceding
current row
1 following
1 PRECEDING
rows between unbounded preceding and current row --指在当前窗口中第一行到当前行的范围
rows between unbounded preceding and 1 following --指在当前窗口中第一行到当前行下一行的范围
rows between unbounded preceding and 1 PRECEDING --指在当前窗口中第一行到当前行前一行的范围
5.1.8.3LEAD() ，LAG()
-- LAG() 方法用来计算当前行向前数若干行的值。
LAG(expr, offset, default) OVER (partition_by_clause order_by_clause)
-- LEAD() 方法用来计算当前行向后数若干行的值。
LEAD(expr, offset, default]) OVER (partition_by_clause order_by_clause)
5.1.8.4窗口函数综合练习
1.打地鼠案例
需求：连续4次命中的人
-- seq:第几次打地鼠
-- m：是否命中，1-》命中，0-》未命中
uid,seq,m
u01,1,1
u01,2,0
u01,3,1
u01,6,1
u02,5,1
u02,6,0
u02,7,0
u02,1,1
u02,2,1
u03,4,1
u03,5,1
u03,6,0
u02,3,0
u02,4,1
u02,8,1
u01,4,1
u01,5,0
u02,9,1
u03,1,1
u03,2,1
u03,3,1
--建表语句
create table hit_mouse
(
user_id varchar(50),
seq int,
m int
)
DUPLICATE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 1;
-- 通过本地文件的方式导入数据
curl \
-u root: \
-H "label:hit_mouse" \
-H "column_separator:," \
-T /root/data/hit_mouse.txt \
http://zuomm01:8040/api/test/hit_mouse/_stream_load
逻辑分析：
1.首先排除没有命中的数据
2.对每个用户进行分组，按照打地鼠编号的升序进行排序，打行号
3.用打地鼠的编号 - 所打的行号 ==》 如果连续命中的话，最后得到的结果应该是相等的
4.count出一样的结果的个数，满足大于等于4的就是我们需要的结果
-- 排除没有命中的数据,开窗打编号
select
user_id,seq,m,
row_number()over(partition by user_id order by seq asc) as num
from hit_mouse
where m != 0 ;
+---------+------+------+------+
| user_id | seq | m | num |
+---------+------+------+------+
| u01 | 1 | 1 | 1 | ==新的值
| u01 | 3 | 1 | 2 |
| u01 | 4 | 1 | 3 |
| u01 | 6 | 1 | 4 |
| u02 | 1 | 1 | 1 |
| u02 | 2 | 1 | 2 |
| u02 | 4 | 1 | 3 |
| u02 | 5 | 1 | 4 |
| u02 | 8 | 1 | 5 |
| u02 | 9 | 1 | 6 |
| u03 | 1 | 1 | 1 |
| u03 | 2 | 1 | 2 |
| u03 | 3 | 1 | 3 |
| u03 | 4 | 1 | 4 |
| u03 | 5 | 1 | 5 |
+---------+------+------+------+
-- 将上面的语句改变下，现在需要的是编号减去行号，可否直接拿编号减行号呢？
select
user_id,seq,m,
seq -row_number()over(partition by user_id order by seq asc) as num
from hit_mouse
where m != 0 ;
+---------+------+------+------+
| user_id | seq | m | num |
+---------+------+------+------+
| u01 | 1 | 1 | 0 |
| u01 | 3 | 1 | 1 |
| u01 | 4 | 1 | 1 |
| u01 | 6 | 1 | 2 |
| u02 | 1 | 1 | 0 |
| u02 | 2 | 1 | 0 |
| u02 | 4 | 1 | 1 |
| u02 | 5 | 1 | 1 |
| u02 | 8 | 1 | 3 |
| u02 | 9 | 1 | 3 |
| u03 | 1 | 1 | 0 |
| u03 | 2 | 1 | 0 |
| u03 | 3 | 1 | 0 |
| u03 | 4 | 1 | 0 |
| u03 | 5 | 1 | 0 |
+---------+------+------+------+
-- 看num重复的个数(在同一个user_id中)
select
user_id,
count(1) as cnt
from
(
select
user_id,seq,m,
seq -row_number()over(partition by user_id order by seq asc) as num
from hit_mouse
where m != 0
) as t
group by user_id,num
having cnt>=4
-- 得到最后的结果
+---------+------+
| user_id | cnt |
+---------+------+
| u03 | 5 |
+---------+------+
方案二：在不需要返回具体连续命中多少次，只需要返回user_id的情况下，还可以这么做
1.在去掉了未命中数据后
2.开窗，拿当前行下面的第三行数据，如果说该用户是连续登录的，
必然下面第三行的序号等于第一行的序号加3，如果结果不等于3，他们必然是不连续的，
并且结果只可能大于3，中间有为名中的被过滤了
3.最后查看结果等于3的用户并返回即可
select
user_id,seq,m,
(lead(seq,3,-4)over(partition by user_id order by seq asc) - seq) as diff
from hit_mouse
where m != 0
+---------+------+------+------+
| user_id | seq | m | diff |
+---------+------+------+------+
| u01 | 1 | 1 | 5 |
| u01 | 3 | 1 | -7 |
| u01 | 4 | 1 | -8 |
| u01 | 6 | 1 | -10 |
| u02 | 1 | 1 | 4 |
| u02 | 2 | 1 | 6 |
| u02 | 4 | 1 | 5 |
| u02 | 5 | 1 | -9 |
| u02 | 8 | 1 | -12 |
| u02 | 9 | 1 | -13 |
| u03 | 1 | 1 | 3 |
| u03 | 2 | 1 | 3 |
| u03 | 3 | 1 | -7 |
| u03 | 4 | 1 | -8 |
| u03 | 5 | 1 | -9 |
+---------+------+------+------+
-- 最后判断diff的差值是否=3，然后返回对应的user_id(有可能会有多条相同的数据，去重)
select
user_id
from
(
select
user_id,seq,m,
(lead(seq,3,-4)over(partition by user_id order by seq asc) - seq) as res
from hit_mouse
where m != 0
) as t
where res = 3
-- group by 去重
group by user_id
+---------+
| user_id |
+---------+
| u03 |
+---------+
2.连续购买案例
需求：连续三天以上有销售记录的店铺名称
-- 数据准备
a,2017-02-05,100
a,2017-02-06,300
a,2017-02-07,800
a,2017-02-08,500
a,2017-02-10,700
b,2017-02-05,200
b,2017-02-06,400
b,2017-02-08,100
b,2017-02-09,400
b,2017-02-10,600
c,2017-01-31,200
c,2017-02-01,600
c,2017-02-02,600
c,2017-02-03,600
c,2017-02-10,700
a,2017-03-01,400
a,2017-03-02,300
a,2017-03-03,700
a,2017-03-04,400
--建表语句
create table shop_sale
(
shop_id varchar(50),
dt date,
amount double
)
DUPLICATE KEY(shop_id)
DISTRIBUTED BY HASH(shop_id) BUCKETS 1;
-- 通过本地文件的方式导入数据
curl \
-u root: \
-H "label:shop_sale" \
-H "column_separator:," \
-T /root/data/shop_sale.txt \
http://zuomm01:8040/api/test/shop_sale/_stream_load
逻辑分析：
这样的连续销售记录问题(连续登录问题)和上面打地鼠的需求是一样的
1.按照店铺分组，对日期排序后打行号
2.用日期减去行号，得到的新的日期值，如果新的日期相同的话就代表是连续的
3.统计相同新日期的个数，来判断连续登录了几天
select
shop_id,new_date,
count(1) as cnt
from
(
select shop_id,dt,
date_sub(dt,row_number()over(partition by shop_id order by dt))as new_date
from shop_sale
) as t
group by shop_id,new_date
having cnt >=3;
+---------+------+
| shop_id | cnt |
+---------+------+
| a | 4 |
| a | 4 |
| b | 3 |
| c | 4 |
+---------+------+
方案二：
需要求连续三天的，我们取下面的第二行日期，拿取过来的下面的日期对当前行的日期相减，取间隔几天
如果他们的值 = 2 就代表是连续的
select
shop_id
from
(
select shop_id,dt,
datediff(lead(dt,2,'9999-12-31')over(partition by shop_id order by dt),dt)as day_diff_num
from shop_sale
) as t
where day_diff_num = 2
-- 给店铺去重
group by shop_id
+---------+
| shop_id |
+---------+
| c |
| a |
| b |
+---------+
3.分组topn案例
需求：
基于上面的表，求每个店铺金额最大的前三条订单 (row_number over)
求每个店铺销售金额前三名的订单
逻辑分析：
a,2017-02-07,800
a,2017-02-10,700
a,2017-02-08,500
a,2017-02-06,300
a,2017-02-05,100
b,2017-02-05,200
b,2017-02-06,400
b,2017-02-08,100
b,2017-02-09,400
b,2017-02-10,600
c,2017-02-10,700
c,2017-02-01,600
c,2017-02-02,600
c,2017-02-03,600
c,2017-01-31,200
d,2017-03-01,400
d,2017-03-02,300
d,2017-03-03,700
d,2017-03-04,400
select
shop_id,dt,amount
from
(
select
shop_id,dt,amount,
row_number()over(partition by shop_id order by amount desc) as num
from shop_sale
) as tmp
where num <=3
4.经典案例：遇到标志划分组
需求：将上面的表转化成下面的形式，首先按照用户进行分组，
在用户分组的基础上，name字段每遇到一个e*就分一组
user_id,name
u1,e1
u1,e1
u1,e*
u1,e2
u1,e3
u1,e*
u2,e1
u2,e2
u2,e*
u2,e1
u2,e3
u2,e*
u2,e*
上面的用户行为记录，每遇到一个e*，就分到一组，得到如下结果：
u1, [e1,e1,e*]
u1, [e2,e3,e*]
u2, [e1,e2,e*]
u2, [e1,e3,e*]
u2, [e*]
--建表语句
drop table if exists window_test;
create table window_test
(
user_id varchar(10),
name string
)
DUPLICATE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 1;
-- 为了保证原数据的插入顺序，一条一条的inert 进去
insert into window_test values ('u1','e1');
insert into window_test values ('u1','e1');
insert into window_test values ('u1','e*');
insert into window_test values ('u1','e2');
insert into window_test values ('u1','e3');
insert into window_test values ('u1','e*');
insert into window_test values ('u2','e1');
insert into window_test values ('u2','e2');
insert into window_test values ('u2','e*');
insert into window_test values ('u2','e1');
insert into window_test values ('u2','e3');
insert into window_test values ('u2','e*');
insert into window_test values ('u2','e*');
逻辑分析：
1.我们需要关注的是name字段中的值是不是e*,所以可以将它转换成flag，1 0这样的标签
2.按照用户分组，来打行号(注意：这边必须要按照原来数据的顺序)
3.开窗，将flag的值从第一行加到当前行
4.将开窗的结果和原flag进行相减，得到一个新的flag标签结果
5.按照用户和新的标签结果进行分组，收集即可
-- 添加标签
select
user_id,
name,
if(name = 'e*',1,0) as flag
from window_test
+---------+------+------+
| user_id | name | flag |
+---------+------+------+
| u1 | e1 | 0 |
| u1 | e1 | 0 |
| u1 | e* | 1 |
| u1 | e2 | 0 |
| u1 | e3 | 0 |
| u1 | e* | 1 |
| u2 | e1 | 0 |
| u2 | e2 | 0 |
| u2 | e* | 1 |
| u2 | e1 | 0 |
| u2 | e3 | 0 |
| u2 | e* | 1 |
| u2 | e* | 1 |
+---------+------+------+
select
user_id,
name,
flag,
sum(flag)over(partition by user_id order by user_id rows between unbounded preceding and current row) as sum_flag
from
(
select
user_id,
name,
if(name = 'e*',1,0) as flag
from window_test
) as t;
+---------+------+------+----------+
| user_id | name | flag | sum_flag |
+---------+------+------+----------+
| u1 | e1 | 0 | 0 |
| u1 | e1 | 0 | 0 |
| u1 | e* | 1 | 1 |
| u1 | e2 | 0 | 1 |
| u1 | e3 | 0 | 1 |
| u1 | e* | 1 | 2 |
| u2 | e1 | 0 | 0 |
| u2 | e2 | 0 | 0 |
| u2 | e* | 1 | 1 |
| u2 | e1 | 0 | 1 |
| u2 | e3 | 0 | 1 |
| u2 | e* | 1 | 2 |
| u2 | e* | 1 | 3 |
+---------+------+------+----------+
观察现象，现在想把user_id和按照遇到e*就分组的这哥逻辑去处理的话，需要一个新标签，同一组相等
可以拿sum_flag - flag 得到的结果就是我们想要的
（plan2：或者拿取sum_flag 上面的一行数据，如果没有数据拿，默认就是0 ，这样的话也行）
select
user_id,
name,
flag,
sum(flag)over(partition by user_id order by user_id rows between unbounded preceding and current row) -flag as diff_flag
from
(
select
user_id,
name,
if(name = 'e*',1,0) as flag
from window_test
) as t;
+---------+------+------+-----------+
| user_id | name | flag | diff_flag |
+---------+------+------+-----------+
| u1 | e1 | 0 | 0 |
| u1 | e1 | 0 | 0 |
| u1 | e* | 1 | 0 |
| u1 | e2 | 0 | 1 |
| u1 | e3 | 0 | 1 |
| u1 | e* | 1 | 1 |
| u2 | e1 | 0 | 0 |
| u2 | e2 | 0 | 0 |
| u2 | e* | 1 | 0 |
| u2 | e1 | 0 | 1 |
| u2 | e3 | 0 | 1 |
| u2 | e* | 1 | 1 |
| u2 | e* | 1 | 2 |
+---------+------+------+-----------+
最后只要group by之后收集就行了，注意收集的时候没有collect_set 和ollect_list，只有group_concat()
select
user_id,
group_concat(name,',') as res
from
(
select
user_id,
name,
flag,
sum(flag)over(partition by user_id order by user_id rows between unbounded preceding and current row) -flag as diff_flag
from
(
select
user_id,
name,
if(name = 'e*',1,0) as flag
from window_test
) as t
) as t1
group by user_id,diff_flag
+---------+----------+
| user_id | res |
+---------+----------+
| u1 | e1,e1,e* |
| u2 | e* |
| u1 | e2,e3,e* |
| u2 | e1,e2,e* |
| u2 | e1,e3,e* |
+---------+----------+
5.2综合案例之漏斗转化分析
业务目标、到达路径，路径步骤、步骤人数，步骤之间的相对转换率和绝对转换率
每一种业务都有他的核心任务和流程，而流程的每一个步骤，都可能有用户流失。 所以如果把每一个步骤及其对应的数据（如UV）拼接起来，就会形成一个上大下小的漏斗形态，这就是漏斗模型。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W2bPDnmO-1677043665265)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230221135647381.png)]
漏斗模型示例：
不同的业务场景有不同的业务路径 : 有先后顺序, 事件可以出现多次
注册转化漏斗 : 启动APP --> APP注册页面—>注册结果 -->提交订单–>支付成功
搜购转化漏斗 : 搜索商品–> 点击商品—>加入购物车–>提交订单–>支付成功
秒杀活动选购转化漏斗: 点击秒杀活动–>参加活动—>参与秒杀–>秒杀成功—>成功支付
电商的购买转化漏斗模型图：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g2MW2tL5-1677043665266)(C:\Users\10575\AppData\Roaming\Typora\typora-user-images\image-20230222131651882.png)]
处理步骤 :
明确漏斗名称：购买转化漏斗
起始事件：浏览了商品的详情页
目标事件：支付
业务流程事件链路：详情页->购物车->下单页->支付
[事件之间有没有时间间隔要求 , 链路中相邻的两个事件是否可以有其他事件]
需求：求购买转化漏斗模型的转换率(事件和事件之间没有时间间隔要求，并且相邻两个事件可以去干其他的事)
1.每一个步骤的uv
2.相对的转换率（下一个步骤的uv/上一个步骤的UV），绝对的转换率(当前步骤的UV第一步骤的UV)
关心的事件：e1,e2,e4,e5 ==> 先后顺序不能乱
-- 准备数据
user_id event_id event_action event_time
u001,e1,view_detail_page,2022-11-01 01:10:21
u001,e2,add_bag_page,2022-11-01 01:11:13
u001,e3,collect_goods_page,2022-11-01 02:07:11
u002,e3,collect_goods_page,2022-11-01 01:10:21
u002,e4,order_detail_page,2022-11-01 01:11:13
u002,e5,pay_detail_page,2022-11-01 02:07:11
u002,e6,click_adver_page,2022-11-01 13:07:23
u002,e7,home_page,2022-11-01 08:18:12
u002,e8,list_detail_page,2022-11-01 23:34:29
u002,e1,view_detail_page,2022-11-01 11:25:32
u002,e2,add_bag_page,2022-11-01 12:41:21
u002,e3,collect_goods_page,2022-11-01 16:21:15
u002,e4,order_detail_page,2022-11-01 21:41:12
u003,e5,pay_detail_page,2022-11-01 01:10:21
u003,e6,click_adver_page,2022-11-01 01:11:13
u003,e7,home_page,2022-11-01 02:07:11
u001,e4,order_detail_page,2022-11-01 13:07:23
u001,e5,pay_detail_page,2022-11-01 08:18:12
u001,e6,click_adver_page,2022-11-01 23:34:29
u001,e7,home_page,2022-11-01 11:25:32
u001,e8,list_detail_page,2022-11-01 12:41:21
u001,e1,view_detail_page,2022-11-01 16:21:15
u001,e2,add_bag_page,2022-11-01 21:41:12
u003,e8,list_detail_page,2022-11-01 13:07:23
u003,e1,view_detail_page,2022-11-01 08:18:12
u003,e2,add_bag_page,2022-11-01 23:34:29
u003,e3,collect_goods_page,2022-11-01 11:25:32
u003,e4,order_detail_page,2022-11-01 12:41:21
u003,e5,pay_detail_page,2022-11-01 16:21:15
u003,e6,click_adver_page,2022-11-01 21:41:12
u004,e7,home_page,2022-11-01 01:10:21
u004,e8,list_detail_page,2022-11-01 01:11:13
u004,e1,view_detail_page,2022-11-01 02:07:11
u004,e2,add_bag_page,2022-11-01 13:07:23
u004,e3,collect_goods_page,2022-11-01 08:18:12
u004,e4,order_detail_page,2022-11-01 23:34:29
u004,e5,pay_detail_page,2022-11-01 11:25:32
u004,e6,click_adver_page,2022-11-01 12:41:21
u004,e7,home_page,2022-11-01 16:21:15
u004,e8,list_detail_page,2022-11-01 21:41:12
u005,e1,view_detail_page,2022-11-01 01:10:21
u005,e2,add_bag_page,2022-11-01 01:11:13
u005,e3,collect_goods_page,2022-11-01 02:07:11
u005,e4,order_detail_page,2022-11-01 13:07:23
u005,e5,pay_detail_page,2022-11-01 08:18:12
u005,e6,click_adver_page,2022-11-01 23:34:29
u005,e7,home_page,2022-11-01 11:25:32
u005,e8,list_detail_page,2022-11-01 12:41:21
u005,e1,view_detail_page,2022-11-01 16:21:15
u005,e2,add_bag_page,2022-11-01 21:41:12
u005,e3,collect_goods_page,2022-11-01 01:10:21
u006,e4,order_detail_page,2022-11-01 01:11:13
u006,e5,pay_detail_page,2022-11-01 02:07:11
u006,e6,click_adver_page,2022-11-01 13:07:23
u006,e7,home_page,2022-11-01 08:18:12
u006,e8,list_detail_page,2022-11-01 23:34:29
u006,e1,view_detail_page,2022-11-01 11:25:32
u006,e2,add_bag_page,2022-11-01 12:41:21
u006,e3,collect_goods_page,2022-11-01 16:21:15
u006,e4,order_detail_page,2022-11-01 21:41:12
u006,e5,pay_detail_page,2022-11-01 23:10:21
u006,e6,click_adver_page,2022-11-01 01:11:13
u007,e7,home_page,2022-11-01 02:07:11
u007,e8,list_detail_page,2022-11-01 13:07:23
u007,e1,view_detail_page,2022-11-01 08:18:12
u007,e2,add_bag_page,2022-11-01 23:34:29
u007,e3,collect_goods_page,2022-11-01 11:25:32
u007,e4,order_detail_page,2022-11-01 12:41:21
u007,e5,pay_detail_page,2022-11-01 16:21:15
u007,e6,click_adver_page,2022-11-01 21:41:12
u007,e7,home_page,2022-11-01 01:10:21
u008,e8,list_detail_page,2022-11-01 01:11:13
u008,e1,view_detail_page,2022-11-01 02:07:11
u008,e2,add_bag_page,2022-11-01 13:07:23
u008,e3,collect_goods_page,2022-11-01 08:18:12
u008,e4,order_detail_page,2022-11-01 23:34:29
u008,e5,pay_detail_page,2022-11-01 11:25:32
u008,e6,click_adver_page,2022-11-01 12:41:21
u008,e7,home_page,2022-11-01 16:21:15
u008,e8,list_detail_page,2022-11-01 21:41:12
u008,e1,view_detail_page,2022-11-01 01:10:21
u009,e2,add_bag_page,2022-11-01 01:11:13
u009,e3,collect_goods_page,2022-11-01 02:07:11
u009,e4,order_detail_page,2022-11-01 13:07:23
u009,e5,pay_detail_page,2022-11-01 08:18:12
u009,e6,click_adver_page,2022-11-01 23:34:29
u009,e7,home_page,2022-11-01 11:25:32
u009,e8,list_detail_page,2022-11-01 12:41:21
u009,e1,view_detail_page,2022-11-01 16:21:15
u009,e2,add_bag_page,2022-11-01 21:41:12
u009,e3,collect_goods_page,2022-11-01 01:10:21
u010,e4,order_detail_page,2022-11-01 01:11:13
u010,e5,pay_detail_page,2022-11-01 02:07:11
u010,e6,click_adver_page,2022-11-01 13:07:23
u010,e7,home_page,2022-11-01 08:18:12
u010,e8,list_detail_page,2022-11-01 23:34:29
u010,e5,pay_detail_page,2022-11-01 11:25:32
u010,e6,click_adver_page,2022-11-01 12:41:21
u010,e7,home_page,2022-11-01 16:21:15
u010,e8,list_detail_page,2022-11-01 21:41:12
-- 创建表
drop table if exists event_info_log;
create table event_info_log
(
user_id varchar(20),
event_id varchar(20),
event_action varchar(20),
event_time datetime
)
DUPLICATE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 1;
-- 通过本地文件的方式导入数据
curl \
-u root: \
-H "label:event_info_log" \
-H "column_separator:," \
-T /root/data/event_log.txt \
http://linux01:8040/api/test/event_info_log/_stream_load
逻辑分析：
--1. 先将用户的事件序列，按照漏斗模型定义的条件进行过滤，留下满足条件的事件
--2. 将同一个人的满足条件的事件ID收集到数组,按时间先后排序,拼接成字符串
--3. 将拼接好的字符串，匹配漏斗模型抽象出来的正则表达式
1.筛选时间条件，确定每个人的事件序列
select
user_id,
max(event_ll) as event_seq
from
(
select
user_id,
group_concat(event_id)over(partition by user_id order by report_date) as event_ll
from
(
select
user_id,event_id,report_date
from event_info_log
where event_id in ('e1','e2','e4','e5')
and to_date(report_date) = '2022-11-01'
order by user_id,report_date
) as temp
) as temp2
group by user_id;
+---------+------------------------+
| user_id | event_ll |
+---------+------------------------+
| u006 | e4, e5, e1, e2, e4, e5 |
| u007 | e1, e4, e5, e2 |
| u005 | e1, e2, e5, e4, e1, e2 |
| u004 | e1, e5, e2, e4 |
| u010 | e4, e5, e5 |
| u001 | e1, e2, e5, e4, e1, e2 |
| u003 | e5, e1, e4, e5, e2 |
| u002 | e4, e5, e1, e2, e4 |
| u008 | e1, e1, e5, e2, e4 |
| u009 | e2, e5, e4, e1, e2 |
+---------+------------------------+
2.确定匹配规则模型
select
user_id,
'购买转化漏斗' as funnel_name ,
case
-- 正则匹配，先触发过e1，在触发过e2，在触发过e4，在触发过e5
when event_seq rlike('e1.*e2.*e4.*e5') then 4
-- 正则匹配，先触发过e1，在触发过e2，在触发过e4
when event_seq rlike('e1.*e2.*e4') then 3
-- 正则匹配，先触发过e1，在触发过e2
when event_seq rlike('e1.*e2') then 2
-- 正则匹配，只触发过e1
when event_seq rlike('e1') then 1
else 0 end step
from
(
select
user_id,
max(event_ll) as event_seq
from
(
select
user_id,
group_concat(event_id)over(partition by user_id order by report_date) as event_ll
from
(
select
user_id,event_id,report_date
from event_info_log
where event_id in ('e1','e2','e4','e5')
and to_date(report_date) = '2022-11-01'
order by user_id,report_date
) as temp
) as temp2
group by user_id
) as tmp3;
+---------+--------------------+------+
| user_id | funnel_name | step |
+---------+--------------------+------+
| u006 | 购买转化漏斗 | 4 |
| u007 | 购买转化漏斗 | 2 |
| u005 | 购买转化漏斗 | 3 |
| u004 | 购买转化漏斗 | 3 |
| u010 | 购买转化漏斗 | 0 |
| u001 | 购买转化漏斗 | 3 |
| u003 | 购买转化漏斗 | 2 |
| u002 | 购买转化漏斗 | 3 |
| u008 | 购买转化漏斗 | 3 |
| u009 | 购买转化漏斗 | 2 |
+---------+--------------------+------+
-- 最后计算转换率
select
funnel_name,
sum(if(step >= 1 ,1,0)) as step1,
sum(if(step >= 2 ,1,0)) as step2,
sum(if(step >= 3 ,1,0)) as step3,
sum(if(step >= 4 ,1,0)) as step4,
round(sum(if(step >= 2 ,1,0))/sum(if(step >= 1 ,1,0)),2) as 'step1->step2_radio',
round(sum(if(step >= 3 ,1,0))/sum(if(step >= 2 ,1,0)),2) as 'step2->step3_radio',
round(sum(if(step >= 4 ,1,0))/sum(if(step >= 3 ,1,0)),2) as 'step3->step4_radio'
from
(
select
'购买转化漏斗' as funnel_name ,
case
-- 正则匹配，先触发过e1，在触发过e2，在触发过e4，在触发过e5
when event_seq regexp('e1.*e2.*e4.*e5') then 4
-- 正则匹配，先触发过e1，在触发过e2，在触发过e4
when event_seq regexp('e1.*e2.*.*e4') then 3
-- 正则匹配，先触发过e1，在触发过e2
when event_seq regexp('e1.*e2') then 2
-- 正则匹配，只触发过e1
when event_seq regexp('e1') then 1
else 0 end step
from
(
select
user_id,
max(event_seq) as event_seq
from
-- 因为在doris1.1版本中还不支持数组，所以拼接字符串的时候还没办法排序
(
select
user_id,
-- 用开窗的方式进行排序，然后在有序的按照时间升序，将事件拼接
group_concat(concat(report_date,'_',event_id),'|')over(partition by user_id order by report_date) as event_seq
from event_info_log
where to_date(report_date) = '2022-11-01'
and event_id in('e1','e4','e5','e2')
) as tmp
group by user_id
) as t1
) as t2
group by funnel_name;
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
| funnel_name | step1 | step2 | step3 | step4 | step1->step2_radio | step2->step3_radio | step3->step4_radio |
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
| 购买转化漏斗 | 9 | 9 | 6 | 1 | 1 | 0.67 | 0.17 |
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
5.3漏斗模型分析函数window_funnel
封装、要素(时间范围，事件的排序时间依据，漏斗模型的事件链)
语法:
window_funnel(window, mode, timestamp_column, event1, event2, ... , eventN)
漏斗分析函数搜索滑动时间窗口内最大的发生的最大事件序列长度。
-- window ：滑动时间窗口大小，单位为秒。
-- mode ：保留，目前只支持default。-- 相邻两个事件之间没有时间间隔要求，并且相邻两个事件中可以做其他的事件
-- timestamp_column ：指定时间列，类型为DATETIME, 滑动窗口沿着此列工作。
-- eventN ：表示事件的布尔表达式。
select
user_id,
window_funnel(3600*24, 'default', event_time, event_id='e1', event_id='e2' , event_id='e4', event_id='e5') as step
from event_info_log
group by user_id
+---------+------+
| user_id | step |
+---------+------+
| u006 | 4 |
| u007 | 2 |
| u005 | 3 |
| u004 | 3 |
| u010 | 0 |
| u001 | 3 |
| u003 | 2 |
| u002 | 3 |
| u008 | 3 |
| u009 | 2 |
+---------+------+
-- 算每一层级的转换率
select
'购买转化漏斗' as funnel_name,
sum(if(step >= 1 ,1,0)) as step1,
sum(if(step >= 2 ,1,0)) as step2,
sum(if(step >= 3 ,1,0)) as step3,
sum(if(step >= 4 ,1,0)) as step4,
round(sum(if(step >= 2 ,1,0))/sum(if(step >= 1 ,1,0)),2) as 'step1->step2_radio',
round(sum(if(step >= 3 ,1,0))/sum(if(step >= 2 ,1,0)),2) as 'step2->step3_radio',
round(sum(if(step >= 4 ,1,0))/sum(if(step >= 3 ,1,0)),2) as 'step3->step4_radio'
from
(
select
user_id,
window_funnel(3600*24, 'default', report_date, event_id='e1', event_id='e2' , event_id='e4', event_id='e5') as step
from event_info_log
where to_date(report_date) = '2022-11-01'
and event_id in('e1','e4','e5','e2')
group by user_id
) as t1
-- res
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
| funnel_name | step1 | step2 | step3 | step4 | step1->step2_radio | step2->step3_radio | step3->step4_radio |
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
| 购买转化漏斗 | 9 | 9 | 6 | 1 | 1 | 0.67 | 0.17 |
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
_log
where event_id in (‘e1’,‘e2’,‘e4’,‘e5’)
and to_date(report_date) = ‘2022-11-01’
order by user_id,report_date
) as temp
) as temp2
group by user_id;
±--------±-----------------------+
| user_id | event_ll |
±--------±-----------------------+
| u006 | e4, e5, e1, e2, e4, e5 |
| u007 | e1, e4, e5, e2 |
| u005 | e1, e2, e5, e4, e1, e2 |
| u004 | e1, e5, e2, e4 |
| u010 | e4, e5, e5 |
| u001 | e1, e2, e5, e4, e1, e2 |
| u003 | e5, e1, e4, e5, e2 |
| u002 | e4, e5, e1, e2, e4 |
| u008 | e1, e1, e5, e2, e4 |
| u009 | e2, e5, e4, e1, e2 |
±--------±-----------------------+
2.确定匹配规则模型
select
user_id,
‘购买转化漏斗’ as funnel_name ,
case
– 正则匹配，先触发过e1，在触发过e2，在触发过e4，在触发过e5
when event_seq rlike(‘e1.*e2.*e4.*e5’) then 4
– 正则匹配，先触发过e1，在触发过e2，在触发过e4
when event_seq rlike(‘e1.*e2.*e4’) then 3
– 正则匹配，先触发过e1，在触发过e2
when event_seq rlike(‘e1.*e2’) then 2
– 正则匹配，只触发过e1
when event_seq rlike(‘e1’) then 1
else 0 end step
from
(
select
user_id,
max(event_ll) as event_seq
from
(
select
user_id,
group_concat(event_id)over(partition by user_id order by report_date) as event_ll
from
(
select
user_id,event_id,report_date
from event_info_log
where event_id in (‘e1’,‘e2’,‘e4’,‘e5’)
and to_date(report_date) = ‘2022-11-01’
order by user_id,report_date
) as temp
) as temp2
group by user_id
) as tmp3;
±--------±-------------------±-----+
| user_id | funnel_name | step |
±--------±-------------------±-----+
| u006 | 购买转化漏斗 | 4 |
| u007 | 购买转化漏斗 | 2 |
| u005 | 购买转化漏斗 | 3 |
| u004 | 购买转化漏斗 | 3 |
| u010 | 购买转化漏斗 | 0 |
| u001 | 购买转化漏斗 | 3 |
| u003 | 购买转化漏斗 | 2 |
| u002 | 购买转化漏斗 | 3 |
| u008 | 购买转化漏斗 | 3 |
| u009 | 购买转化漏斗 | 2 |
±--------±-------------------±-----+
– 最后计算转换率
select
funnel_name,
sum(if(step >= 1 ,1,0)) as step1,
sum(if(step >= 2 ,1,0)) as step2,
sum(if(step >= 3 ,1,0)) as step3,
sum(if(step >= 4 ,1,0)) as step4,
round(sum(if(step >= 2 ,1,0))/sum(if(step >= 1 ,1,0)),2) as ‘step1->step2_radio’,
round(sum(if(step >= 3 ,1,0))/sum(if(step >= 2 ,1,0)),2) as ‘step2->step3_radio’,
round(sum(if(step >= 4 ,1,0))/sum(if(step >= 3 ,1,0)),2) as ‘step3->step4_radio’
from
(
select
‘购买转化漏斗’ as funnel_name ,
case
– 正则匹配，先触发过e1，在触发过e2，在触发过e4，在触发过e5
when event_seq regexp(‘e1.*e2.*e4.*e5’) then 4
– 正则匹配，先触发过e1，在触发过e2，在触发过e4
when event_seq regexp(‘e1.e2..*e4’) then 3
– 正则匹配，先触发过e1，在触发过e2
when event_seq regexp(‘e1.*e2’) then 2
– 正则匹配，只触发过e1
when event_seq regexp(‘e1’) then 1
else 0 end step
from
(
select
user_id,
max(event_seq) as event_seq
from
– 因为在doris1.1版本中还不支持数组，所以拼接字符串的时候还没办法排序
(
select
user_id,
– 用开窗的方式进行排序，然后在有序的按照时间升序，将事件拼接
group_concat(concat(report_date,‘_’,event_id),‘|’)over(partition by user_id order by report_date) as event_seq
from event_info_log
where to_date(report_date) = ‘2022-11-01’
and event_id in(‘e1’,‘e4’,‘e5’,‘e2’)
) as tmp
group by user_id
) as t1
) as t2
group by funnel_name;
±-------------------±------±------±------±------±-------------------±-------------------±-------------------+
| funnel_name | step1 | step2 | step3 | step4 | step1->step2_radio | step2->step3_radio | step3->step4_radio |
±-------------------±------±------±------±------±-------------------±-------------------±-------------------+
| 购买转化漏斗 | 9 | 9 | 6 | 1 | 1 | 0.67 | 0.17 |
±-------------------±------±------±------±------±-------------------±-------------------±-------------------+
## 5.3漏斗模型分析函数window_funnel
封装、要素(时间范围，事件的排序时间依据，漏斗模型的事件链)
```SQL
语法:
window_funnel(window, mode, timestamp_column, event1, event2, ... , eventN)
漏斗分析函数搜索滑动时间窗口内最大的发生的最大事件序列长度。
-- window ：滑动时间窗口大小，单位为秒。
-- mode ：保留，目前只支持default。-- 相邻两个事件之间没有时间间隔要求，并且相邻两个事件中可以做其他的事件
-- timestamp_column ：指定时间列，类型为DATETIME, 滑动窗口沿着此列工作。
-- eventN ：表示事件的布尔表达式。
select
user_id,
window_funnel(3600*24, 'default', event_time, event_id='e1', event_id='e2' , event_id='e4', event_id='e5') as step
from event_info_log
group by user_id
+---------+------+
| user_id | step |
+---------+------+
| u006 | 4 |
| u007 | 2 |
| u005 | 3 |
| u004 | 3 |
| u010 | 0 |
| u001 | 3 |
| u003 | 2 |
| u002 | 3 |
| u008 | 3 |
| u009 | 2 |
+---------+------+
-- 算每一层级的转换率
select
'购买转化漏斗' as funnel_name,
sum(if(step >= 1 ,1,0)) as step1,
sum(if(step >= 2 ,1,0)) as step2,
sum(if(step >= 3 ,1,0)) as step3,
sum(if(step >= 4 ,1,0)) as step4,
round(sum(if(step >= 2 ,1,0))/sum(if(step >= 1 ,1,0)),2) as 'step1->step2_radio',
round(sum(if(step >= 3 ,1,0))/sum(if(step >= 2 ,1,0)),2) as 'step2->step3_radio',
round(sum(if(step >= 4 ,1,0))/sum(if(step >= 3 ,1,0)),2) as 'step3->step4_radio'
from
(
select
user_id,
window_funnel(3600*24, 'default', report_date, event_id='e1', event_id='e2' , event_id='e4', event_id='e5') as step
from event_info_log
where to_date(report_date) = '2022-11-01'
and event_id in('e1','e4','e5','e2')
group by user_id
) as t1
-- res
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
| funnel_name | step1 | step2 | step3 | step4 | step1->step2_radio | step2->step3_radio | step3->step4_radio |
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
| 购买转化漏斗 | 9 | 9 | 6 | 1 | 1 | 0.67 | 0.17 |
+--------------------+-------+-------+-------+-------+--------------------+--------------------+--------------------+
扫一扫
9552
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
