How do I achieve this in Apache Spark Java or Scala?

Question

A device on a car will NOT send a TRIP ID when the trip starts but will send one when the TRIP ends. How do I apply corresponding TRIP IDS to the corresponding records

09:30,25,DEVICE_1
10:30,55,DEVICE_1
10:25,0,DEVICE_1,TRIP_ID_0
11:30,45,DEVICE_1
10:30,55,DEVICE_2
10:30,55,DEVICE_3
11:30,45,DEVICE_3
12:30,0,DEVICE_3,TRIP_ID_3
10:30,55,DEVICE_4
11:30,45,DEVICE_4
11:30,45,DEVICE_2
12:30,0,DEVICE_2,TRIP_ID_2
12:30,0,DEVICE_4,TRIP_ID_4
10:30,55,DEVICE_5
11:30,45,DEVICE_5
12:30,0,DEVICE_5,TRIP_ID_5
12:30,0,DEVICE_1,TRIP_ID_1

So the above becomes like this,

09:30,25,DEVICE_1,TRIP_ID_0
10:25,0,DEVICE_1,TRIP_ID_0
10:30,55,DEVICE_1,TRIP_ID_1
11:30,45,DEVICE_1,TRIP_ID_1
12:30,0,DEVICE_1,TRIP_ID_1
10:30,55,DEVICE_2,TRIP_ID_2
11:30,45,DEVICE_2,TRIP_ID_2
12:30,0,DEVICE_2,TRIP_ID_2
10:30,55,DEVICE_3,TRIP_ID_3
11:30,45,DEVICE_3,TRIP_ID_3
12:30,0,DEVICE_3,TRIP_ID_3
10:30,55,DEVICE_4,TRIP_ID_4
11:30,45,DEVICE_4,TRIP_ID_4
12:30,0,DEVICE_4,TRIP_ID_4
10:30,55,DEVICE_5,TRIP_ID_5
11:30,45,DEVICE_5,TRIP_ID_5
12:30,0,DEVICE_5,TRIP_ID_5

is there a technical term for this kind of sql approach ? like retrospective id'ing or something like that ? — Vinodh Thiagarajan, Aug 01 '18 at 02:27

thebluephantom · Answer 1 · 2018-08-02T00:48:56.167

An interesting problem. Had to fix one bug!

You will need to convert to spark.sql as I tried this in ORACLE. But WITH clause is supported in spark.sql. Also, instead of using date strings, due to the fact it is quite late I just used numbers to represent time, so you will need to look at that.

But here is the SQL that you can adapt.

with X as (select device, time_asc, trip_id from trips where trip_id is not null)
select Y.TRIP_ID, Y.DEVICE, Y.TIME_ASC FROM (
select T1.TIME_ASC, T1.DEVICE, X.TRIP_ID, X.TIME_ASC AS TIME_ASC_COMPARE
      ,RANK() OVER (PARTITION BY T1.TIME_ASC, T1.DEVICE ORDER BY X.TIME_ASC) AS RANK_VAL       from trips T1, X
 where T1.DEVICE = X.DEVICE
   and T1.TIME_ASC <= X.TIME_ASC) Y
 where RANK_VAL = 1
 order by TRIP_ID, TIME_ASC

Get rid of the order by, just used to show.

This data as input:

 ('1','A',null);
 ('2','A','TRIP_01');
 ('5','A',null);
 ('6','A',null);
 ('7','A',null);
 ('23','A','TRIP_02');
 ('56','A',null);
 ('60','A','TRIP_04');
 ('8','B',null);
 ('10','B','TRIP_03');
 ('1','E',null);
 ('2','E','TRIP_05');

removes quotes as I exported and got this format, returns the following, which I think will meet your needs - again excuse formatting:

 ('TRIP_01','A','1');
 ('TRIP_01','A','2');
 ('TRIP_02','A','5');
 ('TRIP_02','A','6');
 ('TRIP_02','A','7');
 ('TRIP_02','A','23');
 ('TRIP_03','B','8');
 ('TRIP_03','B','10');
 ('TRIP_04','A','56');
 ('TRIP_04','A','60');
 ('TRIP_05','E','1');
 ('TRIP_05','E','2');

Am wondering how well SPARK handles this with under the hood performance. This took some effort late at night, so some appreciation is sought. Enjoyable as well.

JOIN syntax may need to be explicit, I tend to use in IMPALA and SPARK ... LEFT JOIN ... etc. — thebluephantom, Aug 02 '18 at 08:05
@VinodhThiagarajan : Can you state how you went on this? I am using it as a test case for performance of DFs. — thebluephantom, Aug 04 '18 at 16:41
@blue - I explained the complexity ( which was unnecessary for the domain I'm working o )n and fixed the issue w.r.t to data-source. I really loved your solution but I haven't used it as my req changed. Let me go upvote the solution. — Vinodh Thiagarajan, Aug 14 '18 at 17:35
success, but the answer is correct in the context of the question asked ... — thebluephantom, Aug 14 '18 at 19:06

How do I achieve this in Apache Spark Java or Scala?

1 Answers1