You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by tridib <tr...@live.com> on 2015/02/26 07:33:28 UTC

group by order by fails

Hi,
I need to find top 10 most selling samples. So query looks like:
select  s.name, count(s.name) from sample s group by s.name order by
count(s.name)

This query fails with following error:
org.apache.spark.sql.catalyst.errors.package$TreeNodeException: sort, tree:
Sort [COUNT(name#0) ASC], true
 Exchange (RangePartitioning [COUNT(name#0) ASC], 200)
  Aggregate false, [name#0], [name#0 AS
name#1,Coalesce(SUM(PartialCount#4L),0) AS count#2L,name#0]
   Exchange (HashPartitioning [name#0], 200)
    Aggregate true, [name#0], [name#0,COUNT(name#0) AS PartialCount#4L]
     PhysicalRDD [name#0], MapPartitionsRDD[1] at mapPartitions at
JavaSQLContext.scala:102

	at
org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:47)
	at org.apache.spark.sql.execution.Sort.execute(basicOperators.scala:206)
	at org.apache.spark.sql.execution.Project.execute(basicOperators.scala:43)
	at
org.apache.spark.sql.execution.SparkPlan.executeCollect(SparkPlan.scala:84)
	at org.apache.spark.sql.SchemaRDD.collect(SchemaRDD.scala:444)
	at
org.apache.spark.sql.api.java.JavaSchemaRDD.collect(JavaSchemaRDD.scala:114)
	at
com.edifecs.platform.df.analytics.spark.domain.dao.OrderByTest.testGetVisitDistributionByPrimaryDx(OrderByTest.java:48)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
	at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
	at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
	at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
	at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
	at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:74)
	at
com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:211)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:67)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at
com.intellij.rt.execution.CommandLineWrapper.main(CommandLineWrapper.java:121)
Caused by: org.apache.spark.sql.catalyst.errors.package$TreeNodeException:
execute, tree:
Exchange (RangePartitioning [COUNT(name#0) ASC], 200)
 Aggregate false, [name#0], [name#0 AS
name#1,Coalesce(SUM(PartialCount#4L),0) AS count#2L,name#0]
  Exchange (HashPartitioning [name#0], 200)
   Aggregate true, [name#0], [name#0,COUNT(name#0) AS PartialCount#4L]
    PhysicalRDD [name#0], MapPartitionsRDD[1] at mapPartitions at
JavaSQLContext.scala:102

	at
org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:47)
	at org.apache.spark.sql.execution.Exchange.execute(Exchange.scala:47)
	at
org.apache.spark.sql.execution.Sort$$anonfun$execute$3.apply(basicOperators.scala:207)
	at
org.apache.spark.sql.execution.Sort$$anonfun$execute$3.apply(basicOperators.scala:207)
	at
org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:46)
	... 37 more
Caused by: org.apache.spark.sql.catalyst.errors.package$TreeNodeException:
No function to evaluate expression. type: Count, tree: COUNT(input[2])
	at
org.apache.spark.sql.catalyst.expressions.AggregateExpression.eval(aggregates.scala:41)
	at
org.apache.spark.sql.catalyst.expressions.RowOrdering.compare(Row.scala:250)
	at
org.apache.spark.sql.catalyst.expressions.RowOrdering.compare(Row.scala:242)
	at scala.math.Ordering$$anon$5.compare(Ordering.scala:122)
	at java.util.TimSort.countRunAndMakeAscending(TimSort.java:351)
	at java.util.TimSort.sort(TimSort.java:216)
	at java.util.Arrays.sort(Arrays.java:1438)
	at scala.collection.SeqLike$class.sorted(SeqLike.scala:615)
	at scala.collection.AbstractSeq.sorted(Seq.scala:40)
	at scala.collection.SeqLike$class.sortBy(SeqLike.scala:594)
	at scala.collection.AbstractSeq.sortBy(Seq.scala:40)
	at
org.apache.spark.RangePartitioner$.determineBounds(Partitioner.scala:279)
	at org.apache.spark.RangePartitioner.<init>(Partitioner.scala:152)
	at
org.apache.spark.sql.execution.Exchange$$anonfun$execute$1.apply(Exchange.scala:88)
	at
org.apache.spark.sql.execution.Exchange$$anonfun$execute$1.apply(Exchange.scala:48)
	at
org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:46)
	... 41 more

Source Code:
        JavaSparkContext sc = new JavaSparkContext("local",
"SparkSchemaTest");
        try {
            List samples = new ArrayList<>();
            samples.add(new SampleVo("Apple"));
            samples.add(new SampleVo("Apple"));
            samples.add(new SampleVo("Apple"));
            samples.add(new SampleVo("Orange"));
            samples.add(new SampleVo("Orange"));


            JavaRDD<Map> claimRdd = sc.parallelize(samples);
            JavaSQLContext sqlCtx = new JavaHiveContext(sc);
            JavaSchemaRDD schemaRdd = sqlCtx.applySchema(claimRdd,
SampleVo.class);
            sqlCtx.registerRDDAsTable(schemaRdd, "sample");

            //String query = "select  s.name, count(s.name) from sample s
group by s.name"; Worked OK
            String query = "select  s.name, count(s.name) from sample s
group by s.name order by count(s.name)";

            JavaSchemaRDD teenagersCost = sqlCtx.sql(query);
            List<org.apache.spark.sql.api.java.Row> rows =
teenagersCost.collect();
            for (org.apache.spark.sql.api.java.Row row : rows) {
                System.out.println(row.getString(0) + "=" + row.getLong(1));
            }
        } finally {
            sc.stop();
        }
----------------------------
public class SampleVo implements Serializable {
    private String name;

    public SampleVo() {
    }

    public SampleVo(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

----------
Does this mean spark sql does not support order by over group by?





--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/group-by-order-by-fails-tp21815.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
For additional commands, e-mail: user-help@spark.apache.org


Re: group by order by fails

Posted by iceback <ro...@utah.edu>.
String query = "select  s.name, count(s.name) as tally from sample s group by
s.name order by tally";



--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/group-by-order-by-fails-tp21815p21854.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
For additional commands, e-mail: user-help@spark.apache.org


RE: group by order by fails

Posted by Tridib Samanta <tr...@live.com>.
Thanks Michael! It worked. Some how my mails are not getting accepted by spark user mailing list. :(
 
From: michael@databricks.com
Date: Thu, 26 Feb 2015 17:49:43 -0800
Subject: Re: group by order by fails
To: tridib.samanta@live.com
CC: akhil@sigmoidanalytics.com; user@spark.apache.org

Assign an alias to the count in the select clause and use that alias in the order by clause.
On Wed, Feb 25, 2015 at 11:17 PM, Tridib Samanta <tr...@live.com> wrote:



Actually I just realized , I am using 1.2.0.
 
Thanks
Tridib
 
Date: Thu, 26 Feb 2015 12:37:06 +0530
Subject: Re: group by order by fails
From: akhil@sigmoidanalytics.com
To: tridib.samanta@live.com
CC: user@spark.apache.org

Which version of spark are you having? It seems there was a similar Jira https://issues.apache.org/jira/browse/SPARK-2474ThanksBest Regards

On Thu, Feb 26, 2015 at 12:03 PM, tridib <tr...@live.com> wrote:
Hi,

I need to find top 10 most selling samples. So query looks like:

select  s.name, count(s.name) from sample s group by s.name order by

count(s.name)



This query fails with following error:

org.apache.spark.sql.catalyst.errors.package$TreeNodeException: sort, tree:

Sort [COUNT(name#0) ASC], true

 Exchange (RangePartitioning [COUNT(name#0) ASC], 200)

  Aggregate false, [name#0], [name#0 AS

name#1,Coalesce(SUM(PartialCount#4L),0) AS count#2L,name#0]

   Exchange (HashPartitioning [name#0], 200)

    Aggregate true, [name#0], [name#0,COUNT(name#0) AS PartialCount#4L]

     PhysicalRDD [name#0], MapPartitionsRDD[1] at mapPartitions at

JavaSQLContext.scala:102



        at

org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:47)

        at org.apache.spark.sql.execution.Sort.execute(basicOperators.scala:206)

        at org.apache.spark.sql.execution.Project.execute(basicOperators.scala:43)

        at

org.apache.spark.sql.execution.SparkPlan.executeCollect(SparkPlan.scala:84)

        at org.apache.spark.sql.SchemaRDD.collect(SchemaRDD.scala:444)

        at

org.apache.spark.sql.api.java.JavaSchemaRDD.collect(JavaSchemaRDD.scala:114)

        at

com.edifecs.platform.df.analytics.spark.domain.dao.OrderByTest.testGetVisitDistributionByPrimaryDx(OrderByTest.java:48)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at

sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

        at

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at

org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)

        at

org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

        at

org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)

        at

org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

        at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)

        at

org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)

        at

org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)

        at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)

        at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)

        at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)

        at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)

        at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)

        at org.junit.runners.ParentRunner.run(ParentRunner.java:309)

        at org.junit.runner.JUnitCore.run(JUnitCore.java:160)

        at

com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:74)

        at

com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:211)

        at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:67)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at

sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

        at

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at

sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

        at

com.intellij.rt.execution.CommandLineWrapper.main(CommandLineWrapper.java:121)

Caused by: org.apache.spark.sql.catalyst.errors.package$TreeNodeException:

execute, tree:

Exchange (RangePartitioning [COUNT(name#0) ASC], 200)

 Aggregate false, [name#0], [name#0 AS

name#1,Coalesce(SUM(PartialCount#4L),0) AS count#2L,name#0]

  Exchange (HashPartitioning [name#0], 200)

   Aggregate true, [name#0], [name#0,COUNT(name#0) AS PartialCount#4L]

    PhysicalRDD [name#0], MapPartitionsRDD[1] at mapPartitions at

JavaSQLContext.scala:102



        at

org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:47)

        at org.apache.spark.sql.execution.Exchange.execute(Exchange.scala:47)

        at

org.apache.spark.sql.execution.Sort$$anonfun$execute$3.apply(basicOperators.scala:207)

        at

org.apache.spark.sql.execution.Sort$$anonfun$execute$3.apply(basicOperators.scala:207)

        at

org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:46)

        ... 37 more

Caused by: org.apache.spark.sql.catalyst.errors.package$TreeNodeException:

No function to evaluate expression. type: Count, tree: COUNT(input[2])

        at

org.apache.spark.sql.catalyst.expressions.AggregateExpression.eval(aggregates.scala:41)

        at

org.apache.spark.sql.catalyst.expressions.RowOrdering.compare(Row.scala:250)

        at

org.apache.spark.sql.catalyst.expressions.RowOrdering.compare(Row.scala:242)

        at scala.math.Ordering$$anon$5.compare(Ordering.scala:122)

        at java.util.TimSort.countRunAndMakeAscending(TimSort.java:351)

        at java.util.TimSort.sort(TimSort.java:216)

        at java.util.Arrays.sort(Arrays.java:1438)

        at scala.collection.SeqLike$class.sorted(SeqLike.scala:615)

        at scala.collection.AbstractSeq.sorted(Seq.scala:40)

        at scala.collection.SeqLike$class.sortBy(SeqLike.scala:594)

        at scala.collection.AbstractSeq.sortBy(Seq.scala:40)

        at

org.apache.spark.RangePartitioner$.determineBounds(Partitioner.scala:279)

        at org.apache.spark.RangePartitioner.<init>(Partitioner.scala:152)

        at

org.apache.spark.sql.execution.Exchange$$anonfun$execute$1.apply(Exchange.scala:88)

        at

org.apache.spark.sql.execution.Exchange$$anonfun$execute$1.apply(Exchange.scala:48)

        at

org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:46)

        ... 41 more



Source Code:

        JavaSparkContext sc = new JavaSparkContext("local",

"SparkSchemaTest");

        try {

            List samples = new ArrayList<>();

            samples.add(new SampleVo("Apple"));

            samples.add(new SampleVo("Apple"));

            samples.add(new SampleVo("Apple"));

            samples.add(new SampleVo("Orange"));

            samples.add(new SampleVo("Orange"));





            JavaRDD<Map> claimRdd = sc.parallelize(samples);

            JavaSQLContext sqlCtx = new JavaHiveContext(sc);

            JavaSchemaRDD schemaRdd = sqlCtx.applySchema(claimRdd,

SampleVo.class);

            sqlCtx.registerRDDAsTable(schemaRdd, "sample");



            //String query = "select  s.name, count(s.name) from sample s

group by s.name"; Worked OK

            String query = "select  s.name, count(s.name) from sample s

group by s.name order by count(s.name)";



            JavaSchemaRDD teenagersCost = sqlCtx.sql(query);

            List<org.apache.spark.sql.api.java.Row> rows =

teenagersCost.collect();

            for (org.apache.spark.sql.api.java.Row row : rows) {

                System.out.println(row.getString(0) + "=" + row.getLong(1));

            }

        } finally {

            sc.stop();

        }

----------------------------

public class SampleVo implements Serializable {

    private String name;



    public SampleVo() {

    }



    public SampleVo(String name) {

        this.name = name;

    }



    public String getName() {

        return name;

    }



    public void setName(String name) {

        this.name = name;

    }

}



----------

Does this mean spark sql does not support order by over group by?











--

View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/group-by-order-by-fails-tp21815.html

Sent from the Apache Spark User List mailing list archive at Nabble.com.



---------------------------------------------------------------------

To unsubscribe, e-mail: user-unsubscribe@spark.apache.org

For additional commands, e-mail: user-help@spark.apache.org




 		 	   		  

 		 	   		  

Re: group by order by fails

Posted by Michael Armbrust <mi...@databricks.com>.
Assign an alias to the count in the select clause and use that alias in the
order by clause.

On Wed, Feb 25, 2015 at 11:17 PM, Tridib Samanta <tr...@live.com>
wrote:

> Actually I just realized , I am using 1.2.0.
>
> Thanks
> Tridib
>
> ------------------------------
> Date: Thu, 26 Feb 2015 12:37:06 +0530
> Subject: Re: group by order by fails
> From: akhil@sigmoidanalytics.com
> To: tridib.samanta@live.com
> CC: user@spark.apache.org
>
>
> Which version of spark are you having? It seems there was a similar Jira
> https://issues.apache.org/jira/browse/SPARK-2474
>
> Thanks
> Best Regards
>
> On Thu, Feb 26, 2015 at 12:03 PM, tridib <tr...@live.com> wrote:
>
> Hi,
> I need to find top 10 most selling samples. So query looks like:
> select  s.name, count(s.name) from sample s group by s.name order by
> count(s.name)
>
> This query fails with following error:
> org.apache.spark.sql.catalyst.errors.package$TreeNodeException: sort, tree:
> Sort [COUNT(name#0) ASC], true
>  Exchange (RangePartitioning [COUNT(name#0) ASC], 200)
>   Aggregate false, [name#0], [name#0 AS
> name#1,Coalesce(SUM(PartialCount#4L),0) AS count#2L,name#0]
>    Exchange (HashPartitioning [name#0], 200)
>     Aggregate true, [name#0], [name#0,COUNT(name#0) AS PartialCount#4L]
>      PhysicalRDD [name#0], MapPartitionsRDD[1] at mapPartitions at
> JavaSQLContext.scala:102
>
>         at
> org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:47)
>         at
> org.apache.spark.sql.execution.Sort.execute(basicOperators.scala:206)
>         at
> org.apache.spark.sql.execution.Project.execute(basicOperators.scala:43)
>         at
> org.apache.spark.sql.execution.SparkPlan.executeCollect(SparkPlan.scala:84)
>         at org.apache.spark.sql.SchemaRDD.collect(SchemaRDD.scala:444)
>         at
>
> org.apache.spark.sql.api.java.JavaSchemaRDD.collect(JavaSchemaRDD.scala:114)
>         at
>
> com.edifecs.platform.df.analytics.spark.domain.dao.OrderByTest.testGetVisitDistributionByPrimaryDx(OrderByTest.java:48)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at
>
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>         at
>
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>         at
>
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
>         at
>
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
>         at
>
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
>         at
>
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
>         at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
>         at
>
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
>         at
>
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
>         at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
>         at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
>         at
> org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
>         at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
>         at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
>         at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
>         at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
>         at
>
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:74)
>         at
>
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:211)
>         at
> com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:67)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at
>
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>         at
>
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>         at
> com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at
>
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>         at
>
> com.intellij.rt.execution.CommandLineWrapper.main(CommandLineWrapper.java:121)
> Caused by: org.apache.spark.sql.catalyst.errors.package$TreeNodeException:
> execute, tree:
> Exchange (RangePartitioning [COUNT(name#0) ASC], 200)
>  Aggregate false, [name#0], [name#0 AS
> name#1,Coalesce(SUM(PartialCount#4L),0) AS count#2L,name#0]
>   Exchange (HashPartitioning [name#0], 200)
>    Aggregate true, [name#0], [name#0,COUNT(name#0) AS PartialCount#4L]
>     PhysicalRDD [name#0], MapPartitionsRDD[1] at mapPartitions at
> JavaSQLContext.scala:102
>
>         at
> org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:47)
>         at
> org.apache.spark.sql.execution.Exchange.execute(Exchange.scala:47)
>         at
>
> org.apache.spark.sql.execution.Sort$$anonfun$execute$3.apply(basicOperators.scala:207)
>         at
>
> org.apache.spark.sql.execution.Sort$$anonfun$execute$3.apply(basicOperators.scala:207)
>         at
> org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:46)
>         ... 37 more
> Caused by: org.apache.spark.sql.catalyst.errors.package$TreeNodeException:
> No function to evaluate expression. type: Count, tree: COUNT(input[2])
>         at
>
> org.apache.spark.sql.catalyst.expressions.AggregateExpression.eval(aggregates.scala:41)
>         at
>
> org.apache.spark.sql.catalyst.expressions.RowOrdering.compare(Row.scala:250)
>         at
>
> org.apache.spark.sql.catalyst.expressions.RowOrdering.compare(Row.scala:242)
>         at scala.math.Ordering$$anon$5.compare(Ordering.scala:122)
>         at java.util.TimSort.countRunAndMakeAscending(TimSort.java:351)
>         at java.util.TimSort.sort(TimSort.java:216)
>         at java.util.Arrays.sort(Arrays.java:1438)
>         at scala.collection.SeqLike$class.sorted(SeqLike.scala:615)
>         at scala.collection.AbstractSeq.sorted(Seq.scala:40)
>         at scala.collection.SeqLike$class.sortBy(SeqLike.scala:594)
>         at scala.collection.AbstractSeq.sortBy(Seq.scala:40)
>         at
> org.apache.spark.RangePartitioner$.determineBounds(Partitioner.scala:279)
>         at org.apache.spark.RangePartitioner.<init>(Partitioner.scala:152)
>         at
>
> org.apache.spark.sql.execution.Exchange$$anonfun$execute$1.apply(Exchange.scala:88)
>         at
>
> org.apache.spark.sql.execution.Exchange$$anonfun$execute$1.apply(Exchange.scala:48)
>         at
> org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:46)
>         ... 41 more
>
> Source Code:
>         JavaSparkContext sc = new JavaSparkContext("local",
> "SparkSchemaTest");
>         try {
>             List samples = new ArrayList<>();
>             samples.add(new SampleVo("Apple"));
>             samples.add(new SampleVo("Apple"));
>             samples.add(new SampleVo("Apple"));
>             samples.add(new SampleVo("Orange"));
>             samples.add(new SampleVo("Orange"));
>
>
>             JavaRDD<Map> claimRdd = sc.parallelize(samples);
>             JavaSQLContext sqlCtx = new JavaHiveContext(sc);
>             JavaSchemaRDD schemaRdd = sqlCtx.applySchema(claimRdd,
> SampleVo.class);
>             sqlCtx.registerRDDAsTable(schemaRdd, "sample");
>
>             //String query = "select  s.name, count(s.name) from sample s
> group by s.name"; Worked OK
>             String query = "select  s.name, count(s.name) from sample s
> group by s.name order by count(s.name)";
>
>             JavaSchemaRDD teenagersCost = sqlCtx.sql(query);
>             List<org.apache.spark.sql.api.java.Row> rows =
> teenagersCost.collect();
>             for (org.apache.spark.sql.api.java.Row row : rows) {
>                 System.out.println(row.getString(0) + "=" +
> row.getLong(1));
>             }
>         } finally {
>             sc.stop();
>         }
> ----------------------------
> public class SampleVo implements Serializable {
>     private String name;
>
>     public SampleVo() {
>     }
>
>     public SampleVo(String name) {
>         this.name = name;
>     }
>
>     public String getName() {
>         return name;
>     }
>
>     public void setName(String name) {
>         this.name = name;
>     }
> }
>
> ----------
> Does this mean spark sql does not support order by over group by?
>
>
>
>
>
> --
> View this message in context:
> http://apache-spark-user-list.1001560.n3.nabble.com/group-by-order-by-fails-tp21815.html
> Sent from the Apache Spark User List mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
> For additional commands, e-mail: user-help@spark.apache.org
>
>
>

RE: group by order by fails

Posted by Tridib Samanta <tr...@live.com>.
Actually I just realized , I am using 1.2.0.
 
Thanks
Tridib
 
Date: Thu, 26 Feb 2015 12:37:06 +0530
Subject: Re: group by order by fails
From: akhil@sigmoidanalytics.com
To: tridib.samanta@live.com
CC: user@spark.apache.org

Which version of spark are you having? It seems there was a similar Jira https://issues.apache.org/jira/browse/SPARK-2474ThanksBest Regards

On Thu, Feb 26, 2015 at 12:03 PM, tridib <tr...@live.com> wrote:
Hi,

I need to find top 10 most selling samples. So query looks like:

select  s.name, count(s.name) from sample s group by s.name order by

count(s.name)



This query fails with following error:

org.apache.spark.sql.catalyst.errors.package$TreeNodeException: sort, tree:

Sort [COUNT(name#0) ASC], true

 Exchange (RangePartitioning [COUNT(name#0) ASC], 200)

  Aggregate false, [name#0], [name#0 AS

name#1,Coalesce(SUM(PartialCount#4L),0) AS count#2L,name#0]

   Exchange (HashPartitioning [name#0], 200)

    Aggregate true, [name#0], [name#0,COUNT(name#0) AS PartialCount#4L]

     PhysicalRDD [name#0], MapPartitionsRDD[1] at mapPartitions at

JavaSQLContext.scala:102



        at

org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:47)

        at org.apache.spark.sql.execution.Sort.execute(basicOperators.scala:206)

        at org.apache.spark.sql.execution.Project.execute(basicOperators.scala:43)

        at

org.apache.spark.sql.execution.SparkPlan.executeCollect(SparkPlan.scala:84)

        at org.apache.spark.sql.SchemaRDD.collect(SchemaRDD.scala:444)

        at

org.apache.spark.sql.api.java.JavaSchemaRDD.collect(JavaSchemaRDD.scala:114)

        at

com.edifecs.platform.df.analytics.spark.domain.dao.OrderByTest.testGetVisitDistributionByPrimaryDx(OrderByTest.java:48)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at

sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

        at

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at

org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)

        at

org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

        at

org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)

        at

org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

        at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)

        at

org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)

        at

org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)

        at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)

        at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)

        at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)

        at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)

        at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)

        at org.junit.runners.ParentRunner.run(ParentRunner.java:309)

        at org.junit.runner.JUnitCore.run(JUnitCore.java:160)

        at

com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:74)

        at

com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:211)

        at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:67)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at

sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

        at

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at

sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

        at

com.intellij.rt.execution.CommandLineWrapper.main(CommandLineWrapper.java:121)

Caused by: org.apache.spark.sql.catalyst.errors.package$TreeNodeException:

execute, tree:

Exchange (RangePartitioning [COUNT(name#0) ASC], 200)

 Aggregate false, [name#0], [name#0 AS

name#1,Coalesce(SUM(PartialCount#4L),0) AS count#2L,name#0]

  Exchange (HashPartitioning [name#0], 200)

   Aggregate true, [name#0], [name#0,COUNT(name#0) AS PartialCount#4L]

    PhysicalRDD [name#0], MapPartitionsRDD[1] at mapPartitions at

JavaSQLContext.scala:102



        at

org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:47)

        at org.apache.spark.sql.execution.Exchange.execute(Exchange.scala:47)

        at

org.apache.spark.sql.execution.Sort$$anonfun$execute$3.apply(basicOperators.scala:207)

        at

org.apache.spark.sql.execution.Sort$$anonfun$execute$3.apply(basicOperators.scala:207)

        at

org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:46)

        ... 37 more

Caused by: org.apache.spark.sql.catalyst.errors.package$TreeNodeException:

No function to evaluate expression. type: Count, tree: COUNT(input[2])

        at

org.apache.spark.sql.catalyst.expressions.AggregateExpression.eval(aggregates.scala:41)

        at

org.apache.spark.sql.catalyst.expressions.RowOrdering.compare(Row.scala:250)

        at

org.apache.spark.sql.catalyst.expressions.RowOrdering.compare(Row.scala:242)

        at scala.math.Ordering$$anon$5.compare(Ordering.scala:122)

        at java.util.TimSort.countRunAndMakeAscending(TimSort.java:351)

        at java.util.TimSort.sort(TimSort.java:216)

        at java.util.Arrays.sort(Arrays.java:1438)

        at scala.collection.SeqLike$class.sorted(SeqLike.scala:615)

        at scala.collection.AbstractSeq.sorted(Seq.scala:40)

        at scala.collection.SeqLike$class.sortBy(SeqLike.scala:594)

        at scala.collection.AbstractSeq.sortBy(Seq.scala:40)

        at

org.apache.spark.RangePartitioner$.determineBounds(Partitioner.scala:279)

        at org.apache.spark.RangePartitioner.<init>(Partitioner.scala:152)

        at

org.apache.spark.sql.execution.Exchange$$anonfun$execute$1.apply(Exchange.scala:88)

        at

org.apache.spark.sql.execution.Exchange$$anonfun$execute$1.apply(Exchange.scala:48)

        at

org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:46)

        ... 41 more



Source Code:

        JavaSparkContext sc = new JavaSparkContext("local",

"SparkSchemaTest");

        try {

            List samples = new ArrayList<>();

            samples.add(new SampleVo("Apple"));

            samples.add(new SampleVo("Apple"));

            samples.add(new SampleVo("Apple"));

            samples.add(new SampleVo("Orange"));

            samples.add(new SampleVo("Orange"));





            JavaRDD<Map> claimRdd = sc.parallelize(samples);

            JavaSQLContext sqlCtx = new JavaHiveContext(sc);

            JavaSchemaRDD schemaRdd = sqlCtx.applySchema(claimRdd,

SampleVo.class);

            sqlCtx.registerRDDAsTable(schemaRdd, "sample");



            //String query = "select  s.name, count(s.name) from sample s

group by s.name"; Worked OK

            String query = "select  s.name, count(s.name) from sample s

group by s.name order by count(s.name)";



            JavaSchemaRDD teenagersCost = sqlCtx.sql(query);

            List<org.apache.spark.sql.api.java.Row> rows =

teenagersCost.collect();

            for (org.apache.spark.sql.api.java.Row row : rows) {

                System.out.println(row.getString(0) + "=" + row.getLong(1));

            }

        } finally {

            sc.stop();

        }

----------------------------

public class SampleVo implements Serializable {

    private String name;



    public SampleVo() {

    }



    public SampleVo(String name) {

        this.name = name;

    }



    public String getName() {

        return name;

    }



    public void setName(String name) {

        this.name = name;

    }

}



----------

Does this mean spark sql does not support order by over group by?











--

View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/group-by-order-by-fails-tp21815.html

Sent from the Apache Spark User List mailing list archive at Nabble.com.



---------------------------------------------------------------------

To unsubscribe, e-mail: user-unsubscribe@spark.apache.org

For additional commands, e-mail: user-help@spark.apache.org




 		 	   		  

Re: group by order by fails

Posted by Akhil Das <ak...@sigmoidanalytics.com>.
Which version of spark are you having? It seems there was a similar Jira
https://issues.apache.org/jira/browse/SPARK-2474

Thanks
Best Regards

On Thu, Feb 26, 2015 at 12:03 PM, tridib <tr...@live.com> wrote:

> Hi,
> I need to find top 10 most selling samples. So query looks like:
> select  s.name, count(s.name) from sample s group by s.name order by
> count(s.name)
>
> This query fails with following error:
> org.apache.spark.sql.catalyst.errors.package$TreeNodeException: sort, tree:
> Sort [COUNT(name#0) ASC], true
>  Exchange (RangePartitioning [COUNT(name#0) ASC], 200)
>   Aggregate false, [name#0], [name#0 AS
> name#1,Coalesce(SUM(PartialCount#4L),0) AS count#2L,name#0]
>    Exchange (HashPartitioning [name#0], 200)
>     Aggregate true, [name#0], [name#0,COUNT(name#0) AS PartialCount#4L]
>      PhysicalRDD [name#0], MapPartitionsRDD[1] at mapPartitions at
> JavaSQLContext.scala:102
>
>         at
> org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:47)
>         at
> org.apache.spark.sql.execution.Sort.execute(basicOperators.scala:206)
>         at
> org.apache.spark.sql.execution.Project.execute(basicOperators.scala:43)
>         at
> org.apache.spark.sql.execution.SparkPlan.executeCollect(SparkPlan.scala:84)
>         at org.apache.spark.sql.SchemaRDD.collect(SchemaRDD.scala:444)
>         at
>
> org.apache.spark.sql.api.java.JavaSchemaRDD.collect(JavaSchemaRDD.scala:114)
>         at
>
> com.edifecs.platform.df.analytics.spark.domain.dao.OrderByTest.testGetVisitDistributionByPrimaryDx(OrderByTest.java:48)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at
>
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>         at
>
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>         at
>
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
>         at
>
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
>         at
>
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
>         at
>
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
>         at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
>         at
>
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
>         at
>
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
>         at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
>         at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
>         at
> org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
>         at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
>         at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
>         at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
>         at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
>         at
>
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:74)
>         at
>
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:211)
>         at
> com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:67)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at
>
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>         at
>
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>         at
> com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at
>
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>         at
>
> com.intellij.rt.execution.CommandLineWrapper.main(CommandLineWrapper.java:121)
> Caused by: org.apache.spark.sql.catalyst.errors.package$TreeNodeException:
> execute, tree:
> Exchange (RangePartitioning [COUNT(name#0) ASC], 200)
>  Aggregate false, [name#0], [name#0 AS
> name#1,Coalesce(SUM(PartialCount#4L),0) AS count#2L,name#0]
>   Exchange (HashPartitioning [name#0], 200)
>    Aggregate true, [name#0], [name#0,COUNT(name#0) AS PartialCount#4L]
>     PhysicalRDD [name#0], MapPartitionsRDD[1] at mapPartitions at
> JavaSQLContext.scala:102
>
>         at
> org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:47)
>         at
> org.apache.spark.sql.execution.Exchange.execute(Exchange.scala:47)
>         at
>
> org.apache.spark.sql.execution.Sort$$anonfun$execute$3.apply(basicOperators.scala:207)
>         at
>
> org.apache.spark.sql.execution.Sort$$anonfun$execute$3.apply(basicOperators.scala:207)
>         at
> org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:46)
>         ... 37 more
> Caused by: org.apache.spark.sql.catalyst.errors.package$TreeNodeException:
> No function to evaluate expression. type: Count, tree: COUNT(input[2])
>         at
>
> org.apache.spark.sql.catalyst.expressions.AggregateExpression.eval(aggregates.scala:41)
>         at
>
> org.apache.spark.sql.catalyst.expressions.RowOrdering.compare(Row.scala:250)
>         at
>
> org.apache.spark.sql.catalyst.expressions.RowOrdering.compare(Row.scala:242)
>         at scala.math.Ordering$$anon$5.compare(Ordering.scala:122)
>         at java.util.TimSort.countRunAndMakeAscending(TimSort.java:351)
>         at java.util.TimSort.sort(TimSort.java:216)
>         at java.util.Arrays.sort(Arrays.java:1438)
>         at scala.collection.SeqLike$class.sorted(SeqLike.scala:615)
>         at scala.collection.AbstractSeq.sorted(Seq.scala:40)
>         at scala.collection.SeqLike$class.sortBy(SeqLike.scala:594)
>         at scala.collection.AbstractSeq.sortBy(Seq.scala:40)
>         at
> org.apache.spark.RangePartitioner$.determineBounds(Partitioner.scala:279)
>         at org.apache.spark.RangePartitioner.<init>(Partitioner.scala:152)
>         at
>
> org.apache.spark.sql.execution.Exchange$$anonfun$execute$1.apply(Exchange.scala:88)
>         at
>
> org.apache.spark.sql.execution.Exchange$$anonfun$execute$1.apply(Exchange.scala:48)
>         at
> org.apache.spark.sql.catalyst.errors.package$.attachTree(package.scala:46)
>         ... 41 more
>
> Source Code:
>         JavaSparkContext sc = new JavaSparkContext("local",
> "SparkSchemaTest");
>         try {
>             List samples = new ArrayList<>();
>             samples.add(new SampleVo("Apple"));
>             samples.add(new SampleVo("Apple"));
>             samples.add(new SampleVo("Apple"));
>             samples.add(new SampleVo("Orange"));
>             samples.add(new SampleVo("Orange"));
>
>
>             JavaRDD<Map> claimRdd = sc.parallelize(samples);
>             JavaSQLContext sqlCtx = new JavaHiveContext(sc);
>             JavaSchemaRDD schemaRdd = sqlCtx.applySchema(claimRdd,
> SampleVo.class);
>             sqlCtx.registerRDDAsTable(schemaRdd, "sample");
>
>             //String query = "select  s.name, count(s.name) from sample s
> group by s.name"; Worked OK
>             String query = "select  s.name, count(s.name) from sample s
> group by s.name order by count(s.name)";
>
>             JavaSchemaRDD teenagersCost = sqlCtx.sql(query);
>             List<org.apache.spark.sql.api.java.Row> rows =
> teenagersCost.collect();
>             for (org.apache.spark.sql.api.java.Row row : rows) {
>                 System.out.println(row.getString(0) + "=" +
> row.getLong(1));
>             }
>         } finally {
>             sc.stop();
>         }
> ----------------------------
> public class SampleVo implements Serializable {
>     private String name;
>
>     public SampleVo() {
>     }
>
>     public SampleVo(String name) {
>         this.name = name;
>     }
>
>     public String getName() {
>         return name;
>     }
>
>     public void setName(String name) {
>         this.name = name;
>     }
> }
>
> ----------
> Does this mean spark sql does not support order by over group by?
>
>
>
>
>
> --
> View this message in context:
> http://apache-spark-user-list.1001560.n3.nabble.com/group-by-order-by-fails-tp21815.html
> Sent from the Apache Spark User List mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
> For additional commands, e-mail: user-help@spark.apache.org
>
>