Spark IN/EXISTS predicate in SELECT statement











up vote
0
down vote

favorite












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







share|improve this question




























    up vote
    0
    down vote

    favorite












    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







    share|improve this question


























      up vote
      0
      down vote

      favorite









      up vote
      0
      down vote

      favorite











      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







      share|improve this question















      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






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 2 days ago

























      asked 2 days ago









      alexanoid

      6,8831175166




      6,8831175166
























          2 Answers
          2






          active

          oldest

          votes

















          up vote
          1
          down vote



          accepted










          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).






          share|improve this answer




























            up vote
            0
            down vote













            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|
            
+------+-------+





            share|improve this answer





















            • Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
              – alexanoid
              2 days ago











            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',
            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
            });


            }
            });














             

            draft saved


            draft discarded


















            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








            up vote
            1
            down vote



            accepted










            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).






            share|improve this answer

























              up vote
              1
              down vote



              accepted










              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).






              share|improve this answer























                up vote
                1
                down vote



                accepted







                up vote
                1
                down vote



                accepted






                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).






                share|improve this answer












                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).







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered yesterday









                morsik

                684815




                684815
























                    up vote
                    0
                    down vote













                    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|
                    
+------+-------+





                    share|improve this answer





















                    • Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
                      – alexanoid
                      2 days ago















                    up vote
                    0
                    down vote













                    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|
                    
+------+-------+





                    share|improve this answer





















                    • Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
                      – alexanoid
                      2 days ago













                    up vote
                    0
                    down vote










                    up vote
                    0
                    down vote









                    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|
                    
+------+-------+





                    share|improve this answer












                    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|
                    
+------+-------+






                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered 2 days ago









                    RudyVerboven

                    403414




                    403414












                    • Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
                      – alexanoid
                      2 days ago


















                    • Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
                      – alexanoid
                      2 days ago
















                    Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
                    – alexanoid
                    2 days ago




                    Thanks for your answer. Right now I'm mostly interested in pure SQL implementation.
                    – alexanoid
                    2 days ago


















                     

                    draft saved


                    draft discarded



















































                     


                    draft saved


                    draft discarded














                    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





















































                    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







                    Popular posts from this blog

                    MongoDB - Not Authorized To Execute Command

                    Image
                    .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box; } 0 I have successfully enabled authorization on MongoDB and I have created an account on the admin database and then I created an account for my database called test. The following connection string to connect to my test database works successfully: mongo --host 192.168.17.52 --port 27017 -u user1 -p password --authenticationDatabase test Only problem I have now is, I cannot execute commands such as: show dbs. I get the following error when I try to do so: "errmsg" : "not authorized on admin to execute command { listDatabases: 1.0, lsid: { id: UUID("a1d5bc0d-bc58-485e-b232-270758a89455") }, $db: "admin...
                    Read more

                    in spring boot 2.1 many test slices are not allowed anymore due to multiple @BootstrapWith

                    Image
                    1 I tried to upgrade a yummy sandwich made of two test slices (@JsonTest and @JdbcTest in my case, crunchy test code in between) adding spring boot 2.1 flavour to it. But it seems it was not much of a success. I cannot annotate my tests with many @...Test since they are now each bringing their own XxxTestContextBootstrapper. It used to work when they all used same SpringBootTestContextBootstrapper. @RunWith(SpringRunner.class) @JdbcTest @JsonTest public class Test { @Test public void test() { System.out.printn("Hello, World !"); } } The error I get from BootstrapUtils is illegalStateException : Configuration error: found multiple declarations of @BootstrapWith for test class I understand I might be doing something wrong here but is there an easy way I could load both Json and Jdbc contex...
                    Read more

                    Npm cannot find a required file even through it is in the searched directory

                    Image
                    up vote 0 down vote favorite I got that error: users-Air:pdfuploader user$ npm run start client@0.1.0 start /Users/user/Documents/pdfuploader react-scripts start Could not find a required file. Name: index.html Searched in: /Users/user/Documents/pdfuploader/public npm ERR! code ELIFECYCLE npm ERR! errno 1 npm ERR! client@0.1.0 start: `react-scripts start` npm ERR! Exit status 1 npm ERR! npm ERR! Failed at the client@0.1.0 start script. npm ERR! This is probably not a problem with npm. There is likely additional logging output above. npm ERR! A complete log of this run can be found in: npm ERR! /Users/user/.npm/_logs/2018-11-19T10_19_14_555Z-debug.log users-Air:pdfuploader user$ And the file is in /Users/user/Documents/pdfuploader/public as you can see in my github repository: https://github.c...
                    Read more