从 CSV 文件中拆分 JSON 值并根据 Spark/Scala 中的 json 键创建新列

问题描述

CSV 文件中的数据如下所示.想要从 Desc 列中拆分 JSON 并使用键创建一个新列.在 Scala 中使用 spark 2.

Have data in CSV file below is the format. Want to split JSON from Desc column and create a new column with key.Using spark 2 with Scala.

+------+------------+----------------------------------+
|  id  |  Category  |           Desc                   |
+------+------------+----------------------------------+
|  201 |  MIS20     | { "Total": 200,"Defective": 21 } |
+------+-----------------------------------------------+
|  202 |  MIS30     | { "Total": 740,"Defective": 58 } |
+------+-----------------------------------------------+

输出:

So the desired output would be:

+------+------------+---------+-------------+
|  id  |  Category  |  Total  |  Defective  |
+------+------------+---------+-------------+
|  201 |  MIS20     |  200    |   21        |
+------+----------------------+-------------+
|  202 |  MIS30     |  740    |   58        |
+------+------------------------------------+

非常感谢任何帮助.

推荐答案

为您的内部 json 创建一个 schema 并使用 from_json 应用该模式> 功能如下

Create a schema for your inner json and apply that schema with from_json function as below

val schema = new StructType()
  .add(StructField("Total", LongType, false)).
  add("Defective", LongType, false)

d.select($"id",$"Category", from_json($"Desc", schema).as("desc"))
  .select($"id",$"Category", $"desc.*")
  .show(false)

输出:

+---+--------+-----+---------+
|id |Category|Total|Defective|
+---+--------+-----+---------+
|201|MIS20   |200  |21       |
|202|MIS30   |740  |58       |
+---+--------+-----+---------+

希望这会有所帮助！

这篇关于从 CSV 文件中拆分 JSON 值并根据 Spark/Scala 中的 json 键创建新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！