Window Functions in Spark SQL

Add a unique id (Long) to a each row in a DF.

import sqlContext.implicits._ import org.apache.spark.sql.Row import org.apache.spark.sql.types.{StructType, StructField, LongType} val df = sc.parallelize(Seq( ("a", -1.0), ("b", -2.0), ("c", -3.0))).toDF("foo", "bar") val schema = df.schema val rows = df.rdd.zipWithUniqueId.map{ case (r: Row, id: Long) => Row.fromSeq(id +: r.toSeq)} val dfWithPK = sqlContext.createDataFrame( rows, StructType(StructField("id", LongType, false) +: schema.fields))

Another easier option

df.withColumn("id", monotonicallyIncreasingId())

+---+----+-----------+

|foo| bar| id|

+---+----+-----------+

| a|-1.0| 8589934592|

| b|-2.0|17179869184|

| c|-3.0|25769803776|

+---+----+-----------+

Window Functions

Download yelp data http://www.yelp.com/dataset_challenge

$ import org.apache.spark.sql.functions._

$ val business = sqlContext.read.json("/Volumes/SONY/Data/yelp_dataset_challenge_academic_dataset/yelp_academic_dataset_business.json")

$ business.registerTempTable("business")

$ sql("SELECT name, city, review_count, row_number() OVER (PARTITION BY city) as city_review_total FROM business").show

+--------------------+--------------+------------+-----------------+

+--------------------+--------------+------------+-----------------+

|A B-Z Auto Care, ...|110. Las Vegas| 15| 1|

| Wild Rice| Allentown| 16| 1|

| Popmart| Allentown| 3| 2|

| Desert Rose Bar| Arlington| 3| 1|

| Co-Op Grill| Arlington| 7| 2|

|West Valley Pedia...| Avondale| 18| 1|

|Dermatology for A...| Avondale| 3| 2|

| Earnhardt Hyundai| Avondale| 33| 3|

|Waterworks Water ...| Avondale| 5| 4|

| Domino's Pizza| Avondale| 8| 5|

|Universal Technic...| Avondale| 4| 6|

|Crystal Springs A...| Avondale| 11| 7|

| Wendy's| Avondale| 9| 8|

| Fire Department| Avondale| 3| 9|

| Gamestop| Avondale| 7| 10|

|Phoenix Plasterin...| Avondale| 5| 11|

| Nails of the World| Avondale| 14| 12|

|St Thomas Aquinas...| Avondale| 3| 13|

| Palermos Pizza| Avondale| 28| 14|

| Dollar Tree| Avondale| 5| 15|

+--------------------+--------------+------------+-----------------+

only showing top 20 rows

Get the top Yelp businesses based on the number reviews in each city and the row number of the business.

$ sql("SELECT name, city, review_count, rank() OVER (PARTITION BY city ORDER BY review_count DESC) as rank FROM business").show

+--------------------+--------------+------------+----+

+--------------------+--------------+------------+----+

|A B-Z Auto Care, ...|110. Las Vegas| 15| 1|

| Wild Rice| Allentown| 16| 1|

| Popmart| Allentown| 3| 2|

| Co-Op Grill| Arlington| 7| 1|

| Desert Rose Bar| Arlington| 3| 2|

|Flavors of Louisiana| Avondale| 352| 1|

| Pita Kitchen| Avondale| 326| 2|

|TJ's Homestyle Re...| Avondale| 178| 3|

| Smashburger| Avondale| 158| 4|

| India Garden| Avondale| 151| 5|

|Don Pancho Mexica...| Avondale| 145| 6|

| Claim Jumper| Avondale| 138| 7|

|Thai Orchid Garde...| Avondale| 128| 8|

| Greek Gyro Express| Avondale| 119| 9|

|Colados Coffee & ...| Avondale| 118| 10|

| Thai Gourmet| Avondale| 117| 11|

| Fiesta Mexicana| Avondale| 108| 12|

|Kreative Kupcakes...| Avondale| 106| 13|

| NYPD Pizza| Avondale| 103| 14|

| Islands Restaurant| Avondale| 102| 15|

+--------------------+--------------+------------+----+

only showing top 20 rows

Check the number reviews for each business compared to the average number of reviews across all business in the city.

$ sql("SELECT name, city, review_count, AVG(review_count) OVER (PARTITION BY city) as city_review_avg FROM business").show

+--------------------+--------------+------------+------------------+

+--------------------+--------------+------------+------------------+

|A B-Z Auto Care, ...|110. Las Vegas| 15| 15.0|

| Wild Rice| Allentown| 16| 9.5|

| Popmart| Allentown| 3| 9.5|

| Desert Rose Bar| Arlington| 3| 5.0|

| Co-Op Grill| Arlington| 7| 5.0|

|West Valley Pedia...| Avondale| 18|21.067357512953368|

|Dermatology for A...| Avondale| 3|21.067357512953368|

| Earnhardt Hyundai| Avondale| 33|21.067357512953368|

|Waterworks Water ...| Avondale| 5|21.067357512953368|

| Domino's Pizza| Avondale| 8|21.067357512953368|

|Universal Technic...| Avondale| 4|21.067357512953368|

|Crystal Springs A...| Avondale| 11|21.067357512953368|

| Wendy's| Avondale| 9|21.067357512953368|

| Fire Department| Avondale| 3|21.067357512953368|

| Gamestop| Avondale| 7|21.067357512953368|

|Phoenix Plasterin...| Avondale| 5|21.067357512953368|

| Nails of the World| Avondale| 14|21.067357512953368|

|St Thomas Aquinas...| Avondale| 3|21.067357512953368|

| Palermos Pizza| Avondale| 28|21.067357512953368|

| Dollar Tree| Avondale| 5|21.067357512953368|

+--------------------+--------------+------------+------------------+

Check how the number of reviews for each business contribute to the total number of reviews for all businesses in the city.

$ sql("SELECT name, city, review_count, SUM(review_count) OVER (PARTITION BY city) as city_review_total FROM business").show

+--------------------+--------------+------------+-----------------+

+--------------------+--------------+------------+-----------------+

|A B-Z Auto Care, ...|110. Las Vegas| 15| 15|

| Wild Rice| Allentown| 16| 19|

| Popmart| Allentown| 3| 19|

| Desert Rose Bar| Arlington| 3| 10|

| Co-Op Grill| Arlington| 7| 10|

|West Valley Pedia...| Avondale| 18| 8132|

|Dermatology for A...| Avondale| 3| 8132|

| Earnhardt Hyundai| Avondale| 33| 8132|

|Waterworks Water ...| Avondale| 5| 8132|

| Domino's Pizza| Avondale| 8| 8132|

|Universal Technic...| Avondale| 4| 8132|

|Crystal Springs A...| Avondale| 11| 8132|

| Wendy's| Avondale| 9| 8132|

| Fire Department| Avondale| 3| 8132|

| Gamestop| Avondale| 7| 8132|

|Phoenix Plasterin...| Avondale| 5| 8132|

| Nails of the World| Avondale| 14| 8132|

|St Thomas Aquinas...| Avondale| 3| 8132|

| Palermos Pizza| Avondale| 28| 8132|

| Dollar Tree| Avondale| 5| 8132|

+--------------------+--------------+------------+-----------------+

only showing top 20 rows

List Top 10 cities and their highest ranked businesses in terms of number of reviews.

$ sql("SELECT * FROM (SELECT name, city, review_count, rank() OVER (PARTITION BY city ORDER BY review_count DESC) as rank FROM business) as business_ranked WHERE rank = 1 ORDER BY review_count DESC").show

+--------------------+-----------------+------------+----+

+--------------------+-----------------+------------+----+

| Mon Ami Gabi| Las Vegas| 5642| 1|

|Phoenix Sky Harbo...| Phoenix| 1786| 1|

| Studio B| Henderson| 1629| 1|

|Four Peaks Brewin...| Tempe| 1551| 1|

| Schwartz's| Montréal| 1224| 1|

|Charlotte Douglas...| Charlotte| 1205| 1|

|Citizen Public House| Scottsdale| 1171| 1|

| Joe's Farm Grill| Gilbert| 1165| 1|

| The Old Fashioned| Madison| 1011| 1|

| Texas de Brazil| Dallas| 990| 1|

| Meat & Potatoes| Pittsburgh| 980| 1|

|SanTan Brewing Co...| Chandler| 843| 1|

|Cornish Pasty Com...| Mesa| 768| 1|

|Black Dog Smoke &...| Urbana| 597| 1|

| Yard House| Glendale| 521| 1|

| Old School Pizzeria| North Las Vegas| 396| 1|

|Rock Springs Cafe...|Black Canyon City| 371| 1|

| El Encanto| Cave Creek| 361| 1|

|Flavors of Louisiana| Avondale| 352| 1|

|Queen Creek Olive...| Queen Creek| 351| 1|

+--------------------+-----------------+------------+----+

only showing top 20 rows

Compare the number of reviews for each business with the top and bottom review counts in the city.

$ sql("SELECT name, city, review_count, FIRST_VALUE(review_count) OVER (PARTITION BY city ORDER BY review_count DESC) as city_level_top, LAST_VALUE(review_count) OVER (PARTITION BY city ORDER BY review_count DESC) as city_level_bottom FROM business").show

+--------------------+--------------+------------+--------------+-----------------+

+--------------------+--------------+------------+--------------+-----------------+

|A B-Z Auto Care, ...|110. Las Vegas| 15| 15| 15|

| Wild Rice| Allentown| 16| 16| 16|

| Popmart| Allentown| 3| 16| 3|

| Co-Op Grill| Arlington| 7| 7| 7|

| Desert Rose Bar| Arlington| 3| 7| 3|

|Flavors of Louisiana| Avondale| 352| 352| 352|

| Pita Kitchen| Avondale| 326| 352| 326|

|TJ's Homestyle Re...| Avondale| 178| 352| 178|

| Smashburger| Avondale| 158| 352| 158|

| India Garden| Avondale| 151| 352| 151|

|Don Pancho Mexica...| Avondale| 145| 352| 145|

| Claim Jumper| Avondale| 138| 352| 138|

|Thai Orchid Garde...| Avondale| 128| 352| 128|

| Greek Gyro Express| Avondale| 119| 352| 119|

|Colados Coffee & ...| Avondale| 118| 352| 118|

| Thai Gourmet| Avondale| 117| 352| 117|

| Fiesta Mexicana| Avondale| 108| 352| 108|

|Kreative Kupcakes...| Avondale| 106| 352| 106|

| NYPD Pizza| Avondale| 103| 352| 103|

| Islands Restaurant| Avondale| 102| 352| 102|

+--------------------+--------------+------------+--------------+-----------------+

only showing top 20 rows

$ sql("SELECT name, city, review_count, FIRST_VALUE(review_count) OVER (PARTITION BY city ORDER BY review_count DESC) as city_level_top, FIRST_VALUE(review_count) OVER (PARTITION BY city ORDER BY review_count ASC) as city_level_bottom FROM business").show

+--------------------+--------------+------------+--------------+-----------------+

+--------------------+--------------+------------+--------------+-----------------+

|A B-Z Auto Care, ...|110. Las Vegas| 15| 15| 15|

| Popmart| Allentown| 3| 16| 3|

| Wild Rice| Allentown| 16| 16| 3|

| Desert Rose Bar| Arlington| 3| 7| 3|

| Co-Op Grill| Arlington| 7| 7| 3|

|Dermatology for A...| Avondale| 3| 352| 3|

| Fire Department| Avondale| 3| 352| 3|

|St Thomas Aquinas...| Avondale| 3| 352| 3|

| Officemax| Avondale| 3| 352| 3|

| Chase Bank| Avondale| 3| 352| 3|

| Subway| Avondale| 3| 352| 3|

| Zymages| Avondale| 3| 352| 3|

|A & C Party Supplies| Avondale| 3| 352| 3|

|Bamboo Palace Buffet| Avondale| 3| 352| 3|

| Ann Nails| Avondale| 3| 352| 3|

|Augusta's House &...| Avondale| 3| 352| 3|

| Bank of America| Avondale| 3| 352| 3|

| T-Mobile| Avondale| 3| 352| 3|

| Wells Fargo Bank| Avondale| 3| 352| 3|

|Fry's & Fry's Mar...| Avondale| 3| 352| 3|

+--------------------+--------------+------------+--------------+-----------------+

only showing top 20 rows

Compare the number of reviews with the number of reviews for the previous and following businesses.

$ sql("SELECT name, city, review_count, LAG(review_count, 1) OVER(PARTITION BY city ORDER BY review_count DESC) AS preceding_count, LEAD(review_count, 1) OVER(PARTITION BY city ORDER BY review_count DESC) AS leading_count FROM business").show

+--------------------+--------------+------------+---------------+-------------+

+--------------------+--------------+------------+---------------+-------------+

| Pita Kitchen| Avondale| 326| 352| 178|

|TJ's Homestyle Re...| Avondale| 178| 326| 158|

| Smashburger| Avondale| 158| 178| 151|

| India Garden| Avondale| 151| 158| 145|

|Don Pancho Mexica...| Avondale| 145| 151| 138|

| Claim Jumper| Avondale| 138| 145| 128|

|Thai Orchid Garde...| Avondale| 128| 138| 119|

| Greek Gyro Express| Avondale| 119| 128| 118|

|Colados Coffee & ...| Avondale| 118| 119| 117|

| Thai Gourmet| Avondale| 117| 118| 108|

| Fiesta Mexicana| Avondale| 108| 117| 106|

|Kreative Kupcakes...| Avondale| 106| 108| 103|

| NYPD Pizza| Avondale| 103| 106| 102|

| Islands Restaurant| Avondale| 102| 103| 99|

+--------------------+--------------+------------+---------------+-------------+

only showing top 20 rows

Find correlation between number of businesses and review counts with a city

$ sql("select count(*) number_of_business, sum(review_count) counts from business group by city").stat.corr("number_of_business","counts")

res59: Double = 0.9516694713907098