Question

Source: https://stackoverflow.com/questions/70470272/spark-sql-find-the-number-of-extensions-for-a-record

I have a dataset as follows:

I need to find the number of extensions available for each record in col1whereby records are sorted already and contiguously in terms of sets which are terminated by a null.

The final result will be like this:

value 2345 extends as2345>2246>2134>2091>nulland hence it has 3 extension relations excluding null.

How to get the 3rd column(No_Of_Extensions) using spark sql/scala?

Answer

For this scenario where the data table’s first column is already ordered, create a new group each time when value of the second column in the previous record is null, and in each group, add a number column according to the specific requirements. It is a great hassle to try to achieve the process in SQL. You need to first create row numbers and the marker column as needed, and perform the grouping according to the marker column and row numbers. A common alternative is to fetch the original data out of the database and process it in Python or SPL. SPL, the open-source Java package, is easier to be integrated into a Java program and generate much simpler code. It expresses the algorithm with only two lines of code:

View SPL source code.