how to solve this spark-scala sql error message

Question

To remove duplicate rows, I attempt this sql

val characters = MongoSpark.load[sparkSQL.Character](sparkSession)
characters.createOrReplaceTempView("characters")
val testsql = sparkSession.select("SELECT * FROM characters GROUP BY title")
testsql.show()

but this sql make this error message. if you know this problem, please answer this questin.

thanks you

Parsing command: SELECT * FROM characters GROUP BY title
Exception in thread "main" org.spache.spark.sql.AnalysisException: 
expression 'characters.`url`' is neither present in the group by, nor is it an aggregate function
Add to Add to group by  or wrap in first() if you don't care which value you get.;;

and then i attempt like this but i don't know this is right solution....

please answer this question. thanks you!

val characters = MongoSpark.load[sparkSQL.Character](sparkSession)
characters.createOrReplaceTempView("characters")
val testsql = sparkSession.select("SELECT * FROM characters")
testgrsql = testsql.groupBy("title")
testgrsql.show()

SELECT colums mentioned in GROUPBY val testsql = sparkSession.sql("SELECT title FROM characters GROUP BY title"), If you are not using any aggregate functions. — mrsrinivas
– mrsrinivas, Commented Aug 28, 2017 at 6:26
Hi, if below answer has solved your problem please consider accepting it or adding your own solution. So, that it indicates to the wider community that you've found a solution. — mrsrinivas
– mrsrinivas, Commented Oct 7, 2017 at 3:15

mrsrinivas · Accepted Answer · 2017-08-28 06:31:00Z

1

Error message explains everything,

Parsing command: SELECT * FROM characters GROUP BY title

Exception in thread "main" org.spache.spark.sql.AnalysisException: expression 'characters.url' is neither present in the group by, nor is it an aggregate function

Add to Add to group by or wrap in first() if you don't care which value you get.;;

So the usage can be, If you want first url value for each title then first(url)

characters.createOrReplaceTempView("characters")
val testsql = sparkSession.sql("SELECT title, first(url) FROM characters GROUP BY title")

answered Aug 28, 2017 at 6:31

mrsrinivas

35.7k13 gold badges133 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to solve this spark-scala sql error message

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related