1

I have the following Dataframe:

   ---------+--------+----------+-----------+--------------------+--------------------+-------+-----+------------
|     id|groupid||              field|   oldstring|    newstring|               created|        pkey|   project     
+-------+-------+---------+--------------------+--------+-------------+--------+-------------+-------+-------+
|1451923| 594128|               Team1|    [RLA N1]|   [N1-UO-SB]|  2013-03-29 13:31:...|DSTECH-55770|   10120|
|1451958| 594140|               Team1|    [SEP N2]|     [SEP N2]|  2013-03-29 13:34:...|DSTECH-56998|   10120|   
|1452282| 594308|               Team1|  [N1-UO-SE]|     [SEP N2]|  2013-03-29 14:09:...|DSTECH-57900|   10120|    
|1492252| 610736|               Team1|  [N1-UO-SE]|     [SEP N2]|  2013-04-17 08:48:...|DSTECH-59560|   10120|
|5105082|2304145|               Team1|     [Aucun]|[SEP-SUPPORT]|  2017-09-01 09:46:...|    ECO-9781|   10280|
|5105084|2304145|               Team2|        null|  SEP-SUPPORT|  2017-09-01 09:46:...|    ECO-9781|   10280|
|5105084|2304145|               Team1|    [ISR N2]| SEP-SUPPORT | 2013-03-29 13:31:... |DSTECH-57895|   10120|
|1451926|594129 |               Team1|  [N1-UO-SE]|   [ISR N2]  |2013-03-29 13:55:...  |DSTECH-57895|   10120|
|1452182|594273 |               Team1|  [N1-UO-SE]| [SEPN1-ENV] |2013-03-29 13:43:...  |DSTECH-57895|   10120|

I would like compute the treatment date/time of the [pkey] For exemple, I have these two lines:

|     id|groupid||              field|   oldstring|    newstring|               created|        pkey|        
+-------+-------+---------+--------------------+--------+-------------+--------+-------------+-------+-------+
|1451923| 594128|               Team1|    [RLA N1]|   [N1-UO-SB]|  2013-03-29 13:31:...|DSTECH-55770|   
|1451958| 594140|               Team1|    [SEP N2]|     [SEP N2]|  2013-03-29 13:34:...|DSTECH-56998|

The treatment date/time of [DSTECH-55770] = [2013-03-29 13:34:...] - [2013-03-29 13:31:...]

How can I to compute this difference with the previous date, I found that I can do it using User Defined Aggregating Functions UDAF. But I do not if this solution is useful to display the difference between two dates in a number (for example: 8h:30min), I do not mean by 8H is at clock 8H but number of hours is 8.

If someone can help me how can I do it using UDAF or if you have another solution ? Thank you

sirine
  • 517
  • 3
  • 7
  • 17
  • Did you think to use jodatime ? – alifirat Jun 11 '18 at 14:00
  • I don't think in this case because, I would do the difference between two lines – sirine Jun 11 '18 at 14:58
  • 1
    Do you need to do this for every successive pair of values in the table? – Mike Allen Jun 11 '18 at 15:09
  • Yes in the case when I have the same project number. If you notice I have two fields oldstring and newstring. I would like to compute the treatment time of each newstring according the project number (should be the same). – sirine Jun 11 '18 at 15:27

1 Answers1

1

Might be a case for SQL window functions. You can find more details here

I suspect the resulting code might look something like

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._

val sparkSession = ...  // Create as do
import sparkSession.implicits._
// For the same project, order rows by `created` column
val partitionWindow = Window.partitionBy("project").orderBy("created".asc)
// Get me the value of `created` column in next row in a new column called datediff
val createdTimeNextRowSameProject = lead($"created",
                                         1,                   // 1 = next_row, 2 = 2 rows after, so on 
                                         "CURRENT_TIMESTAMP"  // default if next is null
                                        ).over(partitionWindow)
val dfWithTimeDiffInSeconds = df.withColumn("datediff", unix_timestamp(leadDate) - unix_timestamp($"created"))
dfWithTimeDiffInSeconds.show(10)
rmathews7
  • 175
  • 8