title: What is the best option to generate sequence numbers in Spark code (Scala)?
body: <p>What is the best way to implement ROW_NUMBER (sequence generator) in Spark program for Billions of records (>25 Billion) ?</p>
  
  <p>Sample code:</p>
  
  <pre><code>select patient_id, 
  department_id, 
  row_number() over (partition by department_id order by dept_id asc) as Pat_serial_Nbr
  from T_patient;
  </code></pre>
  
  <p>Row_number() in Spark program is running for more than 4 hours and failing for 15 Billion records.</p>
  
  <p>I have tried RDD method zipWithIndex() for 15 Billion of records (execution took 40 mins), it is returning expected results.</p>
  
  <pre><code>public RDD&lt;scala.Tuple2&lt;T,Object&gt;&gt; zipWithIndex() 
  </code></pre>
  
  <p>Zips this RDD with its element indices. The ordering is first based on the partition index and then the ordering of items within each partition. So the first item in the first partition gets index 0, and the last item in the last partition receives the largest index. This is similar to Scala's zipWithIndex but it uses Long instead of Int as the index type. This method needs to trigger a spark job when this RDD contains more than one partitions.
  Note that some RDDs, such as those returned by groupBy(), do not guarantee order of elements in a partition. The index assigned to each element is therefore not guaranteed, and may even change if the RDD is reevaluated. If a fixed ordering is required to guarantee the same index assignments, you should sort the RDD with sortByKey() or save it to a file.</p>
  
  <pre><code>scala&gt; List("X", "Y", "Z").zipWithIndex
  res0: List[(String, Int)] = List((X,0), (Y,1), (Z,2))
  </code></pre>
  
  <p>The same works for dataframe - <code>val rdd =  df.rdd.zipWithIndex</code></p>
  
  <p>Second option:</p>
  
  <pre><code>val df_id = df.withColumn("id",monotonicallyIncreasingId)
  </code></pre>
  
  <p>Reference: <a href="https://stackoverflow.com/questions/47894877/spark-monotonically-increasing-id-not-working-as-expected-in-dataframe">Spark-Monotonically increasing id not working as expected in dataframe?</a></p>
  
  <p><strong>Could you please suggest the optimal way to generate sequence numbers in Scala Spark.</strong></p>