首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Spark中DISTINCT与GROUP的重复删除

问Spark中DISTINCT与GROUP的重复删除
EN

Stack Overflow用户
提问于 2020-12-25 17:05:39
回答 2查看 240关注 0票数 0

星星之火-sql我正在使用火花-sql 2.4.

我现在有一个困扰我很长时间的问题--是否使用DISTINCT或GROUP BY (没有任何聚合),以更好的查询性能有效地从表中删除重复项。

对于DISTINCT,我会使用以下方法-

代码语言:javascript
复制
select distinct 
       id, 
       fname, 
       lname, 
       age
from emp_table;

对于GROUP BY,我只需使用:

代码语言:javascript
复制
select id,
       fname,
       lname,
       age
from emp_table
group by 1, 2, 3, 4;

我在某个地方读到过Spark-SQL,只有当数据集的cardinality很高时,才应该使用Distinct,否则使用Group By。然而,在我的日常工作中,我看到即使在基数较低的情况下,Duplicate的性能也优于Group By。

所以我的问题是哪一种方案在什么情况下表现得更好。

有人能给我讲讲这件事吗?在何种条件下,Distinct查询的性能要好于Group By的哪些场景。

谢谢

EN

回答 2

Stack Overflow用户

发布于 2020-12-25 18:35:26

它们在功能上是等价的,并将生成相同的查询计划。为了清晰起见,使用不同的。

票数 1
EN

Stack Overflow用户

发布于 2020-12-25 19:11:26

以下是这两个查询的查询计划。就像@蓝精灵说的,它们是相同的,所以不应该有任何性能上的差异。

代码语言:javascript
复制
create table t1 (a int, b int, c int, d int);

explain select a,b,c,d from t1 group by 1,2,3,4;
== Physical Plan ==
*(2) HashAggregate(keys=[a#14, b#15, c#16, d#17], functions=[])
+- Exchange hashpartitioning(a#14, b#15, c#16, d#17, 200), true, [id=#33]
   +- *(1) HashAggregate(keys=[a#14, b#15, c#16, d#17], functions=[])
      +- Scan hive default.t1 [a#14, b#15, c#16, d#17], HiveTableRelation `default`.`t1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [a#14, b#15, c#16, d#17], Statistics(sizeInBytes=8.0 EiB)
代码语言:javascript
复制
explain select distinct a,b,c,d from t1;
== Physical Plan ==
*(2) HashAggregate(keys=[a#23, b#24, c#25, d#26], functions=[])
+- Exchange hashpartitioning(a#23, b#24, c#25, d#26, 200), true, [id=#58]
   +- *(1) HashAggregate(keys=[a#23, b#24, c#25, d#26], functions=[])
      +- Scan hive default.t1 [a#23, b#24, c#25, d#26], HiveTableRelation `default`.`t1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [a#23, b#24, c#25, d#26], Statistics(sizeInBytes=8.0 EiB)

扩展的解释显示,经过优化后,查询变得相同:

代码语言:javascript
复制
explain extended select a,b,c,d from t1 group by 1,2,3,4;
== Parsed Logical Plan ==
'Aggregate [1, 2, 3, 4], ['a, 'b, 'c, 'd]
+- 'UnresolvedRelation [t1]

== Analyzed Logical Plan ==
a: int, b: int, c: int, d: int
Aggregate [a#41, b#42, c#43, d#44], [a#41, b#42, c#43, d#44]
+- SubqueryAlias spark_catalog.default.t1
   +- HiveTableRelation `default`.`t1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [a#41, b#42, c#43, d#44], Statistics(sizeInBytes=8.0 EiB)

== Optimized Logical Plan ==
Aggregate [a#41, b#42, c#43, d#44], [a#41, b#42, c#43, d#44]
+- HiveTableRelation `default`.`t1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [a#41, b#42, c#43, d#44], Statistics(sizeInBytes=8.0 EiB)

== Physical Plan ==
*(2) HashAggregate(keys=[a#41, b#42, c#43, d#44], functions=[], output=[a#41, b#42, c#43, d#44])
+- Exchange hashpartitioning(a#41, b#42, c#43, d#44, 200), true, [id=#108]
   +- *(1) HashAggregate(keys=[a#41, b#42, c#43, d#44], functions=[], output=[a#41, b#42, c#43, d#44])
      +- Scan hive default.t1 [a#41, b#42, c#43, d#44], HiveTableRelation `default`.`t1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [a#41, b#42, c#43, d#44], Statistics(sizeInBytes=8.0 EiB)
代码语言:javascript
复制
explain extended select distinct a,b,c,d from t1;
== Parsed Logical Plan ==
'Distinct
+- 'Project ['a, 'b, 'c, 'd]
   +- 'UnresolvedRelation [t1]

== Analyzed Logical Plan ==
a: int, b: int, c: int, d: int
Distinct
+- Project [a#50, b#51, c#52, d#53]
   +- SubqueryAlias spark_catalog.default.t1
      +- HiveTableRelation `default`.`t1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [a#50, b#51, c#52, d#53], Statistics(sizeInBytes=8.0 EiB)

== Optimized Logical Plan ==
Aggregate [a#50, b#51, c#52, d#53], [a#50, b#51, c#52, d#53]
+- HiveTableRelation `default`.`t1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [a#50, b#51, c#52, d#53], Statistics(sizeInBytes=8.0 EiB)

== Physical Plan ==
*(2) HashAggregate(keys=[a#50, b#51, c#52, d#53], functions=[], output=[a#50, b#51, c#52, d#53])
+- Exchange hashpartitioning(a#50, b#51, c#52, d#53, 200), true, [id=#133]
   +- *(1) HashAggregate(keys=[a#50, b#51, c#52, d#53], functions=[], output=[a#50, b#51, c#52, d#53])
      +- Scan hive default.t1 [a#50, b#51, c#52, d#53], HiveTableRelation `default`.`t1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [a#50, b#51, c#52, d#53], Statistics(sizeInBytes=8.0 EiB)

事实上,这表明查询引擎似乎更喜欢group by查询。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/65450091

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档