Spark IN/EXISTS predicate in SELECT statement
I have the following Spark SQL test query:
Seq("france").toDF.createOrReplaceTempView("countries")
SELECT CASE WHEN country = 'italy' THEN 'Italy'
ELSE ( CASE WHEN country IN (FROM countries) THEN upperCase(country) ELSE country END )
END AS country FROM users
which throws the following error:
Exception in thread "main" org.apache.spark.sql.AnalysisException:
IN/EXISTS predicate sub-queries can only be used in a Filter
the following part of the query CASE WHEN country IN (FROM countries) is the reason for that.
Is there any workaround in Spark SQL exists in order to emulate country IN (FROM countries) in the select conditions? I interested in pure SQL implementation and not in the implementation via API.
apache-spark apache-spark-sql
asked Nov 19 '18 at 10:54
alexanoidalexanoid
7,3481184184
add a comment |
I have the following Spark SQL test query:
Seq("france").toDF.createOrReplaceTempView("countries")
SELECT CASE WHEN country = 'italy' THEN 'Italy'
ELSE ( CASE WHEN country IN (FROM countries) THEN upperCase(country) ELSE country END )
END AS country FROM users
which throws the following error:
Exception in thread "main" org.apache.spark.sql.AnalysisException:
IN/EXISTS predicate sub-queries can only be used in a Filter
the following part of the query CASE WHEN country IN (FROM countries) is the reason for that.
Is there any workaround in Spark SQL exists in order to emulate country IN (FROM countries) in the select conditions? I interested in pure SQL implementation and not in the implementation via API.
apache-spark apache-spark-sql
asked Nov 19 '18 at 10:54
alexanoidalexanoid
7,3481184184
add a comment |
I have the following Spark SQL test query:
Seq("france").toDF.createOrReplaceTempView("countries")
SELECT CASE WHEN country = 'italy' THEN 'Italy'
ELSE ( CASE WHEN country IN (FROM countries) THEN upperCase(country) ELSE country END )
END AS country FROM users
which throws the following error:
Exception in thread "main" org.apache.spark.sql.AnalysisException:
IN/EXISTS predicate sub-queries can only be used in a Filter
the following part of the query CASE WHEN country IN (FROM countries) is the reason for that.
Is there any workaround in Spark SQL exists in order to emulate country IN (FROM countries) in the select conditions? I interested in pure SQL implementation and not in the implementation via API.
apache-spark apache-spark-sql
asked Nov 19 '18 at 10:54
alexanoidalexanoid
7,3481184184
I have the following Spark SQL test query:
Seq("france").toDF.createOrReplaceTempView("countries")
SELECT CASE WHEN country = 'italy' THEN 'Italy'
ELSE ( CASE WHEN country IN (FROM countries) THEN upperCase(country) ELSE country END )
END AS country FROM users
which throws the following error:
Exception in thread "main" org.apache.spark.sql.AnalysisException:
IN/EXISTS predicate sub-queries can only be used in a Filter
the following part of the query CASE WHEN country IN (FROM countries) is the reason for that.
Is there any workaround in Spark SQL exists in order to emulate country IN (FROM countries) in the select conditions? I interested in pure SQL implementation and not in the implementation via API.
apache-spark apache-spark-sql
apache-spark apache-spark-sql
asked Nov 19 '18 at 10:54
alexanoidalexanoid
7,3481184184
asked Nov 19 '18 at 10:54
alexanoidalexanoid
7,3481184184
edited Nov 19 '18 at 13:56
alexanoid
asked Nov 19 '18 at 10:54
alexanoidalexanoid
7,3481184184
asked Nov 19 '18 at 10:54
alexanoidalexanoid
7,3481184184
asked Nov 19 '18 at 10:54
alexanoidalexanoid
7,3481184184
7,3481184184
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Here's the correct SQL query:
import sparkSession.implicits._
Seq("france").toDF("country").createOrReplaceTempView("countries")
Seq(("user1", "france"), ("user2", "italy"), ("user2", "usa"))
.toDF("user", "country").createOrReplaceTempView("users")
val query =
s"""
|SELECT
| CASE
| WHEN u.country = 'italy' THEN 'Italy'
| ELSE (
| CASE
| WHEN u.country = c.country THEN upper(u.country)
| ELSE u.country
| END
| ) END AS country
|FROM users u
|LEFT JOIN countries c
| ON u.country = c.country
""".stripMargin
sparkSession.sql(query).show()
Result:
+-------+
|country|
+-------+
| FRANCE|
| Italy|
| usa|
+-------+
The reason behind the scene you can use IN/EXISTS sql operators only in predicates is: logic in projections (CASE-WHEN in our case) evaluated for each row in data set returned from selection.
With this in mind, it's not the best idea to run equivalent of CASE WHEN country IN (SELECT * FROM countries) for each row from users table. So, SQL prevents this on language level (sql parser engine).
answered Nov 19 '18 at 20:03
morsikmorsik
699815
add a comment |
As an alternative you can use
withColumn()
and
when()
function (from spark.sql.functions):
val users = Seq(("1", "france"), ("2", "Italy"), ("3", "italy")).toDF("userId", "country")
val countriesList = Seq("france", "italy", "germany").toList
val result = users.withColumn("country", when(col("country") === "italy", "Italy")
.when(col("country") isin(countriesList:_*), upper(col("country"))).otherwise(col("country")))
result.show()
Result:
+------+-------+
|userId|country|
+------+-------+
| 1| FRANCE|
| 2| Italy|
| 3| Italy|
+------+-------+
answered Nov 19 '18 at 13:10
RudyVerbovenRudyVerboven
438414
Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
– alexanoid
Nov 19 '18 at 13:53
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373091%2fspark-in-exists-predicate-in-select-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Here's the correct SQL query:
import sparkSession.implicits._
Seq("france").toDF("country").createOrReplaceTempView("countries")
Seq(("user1", "france"), ("user2", "italy"), ("user2", "usa"))
.toDF("user", "country").createOrReplaceTempView("users")
val query =
s"""
|SELECT
| CASE
| WHEN u.country = 'italy' THEN 'Italy'
| ELSE (
| CASE
| WHEN u.country = c.country THEN upper(u.country)
| ELSE u.country
| END
| ) END AS country
|FROM users u
|LEFT JOIN countries c
| ON u.country = c.country
""".stripMargin
sparkSession.sql(query).show()
Result:
+-------+
|country|
+-------+
| FRANCE|
| Italy|
| usa|
+-------+
The reason behind the scene you can use IN/EXISTS sql operators only in predicates is: logic in projections (CASE-WHEN in our case) evaluated for each row in data set returned from selection.
With this in mind, it's not the best idea to run equivalent of CASE WHEN country IN (SELECT * FROM countries) for each row from users table. So, SQL prevents this on language level (sql parser engine).
answered Nov 19 '18 at 20:03
morsikmorsik
699815
add a comment |
Here's the correct SQL query:
import sparkSession.implicits._
Seq("france").toDF("country").createOrReplaceTempView("countries")
Seq(("user1", "france"), ("user2", "italy"), ("user2", "usa"))
.toDF("user", "country").createOrReplaceTempView("users")
val query =
s"""
|SELECT
| CASE
| WHEN u.country = 'italy' THEN 'Italy'
| ELSE (
| CASE
| WHEN u.country = c.country THEN upper(u.country)
| ELSE u.country
| END
| ) END AS country
|FROM users u
|LEFT JOIN countries c
| ON u.country = c.country
""".stripMargin
sparkSession.sql(query).show()
Result:
+-------+
|country|
+-------+
| FRANCE|
| Italy|
| usa|
+-------+
The reason behind the scene you can use IN/EXISTS sql operators only in predicates is: logic in projections (CASE-WHEN in our case) evaluated for each row in data set returned from selection.
With this in mind, it's not the best idea to run equivalent of CASE WHEN country IN (SELECT * FROM countries) for each row from users table. So, SQL prevents this on language level (sql parser engine).
answered Nov 19 '18 at 20:03
morsikmorsik
699815
add a comment |
Here's the correct SQL query:
import sparkSession.implicits._
Seq("france").toDF("country").createOrReplaceTempView("countries")
Seq(("user1", "france"), ("user2", "italy"), ("user2", "usa"))
.toDF("user", "country").createOrReplaceTempView("users")
val query =
s"""
|SELECT
| CASE
| WHEN u.country = 'italy' THEN 'Italy'
| ELSE (
| CASE
| WHEN u.country = c.country THEN upper(u.country)
| ELSE u.country
| END
| ) END AS country
|FROM users u
|LEFT JOIN countries c
| ON u.country = c.country
""".stripMargin
sparkSession.sql(query).show()
Result:
+-------+
|country|
+-------+
| FRANCE|
| Italy|
| usa|
+-------+
The reason behind the scene you can use IN/EXISTS sql operators only in predicates is: logic in projections (CASE-WHEN in our case) evaluated for each row in data set returned from selection.
With this in mind, it's not the best idea to run equivalent of CASE WHEN country IN (SELECT * FROM countries) for each row from users table. So, SQL prevents this on language level (sql parser engine).
answered Nov 19 '18 at 20:03
morsikmorsik
699815
Here's the correct SQL query:
import sparkSession.implicits._
Seq("france").toDF("country").createOrReplaceTempView("countries")
Seq(("user1", "france"), ("user2", "italy"), ("user2", "usa"))
.toDF("user", "country").createOrReplaceTempView("users")
val query =
s"""
|SELECT
| CASE
| WHEN u.country = 'italy' THEN 'Italy'
| ELSE (
| CASE
| WHEN u.country = c.country THEN upper(u.country)
| ELSE u.country
| END
| ) END AS country
|FROM users u
|LEFT JOIN countries c
| ON u.country = c.country
""".stripMargin
sparkSession.sql(query).show()
Result:
+-------+
|country|
+-------+
| FRANCE|
| Italy|
| usa|
+-------+
The reason behind the scene you can use IN/EXISTS sql operators only in predicates is: logic in projections (CASE-WHEN in our case) evaluated for each row in data set returned from selection.
With this in mind, it's not the best idea to run equivalent of CASE WHEN country IN (SELECT * FROM countries) for each row from users table. So, SQL prevents this on language level (sql parser engine).
answered Nov 19 '18 at 20:03
morsikmorsik
699815
answered Nov 19 '18 at 20:03
morsikmorsik
699815
answered Nov 19 '18 at 20:03
morsikmorsik
699815
answered Nov 19 '18 at 20:03
morsikmorsik
699815
699815
add a comment |
add a comment |
As an alternative you can use
withColumn()
and
when()
function (from spark.sql.functions):
val users = Seq(("1", "france"), ("2", "Italy"), ("3", "italy")).toDF("userId", "country")
val countriesList = Seq("france", "italy", "germany").toList
val result = users.withColumn("country", when(col("country") === "italy", "Italy")
.when(col("country") isin(countriesList:_*), upper(col("country"))).otherwise(col("country")))
result.show()
Result:
+------+-------+
|userId|country|
+------+-------+
| 1| FRANCE|
| 2| Italy|
| 3| Italy|
+------+-------+
answered Nov 19 '18 at 13:10
RudyVerbovenRudyVerboven
438414
Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
– alexanoid
Nov 19 '18 at 13:53
add a comment |
As an alternative you can use
withColumn()
and
when()
function (from spark.sql.functions):
val users = Seq(("1", "france"), ("2", "Italy"), ("3", "italy")).toDF("userId", "country")
val countriesList = Seq("france", "italy", "germany").toList
val result = users.withColumn("country", when(col("country") === "italy", "Italy")
.when(col("country") isin(countriesList:_*), upper(col("country"))).otherwise(col("country")))
result.show()
Result:
+------+-------+
|userId|country|
+------+-------+
| 1| FRANCE|
| 2| Italy|
| 3| Italy|
+------+-------+
answered Nov 19 '18 at 13:10
RudyVerbovenRudyVerboven
438414
Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
– alexanoid
Nov 19 '18 at 13:53
add a comment |
As an alternative you can use
withColumn()
and
when()
function (from spark.sql.functions):
val users = Seq(("1", "france"), ("2", "Italy"), ("3", "italy")).toDF("userId", "country")
val countriesList = Seq("france", "italy", "germany").toList
val result = users.withColumn("country", when(col("country") === "italy", "Italy")
.when(col("country") isin(countriesList:_*), upper(col("country"))).otherwise(col("country")))
result.show()
Result:
+------+-------+
|userId|country|
+------+-------+
| 1| FRANCE|
| 2| Italy|
| 3| Italy|
+------+-------+
answered Nov 19 '18 at 13:10
RudyVerbovenRudyVerboven
438414
As an alternative you can use
withColumn()
and
when()
function (from spark.sql.functions):
val users = Seq(("1", "france"), ("2", "Italy"), ("3", "italy")).toDF("userId", "country")
val countriesList = Seq("france", "italy", "germany").toList
val result = users.withColumn("country", when(col("country") === "italy", "Italy")
.when(col("country") isin(countriesList:_*), upper(col("country"))).otherwise(col("country")))
result.show()
Result:
+------+-------+
|userId|country|
+------+-------+
| 1| FRANCE|
| 2| Italy|
| 3| Italy|
+------+-------+
answered Nov 19 '18 at 13:10
RudyVerbovenRudyVerboven
438414
answered Nov 19 '18 at 13:10
RudyVerbovenRudyVerboven
438414
answered Nov 19 '18 at 13:10
RudyVerbovenRudyVerboven
438414
answered Nov 19 '18 at 13:10
RudyVerbovenRudyVerboven
438414
438414
Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
– alexanoid
Nov 19 '18 at 13:53
add a comment |
Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
– alexanoid
Nov 19 '18 at 13:53
Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
– alexanoid
Nov 19 '18 at 13:53
Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
– alexanoid
Nov 19 '18 at 13:53
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373091%2fspark-in-exists-predicate-in-select-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
