Add sequence number to every row in a dataframe - Spark Scala

Question

I need to add sequence number to each row I am processing in a dataframe. But everytime when I add, we need to get the max of sequence from the existing rows and add + 1 and assign it to new row.

Any idea How we can achieve this with dataframe in spark scala.

Example.

below is the existing data in a table:

row_id,emp_id, sal
1,11,2000
2,22,3000

Now I need to add new row as follows to the table:

3,33,5000

we need to get row id every time when we are inserting new data to the table by getting max(row_id) from the table and add +1 to it.

Please suggest any ideas.

Thanks,

Minato · Accepted Answer · 2018-03-27 05:06:31Z

1

Spark DataFrames are immutable so it is not possible to append / insert rows. Instead use union. Here's a quick solution to your problem. This is not a good solution since you need to perform union every time a new row is added.

val data = spark
  .read
  .option("inferSchema", "true")
  .option("header", "true")
  .csv("data.csv")

data.createOrReplaceTempView("dView")
val sqld = spark.sql("SELECT MAX(row_id)+1,SUM(emp_id),SUM(sal) FROM dView")
val finalD = data.union(sqld)
finalD.show()
spark.stop()

data.csv

row_id,emp_id, sal
1,11,2000
2,22,3000

Output:

+------+------+----+
|row_id|emp_id| sal|
+------+------+----+
|     1|    11|2000|
|     2|    22|3000|
|     3|    33|5000|
+------+------+----+

answered Mar 27, 2018 at 5:06

Minato

4625 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bab Over a year ago

Thank you for your response Binoy J. Suppose if I have a dataframe with 50 updated records and 20 Insert(new records). All update records will have row_id and insert records will not have the value in it. I need to take max(row_id) from the updated records and add 1 to it and add this value to insert records sequentially. Can this be implemented for the above senario..

Collectives™ on Stack Overflow

Add sequence number to every row in a dataframe - Spark Scala

below is the existing data in a table:

Now I need to add new row as follows to the table:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

below is the existing data in a table:

Now I need to add new row as follows to the table:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related