Spark DataFrame - group rows based on difference of timestamp using R

Question

I have a spark DataFrame like this:

timestamp            userId
2016-07-26 12:05:00   a
2016-07-26 12:05:01   b
2016-07-26 12:05:02   c
2016-07-26 12:05:03   d
2016-07-26 12:05:04   e
2016-07-26 12:05:05   f

I want to group the rows that are within 5 sec difference in one group, like:

timestamp            userId   group
2016-07-26 12:05:00   a        1  
2016-07-26 12:05:01   b        1
2016-07-26 12:05:02   c        1
2016-07-26 12:05:03   d        1
2016-07-26 12:05:04   e        1
2016-07-26 12:05:05   f        2

Is there a way to do this without converting the spark DataFrame into R dataframe?

Since you want to process it in R, you will need to use a suitable data structure in R, isn't that so? — Sun Bee, Sep 15 '16 at 11:31
You will need to use Window functions, but these have only been implemented with Spark 2.0 for SparkR. — mtoto, Sep 15 '16 at 13:15
@mtoto You [can use window functions in 1.6](http://stackoverflow.com/q/34885981/1560062) just fine. But you cannot easily apply these to these problem to get exact solution and decent performance. — zero323, Sep 15 '16 at 13:58

score 0 · Answer 1 · answered Sep 19 '16 at 18:02

This particular functionality is commonly known as Sessionization and frequently used by web analysts to identify sessions for a particular user. There are built in UDFs in hive which can be used with sparksqlcontext. For example, https://docs.treasuredata.com/articles/udfs

Spark DataFrame - group rows based on difference of timestamp using R

1 Answers1