How do I get a SQL row_number equivalent for a Spark RDD?

by

The row_number() over (partition by ... order by ...) functionality was added to Spark 1.4. This answer uses PySpark/DataFrames.

Create a test DataFrame:

from pyspark.sql import Row, functions as F

testDF = sc.parallelize(
    (Row(k="key1", v=(1,2,3)),
     Row(k="key1", v=(1,4,7)),
     Row(k="key1", v=(2,2,3)),
     Row(k="key2", v=(5,5,5)),
     Row(k="key2", v=(5,5,9)),
     Row(k="key2", v=(7,5,5))
    )
).toDF()

Add the partitioned row number:

from pyspark.sql.window import Window

(testDF
 .select("k", "v",
         F.rowNumber()
         .over(Window
               .partitionBy("k")
               .orderBy("k")
              )
         .alias("rowNum")
        )
 .show()
)

+----+-------+------+
|   k|      v|rowNum|
+----+-------+------+
|key1|[1,2,3]|     1|
|key1|[1,4,7]|     2|
|key1|[2,2,3]|     3|
|key2|[5,5,5]|     1|
|key2|[5,5,9]|     2|
|key2|[7,5,5]|     3|
+----+-------+------+

More Related Contents:

  1. How to select the first row of each group?
  2. Querying Spark SQL DataFrame with complex types
  3. Concatenate columns in Apache Spark DataFrame
  4. Spark Window Functions – rangeBetween dates
  5. Spark SQL window function with complex condition
  6. Does SparkSQL support subquery?
  7. Including null values in an Apache Spark Join
  8. Spark sql queries vs dataframe functions
  9. Explode (transpose?) multiple columns in Spark SQL table
  10. Equivalent of Oracle’s RowID in SQL Server
  11. How do I use ROW_NUMBER()?
  12. SPARK SQL – case when then
  13. How to get other columns when using Spark DataFrame groupby?
  14. get TopN of all groups after group by using Spark DataFrame
  15. Why using a UDF in a SQL query leads to cartesian product?
  16. How to measure the execution time of a query on Spark
  17. How to aggregate over rolling time window with groups in Spark
  18. What is the difference between cube, rollup and groupBy operators?
  19. Oracle ‘Partition By’ and ‘Row_Number’ keyword
  20. Sequentially number rows by keyed group in SQL?
  21. Difference between === null and isNull in Spark DataDrame
  22. Solutions for INSERT OR UPDATE on SQL Server
  23. ORDER BY the IN value list
  24. How to update selected rows with values from a CSV file in Postgres?
  25. PL/pgSQL checking if a row exists
  26. Combine two tables for one output
  27. sql unique constraint on a 2 columns combination
  28. Finding node order in XML document in SQL Server
  29. Concurrency handling of Sql transactrion
  30. Postgresql query between date ranges

Leave a Comment