将 JSON 文件读入 Spark DataFrame

Question

我正在尝试将 json 文件读入 Spark 数据帧，但我将整个文件作为一行和一列获取，我试图将其拆分为多列：

df = spark.read.json(sc.wholeTextFiles("HW2-DataSets/docs/output-0.json").values())
df.show()

这是我运行这行代码得到的输出：

+--------------------+
|              Images|
+--------------------+
|[[ricky4, 1634180...|
+--------------------+

这是已创建的数据帧的架构：

    root
 |-- Images: struct (nullable = true)
 |    |-- Z4ah9SemQjX2cKN187pX: struct (nullable = true)
 |    |    |-- artist: string (nullable = true)
 |    |    |-- created_at: long (nullable = true)
 |    |    |-- description: string (nullable = true)
 |    |    |-- download_url: string (nullable = true)
 |    |    |-- file_name: string (nullable = true)
 |    |    |-- id: string (nullable = true)
 |    |    |-- key_words: array (nullable = true)
 |    |    |    |-- element: string (containsNull = true)
 |    |    |-- source: string (nullable = true)
 |    |    |-- tagged: boolean (nullable = true)
 |    |    |-- title: string (nullable = true)
 |    |-- Z552dVXF5vp80bAajYrn: struct (nullable = true)
 |    |    |-- artist: string (nullable = true)
 |    |    |-- created_at: long (nullable = true)
 |    |    |-- description: string (nullable = true)
 |    |    |-- download_url: string (nullable = true)
 |    |    |-- file_name: string (nullable = true)
 |    |    |-- id: string (nullable = true)
 |    |    |-- key_words: array (nullable = true)
 |    |    |    |-- element: string (containsNull = true)
 |    |    |-- source: string (nullable = true)
 |    |    |-- tagged: boolean (nullable = true)
 |    |    |-- title: string (nullable = true)
 |    |-- Z598cIDb79GPrC6VXbTb: struct (nullable = true)
           ....

我的目标是将图像中的每个对象放在一行和分隔的列中，这意味着例如“Z4ah9SemQjX2cKN187pX”，其值为：艺术家，created_at…在第一行，“Z552dVXF5vp80bAajYrn”在第二行等。

我很陌生，可以激发每条评论都会有所帮助，谢谢。

使现代化

{
  "Images": {
    "Z4ah9SemQjX2cKN187pX": {
      "artist": "ricky4",
      "file_name": "mermaid_by_ricky4_d59jfzw-fullview.jpg",
      "created_at": 1634180503921,
      "description": "Hi folks! This is my latest work! So glad to be a part of the calendar project from my friend Dee  More details here! :thumb322291755: artistsforacure.deviantart.com… Inspired by: CLOCK DVA - Return to blue www.youtube.com/watch?v=rjfxI2…All resources: my own! Other purple artworks",
      "key_words": [
        "dark",
        "digital",
        "evil",
        "fantasy",
        "fishes",
        "lady",
        "light",
        "magic",
        "mermaid",
        "ocean",
        "sea",
        "utopia",
        "reflections"
      ],
      "source": "https://www.deviantart.com/",
      "title": "Mermaid",
      "tagged": false,
      "download_url": "",
      "id": "Z4ah9SemQjX2cKN187pX"
    },
    "Z552dVXF5vp80bAajYrn": {

Answer 1

首先，您可以使用多行选项读取json文件，并将其作为数据帧变量中的单个列获取，然后可以使用columnname在数据帧变量上使用select语句。*它将为您提供一个包含一行的数据帧，其中每一行都有一个列，列的值与json中的值相同。

代码如下：

#read the json file
df = spark.read.option("multiline","true").json("HW2-DataSets/docs/output-0.json")
#used the select statement to see the output you want
from pyspark.sql.functions import *
df1 = df.select(col("Images.*"))
#do df1.show() to see the output
# I am using Databricks so I do display(df1)

#Convert Columns to rows (Updated)
from itertools import chain

m = create_map(list(chain(*(
        (lit(c), col(c)) for c in df1.columns))))

df2 = df1.withColumn('map', m)\
  .select(explode('map')).drop('map')

您可以看到如下输出: