create a column that group values

Question

To resume i want to put into the same group values that are associated:

Here is what i have :

col1    col2
1        2
1        3
2        3
4        5
5        6

and I want this :

col1    col2    group
1        2        1
1        3        1
2        3        1
4        5        2
5        6        2

To produce those two groups here are the steps if i do it manually.

• row 1 : 1 is associated to 2 so they are on the same group let's call it group 1
• row 2 : 1 is on group 1 and now 1 is associated to 3 so 3 is also on group 1
• row 3 : 2 is on the group 1 and 3 is also on the group 1 so they are in the group 1
• row 4 : 4 is not a value of the group 1 so i create a new group called 2 and i associate it with 5
• row 5 : 5 has a group 2 and is associated to 6 so it has group 2.

Do you have an idea of to resolve this in SQL. Knowing that i am using Hive or pyspark

Answer 1

Based on A.R.Ferguson answer i was able to figure out the solution using pyspark and graphframe:

from graphframes import *
vertices = sqlContext.createDataFrame([
  ("A",  1),
  ("B",  2),
  ("C",  3),
  ("D",  4),
  ("E",  5),
  ("F",  6)], ["name",  "id"])
edges = sqlContext.createDataFrame([
  (1, 2),
  (1, 3),
  (2, 3),
  (4, 5),
  (5, 6)], ["src", "dst"])
g = GraphFrame(vertices, edges)
result = g.connectedComponents()
result.show()

Thanks again Ferguson.