spark sql window function lag

Question

I am looking at the window slide function for a Spark DataFrame in Scala.

I have a DataFrame with columns Col1, Col2, Col3, date, volume and new_col.

Col1    Col2    Col3    date     volume new_col
                        201601  100.5   
                        201602  120.6   100.5
                        201603  450.2   120.6
                        201604  200.7   450.2
                        201605  121.4   200.7`

Now I want to add a new column with name(new_col) with one row slided down, as shown above.

I tried below option to use the window function.

val windSldBrdrxNrx_df = df.withColumn("Prev_brand_rx", lag("Prev_brand_rx",1))

Do you have any suggestion ?

@Ramesh till Spark 2.0, users had to use `HiveContext` instead of `SQLContext` to apply window functions. `HiveContext` is created in the same way as `SQLContext` by passing an instance of `SparkContext`. If I remember correctly, you also need you include `org.apache.spark:spark-hive_2.10` with an appropriate version for your Spark distribution. — Anton Okolnychyi, Dec 15 '16 at 08:12

mrsrinivas · Accepted Answer · 2021-09-11T16:22:44.470

50

You are doing correctly all you missed is over(window expression) on lag

val df = sc.parallelize(Seq((201601, 100.5),
  (201602, 120.6),
  (201603, 450.2),
  (201604, 200.7),
  (201605, 121.4))).toDF("date", "volume")

val w = org.apache.spark.sql.expressions.Window.orderBy("date")  

import org.apache.spark.sql.functions.lag

val leadDf = df.withColumn("new_col", lag("volume", 1, 0).over(w))

leadDf.show()

+------+------+-------+
|  date|volume|new_col|
+------+------+-------+
|201601| 100.5|    0.0|
|201602| 120.6|  100.5|
|201603| 450.2|  120.6|
|201604| 200.7|  450.2|
|201605| 121.4|  200.7|
+------+------+-------+

This code was run on Spark shell 2.0.2

edited Sep 11 '21 at 16:22

answered Dec 15 '16 at 08:16

mrsrinivas

34,112
13
125
125

I don't have 1.5.2 setup and facing issue with maven to load 1.5.2 (spark-hive jar) in my machine. – mrsrinivas Dec 15 '16 at 18:58
I am able to create the hive context now. But still i get the same error. – Ramesh Dec 15 '16 at 19:07
i think since the dataframe is created using the sqlcontext i still cannot use the window function. – Ramesh Dec 15 '16 at 19:07

score 3 · Answer 2 · answered Sep 19 '17 at 14:34

3

You can import below two packages, which will resolve the issue of lag dependencies.

import org.apache.spark.sql.functions.{lead, lag}
import org.apache.spark.sql.expressions.Window

answered Sep 19 '17 at 14:34

Sampat Kumar

492
1
6
14

spark sql window function lag

2 Answers2

Linked