spark sql count(*) query store result

user_1364364 0
Viewed 34707

Hello I use Spark with Python, I performed a basic count(*) query on a dataframe as follow

myquery = sqlContext.sql("SELECT count(*) FROM myDF")

Result is

+--------+
|count(1)|
+--------+
|    3469|
+--------+

How can I save this value in order to perform futher operation.

For instance divide 3469 by 24 [whatever 24 means...]

apache-spark-sql apache-spark sql
6 Answers
>>> myquery = sqlContext.sql("SELECT count(*) FROM myDF").collect()[0][0]
>>> myquery
3469

This would get you only the count. Later type of myquery can be converted and used within successive queries e.g. if you want to show the entire row in the output.

This works in pyspark sql.
Caution: This would dump the entire row on the screen.

>>> sqlContext.sql("SELECT * FROM myDF").show(myquery,False)
user_67933360

It would be useful to alias the count into another name and use it as follows:

        countvar = sqlContext.sql("""SELECT count(*) as cnt FROM 
myDF""").first()["cnt"]
        print(countvar/24)
    
        countvar = sqlContext.sql("""SELECT count(*) as nullcnt FROM 
    myDF where id is null""").first()["nullcnt"]
        print(100*(nullcount/countvar))
    
        countvar = sqlContext.sql("""SELECT  count(distinct(id)) as idcnt 
FROM myDF """).first()["idcnt"]
        print(idcnt))
user_27151820
Related