How can I parse this raw transactional log data in a Spark DataFrame?

Question

I need to process the Raw data with Spark 1.6 such that the first row shows up as a separate column along with rest of its corresponding rows. The first row will always have a 3 digit number followed by its corresponding rows which will always have a 8 digit number. However, the number of corresponding rows can vary. Here is a dummy example of Raw Data and desired output. How can I code this?

Raw Data

765
11111111
22222222
33333333
456
66666666
88888888

Desired output

765 11111111
765 22222222
765 33333333
456 66666666
456 88888888

http://stackoverflow.com/questions/36052480/how-to-read-multiple-line-elements-in-spark ? — the.malkolm, Jul 13 '16 at 00:44
Your best bet is probably to create a custom hadoop input format. Probably similar to NLineInputFormat as @the.malkolm suggested, but I don't think that will work if your number of rows is variable. You'll likely need to roll your own. — Hamel Kothari, Jul 13 '16 at 02:05
I am working on a solution, unless someone else replies I will post it in few hours. — marios, Jul 13 '16 at 16:34
@marios Great. If you happen to have a Spark Python implementation as well, that'll be awesome! — user2895779, Jul 13 '16 at 18:41

score 0 · Answer 1 · answered Jul 14 '16 at 05:50

The idea is to try to solve this problem per partition. This works great as long as each partition starts with a 3-digit number. If a partition doesn't have a 3-digit number we need to find the last 3-digit number of the previous partition.

First we need some utility functions:

The first (last3dig) finds the last 3-digit number of each partition. This will help us to have an initial 3-digit number for those partitions that don't start with one. If we apply this to each partition we get a list of such 3-digit numbers. Each element is the last 3-digit of its corresponding partition if it exists, else None.
The second (fillGaps) takes care of properly filling the gaps of the list of last 3-digits (from previous step). So if we have Some(1), None, None, Some(4) it will make it Some(1), Some(1), Some(1), Some(4).
The third (trasnformRow) will go over the initial RDD and use the utilities functions we created to populate the resulting RDD.

// Finds the last 3-digit number in a Stream (RDD partition)
def last3dig(x: Stream[String]): Option[String] = {
    def help(y: Stream[String], sofar: Option[String]): Option[String] = {
      y match {
        case h #:: tl => if(h.length == 3) help(tl, Some(h)) else help(tl, sofar)
        case Stream.Empty => sofar
      }
    }
    help(x, None)
  }

 def fillGaps(data: Vector[(Int, Option[String])] ): Vector[(Int, Option[String])] =     
    data.foldLeft(Vector.empty[(Int,Option[String])]){ 
       case (col,n) => if(n._2.isEmpty) (n._1, col.head._2) +: col else n +: col
 }

def trasnformRow(x: Stream[String], filler: String): Stream[String] = x match {
     case h #:: tl =>              
        if( h.length != 3 ) 
           s"$h $filler" #:: trasnformRow(tl, filler) 
        else 
            trasnformRow(tl, h) // update the filler
     case Stream.Empty => x
 }




// toy data
val d = Seq(765, 11111111, 22222222, 33333333, 456, 66666666, 88888888).map(_.toString)
val rdd = sc.makeRDD(d,2)

val mappings = rdd.mapPartitionsWithIndex { 
    case(i,iter) => Iterator( (i, last3dig(iter.toStream)) ) 
}.collect().toVector

val filledMappings = fillGaps(mappings)

val mm = sc.broadcast(filledMappings.toMap)

val finalResult = rdd.mapPartitionsWithIndex { 
   case (i,iter) => trasnformRow(iter.toStream, if(i>0) mm.value(i-1).get else "").toIterator 
}.collect() // remove collect() for large dataset

Thanks a lot for your help! Do you also happen to have a Python implementation that you can share? — user2895779, Jul 14 '16 at 14:53

How can I parse this raw transactional log data in a Spark DataFrame?

1 Answers1