sparkDataframe比较并仅显示不同的值

pxq42qpu 于 2021-05-27 发布在 Spark

关注(0)|答案(1)|浏览(366)

我有两个Dataframe进行比较，并使用除了显示在第一个数据集中的数据和在第二个数据集中丢失的数据。它的工作很好，我想只显示不同的值，而不是整个行，以便有人很容易识别有差异的字段。
下面是代码片段

val spark: SparkSession = SparkSession.builder().master("local[*]").appName("Test6").getOrCreate();

  val schemaOrig = List( StructField("key",StringType,true)
    ,StructField("name",StringType,true)
    ,StructField("start_ts",TimestampType,true)
    ,StructField("txn_dt",StringType,true))

  val df =  spark.createDataFrame(spark.sparkContext.parallelize(Seq(Row("1","john",java.sql.Timestamp.valueOf("2018-10-16 00:00:00"),"2020-02-14")))
    ,StructType(schemaOrig))

  val df2 =  spark.createDataFrame(spark.sparkContext.parallelize(Seq(Row("1","andrew",java.sql.Timestamp.valueOf("2017-10-16 00:00:00"),"2020-02-14")))
    ,StructType(schemaOrig))

  df.except(df2).show(true)

+---+----+-------------------+----------+
|key|name|           start_ts|    txn_dt|
+---+----+-------------------+----------+
|  1|john| 2018-10-16 00:00:00 2020-02-14                 |
+---+----+-------------------+----------+

预期产量

+---+-------------+--------------------+
|key|diff columns |     diff values 
+---+----------------------------------+
 1   name,txn_dt      john,2018-10-16 00:00:00

DataFrame apache-spark apache-spark-sql apache-spark-dataset

来源：https://stackoverflow.com/questions/61716786/spark-data-frame-compare-and-show-only-values-that-are-different

1条答案

按热度按时间

3npbholx1#

使用 full outer join &正在提取不匹配的列。
请检查下面的代码。

scala> dfa.printSchema
root
 |-- key: string (nullable = true)
 |-- name: string (nullable = true)
 |-- start_ts: timestamp (nullable = true)
 |-- txn_dt: string (nullable = true)

scala> dfa.show(false)
+---+----+-------------------+----------+
|key|name|start_ts           |txn_dt    |
+---+----+-------------------+----------+
|1  |john|2018-10-16 00:00:00|2020-02-14|
+---+----+-------------------+----------+

scala> dfb.printSchema
root
 |-- key: string (nullable = true)
 |-- name: string (nullable = true)
 |-- start_ts: timestamp (nullable = true)
 |-- txn_dt: string (nullable = true)

scala> dfb.show(false)
+---+------+-------------------+----------+
|key|name  |start_ts           |txn_dt    |
+---+------+-------------------+----------+
|1  |andrew|2017-10-16 00:00:00|2020-02-14|
+---+------+-------------------+----------+

scala> val diff_cols = dfa.columns.filterNot(_ == "key").map(c => when(dfa(c) =!= dfb(c),c))
diff_cols: Array[org.apache.spark.sql.Column] = Array(CASE WHEN (NOT (name = name)) THEN name END, CASE WHEN (NOT (start_ts = start_ts)) THEN start_ts END, CASE WHEN (NOT (txn_dt = txn_dt)) THEN txn_dt END)

scala> val diff_values = dfa.columns.filterNot(_ == "key").map(c => when(dfa(c) =!= dfb(c),dfa(c)))
diff_values: Array[org.apache.spark.sql.Column] = Array(CASE WHEN (NOT (name = name)) THEN name END, CASE WHEN (NOT (start_ts = start_ts)) THEN start_ts END, CASE WHEN (NOT (txn_dt = txn_dt)) THEN txn_dt END)

scala> dfa.join(dfb,dfa("key") === dfb("key"),"full").select(dfa("key"),concat_ws(",",diff_cols:_*).as("diff_columns"),concat_ws(",",diff_values:_*).as("diff_values")).show(false) // using full join & taking diff columns & values.
+---+-------------+------------------------+
|key|diff_columns |diff_values             |
+---+-------------+------------------------+
|1  |name,start_ts|john,2018-10-16 00:00:00|
+---+-------------+------------------------+

scala>

赞(0）回复(0）举报 2021-05-27

我来回答

sparkDataframe比较并仅显示不同的值

1条答案

相关问题

热门标签

最新问答