7 Sparkify SQL

sparkify_7_sql.py

# Show that we can do the same calculation above using SQL
data.createOrReplaceTempView('sparkify')
sub_query = """

SELECT 
    userId, 
    sessionId, 
    max(itemInSession) as itemCount
FROM
    sparkify 
GROUP BY 
    userId, sessionId
ORDER BY 
    userId, sessionId
"""

query = """
SELECT 
    userId, 
    count(userId) as sessionCount, 
    avg(itemCount) as meanSongCount
FROM 
    ({sub_query})
GROUP BY 
    userId
ORDER BY
    userId
""".format(sub_query=sub_query)

spark.sql(query).show(10)