关键词：
【中文标题】Hive 查询优化【英文标题】：Hive query optimization 【发布时间】：2019-10-03 06:45:15 【问题描述】：我的要求是获取拥有超过 1 个电子邮件 ID 和 type=1 的学生的 ID 和姓名。
我正在使用类似的查询
select distinct b.id, b.name, b.email, b.type,a.cnt
from (
select id, count(email) as cnt
from (
select distinct id, email
from table1
) c
group by id
) a
join table1 b on a.id = b.id
where b.type=1
order by b.id
请告诉我这个版本好还是有更简单的版本可用。
Sample data is like:
id name email type
123 AAA abc@xyz.com 1
123 AAA acd@xyz.com 1
123 AAA ayx@xyz.com 3
345 BBB nch@xyz.com 1
345 BBB nch@xyz.com 1
678 CCC iuy@xyz.com 1
Expected Output:
123 AAA abc@xyz.com 1 2
123 AAA acd@xyz.com 1 2
345 BBB nch@xyz.com 1 1
678 CCC iuy@xyz.com 1 1
【问题讨论】：
能否提供具有代表性的数据示例和预期结果 【参考方案1】：您可以使用group by -> having count() 来满足此要求。
select distinct b.id
, b.name,
, b.email
, b.type
from table1 b
where id in
(select distinct id from table1 group by email, id having count(email) > 1)
and b.type=1
order by b.id
【讨论】：
如果相同的 id 和相同的电子邮件出现两次，此选项将无法正常工作。我想要 >1 封不同的电子邮件。 但就我而言，它并不是唯一的。这就是为什么我添加了一个不同的子查询 添加了select distinct id from table1 【参考方案2】：
可以尝试使用count()函数的解析方式：
SELECT sub.ID, sub.NAME
FROM (SELECT ID, NAME, COUNT (*) OVER (PARTITION BY ID, EMAIL) cnt
FROM raw.crddacia_raw) sub
WHERE sub.cnt > 1 AND sub.TYPE = 1
【讨论】：
如果相同的 id 和相同的电子邮件出现两次，此选项将无法正常工作。我想要 >1 封不同的电子邮件 所以，按 id,email 进行分区（我在上面的脚本中修改了）【参考方案3】：我强烈推荐使用窗口函数。但是，Hive 不支持 count(distinct) 作为窗口函数。有不同的方法可以解决这个问题。一个是dense_rank()s的总和：
select id, name, email, type, cnt
from (select t1.*,
(dense_rank() over (partition by id order by email) +
dense_rank() over (partition by id order by email desc)
) as cnt
from table1 t1
) t
where type = 1;
我希望这比您的版本具有更好的性能。但是，值得测试不同的版本，看看哪个具有更好的性能（并随时回来让其他人知道哪个更好）。
【讨论】：
【参考方案4】：另一种方法使用collect_set 并采用返回数组的大小来计算不同的电子邮件。
演示：
--your data example
with table1 as ( --use your table instead of this
select stack(6,
123, 'AAA', 'abc@xyz.com', 1,
123, 'AAA', 'acd@xyz.com', 1,
123, 'AAA', 'ayx@xyz.com', 3,
345, 'BBB', 'nch@xyz.com', 1,
345, 'BBB', 'nch@xyz.com', 1,
678, 'CCC', 'iuy@xyz.com', 1
) as (id, name, email, type )
)
--query
select distinct id, name, email, type,
size(collect_set(email) over(partition by id)) cnt
from table1
where type=1
结果：
id name email type cnt
123 AAA abc@xyz.com 1 2
123 AAA acd@xyz.com 1 2
345 BBB nch@xyz.com 1 1
678 CCC iuy@xyz.com 1 1
这里我们仍然需要 DISTINCT，因为解析函数不会像 345 BBB nch@xyz.com 那样删除重复项。
【讨论】：
【参考方案5】：这与您的查询非常相似，但在这里我在初始步骤（在内部查询中）过滤数据，以便连接不会发生在较少的数据上
select distinct b.id,b.name,b.email,b.type,intr_table.cnt from table1 orig_table join
(
select a.id,a.type,count(a.email) as cnt from table1 as a where a.type=1 group by a
) intr_table on inter_table.id=orig_table.id,inter_table.type=orig_table.type
【讨论】：
优化 HIVE 查询中的连接：c
】优化HIVE查询中的连接：c【英文标题】：OptimizejoininHIVEquery:c【发布时间】：2014-10-1612:34:12【问题描述】：我想知道哪一个是优化连接这三个可能候选者中的两个表的HIVE(0.12)查询的最佳方法（并且可能理解为什么）：SELECT*FROMajo... 查看详情
hive性能优化（新手重新标注版）
...别人整理的基础上进行了一些重点标识和归纳。一个Hive查询生成多个MapReduceJob，一个MapReduceJob又有Map,Spill,Shuffle,Sort,Reduce等多个阶段，所以针对Hive查询的优化可以大致分为针对MR中单个步骤的优化（其中又会有细分），针对MR全... 查看详情
Hive Optimizer 在优化视图查询时是不是考虑视图定义？
】HiveOptimizer在优化视图查询时是不是考虑视图定义？【英文标题】：DoesHiveOptimizerconsiderviewdefinitionwhileoptimizingqueriesonviews?HiveOptimizer在优化视图查询时是否考虑视图定义？【发布时间】：2017-07-0607:40:42【问题描述】：我有这个架... 查看详情
使用 UNION ALL 和 RANK 优化 Hive 查询
】使用UNIONALL和RANK优化Hive查询【英文标题】：OptimizingHiveQuerywithUNIONALLandRANKwithorder【发布时间】：2019-04-0412:42:07【问题描述】：当前情景：我有这个查询，它对两组数据进行联合，然后根据排名选择字段但是根据我的分析，整个... 查看详情
hive查询优化总结
...in优化Join查找操作的基本原则：应该将条目少的表/子查询放在 Join 操作符的左边。原因是在 Join 操作的 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少... 查看详情
学习笔记hive——查询优化(代码片段)
...到视图，但可在Mysql的元数据库中查找到。视图只能查询 查看详情
hive优化笔记(代码片段)
...GROUPBY操作3.6合并小文件5.程序角度优化5.1熟练使用SQL提高查询5.2无效ID在关联时的数据倾斜问题5.3不同数据类型关联产生的倾斜问题5.4利用Hive对UNIONALL优化的特性5.5解决Hive对UNIONALL优化的短板消灭子查询内的COUNT(DISTINCT)&#x 查看详情
优化 Hive 查询。 java.lang.OutOfMemoryError：超出 Java 堆空间/GC 开销限制
】优化Hive查询。java.lang.OutOfMemoryError：超出Java堆空间/GC开销限制【英文标题】：OptimizeHiveQuery.java.lang.OutOfMemoryError:Javaheapspace/GCoverheadlimitexceeded【发布时间】：2020-07-0822:13:01【问题描述】：由于一直遇到此OOM错误，如何优化此表... 查看详情
hive1.2.2优化
...1.1Fetch抓取（鸡肋）Fetch抓取是指，==Hive中对某些情况的查询可以不必使用MapReduce计算==例如：select*fromemployee;在这种情况下，Hive可以简单地读取employee对应的存储目录下的文件，然后输出查询结果到控制台在hive-default.xml.template文... 查看详情
hive优化策略
...置优化1-Fetch抓取配置Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT*FROMemployees;在这种情况下，Hive可以简单地读取employee对应的存储目录下的文件，然后输出查询结果到控制台。在hive-default.xml.templa... 查看详情
hive优化策略
...]通过explain命令，可以查看Hive语句的操作情况，是否为慢查询，是否走索引，一目了然explainselectsum(...)fromtable_name;2.动态分区调整hive.exec.d 查看详情
hive性能优化之表设计优化(代码片段)
目录1分区表1.1Hive查询基本原理1.2普通表结构问题1.3分区表设计思想1.4分区表测试2分桶表2.1Hive中Join的问题2.2分桶表设计思想2.3分桶表测试3索引设计3.1Hive中的索引3.2索引的原理及使用3.3索引的问题与应用1分区表1.1Hive查询基本原... 查看详情
hive的hive体系结构
...编译器、优化器、执行器解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中，并在随后由MapReduce调用执行。HadoopHive的数据存储在HDFS中，大部分的查询... 查看详情
大数据开发工程师hive(hive如何进行优化)
...指定数据存储格式：TextFile、SequenceFile、RCfile、ORCfile；②查询时优化：1)列裁剪，在查询时只读取需要的列，避免全列扫描，不要使用select*fromtable；2)分区裁剪：在查询时只读取需要分区的数据，避免全表扫描；3)开启谓词下推... 查看详情
Hive 中推荐的优化技术都有哪些？
...？【发布时间】：2016-06-0605:52:46【问题描述】：我在编写查询时知道一些优化技术，例如分区和分桶。在TEZ模式下运行配置单元。但是，除此之外，hive中还有其他优化技术。【问题讨论】：【参考方案1】：您可以设置至少以下... 查看详情
hive大表和小表mapjoin关联查询优化
大表和小表关联查询可以采用mapjoin优化查询速度。那什么是mapjoin呢？理解MapJoin之前先介绍另一种Join方式，CommonJoin。我们知道Hive编写SQL语句，Hive会将SQL解析成MapReduce任务。对于一个简单的关联查询，CommonJoin任务设计Map阶段和R... 查看详情
hive的join优化(代码片段)
...hivejoin优化器是从hive0.11.0版本起添加的，本文描述了Hive查询执行计划的优化，以提高join效率并减少对用户提示的需求。Hive自动识别各种用例并对其进行优化。 查看详情
未在 Hive 中优化分桶
...了分桶，数据大小约为700MB，也强制执行分桶。当我执行查询时：selectcount(flightnum)fromflight_buckwhereflightnum=10;在大约46秒内得到响应。映射器总数为27。在具有相同数据的非 查看详情