Get all possible combinations of 3 values from n possible elements

This approach seems to work, but may incur considerable overhead at the query execution level. Assuming n is a fairly small number, we can do this directly using the Scala implementation:

val localItems = items.collect
val combinations = localItems.combinations(3)

The result is iteratorthat one element can be consumed at a time without significant memory overhead.

Spark version (edited)

Given the desire to make this version of Spark, the query planner can be avoided (assuming there is a problem) by dropping down to the RDD level. This is basically the same as the join in the question:

val items = sqlContext.table(SOURCE_DB + "." + SOURCE_TABLE).select("item_id").rdd

val combinations = items.cartesian(items).filter{case(x,y) => x<y}.cartesian(items).filter{case ((x,y),z) => y<z}

Run the equivalent code on your local machine:

val data = List.fill(1000)(scala.util.Random.nextInt(999))
val rdd = sparkContext.parallelize(data)
val combinations = rdd.cartesian(rdd).filter{case(x,y) => x<y}.cartesian(rdd).filter{case ((x,y),z) => y<z}

combinations.count
// res5: Long = 165623528