Hive SQL 优化案例详解

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

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 关注

相关帖子

欢迎来到这里!

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

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

推荐标签 标签

  • etcd

    etcd 是一个分布式、高可用的 key-value 数据存储,专门用于在分布式系统中保存关键数据。

    6 引用 • 26 回帖 • 543 关注
  • Sillot

    Insights(注意当前设置 master 为默认分支)

    汐洛彖夲肜矩阵(Sillot T☳Converbenk Matrix),致力于服务智慧新彖乄,具有彖乄驱动、极致优雅、开发者友好的特点。其中汐洛绞架(Sillot-Gibbet)基于自思源笔记(siyuan-note),前身是思源笔记汐洛版(更早是思源笔记汐洛分支),是智慧新录乄终端(多端融合,移动端优先)。

    主仓库地址:Hi-Windom/Sillot

    文档地址:sillot.db.sc.cn

    注意事项:

    1. ⚠️ 汐洛仍在早期开发阶段,尚不稳定
    2. ⚠️ 汐洛并非面向普通用户设计,使用前请了解风险
    3. ⚠️ 汐洛绞架基于思源笔记,开发者尽最大努力与思源笔记保持兼容,但无法实现 100% 兼容
    29 引用 • 25 回帖 • 126 关注
  • GitLab

    GitLab 是利用 Ruby 一个开源的版本管理系统,实现一个自托管的 Git 项目仓库,可通过 Web 界面操作公开或私有项目。

    46 引用 • 72 回帖
  • JavaScript

    JavaScript 一种动态类型、弱类型、基于原型的直译式脚本语言,内置支持类型。它的解释器被称为 JavaScript 引擎,为浏览器的一部分,广泛用于客户端的脚本语言,最早是在 HTML 网页上使用,用来给 HTML 网页增加动态功能。

    730 引用 • 1282 回帖
  • Spring

    Spring 是一个开源框架,是于 2003 年兴起的一个轻量级的 Java 开发框架,由 Rod Johnson 在其著作《Expert One-On-One J2EE Development and Design》中阐述的部分理念和原型衍生而来。它是为了解决企业应用开发的复杂性而创建的。框架的主要优势之一就是其分层架构,分层架构允许使用者选择使用哪一个组件,同时为 JavaEE 应用程序开发提供集成的框架。

    947 引用 • 1460 回帖 • 1 关注
  • Thymeleaf

    Thymeleaf 是一款用于渲染 XML/XHTML/HTML5 内容的模板引擎。类似 Velocity、 FreeMarker 等,它也可以轻易的与 Spring 等 Web 框架进行集成作为 Web 应用的模板引擎。与其它模板引擎相比,Thymeleaf 最大的特点是能够直接在浏览器中打开并正确显示模板页面,而不需要启动整个 Web 应用。

    11 引用 • 19 回帖 • 394 关注
  • PHP

    PHP(Hypertext Preprocessor)是一种开源脚本语言。语法吸收了 C 语言、 Java 和 Perl 的特点,主要适用于 Web 开发领域,据说是世界上最好的编程语言。

    167 引用 • 408 回帖 • 489 关注
  • 爬虫

    网络爬虫(Spider、Crawler),是一种按照一定的规则,自动地抓取万维网信息的程序。

    106 引用 • 275 回帖
  • Vim

    Vim 是类 UNIX 系统文本编辑器 Vi 的加强版本,加入了更多特性来帮助编辑源代码。Vim 的部分增强功能包括文件比较(vimdiff)、语法高亮、全面的帮助系统、本地脚本(Vimscript)和便于选择的可视化模式。

    29 引用 • 66 回帖 • 1 关注
  • Quicker

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

    37 引用 • 157 回帖 • 1 关注
  • 设计模式

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

    201 引用 • 120 回帖
  • 阿里云

    阿里云是阿里巴巴集团旗下公司,是全球领先的云计算及人工智能科技公司。提供云服务器、云数据库、云安全等云计算服务,以及大数据、人工智能服务、精准定制基于场景的行业解决方案。

    85 引用 • 324 回帖
  • 倾城之链
    23 引用 • 66 回帖 • 167 关注
  • 强迫症

    强迫症(OCD)属于焦虑障碍的一种类型,是一组以强迫思维和强迫行为为主要临床表现的神经精神疾病,其特点为有意识的强迫和反强迫并存,一些毫无意义、甚至违背自己意愿的想法或冲动反反复复侵入患者的日常生活。

    15 引用 • 161 回帖 • 2 关注
  • TGIF

    Thank God It's Friday! 感谢老天,总算到星期五啦!

    291 引用 • 4495 回帖 • 662 关注
  • Hadoop

    Hadoop 是由 Apache 基金会所开发的一个分布式系统基础架构。用户可以在不了解分布式底层细节的情况下,开发分布式程序。充分利用集群的威力进行高速运算和存储。

    93 引用 • 122 回帖 • 618 关注
  • CodeMirror
    2 引用 • 17 回帖 • 168 关注
  • 京东

    京东是中国最大的自营式电商企业,2015 年第一季度在中国自营式 B2C 电商市场的占有率为 56.3%。2014 年 5 月,京东在美国纳斯达克证券交易所正式挂牌上市(股票代码:JD),是中国第一个成功赴美上市的大型综合型电商平台,与腾讯、百度等中国互联网巨头共同跻身全球前十大互联网公司排行榜。

    14 引用 • 102 回帖 • 311 关注
  • 音乐

    你听到信仰的声音了么?

    62 引用 • 512 回帖
  • 微信

    腾讯公司 2011 年 1 月 21 日推出的一款手机通讯软件。用户可以通过摇一摇、搜索号码、扫描二维码等添加好友和关注公众平台,同时可以将自己看到的精彩内容分享到微信朋友圈。

    133 引用 • 796 回帖
  • 分享

    有什么新发现就分享给大家吧!

    248 引用 • 1794 回帖
  • Lute

    Lute 是一款结构化的 Markdown 引擎,支持 Go 和 JavaScript。

    29 引用 • 202 回帖 • 28 关注
  • Git

    Git 是 Linux Torvalds 为了帮助管理 Linux 内核开发而开发的一个开放源码的版本控制软件。

    211 引用 • 358 回帖
  • golang

    Go 语言是 Google 推出的一种全新的编程语言,可以在不损失应用程序性能的情况下降低代码的复杂性。谷歌首席软件工程师罗布派克(Rob Pike)说:我们之所以开发 Go,是因为过去 10 多年间软件开发的难度令人沮丧。Go 是谷歌 2009 发布的第二款编程语言。

    500 引用 • 1396 回帖 • 244 关注
  • Markdown

    Markdown 是一种轻量级标记语言,用户可使用纯文本编辑器来排版文档,最终通过 Markdown 引擎将文档转换为所需格式(比如 HTML、PDF 等)。

    171 引用 • 1537 回帖
  • WebClipper

    Web Clipper 是一款浏览器剪藏扩展,它可以帮助你把网页内容剪藏到本地。

    3 引用 • 9 回帖
  • Oracle

    Oracle(甲骨文)公司,全称甲骨文股份有限公司(甲骨文软件系统有限公司),是全球最大的企业级软件公司,总部位于美国加利福尼亚州的红木滩。1989 年正式进入中国市场。2013 年,甲骨文已超越 IBM,成为继 Microsoft 后全球第二大软件公司。

    107 引用 • 127 回帖 • 344 关注