Hive SQL 优化案例详解

本贴最后更新于 1753 天前,其中的信息可能已经时移世改

HiveSQL 经典优化案例一:
1.1 将要执行的查询(执行了 1 个多小时才出结果):

SELECT dt as DATA_DATE,STRATEGY,AB_GROUP,SOURCE,     count(distinct case when lower(event) not like '%push%' and event!='corner_mark_show' then udid else null end) as DAU,     count(case when event='client_show' then 1 else null end) as TOTAL_VSHOW,     count(distinct case when event='client_show' then vid else null end) as TOTAL_VIDEO_VSHOW,     count(case when event='video_play' then 1 else null end) as TOTAL_VV_VP,     count(distinct case when event='video_play' then udid else null end) as TOTAL_USERS_VP,     count(case when event='effective_play' then 1 else null end) as TOTAL_VV_EP,     count(distinct case when event='effective_play' then udid else null end) as TOTAL_USERS_EP,     sum(case when event='video_over' then duration else 0 end) as TOTAL_DURATION,     count(case when event='video_over' then 1 else null end) as TOTAL_VOVER,     sum(case when event='video_over' then play_cnts else 0 end) as TOTAL_VOVER_PCNTS,     count(case when event='push_video_clk' then 1 else null end) as TOTAL_PUSH_VC,     count(distinct case when event='app_start' and body_source = 'push' then udid else null end) as TOTAL_PUSH_START,     count(case when event='post_comment' then 1 else null end) as TOTAL_REPLY,     count(distinct case when event='post_comment' then udid else null end) as TOTAL_USERS_REPLY     FROM dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zkl group by dt,strategy,ab_group,source;复制代码

1.2 查询语句涉及到的表有 7.7 亿 + 数据。(查询如下)

jdbc:hive2://ks-hdp-master-01.dns.rightpad (default)> select count(*) from dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zkl; 复制代码

1.3 优化思路:既然将要执行的查询是按照 dt, strategy, ab_group, source 这 4 个字段分组, 那么在建表的时候,就按这四个字段中的 N 个(1 或 2 或 3 或 4)个字段组合分区,直接让 count(distinct xx) 之类的查询定位到“更少的数据子集”,其执行效率就应该更高了(不需要每个子任务均从 7.7 亿 + 的数据中(去重)统计)。

1.4 先看每个字段将会有多少分区(因为 Hive 表分区也不宜过多,一般一个查询语句涉及到的 hive 分区 应该控制在 2K 内)

jdbc:hive2://ks-hdp-master-01.dns.rightpad (default)> select count(distinct dt) as dis_dt, count(distinct strategy) as dis_strategy, count(distinct ab_group) as dis_ab_group, count(distinct source) as dis_source from dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zkl;复制代码

[hue@ks-hdp-client-v02 10:55:08 /usr/local/hue]$ python Python 2.7.12 (default, Dec  4 2017, 14:50:18) [GCC 5.4.0 20160609] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> 2*14*72 2016 -- 2016 个分区还可以接受。复制代码

1.5 根据原表,新建分区表,并将原表数据插入新表:

show create table dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zkl; 复制代码
jdbc:hive2://ks-hdp-master-01.dns.rightpad (default)> show create table dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zkl; 复制代码

创建新表:按 dt,source,stragegy,ab_group 分区(注意先后顺序,一般习惯分区数越少的越靠前,根据 1.5 的查询可知:dt=1,source=2,strategy=14,ab_group=72)

create external table `dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_lym`(   event string,   udid string,   vid string,   duration string,   body_source string,   play_cnts string )PARTITIONED BY (   dt string,   source string,   strategy string,   ab_group string );复制代码

将原表数据插入新表:

insert into `dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_lym` partition(dt,source,strategy,ab_group) select event,udid,vid,duration,body_source,play_cnts,dt,source,strategy,ab_group from `dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zkl`;复制代码

核对两表的数据是否一致:

1.6 基于新表执行查询(执行 5 分钟出结果):

HiveSQL 经典优化案例二:

问题描述:一个复杂的 SQL,查询执行一段时间后报错:基本上是查不出来;

分析函数对于大表来说不是 hive 的强项,这个时候我们将其分解成很多子集,并且合理利用 hive 分区表的优势,然后去 join 。

2.1 将要执行的查询

create table bi_tmp.aloha_UserLoyalty_190301_190303 as     select aid, imei, idfa, udid, event, duration, dt, time_local, hour, source,         first_value(time_local) over(partition by udid, event order by time_local) as first_time,         last_value(time_local) over(partition by udid, event order by time_local) as last_time,         count(time_local) over(partition by udid, event, dt) as event_count_per_day,         sum(duration) over(partition by udid, event, dt) as event_duration_each_day     from dwb_v8sp.event_column_info_new_hour     where event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment',         'comment_success')         and dt >= '2019-03-01' and dt <= '2019-03-03'; select count(*) from dwb_v8sp.event_column_info_new_hour where event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment', 'comment_success') and dt >= '2019-03-01' and dt <= '2019-03-03'; 复制代码

select count(distinct event) as dis_event from dwb_v8sp.event_column_info_new_hour where event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment', 'comment_success') and dt >= '2019-03-01' and dt <= '2019-03-03';复制代码

分解成三个子集,并保存到三张表: bi_tmp.zyt1, bi_tmp.zyt2, bi_tmp.zyt3

-- drop table if exists bi_tmp.zyt1; create table bi_tmp.zyt1 partitioned by(event) as select udid,        min(time_local) as first_time,        max(time_local) as last_time,        event from dwb_v8sp.event_column_info_new_hour where event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment', 'comment_success') and dt >= '2019-03-01' and dt <= '2019-03-03' group by udid, event; -- drop table if exists bi_tmp.zyt2 purge; create table bi_tmp.zyt2 partitioned by(dt,event) as select udid,        count(time_local) as event_count_per_day,        sum(duration) as event_duration_each_day,        dt,       event from dwb_v8sp.event_column_info_new_hour where event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment', 'comment_success') and dt >= '2019-03-01' and dt <= '2019-03-03' group by udid, dt, event; create table bi_tmp.zyt3 partitioned by(dt,event) as select aid, imei, idfa, udid, duration, time_local, hour, source, dt, event from dwb_v8sp.event_column_info_new_hour t3     where event in ('app_start', 'app_exit', 'effective_play', 'share_succ', 'like', 'unlike', 'like_comment', 'unlike_comment',         'comment_success')         and dt >= '2019-03-01' and dt <= '2019-03-03'; -- 插入目标表: create table bi_tmp.aloha_UserLoyalty_190301_190303 as     select t3.aid, t3.imei, t3.idfa, t3.udid, t3.event, t3.duration, t3.dt, t3.time_local, t3.hour, t3.source,         t1.first_time,        t1.last_time,        t2.event_count_per_day,        t2.event_duration_each_day    from bi_tmp.zyt1 t1 join bi_tmp.zyt2 t2 on t1.event=t2.event and t1.udid=t2.udid     join bi_tmp.zyt3 t3 on t2.dt=t3.dt and t2.event= t3.event and t2.udid=t3.udid; -- 验证数据:(与上面的查询记录行数对的上)复制代码

HiveSQL 经典优化案例三:

如下 SQL,用到了 PERCENTILE_APPROX 函数,问题描述:如下 SQL,用到了 PERCENTILE_APPROX 函数,个人初步分析认为:由于用到该函数的次数太多,导致性能严重下降。

我仔细查了一下该函数,发现:它是支持“数组传参”的,那么就不难找到优化该 SQL 的方法了。

3.1 原 SQL 性能测试:

3.2 优化后的 SQL,性能测试:

优化后的 SQL,性能提升了 4 倍多。

  • Hive
    22 引用 • 7 回帖 • 1 关注
  • HiveSQL
    1 引用
  • SQL
    129 引用 • 396 回帖 • 3 关注

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...

推荐标签 标签

  • NGINX

    NGINX 是一个高性能的 HTTP 和反向代理服务器,也是一个 IMAP/POP3/SMTP 代理服务器。 NGINX 是由 Igor Sysoev 为俄罗斯访问量第二的 Rambler.ru 站点开发的,第一个公开版本 0.1.0 发布于 2004 年 10 月 4 日。

    315 引用 • 547 回帖 • 1 关注
  • Log4j

    Log4j 是 Apache 开源的一款使用广泛的 Java 日志组件。

    20 引用 • 18 回帖 • 36 关注
  • Quicker

    Quicker 您的指尖工具箱!操作更少,收获更多!

    37 引用 • 157 回帖 • 1 关注
  • 导航

    各种网址链接、内容导航。

    45 引用 • 177 回帖
  • 开源

    Open Source, Open Mind, Open Sight, Open Future!

    415 引用 • 3596 回帖 • 1 关注
  • 设计模式

    设计模式(Design pattern)代表了最佳的实践,通常被有经验的面向对象的软件开发人员所采用。设计模式是软件开发人员在软件开发过程中面临的一般问题的解决方案。这些解决方案是众多软件开发人员经过相当长的一段时间的试验和错误总结出来的。

    201 引用 • 120 回帖 • 1 关注
  • 微软

    微软是一家美国跨国科技公司,也是世界 PC 软件开发的先导,由比尔·盖茨与保罗·艾伦创办于 1975 年,公司总部设立在华盛顿州的雷德蒙德(Redmond,邻近西雅图)。以研发、制造、授权和提供广泛的电脑软件服务业务为主。

    8 引用 • 44 回帖
  • 智能合约

    智能合约(Smart contract)是一种旨在以信息化方式传播、验证或执行合同的计算机协议。智能合约允许在没有第三方的情况下进行可信交易,这些交易可追踪且不可逆转。智能合约概念于 1994 年由 Nick Szabo 首次提出。

    1 引用 • 11 回帖
  • 链书

    链书(Chainbook)是 B3log 开源社区提供的区块链纸质书交易平台,通过 B3T 实现共享激励与价值链。可将你的闲置书籍上架到链书,我们共同构建这个全新的交易平台,让闲置书籍继续发挥它的价值。

    链书社

    链书目前已经下线,也许以后还有计划重制上线。

    14 引用 • 257 回帖 • 2 关注
  • Python

    Python 是一种面向对象、直译式电脑编程语言,具有近二十年的发展历史,成熟且稳定。它包含了一组完善而且容易理解的标准库,能够轻松完成很多常见的任务。它的语法简捷和清晰,尽量使用无异义的英语单词,与其它大多数程序设计语言使用大括号不一样,它使用缩进来定义语句块。

    554 引用 • 675 回帖
  • Gitea

    Gitea 是一个开源社区驱动的轻量级代码托管解决方案,后端采用 Go 编写,采用 MIT 许可证。

    5 引用 • 16 回帖 • 1 关注
  • 外包

    有空闲时间是接外包好呢还是学习好呢?

    26 引用 • 233 回帖 • 1 关注
  • Firefox

    Mozilla Firefox 中文俗称“火狐”(正式缩写为 Fx 或 fx,非正式缩写为 FF),是一个开源的网页浏览器,使用 Gecko 排版引擎,支持多种操作系统,如 Windows、OSX 及 Linux 等。

    7 引用 • 30 回帖 • 376 关注
  • Kafka

    Kafka 是一种高吞吐量的分布式发布订阅消息系统,它可以处理消费者规模的网站中的所有动作流数据。 这种动作(网页浏览,搜索和其他用户的行动)是现代系统中许多功能的基础。 这些数据通常是由于吞吐量的要求而通过处理日志和日志聚合来解决。

    36 引用 • 35 回帖 • 4 关注
  • Sym

    Sym 是一款用 Java 实现的现代化社区(论坛/BBS/社交网络/博客)系统平台。

    下一代的社区系统,为未来而构建

    524 引用 • 4601 回帖 • 710 关注
  • Typecho

    Typecho 是一款博客程序,它在 GPLv2 许可证下发行,基于 PHP 构建,可以运行在各种平台上,支持多种数据库(MySQL、PostgreSQL、SQLite)。

    12 引用 • 67 回帖 • 445 关注
  • 电影

    这是一个不能说的秘密。

    123 引用 • 608 回帖
  • 域名

    域名(Domain Name),简称域名、网域,是由一串用点分隔的名字组成的 Internet 上某一台计算机或计算机组的名称,用于在数据传输时标识计算机的电子方位(有时也指地理位置)。

    43 引用 • 208 回帖 • 1 关注
  • Excel
    31 引用 • 28 回帖 • 1 关注
  • 开源中国

    开源中国是目前中国最大的开源技术社区。传播开源的理念,推广开源项目,为 IT 开发者提供了一个发现、使用、并交流开源技术的平台。目前开源中国社区已收录超过两万款开源软件。

    7 引用 • 86 回帖 • 1 关注
  • H2

    H2 是一个开源的嵌入式数据库引擎,采用 Java 语言编写,不受平台的限制,同时 H2 提供了一个十分方便的 web 控制台用于操作和管理数据库内容。H2 还提供兼容模式,可以兼容一些主流的数据库,因此采用 H2 作为开发期的数据库非常方便。

    11 引用 • 54 回帖 • 672 关注
  • GitHub

    GitHub 于 2008 年上线,目前,除了 Git 代码仓库托管及基本的 Web 管理界面以外,还提供了订阅、讨论组、文本渲染、在线文件编辑器、协作图谱(报表)、代码片段分享(Gist)等功能。正因为这些功能所提供的便利,又经过长期的积累,GitHub 的用户活跃度很高,在开源世界里享有深远的声望,并形成了社交化编程文化(Social Coding)。

    209 引用 • 2040 回帖
  • 面试

    面试造航母,上班拧螺丝。多面试,少加班。

    326 引用 • 1395 回帖 • 2 关注
  • Hprose

    Hprose 是一款先进的轻量级、跨语言、跨平台、无侵入式、高性能动态远程对象调用引擎库。它不仅简单易用,而且功能强大。你无需专门学习,只需看上几眼,就能用它轻松构建分布式应用系统。

    9 引用 • 17 回帖 • 643 关注
  • 自由行
    1 关注
  • GAE

    Google App Engine(GAE)是 Google 管理的数据中心中用于 WEB 应用程序的开发和托管的平台。2008 年 4 月 发布第一个测试版本。目前支持 Python、Java 和 Go 开发部署。全球已有数十万的开发者在其上开发了众多的应用。

    14 引用 • 42 回帖 • 826 关注
  • frp

    frp 是一个可用于内网穿透的高性能的反向代理应用,支持 TCP、UDP、 HTTP 和 HTTPS 协议。

    17 引用 • 7 回帖 • 3 关注