blocks|key|1250187|text|计划特性|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1250188|火花-23945+(Column.isin()应该接受单列DataFrame作为输入)。|unordered-list-item|1250189|火花-18455+(通用支持相关子查询处理)。|1250190|星火2.0%2B|1250191|Spark应该同时支持相关和不相关的子查询。详情请参见SubquerySuite。一些例子包括：|CODE|1250192|select+*+from+l+where+exists+(select+*+from+r+where+l.a+=+r.c)
select+*+from+l+where+not+exists+(select+*+from+r+where+l.a+=+r.c)

select+*+from+l+where+l.a+in+(select+c+from+r)
select+*+from+l+where+a+not+in+(select+c+from+r)|code-block|syntax|javascript|1250193|不幸的是，目前(Spark2.0)不可能使用DataFrame+DSL来表达相同的逻辑。|1250194|火花<+2.0|1250195|Spark支持FROM子句中的子查询(与Hive+<=+0.12相同)。|1250196|SELECT+col+FROM+(SELECT+*++FROM+t1+WHERE+bar)+t2|1250197|它根本不支持WHERE+clause.Generally中的子查询，说到任意子查询(特别是相关子查询)，如果不提升到笛卡尔联接，就无法使用Spark来表示。|1250198|由于子查询性能在典型的关系系统中通常是一个重要的问题，而且每个子查询都可以使用JOIN表示，所以这里不会失去功能。|1250199|entityMap|0|LINK|mutability|MUTABLE|url|https://issues.apache.org/jira/browse/SPARK-23945|1|https://issues.apache.org/jira/browse/SPARK-18455|2|https://github.com/apache/spark/blob/df89f1d43d4eaa1dd8a439a8e48bca16b67d5b48/sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala^0|0|4|0|0|8|0|0|0|8|1|0|0|6|0|R|D|R|D|2|0|0|M|9|0|0|7|0|7|4|0|0|6|5|0|13|4|0^^$0|@$1|2|3|4|5|6|7|1I|8|@$9|1J|A|1K|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|1L|8|@]|D|@$9|1M|A|1N|1|1O]]|E|$]]|$1|I|3|J|5|H|7|1P|8|@]|D|@$9|1Q|A|1R|1|1S]]|E|$]]|$1|K|3|L|5|6|7|1T|8|@$9|1U|A|1V|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|1W|8|@$9|1X|A|1Y|B|O]]|D|@$9|1Z|A|20|1|21]]|E|$]]|$1|P|3|Q|5|R|7|22|8|@]|D|@]|E|$S|T]]|$1|U|3|V|5|6|7|23|8|@$9|24|A|25|B|O]]|D|@]|E|$]]|$1|W|3|X|5|6|7|26|8|@$9|27|A|28|B|C]]|D|@]|E|$]]|$1|Y|3|Z|5|6|7|29|8|@$9|2A|A|2B|B|O]]|D|@]|E|$]]|$1|10|3|11|5|R|7|2C|8|@]|D|@]|E|$S|T]]|$1|12|3|13|5|6|7|2D|8|@$9|2E|A|2F|B|O]]|D|@]|E|$]]|$1|14|3|15|5|6|7|2G|8|@$9|2H|A|2I|B|O]]|D|@]|E|$]]|$1|16|3|-4|5|6|7|2J|8|@]|D|@]|E|$]]]|17|$18|$5|19|1A|1B|E|$1C|1D]]|1E|$5|19|1A|1B|E|$1C|1F]]|1G|$5|19|1A|1B|E|$1C|1H]]]]

Planned features:

<ul>
<li><a href="https://issues.apache.org/jira/browse/SPARK-23945" rel="noreferrer">SPARK-23945</a> (Column.isin() should accept a single-column DataFrame as input).</li>
<li><a href="https://issues.apache.org/jira/browse/SPARK-18455" rel="noreferrer">SPARK-18455</a> (General support for correlated subquery processing).</li>
</ul>

Spark 2.0+

Spark SQL should support both correlated and uncorrelated subqueries. See <a href="https://github.com/apache/spark/blob/df89f1d43d4eaa1dd8a439a8e48bca16b67d5b48/sql/core/src/test/scala/org/apache/spark/sql/SubquerySuite.scala" rel="noreferrer"><code>SubquerySuite</code></a> for details. Some examples include:

<pre><code>select * from l where exists (select * from r where l.a = r.c)
select * from l where not exists (select * from r where l.a = r.c)

select * from l where l.a in (select c from r)
select * from l where a not in (select c from r)
</code></pre>

Unfortunately as for now (Spark 2.0) it is impossible to express the same logic using <code>DataFrame</code> DSL.

Spark &lt; 2.0

Spark supports subqueries in the <code>FROM</code> clause (same as Hive &lt;= 0.12). 

<pre><code>SELECT col FROM (SELECT * FROM t1 WHERE bar) t2
</code></pre>

It simply doesn't support subqueries in the <code>WHERE</code> clause.Generally speaking arbitrary subqueries (in particular correlated subqueries) couldn't be expressed using Spark without promoting to Cartesian join.

Since subquery performance is usually a significant issue in a typical relational system and every subquery can be expressed using <code>JOIN</code> there is no loss-of-function here.

blocks|key|1852537|text|https://issues.apache.org/jira/browse/SPARK-4226|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1852538|有一个执行该功能的拉请求。我猜它可能会降落在Spark2.0。|1852539|entityMap|0|LINK|mutability|MUTABLE|url^0|0|1C|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@$A|N|B|O|1|P]]|C|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|4]]]]

<a href="https://issues.apache.org/jira/browse/SPARK-4226" rel="nofollow">https://issues.apache.org/jira/browse/SPARK-4226</a> 

There is a pull request to implement that feature .. my guess it might land in Spark 2.0.

I am running this query in Spark shell but it gives me error,

<pre><code>sqlContext.sql(
 "select sal from samplecsv where sal &lt; (select MAX(sal) from samplecsv)"
).collect().foreach(println)
</code></pre>

error:

<blockquote>
 java.lang.RuntimeException: [1.47] failure: ``)'' expected but identifier MAX found
 
 select sal from samplecsv where sal &lt; (select MAX(sal) from samplecsv)
 ^
 at scala.sys.package$.error(package.scala:27)
 Can anybody explan me,thanks
</blockquote>

Does SparkSQL support subquery?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在shell中运行这个查询，但是它给了我错误，sqlContext.sql( "select sal from samplecsv where sal < (select MAX(sal) from samplecsv)").collect().foreach(println)错误：java.lang.Runtim...

问SparkSQL支持子查询吗？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SparkSQL支持子查询吗？EN