从 Scala 中的嵌套 json 文件创建一个 spark 数据框 [英] create a spark dataframe from a nested json file in scala

问题描述

我有一个看起来像这样的 json 文件

I have a json file that looks like this

{
"group" : {},
"lang" : [ 
    [ 1, "scala", "functional" ], 
    [ 2, "java","object" ], 
    [ 3, "py","interpreted" ]
]
}

我尝试使用

val path = "some/path/to/jsonFile.json"
val df = sqlContext.read.json(path)
df.show()

当我运行这个时，我得到

when I run this I get

df: org.apache.spark.sql.DataFrame = [_corrupt_record: string]

我们如何根据lang"键的内容创建一个df?我不关心组{}，我只需要从lang"中提取数据并像这样应用案例类

How do we create a df based on contents of "lang" key? I do not care about group{} all I need is, pull data out of "lang" and apply case class like this

case class ProgLang (id: Int, lang: String, type: String )

我已阅读这篇文章使用 Apache Spark 读取 JSON - `corrupt_record` 并了解每条记录都需要换行，但就我而言，我无法更改文件结构

I have read this post Reading JSON with Apache Spark - `corrupt_record` and understand that each record needs to be on a newline but in my case I cannot change the file structure

推荐答案

json 格式错误.sqlContext 的 json api 正在读取它作为损坏的记录.正确的形式是

The json format is wrong. The the json api of sqlContext is reading it as corrupt record. Correct form is

{"group":{},"lang":[[1,"scala","functional"],[2,"java","object"],[3,"py","interpreted"]]}

假设你在一个文件(/home/test.json")中有它，那么你可以使用下面的方法来获取你想要的dataframe

and supposing you have it in a file ("/home/test.json"), then you can use following method to get the dataframe you want

import org.apache.spark.sql.functions._
import sqlContext.implicits._

val df = sqlContext.read.json("/home/test.json")

val df2 = df.withColumn("lang", explode($"lang"))
    .withColumn("id", $"lang"(0))
    .withColumn("langs", $"lang"(1))
    .withColumn("type", $"lang"(2))
    .drop("lang")
    .withColumnRenamed("langs", "lang")
    .show(false)

你应该有

+---+-----+-----------+
|id |lang |type       |
+---+-----+-----------+
|1  |scala|functional |
|2  |java |object     |
|3  |py   |interpreted|
+---+-----+-----------+

更新

如果你不想改变你在下面评论中提到的输入 json 格式，你可以使用 wholeTextFiles 来读取 json 文件和 parse如下

If you don't want to change your input json format as mentioned in your comment below, you can use wholeTextFiles to read the json file and parse it as below

import sqlContext.implicits._
import org.apache.spark.sql.functions._

val readJSON = sc.wholeTextFiles("/home/test.json")
  .map(x => x._2)
  .map(data => data.replaceAll("\n", ""))

val df = sqlContext.read.json(readJSON)

val df2 = df.withColumn("lang", explode($"lang"))
  .withColumn("id", $"lang"(0).cast(IntegerType))
  .withColumn("langs", $"lang"(1))
  .withColumn("type", $"lang"(2))
  .drop("lang")
  .withColumnRenamed("langs", "lang")

df2.show(false)
df2.printSchema

它应该给你 dataframe 如上和 schema 作为

It should give you dataframe as above and schema as

root
 |-- id: integer (nullable = true)
 |-- lang: string (nullable = true)
 |-- type: string (nullable = true)

这篇关于从 Scala 中的嵌套 json 文件创建一个 spark 数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！