当前位置: 首页 > news >正文

hive、spark、presto 中的增强聚合-grouping sets、rollup、cube

目录

1、什么是增强聚合和多维分析函数?

2、grouping sets - 指定维度组合

3、with rollup - 上卷维度组合

4、with cube - 全维度组合

5、Grouping__ID、grouping() 的使用场景

6、使用 增强聚合 会不会对查询性能有提升呢?

7、对grouping sets、with cube、with rollup 的优化


1、什么是增强聚合和多维分析函数?

        增强聚合指的是:

                在SQL中使用分组聚合查询时,使用 grouping sets、rollup、cube 语句进行操作

                在常见的数据引擎中都支持这种语法,比如hive、spark、presto、ck、flinkSQL

                使用增强聚合不仅可以简化SQL代码,而且还能对SQL语句的性能有所提升

        多维分析指的是:

                SQL语法中的多维分析指的是 多种维度组合的分析,而不是多种维度的分析

         hive官网链接:hive官网

                


2、grouping sets - 指定维度组合

功能说明:

        对指定的分组字段进行多种维度组合的聚合计算

hive-语法:

-- TODO 必须开区map端合并
select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping__id
from 表名 [where ]
group by A,B,C 
grouping sets( (A),(A,B),(A,B,C),..维度组合 )
presto、FlinkSQL、SparkSQL-语法:
select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping(A,B,C) as grouping_id
from 表名 [where ]
group by
grouping sets( (A),(A,B),(A,B,C),..维度组合 )

语法区别:

        1、hiveSQL中 group by 后面必须添加分组的字段

             presto、flinksql、sparksql group by 后面不需要指定分组字段

        2、hiveSQL中 可以使用 grouping__id字段

              presto、flinksql、sparksql 中并没有提供 grouping__id字段,需要使用grouping(a,b,c) 函数来计算

代码示例(HiveSQL):

-- TODO 必须开区map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','郑州市','高开区','张3' union all select '河南省','郑州市','高开区','张4' union all select '河南省','郑州市','高开区','张5' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov,city,area grouping sets ((prov,city,area),(prov)
)
;

代码示例(presto、flinkSQL、sparkSQL):

SELECT prov,city,area,count(1),grouping(prov,city,area) as grouping_id
FROM (VALUES  ('河北省','石家庄市','新华区','张1'),('河北省','石家庄市','新华区','张2'),('河南省','郑州市','高开区','张3'),('河南省','郑州市','高开区','张4'),('河南省','郑州市','高开区','张5'),('河南省','新乡市','中华区','张6')) AS person_info_df (prov,city,area,name)
group by grouping sets ((prov,city,area),(prov)
)
;

3、with rollup - 上卷维度组合

功能说明:

        上卷维度组合,较grouping sets相比,不需要指定维度组合

         GROUP BY a, b, c, WITH ROLLUP  等价于  

         GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( ))

hive-语法:

-- TODO 必须开区map端合并
select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping__id
from 表名 [where ]
group by A,B,C 
with rollup

presto、FlinkSQL、SparkSQL-语法:

select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping(A,B,C) as grouping_id
from 表名 [where ]
group by
rollup(A,B,C) 

代码示例(HiveSQL):

-- 1.必须开区map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','郑州市','高开区','张3' union all select '河南省','郑州市','高开区','张4' union all select '河南省','郑州市','高开区','张5' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov,city,area with rollup
;

代码示例(presto、flinkSQL、sparkSQL):

SELECT prov,city,area,count(1),grouping(prov,city,area) as grouping_id
FROM (VALUES  ('河北省','石家庄市','新华区','张1'),('河北省','石家庄市','新华区','张2'),('河南省','郑州市','高开区','张3'),('河南省','郑州市','高开区','张4'),('河南省','郑州市','高开区','张5'),('河南省','新乡市','中华区','张6')) AS person_info_df (prov,city,area,name)
group by rollup(prov,city,area) 
;

4、with cube - 全维度组合

功能说明:

        多维度组合,会计算所有分组字段的维度组合,较grouping sets相比,不需要指定维度组合

         GROUP BY a, b, c, WITH CUBE  等价于  

         GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( ))

cube(key1,key2...) 维度组合数:

hive-语法:

-- TODO 必须开区map端合并
select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping__id
from 表名 [where ]
group by A,B,C 
with cube

presto、FlinkSQL、SparkSQL-语法:

select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping(A,B,C) as grouping_id
from 表名 [where ]
group by
cube(A,B,C) 

代码示例(HiveSQL):

-- 1.必须开区map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','郑州市','高开区','张3' union all select '河南省','郑州市','高开区','张4' union all select '河南省','郑州市','高开区','张5' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov,city,area with cube
;

代码示例(presto、flinkSQL、sparkSQL):

SELECT prov,city,area,count(1),grouping(prov,city,area) as grouping_id
FROM (VALUES  ('河北省','石家庄市','新华区','张1'),('河北省','石家庄市','新华区','张2'),('河南省','郑州市','高开区','张3'),('河南省','郑州市','高开区','张4'),('河南省','郑州市','高开区','张5'),('河南省','新乡市','中华区','张6')) AS person_info_df (prov,city,area,name)
group by cube(prov,city,area) 
;

5、Grouping__ID、grouping() 的使用场景

功能说明:

        可以用来判断分组字段是否参与聚合,下面为 Grouping__ID 、grouping() 计算逻辑

使用场景:

        当使用 grouping sets、with rollup、with cube进行聚合时,对不参与聚合的字段会使用null进行填充,这就导致查询结果中分组字段为null时,无法区分是填充的null还是分组字段本身的null

        遇到上述情况,可以使用下面两种解决方式

                1、将分组字段中的null进行替换处理,比如9999、other、其他

                2、使用 Grouping__ID 或者 grouping() 进行区分


6、使用 增强聚合 会不会对查询性能有提升呢?

测试用例-grouping sets:

-- TODO 必须开区map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov,city,area grouping sets ((prov,city,area),(prov,city),(prov)
)
;

测试用例-group by + union all:

set hive.map.aggr=true;
SELECT prov,city,area,count(1)
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','新乡市','中华区','张6'
) AS person_info_df
group by prov,city,areaunion all SELECT prov,city,null as area,count(1)
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov,cityunion all SELECT prov,null as city,null as area,count(1)
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov

对比执行计划:

对比运行时长:

结论:

通过上面执行计划和运行时长的对比,使用 grouping sets、with cube、with rollup 确实比

group by + union all 方式的性能要好,因为 增强group by避免了多次读取底表,降低生成

job的个数,从而减轻了磁盘和网络I/O时的压力。


7、对grouping sets、with cube、with rollup 的优化

由于在使用增强group by时,会在同一个job中完成多种维度组合的聚合(2的N次方),当底表数据量太大 或 维度过多时,可能造成计算资源不够而导致任务失败。

在 Hive中可以使用 set hive.new.job.grouping.set.cardinality=30 来对job进行拆分。

参数说明:

验证SQL-实验组:

验证SQL-对照组:

相关文章:

hive、spark、presto 中的增强聚合-grouping sets、rollup、cube

目录 1、什么是增强聚合和多维分析函数? 2、grouping sets - 指定维度组合 3、with rollup - 上卷维度组合 4、with cube - 全维度组合 5、Grouping__ID、grouping() 的使用场景 6、使用 增强聚合 会不会对查询性能有提升呢? 7、对grouping sets、…...

elasticsearch bulk 批量操作

1:bulk 是 elasticsearch 提供的一种批量增删改的操作API bulk 对 JSON串 有着严格的要求。每个JSON串 不能换行 ,只能放在同一行,同时, 相邻的JSON串之间必须要有换行 (Linux下是\n;Window下是\r\n&#…...

力扣11、 盛最多水的容器

方法一:双指针 考察: 贪心、数组、双指针 说明 本题是一道经典的面试题,最优的做法是使用「双指针」。如果读者第一次看到这题,不一定能想出双指针的做法。 复杂度分析 时间复杂度:O(N),双指针总计最多…...

IIC协议详解

目录 1.IIC协议概述 2.IIC总线传输 3.IIC-51单片机应用 1.起始信号 2.终止信号 3.应答信号 4.数据发送 4.IIC-32单片机应用 用到的库函数: 1.IIC协议概述 IIC全称Inter-Integrated Circuit (集成电路总线)是由PHILIPS公司在80年代开发的两线式串行总线&…...

element ui-表头自定义提示框

版本 “element-ui”: “^2.15.5”,需求&#xff1a;鼠标悬浮到该列表头&#xff0c;显示提示框代码 <el-table:data"xxxx"><el-table-column label"序号" width"40" type"index" /><el-table-columnv-for"(ite…...

Python 图形化界面基础篇:创建顶部菜单

Python 图形化界面基础篇&#xff1a;创建顶部菜单 引言 Tkinter 库简介步骤1&#xff1a;导入 Tkinter 模块步骤2&#xff1a;创建 Tkinter 窗口步骤3&#xff1a;创建顶部菜单栏步骤4&#xff1a;处理菜单项的点击事件步骤5&#xff1a;启动 Tkinter 主事件循环 完整示例代码…...

java实现十大排序算法

文章目录 冒泡排序选择排序插入排序希尔排序归并排序快速排序堆排序桶排序基数排序计数排序验证各个排序的时间复杂度和空间复杂度 冒泡排序 冒泡排序&#xff08;Bubble Sort&#xff09;是一种简单的比较排序算法&#xff0c;它的基本思想是重复地交换相邻的两个元素&#x…...

Linux日志管理-logrotate(crontab定时任务、Ceph日志转储)

文章目录 一、logrotate概述二、logrotate基本用法三、logrotate运行机制logrotate参数 四、logrotate是怎么做到滚动日志时不影响程序正常的日志输出呢&#xff1f;Linux文件操作机制方案一方案二 五、logrotate实战--Ceph日志转储参考 一、logrotate概述 logrotate是一个用于…...

用PHP异步协程控制python爬虫脚本,实现多协程分布式爬取

背景 公司需要爬取指定网站的产品数据。但是个人对python的多进程和协程不是特别熟悉。所以&#xff0c;想通过php异步协程&#xff0c;发起爬取url请求控制python爬虫脚本&#xff0c;达到分布式爬取的效果。 准备 1.准备一个mongodb数据库用于存放爬取数据2.引入flask包&a…...

VUE3写后台管理(3)

VUE3写后台管理&#xff08;3&#xff09; 1.环境1.node2.vite3.Element-plus4.vue-router5.element icon6.less7.vuex8.vue-demi9.mockjs10.axios11.echarts 2.首页1.布局Main2.头部导航栏CommonHeader3.左侧菜单栏CommonLeft4.首页Home1.从后端获取数据显示到前端table的三种…...

机器学习笔记之最优化理论与算法(十二)无约束优化问题——共轭梯度法

机器学习笔记之最优化理论与方法——共轭梯度法 引言回顾&#xff1a;共轭方向法的重要特征线性共轭梯度法共轭方向公式的证明过程 关于线搜索公式中参数的化简关于线搜索公式中步长部分的化简关于线搜索公式中共轭方向系数的化简参数化简的目的 非线性共轭梯度法(FR,PRP方法)关…...

JVM中的java同步互斥工具应用演示及设计分析

1.火车站售票系统仿真 某火车站目前正在出售火车票&#xff0c;共有50张票&#xff0c;而它有3个售票窗口同时售票&#xff0c;下面设计了一个程序模拟该火车站售票&#xff0c;通过实现Runnable接口实现&#xff08;模拟网络延迟&#xff09;。 伪代码&#xff1a; Ticket类…...

数据治理-数据质量

实现数据质量的前提就是数据本身是可靠和可信的。 导致数据质量低下的因素 组织缺乏对低质量数据影响的理解&#xff0c;缺乏规划、孤岛式系统设计、不一致的开发过程、不完整的文档、缺乏标准或缺乏治理等。 所有组织都会遇到与数据质量有关的问题。数据质量需要跨职能的承诺…...

[sqoop]hive3.1.2 hadoop3.1.1安装sqoop1.4.7

参考: Hadoop3.2.4Hive3.1.2sqoop1.4.7安装部署_hadoop sqoop安装_alicely07的博客-CSDN博客 一、安装 1、解压 tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /home/data_warehouse/module mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop-1.4.72、配置文件 sqoop-env.s…...

js事件的详细介绍

11.事件 1.什么是事件 js属于事件驱动编程,把驱动,执行,调用通过一些交互,触发一些函数事件:发起-->执行绑定事件-->触发事件on 绑定 emit触发 off解绑2.事件分类 鼠标事件 点击事件 onclick 双击事件 ondblclick 按下事件 onmousedown 抬起事件 onmouseup 鼠标进…...

虚幻4学习笔记(12)操控导入的角色、动画蓝图、播放蒙太奇和打包、角色重定向

虚幻4学习笔记 操控导入的角色设置鼠标旋转关掉动态模糊 动画蓝图、播放蒙太奇和打包角色走路奔跑动画shift 奔跑F 跳舞移动打断 跳舞 打包角色重定向姿势调整解决跑步 腿分太开隐藏剑 B站UP谌嘉诚课程&#xff1a;https://www.bilibili.com/video/BV164411Y732 操控导入的角色…...

hive with tez:无法从链中的任何提供者加载aws凭据

环境信息 hadoop 3.1.0 hive-3.1.3 tez 0.9.1 问题描述 可以从hadoop命令行正确地访问s3a uri。我可以创建外部表和如下命令&#xff1a; create external table mytable(a string, b string) location s3a://mybucket/myfolder/; select * from mytable limit 20; 执行正…...

Ubuntu修改静态IP、网关和DNS的方法总结

Ubuntu修改静态IP、网关和DNS的方法总结 ubuntu系统&#xff08;其他debian的衍生版本好像也可以&#xff09;修改静态IP有以下几种方法。&#xff08;搜索总结&#xff0c;可能也不太对&#xff09; /etc/netplan (use) Ubuntu 18.04开始可以使用netplan配置网络&#xff0…...

Eureka服务器注册

一。Eureka服务器注册 1.pom.xml <?xml version"1.0" encoding"UTF-8"?> <project xmlns"http://maven.apache.org/POM/4.0.0"xmlns:xsi"http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation"http://mav…...

Windows安装GPU版本的pytorch详细教程

文章目录 chatGLM2-6B安装教程正式安装 chatGLM2-6B ChatGLM2-6B版本要装pytorch2.0&#xff0c;而且要2.0.1 &#xff0c;因此CUDA不能用12.0 &#xff0c;也不能用10.0&#xff0c;只能用11.x 版本。 安装教程 pip install直接下载安装 官网&#xff1a; https://pytorch.…...

业务系统对接大模型的基础方案:架构设计与关键步骤

业务系统对接大模型&#xff1a;架构设计与关键步骤 在当今数字化转型的浪潮中&#xff0c;大语言模型&#xff08;LLM&#xff09;已成为企业提升业务效率和创新能力的关键技术之一。将大模型集成到业务系统中&#xff0c;不仅可以优化用户体验&#xff0c;还能为业务决策提供…...

使用VSCode开发Django指南

使用VSCode开发Django指南 一、概述 Django 是一个高级 Python 框架&#xff0c;专为快速、安全和可扩展的 Web 开发而设计。Django 包含对 URL 路由、页面模板和数据处理的丰富支持。 本文将创建一个简单的 Django 应用&#xff0c;其中包含三个使用通用基本模板的页面。在此…...

反向工程与模型迁移:打造未来商品详情API的可持续创新体系

在电商行业蓬勃发展的当下&#xff0c;商品详情API作为连接电商平台与开发者、商家及用户的关键纽带&#xff0c;其重要性日益凸显。传统商品详情API主要聚焦于商品基本信息&#xff08;如名称、价格、库存等&#xff09;的获取与展示&#xff0c;已难以满足市场对个性化、智能…...

uni-app学习笔记二十二---使用vite.config.js全局导入常用依赖

在前面的练习中&#xff0c;每个页面需要使用ref&#xff0c;onShow等生命周期钩子函数时都需要像下面这样导入 import {onMounted, ref} from "vue" 如果不想每个页面都导入&#xff0c;需要使用node.js命令npm安装unplugin-auto-import npm install unplugin-au…...

使用分级同态加密防御梯度泄漏

抽象 联邦学习 &#xff08;FL&#xff09; 支持跨分布式客户端进行协作模型训练&#xff0c;而无需共享原始数据&#xff0c;这使其成为在互联和自动驾驶汽车 &#xff08;CAV&#xff09; 等领域保护隐私的机器学习的一种很有前途的方法。然而&#xff0c;最近的研究表明&…...

cf2117E

原题链接&#xff1a;https://codeforces.com/contest/2117/problem/E 题目背景&#xff1a; 给定两个数组a,b&#xff0c;可以执行多次以下操作&#xff1a;选择 i (1 < i < n - 1)&#xff0c;并设置 或&#xff0c;也可以在执行上述操作前执行一次删除任意 和 。求…...

新能源汽车智慧充电桩管理方案:新能源充电桩散热问题及消防安全监管方案

随着新能源汽车的快速普及&#xff0c;充电桩作为核心配套设施&#xff0c;其安全性与可靠性备受关注。然而&#xff0c;在高温、高负荷运行环境下&#xff0c;充电桩的散热问题与消防安全隐患日益凸显&#xff0c;成为制约行业发展的关键瓶颈。 如何通过智慧化管理手段优化散…...

Rapidio门铃消息FIFO溢出机制

关于RapidIO门铃消息FIFO的溢出机制及其与中断抖动的关系&#xff0c;以下是深入解析&#xff1a; 门铃FIFO溢出的本质 在RapidIO系统中&#xff0c;门铃消息FIFO是硬件控制器内部的缓冲区&#xff0c;用于临时存储接收到的门铃消息&#xff08;Doorbell Message&#xff09;。…...

使用Matplotlib创建炫酷的3D散点图:数据可视化的新维度

文章目录 基础实现代码代码解析进阶技巧1. 自定义点的大小和颜色2. 添加图例和样式美化3. 真实数据应用示例实用技巧与注意事项完整示例(带样式)应用场景在数据科学和可视化领域,三维图形能为我们提供更丰富的数据洞察。本文将手把手教你如何使用Python的Matplotlib库创建引…...

基于 TAPD 进行项目管理

起因 自己写了个小工具&#xff0c;仓库用的Github。之前在用markdown进行需求管理&#xff0c;现在随着功能的增加&#xff0c;感觉有点难以管理了&#xff0c;所以用TAPD这个工具进行需求、Bug管理。 操作流程 注册 TAPD&#xff0c;需要提供一个企业名新建一个项目&#…...