Transforming two dataframes in spark sql

Question

I am having two dataframes in spark scala registered as tables. From these two tables

Table 1:

   +-----+--------+
   |id   |values  |
   +-----+-----   +
   |   0 |  v1    |
   |   0 |  v2    |
   |   1 |  v3    |
   |   1 |  v1    |
   +-----+-----   +

Table 2:

   +-----+----+--- +----+
   |id   |v1  |v2  | v3
   +-----+-------- +----+
   |   0 |  a1|  b1| -  |
   |   1 |  a2|  - | c2 |

   +-----+---------+----+

I want to generate a new table using the above two tables.

Table 3:

   +-----+--------+--------+
   |id   |values  | field  |
   +-----+--------+--------+
   |   0 |  v1    | a1     |
   |   0 |  v2    | b1     |
   |   1 |  v3    | c2     |
   |   1 |  v1    | a2     |
   +-----+--------+--------+

Here v1 is of the form

 v1: struct (nullable = true)
    |    |-- level1: string (nullable = true)
    |    |-- level2: string (nullable = true)
    |    |-- level3: string (nullable = true)
    |    |-- level4: string (nullable = true)
    |    |-- level5: string (nullable = true)

I am using spark sql in scala .

Is it possible to do the desired thing by writing some sql query or using some spark functions on dataframes.

I was trying INSERT INTO table1 (field), VALUES (SELECT column_name FROM table2 where table1.id = table2.id ) But for this I require column_name dynamically from table1 — satyambansal117, Nov 09 '16 at 08:32
The number of column names are not known beforehand and they are of same number as there are distinct values in column2 — satyambansal117, Nov 09 '16 at 08:54
Like @eliasah said, I would like to see some own effort at solving this. — maasg, Nov 10 '16 at 12:19

score 1 · Accepted Answer · answered Nov 09 '16 at 19:23

Here is the sample code that you can use , that will generate this output :

The code looks like this :

val df1=sc.parallelize(Seq((0,"v1"),(0,"v2"),(1,"v3"),(1,"v1"))).toDF("id","values")
val df2=sc.parallelize(Seq((0,"a1","b1","-"),(1,"a2","-","b2"))).toDF("id","v1","v2","v3")
val joinedDF=df1.join(df2,"id")
val resultDF=joinedDF.rdd.map{row=>
val id=row.getAs[Int]("id")
val values=row.getAs[String]("values")
val feilds=row.getAs[String](values)
(id,values,feilds)
}.toDF("id","values","feilds")

While testing on Console:

scala> val df1=sc.parallelize(Seq((0,"v1"),(0,"v2"),(1,"v3"),(1,"v1"))).toDF("id","values")
df1: org.apache.spark.sql.DataFrame = [id: int, values: string]

scala> df1.show
+---+------+
| id|values|
+---+------+
|  0|    v1|
|  0|    v2|
|  1|    v3|
|  1|    v1|
+---+------+


scala> val df2=sc.parallelize(Seq((0,"a1","b1","-"),(1,"a2","-","b2"))).toDF("id","v1","v2","v3")
df2: org.apache.spark.sql.DataFrame = [id: int, v1: string ... 2 more fields]

scala> df2.show
+---+---+---+---+
| id| v1| v2| v3|
+---+---+---+---+
|  0| a1| b1|  -|
|  1| a2|  -| b2|
+---+---+---+---+


scala> val joinedDF=df1.join(df2,"id")
joinedDF: org.apache.spark.sql.DataFrame = [id: int, values: string ... 3 more fields]

scala> joinedDF.show
+---+------+---+---+---+                                                        
| id|values| v1| v2| v3|
+---+------+---+---+---+
|  1|    v3| a2|  -| b2|
|  1|    v1| a2|  -| b2|
|  0|    v1| a1| b1|  -|
|  0|    v2| a1| b1|  -|
+---+------+---+---+---+


scala> val resultDF=joinedDF.rdd.map{row=>
     | val id=row.getAs[Int]("id")
     | val values=row.getAs[String]("values")
     | val feilds=row.getAs[String](values)
     | (id,values,feilds)
     | }.toDF("id","values","feilds")
resultDF: org.apache.spark.sql.DataFrame = [id: int, values: string ... 1 more field]

scala> 

scala> resultDF.show
+---+------+------+                                                             
| id|values|feilds|
+---+------+------+
|  1|    v3|    b2|
|  1|    v1|    a2|
|  0|    v1|    a1|
|  0|    v2|    b1|
+---+------+------+

I hope this might your problem. Thanks!

If my values are of type struct in place of string then what will be the change for that — satyambansal117, Nov 10 '16 at 07:18
Then while extracting use Struct instead of string and then transform it accordingly. — Shivansh, Nov 10 '16 at 07:28
Thanks for the help , Here v1,v2,v3 are of type struct with subfields as level1:string,level2:string .Now when I try getAs[Struct] (values) it says Struct type is not found ....How to do this — satyambansal117, Nov 10 '16 at 07:44
look into this : http://stackoverflow.com/questions/37553059/extracting-seqstring-string-string-from-spark-dataframe — Shivansh, Nov 10 '16 at 07:52

Transforming two dataframes in spark sql

1 Answers1

Linked