Why pyspark sql does not count correctly with group by clause?

Question

I load parquet file into sql context like this:

sqlCtx = SQLContext(sc)
rdd_file = sqlCtx.read.parquet("hdfs:///my_file.parquet")
rdd_file.registerTempTable("type_table")

Then I run this simple query:

sqlCtx.sql('SELECT count(name), name from type_table group by name order by count(name)').show()

The result:

+----------------+----------+
|count(name)     |name      |
+----------------+----------+
|               0|      null|
|          226307|         x|
+----------------+----------+

However, if I use groupBy from rdd set. I got a different result:

sqlCtx.sql("SELECT name FROM type_table").groupBy("name").count().show()

+----------+------+
| name     | count|
+----------+------+
|         x|226307|
|      null|586822|
+----------+------+

The count of x is the same for the two methods but null is quite different. It seems like the sql statement doesn't count null with group by correctly. Can you point out what I did wrong?

Thanks,

Can you print out your DataFrame schema ?

eliasah
– eliasah

2016-09-30 06:28:33 +00:00
Commented Sep 30, 2016 at 6:28 — eliasah
– eliasah, Commented Sep 30, 2016 at 6:28

Arunakiran Nulu · Accepted Answer · 2016-09-30 05:31:08Z

3

count(name) will exclude null values , if you give count(*) it will give you the null values as well .

Try below.

sqlCtx.sql('SELECT count(*), name from type_table group by name order by count(*)').show()

answered Sep 30, 2016 at 5:31

Arunakiran Nulu

2,0991 gold badge12 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Why pyspark sql does not count correctly with group by clause?

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