MaxCompute - ODPS重装上阵 第七弹 - Grouping Set, Cube and Rollup

本文涉及的产品
云原生大数据计算服务 MaxCompute,5000CU*H 100GB 3个月
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
简介: MaxCompute中的GROUPING SETS功能是SELECT语句中GROUP BY子句的扩展。允许采用多种方式对结果分组,而不必使用多个SELECT语句来实现这一目的。这样能够使MaxCompute的引擎给出更有的执行计划,从而提高执行性能。

MaxCompute(原ODPS)是阿里云自主研发的具有业界领先水平的分布式大数据处理平台, 尤其在集团内部得到广泛应用,支撑了多个BU的核心业务。 MaxCompute除了持续优化性能外,也致力于提升SQL语言的用户体验和表达能力,提高广大ODPS开发者的生产力。

MaxCompute基于ODPS2.0新一代的SQL引擎,显著提升了SQL语言编译过程的易用性与语言的表达能力。我们在此推出MaxCompute(ODPS2.0)重装上阵系列文章

第六弹向您介绍了User Defined Type,本篇将向您介绍MaxCompute对GROUPING SETS的支持。

场景

由于业务需求,需要经常对数据进行多维度的聚合分析,如既需要对a列做聚合也要对b列做聚合,同时也要按照a、b两列同时做聚合,所以,不得不写很多很多的UNION ALL,因此造成了很多重复代码,维护起来不方便。

该场景的问题,可以通过使用Grouping Sets能够非常好地解决。

本文中很多例子采用MaxCompute Studio作展示,没有安装MaxCompute Studio的用户,可以参照wiki安装MaxCompute Studio导入测试MaxCompute项目,创建工程

功能简介

MaxCompute中的GROUPING SETS功能是SELECT语句中GROUP BY子句的扩展。允许采用多种方式对结果分组,而不必使用多个SELECT语句来实现这一目的。这样能够使MaxCompute的引擎给出更有的执行计划,从而提高执行性能。

如下例子:

  • 准备数据。数据源requests表记录了某系统收到的请求。

    create table requests LIFECYCLE 20 as
    select * from values
        (1, 'windows', 'PC', 'Beijing'),
        (2, 'windows', 'PC', 'Shijiazhuang'),
        (3, 'linux', 'Phone', 'Beijing'),
        (4, 'windows', 'PC', 'Beijing'),
        (5, 'ios', 'Phone', 'Shijiazhuang'),
        (6, 'linux', 'PC', 'Beijing'),
        (7, 'windows', 'Phone', 'Shijiazhuang')
    as t(id, os, device, city);
  • 需求:在city维度和os、device维度计算请求数量,同时计算总的请求数。
  • 之前解法,必须使用多个 SELECT 语句计算多个分组,并且用UNION ALL把它们连接起来:

      SELECT NULL, NULL, NULL, COUNT(*)
      FROM requests
      UNION ALL
      SELECT os, device, NULL, COUNT(*)
      FROM requests GROUP BY os, device
      UNION ALL
      SELECT NULL, NULL, city, COUNT(*)
      FROM requests GROUP BY city;

    通过MaxCompute Studio的执行图,我们可以看出,物理执行计划是做了3次聚合,然后再UNION起来。
    image

  • GROUPING SETS语法可以做到相同的逻辑,同时,使用更少的代码,消耗更少的集群资源:

      SELECT os,device, city ,COUNT(*)
      FROM requests
      GROUP BY os, device, city GROUPING SETS((os, device), (city), ()); 
    • GROUPING SETS语法和普通GROUP BY类似,但需要额外执行所需的多个GROUP BY组合。例如以上SQL的((os, device), (city), ()), 请注意这是一个2层的括号,每个内层括号执行一个GROUP BY组合;空括号表示GROUP BY列表为空,即COUNT所有列。
    • 观察MaxCompute Studio的执行图,我们发现,物理执行计划只包含一个Reduce阶段,无需进行UNION操作。
      image
  • 两种方法均产生相同的结果,如下所示:
    image

请注意:

  • 若分组集里不使用表达式,系统会使用NULL充当占位符,使得这些结果集可以做UNION操作。例如,结果第 1-5 行的city列。
  • 相比于UNION多个group by的实现,GROUPINGSETS方式在总的资源消耗上面占优的。但使用GROUPINGSETS会使Reducer的阶段变少,如上例,从3个(R2_1, R3_1, R4_1)变为1个(R2_1), 从而导致总的Reducer instance数变少,可能会使任务端到端时间变长。
    这种情况建议使用 odps.sql.reducer.instances手动调大reducer的instance数目。例如上面的示例,可以 set odps.sql.reducer.instances=3; 来保持和原来instance数不变。

CUBE and ROLLUP

CUBE和ROLLUP可以认为是特殊的GROUPING SETS。

CUBE会枚举指定列的所有可能组合作为GROUPING SETS。而ROLLUP会以按层级聚合的方式产生GROUPING SETS。

例如:

  • GROUP BY CUBE(a, b, c) 等价于 GROUPING SETS( (a,b,c), (a,b), (a,c), (b,c), (a) ,(b), (c), () )
  • GROUP BY ROLLUP(a,b,c) 等价于 GROUPING SETS( (a,b,c), (a,b), (a), () )

CUBE会把GROUP BY列进行全量组合,即N个列会产生 2^N中组合,故不建议N的数目超过5。

GROUPING() and GROUPING_ID()

前面提到,系统在GROUPING SETS结果中用 NULL 用作占位符,当出现此情况后,将无法区分占位符 NULL 与数据中真正的 NULL,针对这个问题,MaxCompute提供了GROUPING 函数。

GROUPING 函数接受一个列名作为参数,如果结果对应行使用了参数列做聚合,返回0,此时意味着NULL来自输入数据。否则返回1,此时意味着NULL是GROUPING SETS的占位符。

此外,MaxCompute还提供了GROUPING_ID函数,此函数接受1个或多个列名作为参数。结果是将参数列的GROUPING结果按照BitMap的方式组成整数。如:

SELECT 
    a,b,c ,COUNT(*),
    GROUPING(a) ga, GROUPING(b) gb, GROUPING(c) gc, GROUPING_ID(a,b,c) groupingid
FROM VALUES (1,2,3) as t(a,b,c)
GROUP BY CUBE(a,b,c);

结果:

image

默认情况,GROUP BY列表中不被使用的列,会被填充为NULL。我们可以通过GROUPING函数输出更有实际意义的值。如:

SELECT 
  IF(GROUPING(os) == 0, os, 'ALL') as os,
  IF(GROUPING(device) == 0, device, 'ALL') as device, 
  IF(GROUPING(city) == 0, city, 'ALL') as city ,
  COUNT(*) as count
FROM requests
GROUP BY os, device, city GROUPING SETS((os, device), (city), ());

输出结果:

image

注意事项

CUBE会把GROUP BY列进行全量组合,即N个列会产生 2^N中组合,目前我们设置了GROUP BY列上限为13个。

小节

GROUPING SETS扩充了GROUP BY的聚合功能,在易用性,兼容性和性能方面,可以更好的满足您的需求。

对于SQL比较熟悉的专家会发现,上述功能大部分是标准的SQL支持的功能。MaxCompute会持续提升与标准SQL和业界常用产品的兼容性。

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
目录
相关文章
|
2月前
|
SQL 存储 分布式计算
ODPS技术架构深度剖析与实战指南——从零开始掌握阿里巴巴大数据处理平台的核心要义与应用技巧
【10月更文挑战第9天】ODPS是阿里巴巴推出的大数据处理平台,支持海量数据的存储与计算,适用于数据仓库、数据挖掘等场景。其核心组件涵盖数据存储、计算引擎、任务调度、资源管理和用户界面,确保数据处理的稳定、安全与高效。通过创建项目、上传数据、编写SQL或MapReduce程序,用户可轻松完成复杂的数据处理任务。示例展示了如何使用ODPS SQL查询每个用户的最早登录时间。
122 1
|
4月前
|
分布式计算 搜索推荐 物联网
大数据及AI典型场景实践问题之通过KafKa+OTS+MaxCompute完成物联网系统技术重构如何解决
大数据及AI典型场景实践问题之通过KafKa+OTS+MaxCompute完成物联网系统技术重构如何解决
|
4月前
|
人工智能 分布式计算 架构师
大数据及AI典型场景实践问题之基于MaxCompute构建Noxmobi全球化精准营销系统如何解决
大数据及AI典型场景实践问题之基于MaxCompute构建Noxmobi全球化精准营销系统如何解决
|
4月前
|
SQL 存储 分布式计算
MaxCompute 入门:大数据处理的第一步
【8月更文第31天】在当今数字化转型的时代,企业和组织每天都在产生大量的数据。有效地管理和分析这些数据变得至关重要。阿里云的 MaxCompute(原名 ODPS)是一个用于处理海量数据的大规模分布式计算服务。它提供了强大的存储能力以及丰富的数据处理功能,让开发者能够快速构建数据仓库、实时报表系统、数据挖掘等应用。本文将介绍 MaxCompute 的基本概念、架构,并演示如何开始使用这一大数据处理平台。
651 0
|
4月前
|
SQL 分布式计算 大数据
"大数据计算难题揭秘:MaxCompute中hash join内存超限,究竟该如何破解?"
【8月更文挑战第20天】在大数据处理领域,阿里云的MaxCompute以高效稳定著称,但复杂的hash join操作常导致内存超限。本文通过一个实例解析此问题:数据分析师小王需对两个共计300GB的大表进行join,却遭遇内存不足。经分析发现,单个mapper任务内存默认为2GB,不足以支持大型hash表的构建。为此,提出三种解决方案:1) 提升mapper任务内存;2) 利用map join优化小表连接;3) 实施分而治之策略,将大表分割后逐一处理再合并结果。这些方法有助于提升大数据处理效率及稳定性。
93 0
|
4月前
|
SQL 分布式计算 大数据
"揭秘MaxCompute大数据秘术:如何用切片技术在数据海洋中精准打捞?"
【8月更文挑战第20天】在大数据领域,MaxCompute(曾名ODPS)作为阿里集团自主研发的服务,提供强大、可靠且易用的大数据处理平台。数据切片是其提升处理效率的关键技术之一,它通过将数据集分割为小块来优化处理流程。使用MaxCompute进行切片可显著提高查询性能、支持并行处理、简化数据管理并增强灵活性。例如,可通过SQL按时间或其他维度对数据进行切片。此外,MaxCompute还支持高级切片技术如分区表和分桶表等,进一步加速数据处理速度。掌握这些技术有助于高效应对大数据挑战。
119 0
|
5月前
|
分布式计算 运维 大数据
混合云模式下 MaxCompute + Hadoop 混搭大数据架构实践。
除了资源效率和成本的优势外,混合云模式还为斗鱼带来了可量化的成本、增值服务以及额外的专业服务。阿里云的专业团队可以为斗鱼提供技术咨询和解决方案,帮助斗鱼解决业务难题。此外,计算资源的可量化也使得斗鱼能够清晰地了解资源使用情况,为业务决策提供依据。
|
5月前
|
存储 SQL 机器学习/深度学习
阿里云数加大数据计算服务MaxCompute学习路线图:从入门到精通
将所学知识应用于实际工作中并不断进行实践和创新是提升技术能力的关键所在。用户可以结合业务需求和技术发展趋势积极探索新的应用场景和解决方案,并在实践中不断总结经验和教训以提升自己的技术水平和实践能力。
|
5月前
|
SQL 分布式计算 数据处理
SQL 能力问题之MaxCompute(ODPS)SQL有哪些特点
SQL 能力问题之MaxCompute(ODPS)SQL有哪些特点
|
24天前
|
存储 分布式计算 数据挖掘
数据架构 ODPS 是什么?
数据架构 ODPS 是什么?
196 7

相关产品

  • 云原生大数据计算服务 MaxCompute