[Spark SQL基础]-- 基本语法之 select [hints ...]

背景

      今天偶然有机会看见了以前一位同学在 join 中使用了 mapjoin 小表广播的优化,由此激起了我对 select 语法中的 hints 部分的深入挖掘,并分享出来,供小伙伴们参考,不足之处,还望赐教!

      注:本文基于 spark 2.4.5,最新版本的 spark 可能会有 api 变化。

目录

  • select 基本语法
  • hints 来源
  • hints 的语法和选项
  • hints 使用的组合

内容

1 select 基本语法结构

SELECT [hints, ...] [ALL|DISTINCT] named_expression[, named_expression, ...]
  FROM relation[, relation, ...]
  [lateral_view[, lateral_view, ...]]
  [WHERE boolean_expression]
  [aggregation [HAVING boolean_expression]]
  [ORDER BY sort_expressions]
  [CLUSTER BY expressions]
  [DISTRIBUTE BY expressions]
  [SORT BY sort_expressions]
  [WINDOW named_window[, WINDOW named_window, ...]]
  [LIMIT num_rows]

named_expression:
  : expression [AS alias]

relation:
  | join_relation
  | (table_name|query|relation) [sample] [AS alias]
  : VALUES (expressions)[, (expressions), ...]
        [AS (column_name[, column_name, ...])]

expressions:
  : expression[, expression, ...]

sort_expressions:
  : expression [ASC|DESC][, expression [ASC|DESC], ...]

2 hints 来源

这是来源于创始人 Reynold Xin 提出的,自 Spark-2.2 开始增加的 框架。

Patch:https://issues.apache.org/jira/browse/SPARK-20857

 

3 hints 的语法和选项

SELECT /*+ MAPJOIN(table_name) */

SELECT /*+ BROADCASTJOIN(table_name) */ 

SELECT /*+ BROADCAST(table_name) */ 

// spark -2.4.0 之后新增的功能
// 由中国贡献者提出并参与贡献
// https://issues.apache.org/jira/browse/SPARK-24940

SELECT /*+ REPARTITION(number) */ 

SELECT /*+ COALESCE(number) */ 


4 hints 使用的组合

  • mapjoin 结合 unionall 使用:select /*+ mapjoin(a) ,a.*,b.* from t_test a join t_map b on a.id=bid.id;
  • repartition 和 coalesce 结合 group by 使用,用于修改 并行度和分区数量

 

参考

https://docs.databricks.com/spark/latest/spark-sql/language-manual/select.html

https://jaceklaskowski.gitbooks.io/mastering-spark-sql/spark-sql-hint-framework.html

https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/joins.scala

https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/hints.scala

https://issues.apache.org/jira/browse/SPARK-16475

https://issues.apache.org/jira/browse/SPARK-20857

https://issues.apache.org/jira/browse/SPARK-24940

 

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页