問題描述

我在 spark scala 中有兩個數據框注冊為表.從這兩個表

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

表 1:

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

表 2:

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

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

我想用上面兩個表生成一個新表.

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

表 3:

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

這里 v1 的形式是

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)

我在 scala 中使用 spark sql.

I am using spark sql in scala .

是否可以通過在數據幀上編寫一些 sql 查詢或使用一些 spark 函數來完成所需的操作.

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

推薦答案

這是您可以使用的示例代碼，它將生成此輸出:

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

代碼如下:

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")

在控制臺上測試時:

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!

這篇關于在spark sql中轉換兩個數據幀的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！